'How do I use a noindex hint in the outermost query of a nested query?
I have a query structure where I've got 3 nested queries (e.g. select * from (select * from (select * from table ) a ) b
There's an index in trying to avoid, because it was causing long run times. It appeared twice in the plan. I used a noindex hint on the innermost query to remove one instance of it, but I can't seem to get rid of the second instance.
Is there a way to use noindex on the outer queries of a nested query?
Edit: More detailed query example. Note, in the real query there are GROUP BY statements on every level, but a WHERE statement only in the innermost query.
Select /*+ parallel(8) no_index(b,bad_index) */
b.col1
b.col2
b.col3
FROM
(Select /*+ parallel(8) no_index(a,bad_index) */
a.col1
a.col2
a.col3
FROM
(SELECT /*+ parallel(8) no_index(t,bad_index) */
t.col1
t.col2
t.col3
FROM table_1 t
INNER JOIN table_2 t2
ON t.col1=t2.col1
WHERE
t.col2='value' ) a
) b
Plan is below. Note, this is the actual execution plan that I changed the names of the tables for anonymity. There are joins I didn't include in the example for simplicity's sake. Lines 72/73 include the index that I'm trying to avoid. I added the predicate information.
| Id | Operation | Name | Rows | Bytes | Cost | Time |
---------------------------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 84613730 | 20138067740 | 5534656 | 00:03:37 |
| 1 | PX COORDINATOR | | | | | |
| 2 | PX SEND QC (RANDOM) | :TQ10014 | 84613730 | 20138067740 | 5534656 | 00:03:37 |
| 3 | SORT GROUP BY | | 84613730 | 20138067740 | 5534656 | 00:03:37 |
| 4 | PX RECEIVE | | 84613730 | 20138067740 | 5534656 | 00:03:37 |
| 5 | PX SEND HASH | :TQ10013 | 84613730 | 20138067740 | 5534656 | 00:03:37 |
| 6 | SORT GROUP BY | | 84613730 | 20138067740 | 5534656 | 00:03:37 |
| 7 | PX RECEIVE | | 84613730 | 20138067740 | 5534656 | 00:03:37 |
| 8 | PX SEND HASH | :TQ10012 | 84613730 | 20138067740 | 5534656 | 00:03:37 |
| 9 | SORT GROUP BY | | 84613730 | 20138067740 | 5534656 | 00:03:37 |
| 10 | VIEW | | 84613730 | 20138067740 | 5135062 | 00:03:21 |
| 11 | WINDOW SORT | | 84613730 | 22338024720 | 5135062 | 00:03:21 |
| 12 | PX RECEIVE | | 84613730 | 22338024720 | 5135062 | 00:03:21 |
| 13 | PX SEND HASH | :TQ10011 | 84613730 | 22338024720 | 5135062 | 00:03:21 |
| 14 | WINDOW BUFFER | | 84613730 | 22338024720 | 5135062 | 00:03:21 |
| 15 | SORT GROUP BY | | 84613730 | 22338024720 | 5135062 | 00:03:21 |
| 16 | PX RECEIVE | | 84613730 | 22338024720 | 5135062 | 00:03:21 |
| 17 | PX SEND HASH | :TQ10010 | 84613730 | 22338024720 | 5135062 | 00:03:21 |
| 18 | HASH GROUP BY | | 84613730 | 22338024720 | 5135062 | 00:03:21 |
| 19 | VIEW | | 84613730 | 22338024720 | 4201665 | 00:02:45 |
| 20 | WINDOW SORT | | 84613730 | 33760878270 | 4201665 | 00:02:45 |
| 21 | PX RECEIVE | | 84613730 | 33760878270 | 4201665 | 00:02:45 |
| 22 | PX SEND HASH | :TQ10009 | 84613730 | 33760878270 | 4201665 | 00:02:45 |
| 23 | WINDOW BUFFER | | 84613730 | 33760878270 | 4201665 | 00:02:45 |
| 24 | SORT GROUP BY | | 84613730 | 33760878270 | 4201665 | 00:02:45 |
| 25 | PX RECEIVE | | 84613730 | 33760878270 | 4201665 | 00:02:45 |
| 26 | PX SEND HASH | :TQ10008 | 84613730 | 33760878270 | 4201665 | 00:02:45 |
| 27 | HASH GROUP BY | | 84613730 | 33760878270 | 4201665 | 00:02:45 |
| * 28 | HASH JOIN | | 84613730 | 33760878270 | 2809746 | 00:01:50 |
| 29 | PX RECEIVE | | 48812 | 1366736 | 2 | 00:00:01 |
| 30 | PX SEND BROADCAST | :TQ10003 | 48812 | 1366736 | 2 | 00:00:01 |
| 31 | PX BLOCK ITERATOR | | 48812 | 1366736 | 2 | 00:00:01 |
| 32 | TABLE ACCESS STORAGE FULL | Table5 | 48812 | 1366736 | 2 | 00:00:01 |
| * 33 | HASH JOIN RIGHT OUTER | | 84350244 | 31293940524 | 2809707 | 00:01:50 |
| 34 | PX RECEIVE | | 48812 | 1025052 | 2 | 00:00:01 |
| 35 | PX SEND BROADCAST | :TQ10004 | 48812 | 1025052 | 2 | 00:00:01 |
| 36 | PX BLOCK ITERATOR | | 48812 | 1025052 | 2 | 00:00:01 |
| 37 | TABLE ACCESS STORAGE FULL | Table5 | 48812 | 1025052 | 2 | 00:00:01 |
| * 38 | HASH JOIN RIGHT OUTER | | 84087578 | 29430652300 | 2809669 | 00:01:50 |
| 39 | PX RECEIVE | | 37177 | 669186 | 3 | 00:00:01 |
| 40 | PX SEND BROADCAST | :TQ10005 | 37177 | 669186 | 3 | 00:00:01 |
| 41 | PX BLOCK ITERATOR | | 37177 | 669186 | 3 | 00:00:01 |
| 42 | TABLE ACCESS STORAGE FULL | Table4 | 37177 | 669186 | 3 | 00:00:01 |
| * 43 | HASH JOIN | | 84087578 | 27917075896 | 2809629 | 00:01:50 |
| 44 | TABLE ACCESS STORAGE FULL | Table4 | 37177 | 669186 | 3 | 00:00:01 |
| * 45 | HASH JOIN OUTER | | 84087578 | 26403499492 | 2809590 | 00:01:50 |
| 46 | JOIN FILTER CREATE | :BF0000 | 84087578 | 17490216224 | 2134146 | 00:01:24 |
| 47 | PX RECEIVE | | 84087578 | 17490216224 | 2134146 | 00:01:24 |
| 48 | PX SEND HASH (NULL RANDOM) | :TQ10006 | 84087578 | 17490216224 | 2134146 | 00:01:24 |
| * 49 | HASH JOIN BUFFERED | | 84087578 | 17490216224 | 2134146 | 00:01:24 |
| 50 | PART JOIN FILTER CREATE | :BF0001 | 345508495 | 11401780335 | 589579 | 00:00:24 |
| 51 | PX RECEIVE | | 345508495 | 11401780335 | 589579 | 00:00:24 |
| 52 | PX SEND HASH | :TQ10000 | 345508495 | 11401780335 | 589579 | 00:00:24 |
| 53 | PX PARTITION LIST INLIST | | 345508495 | 11401780335 | 589579 | 00:00:24 |
| * 54 | TABLE ACCESS STORAGE FULL | Table3 | 345508495 | 11401780335 | 589579 | 00:00:24 |
| * 55 | HASH JOIN | | 84087578 | 14715326150 | 1532914 | 00:01:00 |
| 56 | JOIN FILTER CREATE | :BF0003 | 84087578 | 12276786388 | 1201043 | 00:00:47 |
| 57 | PART JOIN FILTER CREATE | :BF0002 | 84087578 | 12276786388 | 1201043 | 00:00:47 |
| 58 | PX RECEIVE | | 84087578 | 12276786388 | 1201043 | 00:00:47 |
| 59 | PX SEND HASH | :TQ10001 | 84087578 | 12276786388 | 1201043 | 00:00:47 |
| 60 | PX PARTITION LIST INLIST | | 84087578 | 12276786388 | 1201043 | 00:00:47 |
| * 61 | TABLE ACCESS STORAGE FULL | Table1 | 84087578 | 12276786388 | 1201043 | 00:00:47 |
| 62 | PX RECEIVE | | 388151817 | 11256402693 | 320877 | 00:00:13 |
| 63 | PX SEND HASH | :TQ10002 | 388151817 | 11256402693 | 320877 | 00:00:13 |
| 64 | JOIN FILTER USE | :BF0003 | 388151817 | 11256402693 | 320877 | 00:00:13 |
| 65 | PX PARTITION LIST INLIST | | 388151817 | 11256402693 | 320877 | 00:00:13 |
| * 66 | TABLE ACCESS STORAGE FULL |Table2 | 388151817 | 11256402693 | 320877 | 00:00:13 |
| 67 | PX RECEIVE | | 191371730 | 20285403380 | 660306 | 00:00:26 |
| 68 | PX SEND HASH | :TQ10007 | 191371730 | 20285403380 | 660306 | 00:00:26 |
| 69 | JOIN FILTER USE | :BF0000 | 191371730 | 20285403380 | 660306 | 00:00:26 |
| 70 | PX PARTITION LIST INLIST | | 191371730 | 20285403380 | 660306 | 00:00:26 |
| * 71 | TABLE ACCESS BY LOCAL INDEX ROWID BATCHED | Table1 | 191371730 | 20285403380 | 660306 | 00:00:26 |
| 72 | BITMAP CONVERSION TO ROWIDS | | | | | |
| * 73 | BITMAP INDEX RANGE SCAN | BAD_INDEX | | | | |
* 73 - access("B"."date_field"(+)>=TO_NUMBER(TO_CHAR(LAST_DAY(SYSDATE@!-INTERVAL'+00-06' YEAR(2) TO MONTH),'YYYYMMDD')))
* 73 - filter(NVL("B"."date_field"(+),99990101)>=TO_NUMBER(TO_CHAR(LAST_DAY(SYSDATE@!-INTERVAL'+00-06' YEAR(2) TO MONTH),'YYYYMMDD')) AND
"B"."date_field"(+)>=TO_NUMBER(TO_CHAR(LAST_DAY(SYSDATE@!-INTERVAL'+00-06' YEAR(2) TO MONTH),'YYYYMMDD')))
Sources
This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.
Source: Stack Overflow
| Solution | Source |
|---|