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个逻辑读。因为一次扫描索引块可以同时解决三个问题。

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!