oracle优化之count的优化

yunde110

select count(*) from t1;　　这句话比较简单，但很有玄机！对这句话运行的理解，反映了你对数据库的理解深度！
　　建立实验的大表他t1
　　SQL> conn scott/tiger
　　已连接。
　　SQL> drop table t1 purge;
　　表已删除。
　　SQL> create table t1 as select * from emp where 0=9;
　　表已创建。
　　SQL> insert into t1 select * from emp;
　　已创建14行。
　　SQL> insert into t1 select * from t1;
　　已创建14行。
　　SQL> /
　　已创建28行。
　　SQL> /
　　已创建56行。
　　SQL> /
　　已创建112行。
　　SQL> /
　　已创建224行。
　　SQL> /
　　已创建448行。
　　SQL> /
　　已创建896行。
　　SQL> /
　　已创建1792行。
　　SQL> /
　　已创建3584行。
　　SQL> /
　　已创建7168行。
　　SQL> /
　　已创建14336行。
　　SQL> /
　　已创建28672行。
　　SQL> /
　　已创建57344行。
　　SQL> commit;
　　提交完成。
　　收集统计信息
　　SQL> execute dbms_stats.gather_table_stats('SCOTT','T1');
　　PL/SQL 过程已成功完成。
　　SQL> SET AUTOT TRACE EXP
　　SQL> SELECT COUNT(*) FROM T1;
　　执行计划
　　--------------------------------------------------

　　|>　　-------------------------------------------------------------------
　　| 0 | SELECT STATEMENT | | 1 | 124 (4)| 00:00:02 |
　　| 1 | SORT AGGREGATE | | 1 | | |
　　| 2 | TABLE ACCESS FULL| T1 | 116K| 124 (4)| 00:00:02 |
　　-----------------------------------------------------
　　代价为124，运行的计划为全表扫描。
　　SQL> DELETE T1 WHERE DEPTNO=10;
　　已删除24576行。
　　SQL> COMMIT;
　　提交完成。
　　SQL> execute dbms_stats.gather_table_stats('SCOTT','T1');
　　PL/SQL 过程已成功完成。
　　SQL> SELECT COUNT(*) FROM T1;
　　执行计划
　　-----------------------------------------------------

　　|>　　-------------------------------------------------------------------
　　| 0 | SELECT STATEMENT | | 1 | 123 (3)| 00:00:02 |
　　| 1 | SORT AGGREGATE | | 1 | | |
　　| 2 | TABLE ACCESS FULL| T1 | 90286 | 123 (3)| 00:00:02 |
　　-----------------------------------------------------
　　SQL> --1.降低高水位

　　SQL>>　　表已更改。
　　SQL> execute dbms_stats.gather_table_stats('SCOTT','T1');
　　PL/SQL 过程已成功完成。
　　SQL> SELECT COUNT(*) FROM T1;
　　执行计划
　　-----------------------------------------------------

　　|>　　-------------------------------------------------------------------
　　| 0 | SELECT STATEMENT | | 1 | 102 (3)| 00:00:02 |
　　| 1 | SORT AGGREGATE | | 1 | | |
　　| 2 | TABLE ACCESS FULL| T1 | 90667 | 102 (3)| 00:00:02 |
　　-----------------------------------------------------
　　代价为102，降低了
　　SQL> --2.修改pctfree

　　SQL>>　　表已更改。

　　SQL>>　　表已更改。
　　SQL> execute dbms_stats.gather_table_stats('SCOTT','T1');
　　PL/SQL 过程已成功完成。
　　SQL> SELECT COUNT(*) FROM T1;
　　执行计划
　　----------------------------------------------------------
　　Plan hash value: 3724264953
　　-------------------------------------------------------------------

　　|>　　-------------------------------------------------------------------
　　| 0 | SELECT STATEMENT | | 1 | 92 (4)| 00:00:02 |
　　| 1 | SORT AGGREGATE | | 1 | | |
　　| 2 | TABLE ACCESS FULL| T1 | 91791 | 92 (4)| 00:00:02 |
　　-------------------------------------------------------------------
　　代价为92，降低了10%
　　SQL> --3.参数db_file_multiblock_read_count=64
　　SQL> --4.建立b*tree类型的索引
　　SQL> create index i1 on t1(empno);
　　索引已创建。
　　SQL> execute dbms_stats.gather_index_stats('SCOTT','I1');
　　PL/SQL 过程已成功完成。
　　SQL> SELECT COUNT(*) FROM T1;
　　执行计划
　　----------------------------------------------------------
　　Plan hash value: 3724264953
　　-------------------------------------------------------------------

　　|>　　-------------------------------------------------------------------
　　| 0 | SELECT STATEMENT | | 1 | 92 (4)| 00:00:02 |
　　| 1 | SORT AGGREGATE | | 1 | | |
　　| 2 | TABLE ACCESS FULL| T1 | 91791 | 92 (4)| 00:00:02 |
　　-------------------------------------------------------------------
　　为什么没有使用我们建立的索引，因为null不进入普通的索引！

　　SQL>>　　表已更改。
　　SQL> SELECT COUNT(*) FROM T1;
　　执行计划
　　----------------------------------------------------------
　　Plan hash value: 129980005
　　----------------------------------------------------------------------

　　|>　　----------------------------------------------------------------------
　　| 0 | SELECT STATEMENT | | 1 | 36 (6)| 00:00:01 |
　　| 1 | SORT AGGREGATE | | 1 | | |
　　| 2 | INDEX FAST FULL SCAN| I1 | 91791 | 36 (6)| 00:00:01 |
　　----------------------------------------------------------------------
　　我们的索引起到了很大的作用！
　　SQL> --5.使用并行查询的特性
　　强制全表扫描，屏蔽索引
　　SQL> select /*+ full(t1) parallel(t1 2) */ COUNT(*) FROM T1;
　　执行计划
　　----------------------------------------------------------------------------------------

　　|>　　----------------------------------------------------------------------------------------------
　　| 0 | SELECT STATEMENT | | 1 | 51 (4)| 00:00:01 | | | |
　　| 1 | SORT AGGREGATE | | 1 | | | | | |
　　| 2 | PX COORDINATOR | | | | | | | |
　　| 3 | PX SEND QC (RANDOM) | :TQ10000 | 1 | | | Q1,00 | P->S | QC(RAND) |
　　| 4 | SORT AGGREGATE | | 1 | | | Q1,00 | PCWP | |
　　| 5 | PX BLOCK ITERATOR | | 91791 | 51 (4)| 00:00:01 | Q1,00 | PCWC| |
　　| 6 | TABLE ACCESS FULL| T1 | 91791 | 51 (4)| 00:00:01 | Q1,00 | PCWP | |
　　-----------------------------------------------------------------------------------------------
　　并行度越高，代价越低

　　SQL>>　　表已更改。
　　也可以通过使用表的属性来定义并行度，但是影响比较大，不如语句级别限制并行！
　　SQL> select count(*) from t1;
　　执行计划
　　-----------------------------------------------------------------------------------------

　　|>　　---------------------------------------------------------------------------------------------
　　| 0 | SELECT STATEMENT | | 1 | 25 (0)| 00:00:01 | | | |
　　| 1 | SORT AGGREGATE | | 1 | | | | | |
　　| 2 | PX COORDINATOR | | | | | | | |
　　| 3 | PX SEND QC (RANDOM) | :TQ10000 | 1 | | | Q1,00 | P->S | QC(RAND) |
　　| 4 | SORT AGGREGATE | | 1 | | | Q1,00 | PCWP | |
　　| 5 | PX BLOCK ITERATOR | | 91791 | 25 (0)| 00:00:01 | Q1,00 | PCWC | |
　　| 6 | TABLE ACCESS FULL| T1 | 91791 | 25 (0)| 00:00:01 | Q1,00 | PCWP | |
　　---------------------------------------------------------------------------------------------
　　代价为25，代价比两个的又少一半！
　　SQL> --6.建立位图索引来避免全表扫描
　　SQL> create bitmap index i2 on t1(deptno);
　　索引已创建。
　　SQL> execute dbms_stats.gather_index_stats('SCOTT','I2');
　　PL/SQL 过程已成功完成。
　　SQL> select count(*) from t1;
　　执行计划
　　----------------------------------------------------------
　　Plan hash value: 3738977131
　　------------------------------------------------------------------------------

　　|>　　------------------------------------------------------------------------------
　　| 0 | SELECT STATEMENT | | 1 | 4 (0)| 00:00:01 |
　　| 1 | SORT AGGREGATE | | 1 | | |
　　| 2 | BITMAP CONVERSION COUNT | | 91791 | 4 (0)| 00:00:01 |
　　| 3 | BITMAP INDEX FAST FULL SCAN| I2 | | | |
　　------------------------------------------------------------------------------

　　SQL>>　　索引已更改。
　　SQL> select count(*) from t1;
　　执行计划
　　----------------------------------------------------------------------------------------

　　|>　　----------------------------------------------------------------------------------------------
　　| 0 | SELECT STATEMENT | | 1 | 2 (0)| 00:00:01 | | | |
　　| 1 | SORT AGGREGATE | | 1 | | | | | |
　　| 2 | PX COORDINATOR | | | | | | | |
　　| 3 | PX SEND QC (RANDOM) | :TQ10000 | 1 | | | Q1,00 | P->S | QC (RAND) |
　　| 4 | SORT AGGREGATE | | 1 | | | Q1,00 | PCWP | |
　　| 5 | PX BLOCK ITERATOR | | 91791 | 2 (0)| 00:00:01 | Q1,00 |PCWC | |
　　| 6 | BITMAP CONVERSION COUNT | | 91791 | 2 (0)| 00:00:01 | Q1,00 |PCWP | |
　　| 7 | BITMAP INDEX FAST FULL SCAN| I2 | | | | Q1,00 | PCWP | |
　　--------------------------------------------------------------------------------------------
　　代价为2，原来为124，优化无止境呀！
　　只有你把握原理，一切尽在掌握！