Most Popular Performance Tuning

Using PIVOT in Oracle 11g and Above

As of Oracle Database 11g, you can use the PIVOT and UNPIVOT operators to work with “crosstab” data. In a crosstab report, rows of data are displayed in separate columns. In this query, the second and third columns are the Fred Fuller columns, the fourth and fifth are the Dorah Talbot columns, and so on. […]

Read More

How to Create an Index-Organized Table in Oracle 12c

An index-organized table keeps its data sorted according to the primary key column values for the table. An index-organized table stores its data as if the entire table was stored in an index. Indexes serve two main purposes:  To enforce uniqueness   When a PRIMARY KEY or UNIQUE constraint is created, Oracle creates an index to enforce […]

Read More

Tuning for Oracle 12c Cloud Control, Part 1

Tuning, to a degree, is similar to maintenance. In order to be effective, the effort needs to be continuous. Occasionally you’ll encounter the silver bullet, but these occasions are few and far between. Effective tuning requires routine care involving collecting baselines, removing superfluous tasks, monitoring metrics, modifying parameters or objects, and testing the results. In […]

Read More

Using the USER Pseudo-Column in Oracle Views

The USER pseudo-column is quite useful when you are using remote data access methods. For example, you may not want all remote users to see all rows in a table. To solve this problem, you must think of remote users as special users within your database. To enforce the data restriction, you need to create […]

Read More

Tuning Oracle Data Loads for SQL*Loader

In addition to running the data load processes at off-peak hours, you can take other steps to improve the load performance. The following steps all impact your overall database environment and must be coordinated with the database administrator. The tuning of a data load should not be allowed to have a negative impact on the […]

Read More

Using Synonyms for Location Transparency in Oracle

Over an application’s lifespan, its data very likely will move from one database to another or from one host to another. Therefore, it will simplify application maintenance if the exact physical location of a database object is shielded from the user (and the application). The best way to implement such location transparency is through the […]

Read More

Using an Oracle Database Link for Remote Updates

The database link syntax for remote updates is the same as that for remote queries. Append the name of the database link to the name of the table being updated. For example, to change the RATING values for books in a remote BOOKSHELF table, you would execute the UPDATE command shown in the following listing: […]

Read More