drop table test1;

create table test1 as select * from dba_objects where rownum<1000;

drop table test2;

create table test2 as select * from dba_objects where rownum<1000;

insert into test1 select * from test1;

update test2 set owner='SCOTT';

BEGIN

  DBMS_STATS.GATHER_TABLE_STATS(ownname          => 'SCOTT',

                                tabname          => 'TEST1',

                                estimate_percent => 100,

                                method_opt       => 'for  columns owner size 200',

                                no_invalidate    => FALSE,

                                degree           => 1,

                                cascade          => TRUE);

END;

/

BEGIN

  DBMS_STATS.GATHER_TABLE_STATS(ownname          => 'SCOTT',

                                tabname          => 'TEST2',

                                estimate_percent => 100,

                                method_opt       => 'for  columns owner size 200',

                                no_invalidate    => FALSE,

                                degree           => 1,

                                cascade          => TRUE);

END;

/

SQL> select * from table(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST'));

PLAN_TABLE_OUTPUT

------------------------------------------------------------------------------------------------------------------------------------------------------------

--------------------------------------------

SQL_ID	a23r1uchdaafg, child number 0

-------------------------------------

An uncaught error happened in prepare_sql_statement : ORA-01403: no data found

Plan hash value: 1896454807

--------------------------------------------------------------------------------------

| Id  | Operation	   | Name  | Starts | E-Rows | A-Rows |   A-Time   | Buffers |

--------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT   |	   |	  1 |	     |	 1998 |00:00:00.07 |   13763 |

|*  1 |  FILTER 	   |	   |	  1 |	     |	 1998 |00:00:00.07 |   13763 |

|   2 |   TABLE ACCESS FULL| TEST1 |	  1 |	 999 |	 1998 |00:00:00.01 |	 163 |

|*  3 |   TABLE ACCESS FULL| TEST2 |   1346 |	   1 |	 1346 |00:00:00.06 |   13600 |

--------------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

   1 - filter(("OWNER"='SCOTT' OR  IS NOT NULL))

   3 - filter(("OWNER"='SCOTT' AND "OBJECT_ID"=:B1))

21 rows selected.

查看FILTER的执行次数,此时 被驱动表TEST2执行了1346次,那这个1346次是怎么计算的呢？

SQL> select count(*) from test1;

  COUNT(*)

----------

      1998

SQL> select count(distinct object_id) from test1;       

COUNT(DISTINCTOBJECT_ID)

------------------------

		     999

网上有人说是count(distinct join列),看来结果不对,有待研究

FILTER能改变驱动表？

SQL> select /*+ leading(test2)*/ * from test1 where owner='SCOTT' or object_id in(select object_id from test2 where owner='SCOTT')

  2  ;

1998 rows selected.

Execution Plan

----------------------------------------------------------

Plan hash value: 1896454807

----------------------------------------------------------------------------

| Id  | Operation	   | Name  | Rows  | Bytes | Cost (%CPU)| Time	   |

----------------------------------------------------------------------------

|   0 | SELECT STATEMENT   |	   |	52 |  4420 |	 5   (0)| 00:00:01 |

|*  1 |  FILTER 	   |	   |	   |	   |		|	   |

|   2 |   TABLE ACCESS FULL| TEST1 |   999 | 84915 |	 5   (0)| 00:00:01 |

|*  3 |   TABLE ACCESS FULL| TEST2 |	 1 |	18 |	 5   (0)| 00:00:01 |

----------------------------------------------------------------------------

FILTER无法改变驱动表

1. 减少扫描 次数 

2. 无法减少扫描次数，减少扫描体积