学习优化器--filter-优快云博客

本文通过创建测试表并执行特定SQL查询的方式，展示了数据库优化器如何根据表中的统计数据估算查询成本的过程。具体包括表结构设计、数据填充、统计信息收集及查询计划分析等内容。

首先创建测试表test，该表中a列对应10个不同的值，b列对应20万个不同值。

SQL> create table test ( a number, b number ,c varchar2(10));

Table created.

SQL> begin
2 for i in 1..200000 loop
3 insert into test values (mod(i,10),i,'xyz');
4 end loop;
5 commit;
6 end;
7 /

PL/SQL procedure successfully completed.

SQL> exec dbms_stats.gather_table_stats(ownname=>user,tabname=>'TEST',method_opt=>'for all columns size auto');

PL/SQL procedure successfully completed.

SQL> set autotrace traceonly explain

SQL> alter session set "_optimizer_cost_model"=io; ----此处修改优化器模式，忽略cpu costing的计算。

Session altered.

SQL> select count(1) from test outer where outer.b>(select /*+ no_unnest */ avg(inner.b) from test inner where inner.a =outer.a);

Execution Plan
----------------------------------------------------------
Plan hash value: 2139949240

-------------------------------------------------------------
| Id | Operation            | Name | Rows | Bytes | Cost |
-------------------------------------------------------------
|   0 | SELECT STATEMENT     |      |     1 |     8 |   847 |
|   1 | SORT AGGREGATE      |      |     1 |     8 |       |
|* 2 |   FILTER             |      |       |       |       |
|   3 |    TABLE ACCESS FULL | TEST |   198K| 1550K|    77 |
|   4 |    SORT AGGREGATE    |      |     1 |     8 |       |
|* 5 |     TABLE ACCESS FULL| TEST | 19840 |   155K|    77 |
-------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter("OUTER"."B"> (SELECT /*+ NO_UNNEST */ AVG("INNER"."B")
              FROM "TEST" "INNER" WHERE "INNER"."A"=:B1))
   5 - filter("INNER"."A"=:B1)

Note
-----
- cpu costing is off (consider enabling it)

SQL> set autot off
SQL> select 77+77*10 from dual;

77+77*10
----------
       847
   可以看出，对于此条sql，优化器在估算成本时，观察到在列a上只有10个不同值。这样对外层表做一次全表扫描，成本为77，之后针对a的10个不同值，对内层表做10次全表扫描
，成本为77*10=770，这样总的成本为770+77=847。
   也可以看出，优化器在发现a只有10个不同值后，只需要对内层表进行10次扫描就可以取得想要的结果，而不是内层表的每条记录都进行扫描。

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/10972173/viewspace-608147/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/10972173/viewspace-608147/