谓词推入(PUSHING PREDICATE):
create or replace view vtest as select * from test1 where object_id>1000;
SQL> explain plan for select /*+use_nl(test2 vtest) no_merge(vtest)*/ * from test2,vtest
where test2.object_id=vtest.object_id(+) 2 ;
Explained.
SQL> select * from table(dbms_xplan.display());
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 3060052891
---------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 5904 | 1620K| 11836 (1)| 00:02:23 |
| 1 | NESTED LOOPS OUTER | | 5904 | 1620K| 11836 (1)| 00:02:23 |
| 2 | TABLE ACCESS FULL | TEST2 | 5904 | 490K| 25 (0)| 00:00:01 |
| 3 | VIEW PUSHED PREDICATE | VTEST | 1 | 196 | 2 (0)| 00:00:01 |
|* 4 | FILTER | | | | | |
| 5 | TABLE ACCESS BY INDEX ROWID| TEST1 | 1 | 85 | 2 (0)| 00:00:01 |
|* 6 | INDEX RANGE SCAN | TEST1_IDX1 | 1 | | 1 (0)| 00:00:01 |
---------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - filter(1000<"TEST2"."OBJECT_ID")
6 - access("OBJECT_ID"="TEST2"."OBJECT_ID")
filter("OBJECT_ID">1000)
20 rows selected.
SQL> select * from table(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID 6a8nvggau807m, child number 0
-------------------------------------
select /*+use_nl(test2 vtest) no_merge(vtest)*/ * from test2,vtest
where test2.object_id=vtest.object_id(+)
Plan hash value: 3060052891
----------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | Reads |
----------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 5904 |00:00:00.03 | 1623 | 10 |
| 1 | NESTED LOOPS OUTER | | 1 | 5904 | 5904 |00:00:00.03 | 1623 | 10 |
| 2 | TABLE ACCESS FULL | TEST2 | 1 | 5904 | 5904 |00:00:00.01 | 474 | 0 |
| 3 | VIEW PUSHED PREDICATE | VTEST | 5904 | 1 | 4961 |00:00:00.02 | 1149 | 10 |
|* 4 | FILTER | | 5904 | | 4961 |00:00:00.01 | 1149 | 10 |
| 5 | TABLE ACCESS BY INDEX ROWID| TEST1 | 4961 | 1 | 4961 |00:00:00.01 | 1149 | 10 |
|* 6 | INDEX RANGE SCAN | TEST1_IDX1 | 4961 | 1 | 4961 |00:00:00.01 | 757 | 10 |
----------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - filter(1000<"TEST2"."OBJECT_ID")
6 - access("OBJECT_ID"="TEST2"."OBJECT_ID")
filter("OBJECT_ID">1000)
26 rows selected.
加大数据量:
SQL> select /*+use_nl(test2 vtest) no_merge(vtest)*/ count(*) from test2,vtest
where test2.object_id=vtest.object_id(+) 2 ;
COUNT(*)
----------
162592224
SQL> select * from table(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID 8asdzmaaqkb3x, child number 0
-------------------------------------
select /*+use_nl(test2 vtest) no_merge(vtest)*/ count(*) from
test2,vtest where test2.object_id=vtest.object_id(+)
Plan hash value: 2785124820
-------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
-------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 1 |00:01:09.93 | 828K|
| 1 | SORT AGGREGATE | | 1 | 1 | 1 |00:01:09.93 | 828K|
| 2 | NESTED LOOPS OUTER | | 1 | 5904 | 162M|00:01:02.76 | 828K|
| 3 | TABLE ACCESS FULL | TEST2 | 1 | 5904 | 188K|00:00:00.13 | 2392 |
| 4 | VIEW PUSHED PREDICATE | VTEST | 188K| 1 | 162M|00:00:47.02 | 826K|
|* 5 | FILTER | | 188K| | 162M|00:00:30.88 | 826K|
|* 6 | INDEX RANGE SCAN | TEST1_IDX1 | 158K| 1 | 162M|00:00:15.08 | 826K|
-------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
5 - filter(1000<"TEST2"."OBJECT_ID")
6 - access("OBJECT_ID"="TEST2"."OBJECT_ID")
filter("OBJECT_ID">1000)
26 rows selected
SQL> select count(*) from test2,vtest
where test2.object_id=vtest.object_id(+) 2 ;
COUNT(*)
----------
1300737792
Elapsed: 00:00:58.53
SQL> select /*+use_nl(test2 vtest) no_merge(vtest)*/ count(*) from test2,vtest
where test2.object_id=vtest.object_id(+) 2 ;
COUNT(*)
----------
1300737792
Elapsed: 00:01:07.16
SQL> select count(*) from test2,vtest
where test2.object_id=vtest.object_id(+) 2 ;
COUNT(*)
----------
162592224
SQL> select * from table(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID 8113kkjmdz6jg, child number 1
-------------------------------------
select count(*) from test2,vtest where
test2.object_id=vtest.object_id(+)
Plan hash value: 567585662
--------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem |
--------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 1 |00:00:22.79 | 18134 | | | |
| 1 | SORT AGGREGATE | | 1 | 1 | 1 |00:00:22.79 | 18134 | | | |
|* 2 | HASH JOIN OUTER | | 1 | 5904 | 162M|00:00:15.57 | 18134 | 5936K| 2022K| 13M (0)|
| 3 | TABLE ACCESS FULL | TEST2 | 1 | 5904 | 188K|00:00:00.03 | 2392 | | | |
|* 4 | INDEX FAST FULL SCAN| TEST1_IDX1 | 1 | 5828 | 5080K|00:00:00.56 | 15742 | | | |
--------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("TEST2"."OBJECT_ID"="OBJECT_ID")
4 - filter("OBJECT_ID">1000)
23 rows selected.
推论:如果把比较写的结果集推入到视图是可以的,比较大的结果集还是走HASH JOIN。