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. 无法减少扫描次数，减少扫描体积
