彻底理解谓词推入的利弊

谓词推入(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。