Oracle SQL ignoring ordered index hint
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?
Solution
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 |
--------------------------------------------------------------------------------------------------------
- Query to find position in a string before keyword and extract data before that position
- SQL: get next relative day of week. (Next Monday, Tuesday, Wed.....)
- #1273 – Unknown collation: ‘utf8mb4_unicode_520_ci’
- java.sql.SQLException: Column Index out of range, 2 > 1
- Should LIKE 'searchstr%' use an index?
- Multiple STRING_AGG in one query
- Postgres: Add constraint if it doesn't already exist
- UPDATE Statement ignores INNER JOIN
- Execute Stored Procedure Minimal API .Net
- Change default schema for user
- Oracle SQL - Select oldest of 3 dates, but ignore NULLs
- Housing Society management system database structure
- Keep getting error: PLS-00103: Encountered the symbol "CREATE"
- How to use an UPDATE Query with an INNER JOIN to update fields within a table
- Extract 4 million rows from database in chunks of 1 million rows each
- Aggregate into custom groups
- SQL query for chat application
- SQL basic problem when trying to SELECT 4 atributes and use GROUP BY with only 2
- How to modify query converting DATEDIFF to Oracle accepted syntax?
- Sales data analysis: calculate percentage of each product as per total sales
- Calculate the sum of values for the columns that meet the criteria
- Insert missing rows in time series consumption history
- Oracle database dependencies in PL/SQL
- How to autofill a field with data from another field in the same table?
- In Oracle: How to sort by group/cluster by a column value
- oracle SQL query - add date rows using different date
- How to fix products without cover image in PrestaShop
- IF ROW_COUNT() return error
- Select with like is not returning the correct results
- I am trying to get the start date ONLY from the column below using a SUBSTRING. Can someone tell me what I'm doing wrong?