If you are here, then you have a SQL that is not using an index. You have already:
Indexed Table Joins are useful when we have identified a small number of rows in one table, and we want to join them to a small number of rows in a second table using an index on the second table to identify matching rows.
Consider the following SQL
SELECT b.* FROM order a, order_line b WHERE a.cust_id = 1244 AND a.order_id = b.order_id
Let's assume the following:
The best result for this SQL is to read ORDER retrieving rows where CUST_ID = 1244 (an index on CUST_ID will help, but is not necessary for the join), then for each row use an index to lookup ORDER_LINE on each ORDER_ID.
So what happens if Oracle doesn't do it this way? Some possibilities are:
If statistics on the tables are up to date and we are determined to use the index on ORDER_LINE, then we must do three things to counteract the Cost Based Optimizer:
SELECT /*+ ORDERED USE_NL(b) INDEX(b)*/ b.* FROM order a, order_line b WHERE a.cust_id = 1244 AND a.order_id = b.order_id
Check the execution plan with Explain Plan.
Is it using the index now? If not, then the index is somehow unusable - see your DBA. Even if it is now using the index, the Cost Based Optimiser thinks there is a better way. Do it the courtesy of comparing the SQL with and without hints.
Consider the same example above, except this time
order_line is hash clustered on
order_id. This can be quite a sensible approach, as it ensures that all lines for an order will be stored in the same database block. If the hash cluster is well designed, then it can be more efficient to join to
order_line using a hash cluster scan rather than an index scan.
The process for forcing a hash cluster scan on one table in the join is almost identical to the process described above for Indexed Joins, except the HASH hint is used in place of the INDEX hint.