優化select count(*) from t1
分享下如何優化一條簡單的SQL語句，資料庫優化無止境，學習資料庫優化，我們先從一條最簡單的SQL語句開始。
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;
執行計劃
--------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
-------------------------------------------------------------------
| 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，執行的計劃為全表掃描。
那麼我們刪除表中所有的資料，是不是代價就能少了那？按照我們的正常思維是這樣的，但是oracle資料庫
沒有這麼做，沒有那麼智慧，畢竟它只是一個程式。
我們刪除T1的所有資料，我們來看看。
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;
執行計劃
-----------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
-------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 123 (3)| 00:00:02 |
| 1 | SORT AGGREGATE | | 1 | | |
| 2 | TABLE ACCESS FULL| T1 | 90286 | 123 (3)| 00:00:02 |
-----------------------------------------------------
我們看到了，代價為123幾乎沒有變化。
第一種優化的手段：
--1.降低高水位
SQL> alter table t1 move tablespace users;
表已更改。
SQL> execute dbms_stats.gather_table_stats('SCOTT','T1');
PL/SQL 過程已成功完成。
SQL> SELECT COUNT(*) FROM T1;
執行計劃
-----------------------------------------------------
| Id | Operation | Name | Rows |Cost (%CPU)| Time |
-------------------------------------------------------------------
| 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，降低了，但是不多。
第二個手段是：
2.修改pctfree
SQL> alter table t1 pctfree 0;
表已更改。
SQL> alter table t1 move tablespace users;
表已更改。
SQL> execute dbms_stats.gather_table_stats('SCOTT','T1');
PL/SQL 過程已成功完成。
SQL> SELECT COUNT(*) FROM T1;
執行計劃
----------------------------------------------------------
Plan hash value: 3724264953
-------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
-------------------------------------------------------------------
| 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%
第三種手段是：
--3.引數db_file_multiblock_read_count=64
該引數是一次性讀的資料庫塊數。一次性讀的塊數越多。資料庫的效能就越高。
第四種手段是：
--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
-------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
-------------------------------------------------------------------
| 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> alter table t1 modify(empno not null);
表已更改。
SQL> SELECT COUNT(*) FROM T1;
執行計劃
----------------------------------------------------------
Plan hash value: 129980005
----------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
----------------------------------------------------------------------
| 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 |
----------------------------------------------------------------------
代價為36，我們的索引起到了很大的作用！
第五種手段是：
SQL> --5.使用並行查詢的特性
強制全表掃描，遮蔽索引
SQL> select /*+ full(t1) parallel(t1 2) */ COUNT(*) FROM T1;
執行計劃
----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time | TQ |IN-OUT| PQ Distrib |
----------------------------------------------------------------------------------------------
| 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> alter table t1 parallel 4;
表已更改。
也可以通過使用表的屬性來定義並行度，但是影響比較大，不如語句級別限制並行！
SQL> select count(*) from t1;
執行計劃
-----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time | TQ |IN-OUT| PQDistrib |
---------------------------------------------------------------------------------------------
| 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
------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
------------------------------------------------------------------------------
| 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> alter index i2 parallel 4;
索引已更改。
SQL> select count(*) from t1;
執行計劃
----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time | TQ |IN-OUT| PQ Distrib |
----------------------------------------------------------------------------------------------
| 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，優化無止境呀！
只有你把握原理，一切盡在掌握！
要去開早會了，今天就到這裡吧，希望大家能學到一點有用的知識。在工作中能用到。
pxboracle@live.com
2014.08.12 08:24
share you knowledge with the world.
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/12798004/viewspace-1249555/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- count(*) 優化優化
- count(*)優化優化
- count(*)小優化優化
- select count(*)和select count(1)的區別
- 【優化】COUNT(1)、COUNT(*)、COUNT(常量)、COUNT(主鍵)、COUNT(ROWID)等優化
- 【MySQL】效能優化之 count(*) VS count(col)MySql優化
- Ask Hoegh（4）——select count(*)和select count(1)、count(column)有區別嗎？
- MySQL優化COUNT()查詢MySql優化
- MySQL：SELECT COUNT 小結MySql
- 百萬資料 mysql count（*）優化MySql優化
- node express 在使用mysql執行SELECT count(*) from xx獲取總數取值格式問題ExpressMySql
- Sql優化（二） 快速計算Distinct CountSQL優化
- 【案例】MySQL count操作優化案例一則MySql優化
- SELECT INTO FROM mysql Undeclared variableMySql
- COUNT(*)計算行數有哪些優化手段優化
- Select from subquery 子查詢
- sql net message from|to client與sql execution countSQLclient
- SELECT COUNT(*) 索引會走 index fast full scan索引IndexAST
- select into from 和 insert into select 的用法和區別
- mysql 大表中count() 使用方法以及效能優化.MySql優化
- mysql count函式與分頁功能極限優化MySql函式優化
- 分散式查詢優化SERIAL_FROM_REMOTE分散式優化REM
- SQL-Hive中的Select From解析SQLHive
- mybatis中insert into ...select ...from dual union all select ... from dual 提示sql命令未結束的問題MyBatisSQL
- 16、MySQL Case-索引key對select count(*)的影響MySql索引
- select hang住等待SQL*Net message from ClientSQLclient
- MySQL 5.6，5.7的優化器對於count(*）的處理方式MySql優化
- [ OCRSRV][21]th_select_handler: Failed to retrieve procctx from......AI
- MySQL的COUNT語句--count(*)、 count(常量)、 count(列名)MySql
- count(0),count(1),count(*)總結與count(column)
- [ OCRSRV][3736]th_select_handler: Failed to retrieve procctx from htAI
- MySQL的count(*)的優化，獲取千萬級資料表的總行數MySql優化
- count(1),count（*），count（列）的區別
- 還在用SELECT COUNT統計資料庫表的行數？Out了資料庫
- count（*）、count（1）和count（列名）的區別
- count (*) 和 count (1) 和 count (列名) 區別
- count(*) 和 count(1)和count(列名)區別
- ORA-02030: can only select from fixed tables/viewsView