Oracle SQL ignoring ordered index hint

Question

I have the following query with the specified ordered index hint. I am wondering whether I'm doing something wrong that makes it ignore my ordered index hint.

select /*+ ORDERED USE_NL (ptp payr) INDEX (ptp, PER_TIME_PERIODS_N50) INDEX (ppa, PAY_PAYROLL_ACTIONS_FK8) USE_NL (ppa ptp) USE_NL (ppa pcs) USE_NL (paa ppa) INDEX (prr2, PAY_RUN_RESULTS_N50) USE_NL (prr2 paa) INDEX (prr, PAY_RUN_RESULTS_N50) USE_NL (prr paa) */ 
       papf.employee_number, 
       papf.full_name, 
       ppos.date_start
from   pay_all_payrolls_f     payr,
       per_time_periods       ptp,
       pay_payroll_actions    ppa,
       pay_consolidation_sets pcs,
       pay_assignment_actions paa,
       per_all_assignments_f  paaf,
       per_all_people_f       papf,
       per_periods_of_service ppos
where  payr.attribute2 = 'N'  -- Non-Flexi aka Monthly
and    trunc(sysdate) between payr.effective_start_date and payr.effective_end_date
and    ptp.payroll_id = payr.payroll_id
and    trunc(sysdate) between ptp.start_date and ptp.end_date
and    ppa.time_period_id = ptp.time_period_id
and    ppa.action_type = 'R'
and    ppa.action_status = 'C'  
and    pcs.consolidation_set_id = ppa.consolidation_set_id
and    pcs.consolidation_set_name = 'Main Run' 
and    paa.payroll_action_id = ppa.payroll_action_id
and    paa.action_status = 'C'
and    paaf.assignment_id = paa.assignment_id
and    paaf.effective_start_date = 
(
   select max(paaf2.effective_start_date)
   from   per_all_assignments_f paaf2
   where  paaf2.assignment_id = paaf.assignment_id
   and    paaf2.effective_start_date <= ptp.end_date
)
and    papf.person_id = paaf.person_id
and    paaf.effective_start_date between papf.effective_start_date and papf.effective_end_date
and    ppos.period_of_service_id = paaf.period_of_service_id
and    not exists
(
   select *
   from   pay_element_types_f    petf,
          pay_input_values_f     pivf,
          pay_run_result_values  prrv,
          pay_run_results        prr
   where  petf.attribute1 = '401'  
   and    trunc(sysdate) between petf.effective_start_date and petf.effective_end_date
   and    pivf.element_type_id = petf.element_type_id
   and    pivf.name = 'Pay Value'
   and    trunc(sysdate) between pivf.effective_start_date and pivf.effective_end_date
   and    prrv.input_value_id = pivf.input_value_id
   and    prr.run_result_id = prrv.run_result_id
   and    prr.assignment_action_id = paa.assignment_action_id
)
and    exists
(
   select *
   from   pay_run_results prr2
   where  prr2.assignment_action_id = paa.assignment_action_id
)

The explain plan is as follows:

Plan
SELECT STATEMENT  ALL_ROWSCost: 10 304 693  Bytes: 113 685  Cardinality: 583                                                        
    41 NESTED LOOPS                                                     
        39 NESTED LOOPS  Cost: 10 278 570  Bytes: 195  Cardinality: 1                                               
            37 NESTED LOOPS  Cost: 10 278 569  Bytes: 182  Cardinality: 1                                           
                34 NESTED LOOPS  Cost: 10 278 566  Bytes: 128  Cardinality: 1                                       
                    28 NESTED LOOPS  Cost: 10 278 552  Bytes: 742  Cardinality: 7                                   
                        13 NESTED LOOPS  Cost: 9 961 317  Bytes: 13 947 120  Cardinality: 158 490                               
                            10 NESTED LOOPS  Cost: 8 836 765  Bytes: 76 406 568  Cardinality: 1 123 626                             
                                7 NESTED LOOPS  Cost: 4 635 168  Bytes: 3 927 462 339  Cardinality: 74 103 063                          
                                    4 NESTED LOOPS  Cost: 890 613  Bytes: 52 268 552  Cardinality: 1 866 734                    
                                        2 SORT UNIQUE  Cost: 10 691  Bytes: 35 938 560  Cardinality: 5 989 760                  
                                            1 TABLE ACCESS FULL TABLE HR.PAY_RUN_RESULTS Cost: 10 691  Bytes: 35 938 560  Cardinality: 5 989 760            
                                        3 TABLE ACCESS FULL TABLE HR.PAY_ALL_PAYROLLS_F Cost: 4  Bytes: 176  Cardinality: 8                 
                                    6 TABLE ACCESS BY INDEX ROWID TABLE HR.PER_TIME_PERIODS Cost: 2  Bytes: 1 000  Cardinality: 40                      
                                        5 INDEX RANGE SCAN INDEX HR.PER_TIME_PERIODS_N50 Cost: 1  Cardinality: 40               
                                9 TABLE ACCESS BY INDEX ROWID TABLE HR.PAY_PAYROLL_ACTIONS Cost: 2  Bytes: 15  Cardinality: 1                       
                                    8 INDEX RANGE SCAN INDEX HR.PAY_PAYROLL_ACTIONS_FK8 Cost: 0  Cardinality: 1                     
                            12 TABLE ACCESS BY INDEX ROWID TABLE HR.PAY_CONSOLIDATION_SETS Cost: 1  Bytes: 20  Cardinality: 1                           
                                11 INDEX UNIQUE SCAN INDEX (UNIQUE) HR.PAY_CONSOLIDATION_SETS_PK Cost: 0  Cardinality: 1                        
                        27 TABLE ACCESS BY INDEX ROWID TABLE HR.PAY_ASSIGNMENT_ACTIONS Cost: 2  Bytes: 18  Cardinality: 1                               
                            26 INDEX UNIQUE SCAN INDEX (UNIQUE) HR.PAY_ASSIGNMENT_ACTIONS_PK Cost: 1  Cardinality: 1                            
                                25 NESTED LOOPS                         
                                    23 NESTED LOOPS  Cost: 88  Bytes: 83  Cardinality: 1                    
                                        21 HASH JOIN  Cost: 86  Bytes: 60  Cardinality: 1               
                                            19 NESTED LOOPS             
                                                17 NESTED LOOPS  Cost: 68  Bytes: 3 102  Cardinality: 141       
                                                    15 TABLE ACCESS BY INDEX ROWID TABLE HR.PAY_RUN_RESULTS Cost: 6  Bytes: 300  Cardinality: 25    
                                                        14 INDEX RANGE SCAN INDEX HR.PAY_RUN_RESULTS_N50 Cost: 3  Cardinality: 25  
                                                    16 INDEX RANGE SCAN INDEX HR.PAY_RUN_RESULT_VALUES_N50 Cost: 2  Cardinality: 6      
                                                18 TABLE ACCESS BY INDEX ROWID TABLE HR.PAY_RUN_RESULT_VALUES Cost: 3  Bytes: 60  Cardinality: 6        
                                            20 TABLE ACCESS FULL TABLE HR.PAY_INPUT_VALUES_F Cost: 17  Bytes: 152  Cardinality: 4           
                                        22 INDEX RANGE SCAN INDEX (UNIQUE) HR.PAY_ELEMENT_TYPES_F_PK Cost: 1  Cardinality: 1                
                                    24 TABLE ACCESS BY INDEX ROWID TABLE HR.PAY_ELEMENT_TYPES_F Cost: 2  Bytes: 23  Cardinality: 1                      
                    33 TABLE ACCESS BY INDEX ROWID TABLE HR.PER_ALL_ASSIGNMENTS_F Cost: 2  Bytes: 22  Cardinality: 1                                    
                        32 INDEX RANGE SCAN INDEX (UNIQUE) HR.PER_ASSIGNMENTS_F_PK Cost: 1  Cardinality: 1                                  
                            31 SORT AGGREGATE  Bytes: 13  Cardinality: 1                            
                                30 FIRST ROW  Cost: 2  Bytes: 13  Cardinality: 1                        
                                    29 INDEX RANGE SCAN (MIN/MAX) INDEX (UNIQUE) HR.PER_ASSIGNMENTS_F_PK Cost: 2  Bytes: 13  Cardinality: 1                     
                36 TABLE ACCESS BY INDEX ROWID TABLE HR.PER_ALL_PEOPLE_F Cost: 3  Bytes: 54  Cardinality: 1                                         
                    35 INDEX RANGE SCAN INDEX (UNIQUE) HR.PER_PEOPLE_F_PK Cost: 1  Cardinality: 1                                   
            38 INDEX UNIQUE SCAN INDEX (UNIQUE) HR.PER_PERIODS_OF_SERVICE_PK Cost: 0  Cardinality: 1                                            
        40 TABLE ACCESS BY INDEX ROWID TABLE HR.PER_PERIODS_OF_SERVICE Cost: 1  Bytes: 13  Cardinality: 1                                   

I would like it to go to the pay_run_results table from the pay_assignment_actions table, but instead it goes there first and does a full table scan on it; in total disregard of my hint and with horrible performance. Anybody know why or how to fix this?

Answer 1

You need to reference the query block name in your index hint, if that index is in an inline view or subquery. You can use the automatically generated query block name or create one yourself with the qb_name hint.

Sample schema and data

create table pay_assignment_actions
(
    assignment_action_id number,
    some_other_column number
);

create table pay_run_results
(
    assignment_action_id number,
    some_other_column number
);
create index PAY_RUN_RESULTS_N50 on pay_run_results(assignment_action_id);

insert into pay_assignment_actions
    select level, level from dual connect by level <= 100000;
insert into pay_run_results
    select level, level from dual connect by level <= 100000;
begin
    dbms_stats.gather_table_stats(user, 'pay_assignment_actions');
    dbms_stats.gather_table_stats(user, 'pay_run_results');
end;
/

Default plan: no indexes, use the alias format to find the queryblock name

explain plan for
select *
from pay_assignment_actions paa
where exists
(
    select *
    from pay_run_results prr
    where prr.assignment_action_id = paa.assignment_action_id
        --to stop the default plan from using an index
        and some_other_column = 5
);

select * from table(dbms_xplan.display(format => 'alias -predicate'));

Plan hash value: 1799975253

-----------------------------------------------------------------------------------------------
| Id  | Operation            | Name                   | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |                        |     1 |    20 |   139   (3)| 00:00:01 |
|   1 |  HASH JOIN RIGHT SEMI|                        |     1 |    20 |   139   (3)| 00:00:01 |
|   2 |   TABLE ACCESS FULL  | PAY_RUN_RESULTS        |     1 |    10 |    69   (2)| 00:00:01 |
|   3 |   TABLE ACCESS FULL  | PAY_ASSIGNMENT_ACTIONS |   100K|   976K|    69   (2)| 00:00:01 |
-----------------------------------------------------------------------------------------------

Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------

   1 - SEL$5DA710D3
   2 - SEL$5DA710D3 / PRR@SEL$2
   3 - SEL$5DA710D3 / PAA@SEL$1

Use auto-generated query block name to force index

explain plan for
select /*+ index(@SEL$2 prr) */ *
from pay_assignment_actions paa
where exists
(
    select *
    from pay_run_results prr
    where prr.assignment_action_id = paa.assignment_action_id
        --to stop the default plan from using an index
        and some_other_column = 5
);

select * from table(dbms_xplan.display(format => '-predicate'));

Plan hash value: 1805560573

-------------------------------------------------------------------------------------------------------
| Id  | Operation                    | Name                   | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |                        |     1 |    20 |   476   (1)| 00:00:01 |
|   1 |  HASH JOIN RIGHT SEMI        |                        |     1 |    20 |   476   (1)| 00:00:01 |
|   2 |   TABLE ACCESS BY INDEX ROWID| PAY_RUN_RESULTS        |     1 |    10 |   406   (1)| 00:00:01 |
|   3 |    INDEX FULL SCAN           | PAY_RUN_RESULTS_N50    |   100K|       |   201   (1)| 00:00:01 |
|   4 |   TABLE ACCESS FULL          | PAY_ASSIGNMENT_ACTIONS |   100K|   976K|    69   (2)| 00:00:01 |
-------------------------------------------------------------------------------------------------------

Use QB_NAME to make your own query block name

explain plan for
select /*+ index(@my_subquery prr) */ *
from pay_assignment_actions paa
where exists
(
    select /*+ qb_name(my_subquery) */ *
    from pay_run_results prr
    where prr.assignment_action_id = paa.assignment_action_id
        --to stop the default plan from using an index
        and some_other_column = 5
);

select * from table(dbms_xplan.display(format => 'alias -predicate'));

Plan hash value: 1805560573

-------------------------------------------------------------------------------------------------------
| Id  | Operation                    | Name                   | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |                        |     1 |    20 |   476   (1)| 00:00:01 |
|   1 |  HASH JOIN RIGHT SEMI        |                        |     1 |    20 |   476   (1)| 00:00:01 |
|   2 |   TABLE ACCESS BY INDEX ROWID| PAY_RUN_RESULTS        |     1 |    10 |   406   (1)| 00:00:01 |
|   3 |    INDEX FULL SCAN           | PAY_RUN_RESULTS_N50    |   100K|       |   201   (1)| 00:00:01 |
|   4 |   TABLE ACCESS FULL          | PAY_ASSIGNMENT_ACTIONS |   100K|   976K|    69   (2)| 00:00:01 |
-------------------------------------------------------------------------------------------------------

Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------

   1 - SEL$08BA3066
   2 - SEL$08BA3066 / PRR@MY_SUBQUERY
   3 - SEL$08BA3066 / PRR@MY_SUBQUERY
   4 - SEL$08BA3066 / PAA@SEL$1

USE_NL when objects span query blocks

Objects in different query blocks can be referenced by their full object alias, like this:

explain plan for
select /*+ index(@SEL$2 prr) use_nl(prr@sel$2 paa@sel$1) */ *
from pay_assignment_actions paa
where exists
(
    select *
    from pay_run_results prr
    where prr.assignment_action_id = paa.assignment_action_id
        --to stop the default plan from using an index
        and some_other_column = 5
);

select * from table(dbms_xplan.display(format => '-predicate'));

Plan hash value: 2326545440

--------------------------------------------------------------------------------------------------------
| Id  | Operation                     | Name                   | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |                        |     1 |    20 |   476   (1)| 00:00:06 |
|   1 |  NESTED LOOPS                 |                        |     1 |    20 |   476   (1)| 00:00:06 |
|   2 |   SORT UNIQUE                 |                        |     1 |    10 |   406   (1)| 00:00:05 |
|   3 |    TABLE ACCESS BY INDEX ROWID| PAY_RUN_RESULTS        |     1 |    10 |   406   (1)| 00:00:05 |
|   4 |     INDEX FULL SCAN           | PAY_RUN_RESULTS_N50    |   100K|       |   201   (1)| 00:00:03 |
|   5 |   TABLE ACCESS FULL           | PAY_ASSIGNMENT_ACTIONS |     1 |    10 |    69   (2)| 00:00:01 |
--------------------------------------------------------------------------------------------------------