1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
select /*+ push_pred (d)*/ *
from dual x
left join
(select /*+ no_merge*/ dummy from dual) d
on x.dummy = d.dummy
where x.dummy in (select x from test)
------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 6 | 6 (0)| 00:00:01 |
| 1 | NESTED LOOPS OUTER | | 1 | 6 | 6 (0)| 00:00:01 |
| 2 | NESTED LOOPS SEMI | | 1 | 4 | 4 (0)| 00:00:01 |
| 3 | TABLE ACCESS FULL | DUAL | 1 | 2 | 2 (0)| 00:00:01 |
|* 4 | VIEW | VW_NSO_1 | 4 | 8 | 2 (0)| 00:00:01 |
| 5 | TABLE ACCESS FULL | TEST | 4 | 8 | 2 (0)| 00:00:01 |
| 6 | VIEW PUSHED PREDICATE | | 1 | 2 | 2 (0)| 00:00:01 |
|* 7 | TABLE ACCESS FULL | DUAL | 1 | 2 | 2 (0)| 00:00:01 |
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
select /*+ push_pred (d)*/ *
from dual x
left join
(select /*+ no_merge*/ dummy from dual) d
on x.dummy = d.dummy
where x.dummy in ( select column_value from table(ORA_MINING_VARCHAR2_NT('X')))
-----------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 2006 | 33 (0)| 00:00:01 |
| 1 | NESTED LOOPS OUTER | | 1 | 2006 | 33 (0)| 00:00:01 |
| 2 | NESTED LOOPS SEMI | | 1 | 2004 | 31 (0)| 00:00:01 |
| 3 | TABLE ACCESS FULL | DUAL | 1 | 2 | 2 (0)| 00:00:01 |
|* 4 | VIEW | VW_NSO_1 | 8168 | 15M| 29 (0)| 00:00:01 |
| 5 | COLLECTION ITERATOR CONSTRUCTOR FETCH| | 8168 | 16336 | 29 (0)| 00:00:01 |
|* 6 | VIEW | | 1 | 2 | 2 (0)| 00:00:01 |
| 7 | TABLE ACCESS FULL | DUAL | 1 | 2 | 2 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------------
Почему во втором случае PUSH_PRED не работает?
Реальный запрос не хинтуется, но при замене подзапроса из GTT на подзапрос из NT, pushed predicate аналогично отваливается.
