oracle count最快的方法,Oracle count哪种写法更快
1)創(chuàng)建測(cè)試表
test@CISCOSYS> create table t as select * from dba_objects;
表已創(chuàng)建。
test@CISCOSYS> update t set object_id =rownum ;
已更新50967行。
2)使用count(*)進(jìn)行統(tǒng)計(jì)
test@CISCOSYS> select count(*) from t;
COUNT(*)
----------
50967
已用時(shí)間: 00: 00: 00.01
執(zhí)行計(jì)劃
----------------------------------------------------------
Plan hash value: 2966233522
-------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
-------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 161 (2)| 00:00:02 |
| 1 | SORT AGGREGATE | | 1 | | |
| 2 | TABLE ACCESS FULL| T | 44475 | 161 (2)| 00:00:02 |
-------------------------------------------------------------------
Note
-----
- dynamic sampling used for this statement
統(tǒng)計(jì)信息
----------------------------------------------------------
4 recursive calls
0 db block gets
764 consistent gets
0 physical reads
0 redo size
410 bytes sent via SQL*Net to client
385 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
3)使用COUNT(列)進(jìn)行統(tǒng)計(jì)
test@CISCOSYS> select count(*) from t;
COUNT(*)
----------
50967
已用時(shí)間: 00: 00: 00.01
執(zhí)行計(jì)劃
----------------------------------------------------------
Plan hash value: 2966233522
-------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
-------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 161 (2)| 00:00:02 |
| 1 | SORT AGGREGATE | | 1 | | |
| 2 | TABLE ACCESS FULL| T | 44475 | 161 (2)| 00:00:02 |
-------------------------------------------------------------------
Note
-----
- dynamic sampling used for this statement
統(tǒng)計(jì)信息
----------------------------------------------------------
4 recursive calls
0 db block gets
764 consistent gets
0 physical reads
0 redo size
410 bytes sent via SQL*Net to client
385 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
解釋一下 :物理讀為0，是因?yàn)閯?chuàng)建表的時(shí)候，數(shù)據(jù)已經(jīng)載入load buffer.
可以使用
test@CISCOSYS> alter system flush buffer_cache;
通過比較COUNT(*) 和Count(列) ,兩種情況的COST 是完全一樣的。
繼續(xù)試驗(yàn)！！！
為表創(chuàng)建索引
test@CISCOSYS> create index idx_t_id on t(object_id);
索引已創(chuàng)建。
test@CISCOSYS> alter system flush buffer_cache;
系統(tǒng)已更改。
test@CISCOSYS> select count(*) from t;
COUNT(*)
----------
50967
已用時(shí)間: 00: 00: 00.26
執(zhí)行計(jì)劃
----------------------------------------------------------
Plan hash value: 2966233522
-------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
-------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 161 (2)| 00:00:02 |
| 1 | SORT AGGREGATE | | 1 | | |
| 2 | TABLE ACCESS FULL| T | 44475 | 161 (2)| 00:00:02 |
-------------------------------------------------------------------
Note
-----
- dynamic sampling used for this statement
統(tǒng)計(jì)信息
----------------------------------------------------------
5 recursive calls
0 db block gets
765 consistent gets
705 physical reads
0 redo size
410 bytes sent via SQL*Net to client
385 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
test@CISCOSYS> select count(object_id) from t;
COUNT(OBJECT_ID)
----------------
50967
已用時(shí)間: 00: 00: 00.09
執(zhí)行計(jì)劃
----------------------------------------------------------
Plan hash value: 3570898368
--------------------------------------------------------------------------------
--
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time
|
--------------------------------------------------------------------------------
--
| 0 | SELECT STATEMENT | | 1 | 13 | 30 (4)| 00:00:01
|
| 1 | SORT AGGREGATE | | 1 | 13 | |
|
| 2 | INDEX FAST FULL SCAN| IDX_T_ID | 44475 | 564K| 30 (4)| 00:00:01
|
--------------------------------------------------------------------------------
--
Note
-----
- dynamic sampling used for this statement
統(tǒng)計(jì)信息
----------------------------------------------------------
4 recursive calls
0 db block gets
181 consistent gets
477 physical reads
0 redo size
418 bytes sent via SQL*Net to client
385 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
在這里，用COUNT(列)比COUNT(*)要快。通過比較執(zhí)行計(jì)劃。可以看出COUNT(*)不能用到索引，而COUNT(列)可以
繼續(xù)試驗(yàn)！！！
將鍵值設(shè)為非空
test@CISCOSYS> alter table T modify object_id not null;
表已更改。
已用時(shí)間: 00: 00: 01.34
test@CISCOSYS> alter system flush buffer_cache;
系統(tǒng)已更改。
已用時(shí)間: 00: 00: 00.01
test@CISCOSYS> select count(*) from t;
COUNT(*)
----------
50967
已用時(shí)間: 00: 00: 00.31
執(zhí)行計(jì)劃
----------------------------------------------------------
Plan hash value: 3570898368
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 30 (4)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | | |
| 2 | INDEX FAST FULL SCAN| IDX_T_ID | 44475 | 30 (4)| 00:00:01 |
--------------------------------------------------------------------------
Note
-----
- dynamic sampling used for this statement
統(tǒng)計(jì)信息
----------------------------------------------------------
205 recursive calls
0 db block gets
213 consistent gets
496 physical reads
0 redo size
410 bytes sent via SQL*Net to client
385 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
5 sorts (memory)
0 sorts (disk)
1 rows processed
test@CISCOSYS> alter system flush buffer_cache;
系統(tǒng)已更改。
已用時(shí)間: 00: 00: 00.04
test@CISCOSYS> select count(object_id) from t;
COUNT(OBJECT_ID)
----------------
50967
已用時(shí)間: 00: 00: 00.20
執(zhí)行計(jì)劃
----------------------------------------------------------
Plan hash value: 3570898368
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 30 (4)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | | |
| 2 | INDEX FAST FULL SCAN| IDX_T_ID | 44475 | 30 (4)| 00:00:01 |
--------------------------------------------------------------------------
Note
-----
- dynamic sampling used for this statement
統(tǒng)計(jì)信息
----------------------------------------------------------
4 recursive calls
0 db block gets
181 consistent gets
477 physical reads
0 redo size
418 bytes sent via SQL*Net to client
385 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
將一些記錄object_id置為null.
test@CISCOSYS> alter table t modify (object_id number null);
表已更改。
test@CISCOSYS> update t set object_id=null where object_id<=10;
已更新10行。
test@CISCOSYS> select count(*) from t;
COUNT(*)
----------
50967
已用時(shí)間: 00: 00: 00.00
test@CISCOSYS> select count(object_id) from t;
COUNT(OBJECT_ID)
----------------
50957
發(fā)現(xiàn)count(*)和count(列)記錄不一樣。也就是說，兩個(gè)功能上根本不是等價(jià)的。
如果一個(gè)列上存在索引，且非空。 COUNT(*)和COUNT(列)功能相當(dāng)。
反之，COUNT(*) 和COUNT(列)兩者功能本身就功能不同，不應(yīng)等同對(duì)待。
基于案例學(xué)SQL
總結(jié)
以上是生活随笔為你收集整理的oracle count最快的方法,Oracle count哪种写法更快的全部?jī)?nèi)容，希望文章能夠幫你解決所遇到的問題。
- 上一篇： oracle shutdown 默认,O
- 下一篇： it oracle 培训,Oracle数