Nested Loop - Influencing the Join Path

Example

select COMPANY.Name 
from COMPANY, SALES
where COMPANY.Company_ID = SALES.Company_ID
and SALES.Period_ID =3
and SALES.Sales_Total>1000;

Execution Plan

NESTED LOOPS
TABLE ACCESS FULL SALES
TABLE ACCESS BY ROWID COMPANY
INDEX UNIQUE SCAN COMPANY_PK

Influencing

To modify the join path of a NESTED LOOPS join, you can either use hints or you can manually modify the FROM clause and WHERE conditions in the query. Hints related to the join path of NESTED LOOPS joins include:

  • ORDERED - Join the tables based on their order in the FROM clause.
  • INDEX - List specific indexes to use.
  • FULL TABLE SCAN List a specific table for a full table scan - this table may serve as a driving table for the query.
  • USE NESTED LOOPS - List tables to join using NESTED LOOPS joins

You can modify the WHERE clause of a query to nullify the use of indexes on specific columns. By nullifying indexes, you can influence which tables will be more likely to have full table scans during the query - and thus more likely to be driving tables for the query.

The optimizer will use an index if the indexed columns are used in a limiting condition. If an indexed column is used within a function, such as UPPER, then an index on that column would not be used by the optimizer. When you want to force the optimizer to ignore an index, use the following conventions:

  • For numeric or date columns, add zero to the value. For example, replace
  where Company_ID = 12345

with

  where Company_ID+0 = 12345

Adding 0 to the Company_ID value does not change the value, but it prevents the optimizer from using the index on the Company_ID column during the query.

  • For character columns, concatenate an empty string (‘ ’) to the value. For example, replace:
  where City = ‘ROANOKE’

with

  where City||’’ = ‘ROANOKE’

Adding the empty string to the City value does not change the value, but it prevents the optimizer from using the index on the City column during the query.

You need to establish and follow a consistent syntax for nullifying indexes in commonly used queries. Whenever you see a query that has been modified using this syntax, you will know that the query was modified for performance improvements, and the goal was to nullify the index rather than to indicate a significant business rule.


Powered by ComboStrap