In this article I will discuss some great tips that will help you optimize a poorly performing join query or a new application which uses join queries.

All Join Columns and/or Row Selection columns must have Indexes:

Indexes should be available over all of the join columns and row selection columns or both. The optimizer can provide index advice in several places to aid in this process.

Indexes on join columns and/or row selection columns give query optimizer a better opportunity to select an efficient access method as it can determine the average number of duplicate values. Many queries may be able to use the existing index to implement the query and avoid the cost of creating a temporary index or hash table.

Selectivity of each dial:

The query optimizer does not add predicates for predicates connected by OR or non-isolatable predicates, or predicate operators of LIKE. However modifying the query by adding these predicates can be helpful. The query should be checked to see whether some complex predicates should be added to other dials to allow the optimizer to get a better idea of the selectivity of each dial.

ALWCPYDTA(*YES):

Use ALWCPYDTA(*YES) if the query is creating a temporary index or hash table. In such case the processing time may be better if the optimizer only used the existing index or hash table.

ALWCPYDTA(*OPTIMIZE):

Use ALWCPYDTA(*OPTIMIZE) if the query is not creating a temporary index or hash table. In such case processing time may be better if a temporary index is created.

ALWCPYDTA(*OPTIMIZE) allow the query optimizer to use a sort routine. This allows the query optimizer to consider all possible join orders when ordering is specified and all key columns are from a single dial.

Specify join predicates:

Specifying join predicates prevents all the rows from one table from being joined to every row in the other table. This improves performance by reducing the join fan-out. Every secondary table should have at least one join predicate that references on of its columns as a 'join-to' column.

OPTIMIZE FOR n ROWS:

You can specify the OPTIMIZE FOR n ROWS to inform the optimizer of the application has intention to read every resulting row. For this you can set n to a large number or you can also set n to a small number before ending the query.

OPNQRYF:

Use *FIRSTIO, if you want the optimizer to optimize the query to retrieve the first block of rows most efficiently. However this biases the optimizer toward using existing objects.

Use *ALLIO if you want to optimize the retrieval time for the entire answer set. This may cause the optimizer to create temporary objects such as temporary indexes or hash tables in order to minimize I/O.

Star join queries:

A star join joins one table with all secondary tables consecutively. In the case of a star join where all secondary join predicates contain a column reference to a particular table, there may be performance advantages if that table is placed in join position one.

Above example joins all tables to table EMPLOYEE. The query optimizer can freely determine the join order.

The join type such as an inner join can have no default values returned. The table can be forced into the first position to avoid random I/O processing. If EMPLOYEE is not in join position one, every row in EMPLOYEE can be examined repeatedly during the join process. If EMPLOYEE is fairly large then random I/O processing occurs that result in poor performance. However by forcing EMPLOYEE to the first position, random I/O processing is minimized.

 More Oracle Articles, Database Articles and DBA Tips
   Database Security: Step by step guideline
   Effectively Prevent Oracle Minimum Downtime Migration Error!!
   Oracle SQL Resource Usage, Some Concerns
   Great Tips for Growing Listener Log Files!!
   Web Applications: Oracle-SQL Database Security