18.读书笔记收获不止Oracle之 索引SUM和AVG优化
看下索引在sum()和avg之类的聚合语句中的优化。
drop table t purge;
sql> set timing off
sql> set autotrace off
sql> create table t as select * from dba_objects;
sql> create index idx1_object_id on t(object_id);
使用了SUM看看
Set autotrace on
Set linesize 1000
Set timing on
Select sum(object_id) from t;
SUM(OBJECT_ID)
--------------
4246036815
Elapsed: 00:00:00.06
Execution Plan
----------------------------------------------------------
Plan hash value: 1296839119
----------------------------------------------------------------------------------------
| Id| Operation | Name | Rows| Bytes | Cost (%cpu)| Time |
----------------------------------------------------------------------------------------
| 0| SELECT STATEMENT | |1 | 5 | 57 (0)| 00:00:01 |
| 1| SORT AGGREGATE | |1 | 5 | | |
| 2| INDEX FAST FULL SCAN| IDX1_OBJECT_ID | 91717| 447K| 57 (0)| 00:00:01 |
----------------------------------------------------------------------------------------
Statistics
----------------------------------------------------------
1recursive calls
0 dbblock gets
211 consistent gets
203 physical reads
0 redosize
552 bytes sent via sql*Net to client
551 bytes received via sql*Net from client
2sql*Net roundtrips to/from client
0sorts (memory)
0sorts (disk)
1 rowsprocessed
Database 12c 直接使用 索引了，没有进行全表扫描。因为列有空值不影响在索引中进行SUM和AVG运算的，所以优化器直接进行了优化。
一个SUM使用了211次逻辑读。
1. SUM和AVG的经典语法
sql> select sum(object_id),avg(object_id),count(*) from t;
SUM(OBJECT_ID) AVG(OBJECT_ID) COUNT(*)
-------------- -------------- ----------
4246036815 46294.9815 91717
Elapsed: 00:00:00.21
Execution Plan
----------------------------------------------------------
Plan hash value: 2966233522
---------------------------------------------------------------------------
| Id| Operation | Name | Rows | Bytes | Cost (%cpu)| Time |
---------------------------------------------------------------------------
| 0| SELECT STATEMENT | | 1 | 5| 429(1)| 00:00:01 |
| 1| SORT AGGREGATE | | 1 | 5 | | |
| 2| TABLE ACCESS FULL| T | 91717 | 447K|429 (1)| 00:00:01 |
---------------------------------------------------------------------------
Statistics
----------------------------------------------------------
1recursive calls
0 dbblock gets
1542 consistent gets
1539 physical reads
0 redosize
725 bytes sent via sql*Net to client
551 bytes received via sql*Net from client
2sql*Net roundtrips to/from client
0sorts (memory)
0sorts (disk)
1 rowsprocessed
sql> select sum(object_id),count(*) from t where object_id is not null;
SUM(OBJECT_ID) AVG(OBJECT_ID) COUNT(*)
-------------- -------------- ----------
4246036815 46294.9815 91717
Elapsed: 00:00:00.02
Execution Plan
----------------------------------------------------------
Plan hash value: 1296839119
----------------------------------------------------------------------------------------
| Id| Operation | Name | Rows| Bytes | Cost (%cpu)| Time |
----------------------------------------------------------------------------------------
| 0| SELECT STATEMENT | |1 | 5 | 57 (0)| 00:00:01 |
| 1| SORT AGGREGATE | |1 | 5 | | |
|* 2| INDEX FAST FULL SCAN| IDX1_OBJECT_ID | 91717| 447K|57 (0)| 00:00:01 |
----------------------------------------------------------------------------------------
Predicate Information (identified byoperation id):
---------------------------------------------------
2- filter("OBJECT_ID" IS NOT NULL)
Statistics
----------------------------------------------------------
1recursive calls
0 dbblock gets
211 consistent gets
0physical reads
0 redosize
725 bytes sent via sql*Net to client
551 bytes received via sql*Net from client
2sql*Net roundtrips to/from client
0sorts (memory)
0sorts (disk)
1 rowsprocessed
这个COUNT(*),SUM,AVG连续三个聚合语句写在一起，逻辑读和单个SUM运算性能一样都是221个逻辑读。因为一次扫描索引块可以同时解决三个问题。
原文链接：https://www.f2er.com/oracle/207069.html