Oracle SQL ignoring ordered index hint -
i have following query specified ordered index hint. wondering whether i'm doing wrong makes ignore 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 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 payr.attribute2 = 'n' -- non-flexi aka monthly , trunc(sysdate) between payr.effective_start_date , payr.effective_end_date , ptp.payroll_id = payr.payroll_id , trunc(sysdate) between ptp.start_date , ptp.end_date , ppa.time_period_id = ptp.time_period_id , ppa.action_type = 'r' , ppa.action_status = 'c' , pcs.consolidation_set_id = ppa.consolidation_set_id , pcs.consolidation_set_name = 'main run' , paa.payroll_action_id = ppa.payroll_action_id , paa.action_status = 'c' , paaf.assignment_id = paa.assignment_id , paaf.effective_start_date = ( select max(paaf2.effective_start_date) per_all_assignments_f paaf2 paaf2.assignment_id = paaf.assignment_id , paaf2.effective_start_date <= ptp.end_date ) , papf.person_id = paaf.person_id , paaf.effective_start_date between papf.effective_start_date , papf.effective_end_date , ppos.period_of_service_id = paaf.period_of_service_id , not exists ( select * pay_element_types_f petf, pay_input_values_f pivf, pay_run_result_values prrv, pay_run_results prr petf.attribute1 = '401' , trunc(sysdate) between petf.effective_start_date , petf.effective_end_date , pivf.element_type_id = petf.element_type_id , pivf.name = 'pay value' , trunc(sysdate) between pivf.effective_start_date , pivf.effective_end_date , prrv.input_value_id = pivf.input_value_id , prr.run_result_id = prrv.run_result_id , prr.assignment_action_id = paa.assignment_action_id ) , exists ( select * pay_run_results prr2 prr2.assignment_action_id = paa.assignment_action_id ) the explain plan 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 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 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 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 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 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 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 index rowid table hr.pay_element_types_f cost: 2 bytes: 23 cardinality: 1 33 table access 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 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 index rowid table hr.per_periods_of_service cost: 1 bytes: 13 cardinality: 1 i go pay_run_results table pay_assignment_actions table, instead goes there first , full table scan on it; in total disregard of hint , horrible performance. know why or how fix this?
you need reference query block name in index hint, if index in inline view or subquery. can use automatically generated query block name or create 1 qb_name hint.
sample schema , 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 pay_assignment_actions select level, level dual connect level <= 100000; insert pay_run_results select level, level dual connect 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 alias format find queryblock name
explain plan select * pay_assignment_actions paa exists ( select * pay_run_results prr prr.assignment_action_id = paa.assignment_action_id --to stop default plan using index , some_other_column = 5 ); select * 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 operation id): ------------------------------------------------------------- 1 - sel$5da710d3 2 - sel$5da710d3 / prr@sel$2 3 - sel$5da710d3 / paa@sel$1 use auto-generated query block name force index
explain plan select /*+ index(@sel$2 prr) */ * pay_assignment_actions paa exists ( select * pay_run_results prr prr.assignment_action_id = paa.assignment_action_id --to stop default plan using index , some_other_column = 5 ); select * 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 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 make own query block name
explain plan select /*+ index(@my_subquery prr) */ * pay_assignment_actions paa exists ( select /*+ qb_name(my_subquery) */ * pay_run_results prr prr.assignment_action_id = paa.assignment_action_id --to stop default plan using index , some_other_column = 5 ); select * 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 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 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 referenced full object alias, this:
explain plan select /*+ index(@sel$2 prr) use_nl(prr@sel$2 paa@sel$1) */ * pay_assignment_actions paa exists ( select * pay_run_results prr prr.assignment_action_id = paa.assignment_action_id --to stop default plan using index , some_other_column = 5 ); select * 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 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 | --------------------------------------------------------------------------------------------------------
Comments
Post a Comment