Databases Reference
In-Depth Information
realizes that it might end up with wrong results because there may be some rows that don't appear in the
index. To avoid this, Oracle Database simply ignores the
INDEX
hint you provide.
There is a way to get around this inability of the optimizer to honor your index hint. You do so by
adding more hints to the query! You must endeavor to specify a full set of hints to make sure that the
optimizer has no choice but to use the index. The additional hints that you specify control things such as
the precise join order between the tables and the exact join methods as well. The following example
illustrates this:
SQL> select /*+ leading(e2 e1) use_nl(e1) index(e1 emp_emp_id_pk)
use_merge(j) full(j) */
e1.first_name, e1.last_name, j.job_id, sum(e2.salary) total_sal
from employees e1, employees e2, job_history j
where e1.employee_id = e2.manager_id
and e1.employee_id = j.employee_id
and e1.hire_date = j.start_date
group by e1.first_name, e1.last_name, j.job_id
order by total_sal;
And here's the execution plan for the previous statement:
FIRST_NAME LAST_NAME JOB_ID TOTAL_SAL
-------------------- ------------------------- ---------- ----------
Michael Hartstein MK_REP 6000
Lex De Haan IT_PROG 9000
Execution Plan
----------------------------------------------------------
Plan hash value: 4097587549
--------------------------------------------------------------------------------------
| Id| Operation |Name |Rows|Bytes|Cost(%CPU)|Time |
--------------------------------------------------------------------------------------
| 0|SELECT STATEMENT | | 105| 5880|117 (5) |00:00:04|
| 1| SORT ORDER BY | | 105| 5880|117 (5) |00:00:04|
| 2| HASH GROUP BY | | 105| 5880|117 (5) |00:00:04|
| 3| MERGE JOIN | | 105| 5880|115 (3) |00:00:03|
| 4| SORT JOIN | | 105| 3675|111 (2) |00:00:03|
| 5| NESTED LOOPS | | | | | |
| 6| NESTED LOOPS | | 105| 3675|110 (1) |00:00:03|
| 7| VIEW |index$_join$_002| 107| 856| 3 (34) |00:00:01|
|* 8| HASH JOIN | | | | | |
| 9| INDEX FAST FULL SCAN |EMP_MANAGER_IX | 107| 856| 1 (0) |00:00:01|
| 10|INDEX FAST FULL SCAN |EMPLOYEES_IDX1 | 107| 856| 1 (0) |00:00:01|
|*11| INDEX UNIQUE SCAN |EMP_EMP_ID_PK | 1| | 0 (0) |00:00:01|
| 12| TABLE ACCESS BY INDEX ROWID|EMPLOYEES | 1| 27| (0) |00:00:01|
|*13| SORT JOIN | | 10| 210| 4 (25) |00:00:01|
| 14| TABLE ACCESS FULL |JOB_HISTORY | 10| 210| 3 (0) |00:00:01|
---------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
8 - access(ROWID=ROWID)
11 - access("E1"."EMPLOYEE_ID"="E2"."MANAGER_ID")
13 - access("E1"."EMPLOYEE_ID"="J"."EMPLOYEE_ID" AND "E1"."HIRE_DATE"="J"."STA
