18.读书笔记收获不止Oracle之 索引SUM和AVG优化

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

----------------------------------------------------------

             1 recursive 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

             2 SQL*Net roundtrips to/from client

             0 sorts (memory)

             0 sorts (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

----------------------------------------------------------

             1 recursive 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

             2 SQL*Net roundtrips to/from client

             0 sorts (memory)

             0 sorts (disk)

             1  rowsprocessed

 

SQL> select sum(object_id),avg(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

----------------------------------------------------------

             1 recursive calls

             0  dbblock gets

           211  consistent gets

             0 physical reads

             0  redosize

           725  bytes sent via SQL*Net to client

           551  bytes received via SQL*Net from client

             2 SQL*Net roundtrips to/from client

             0 sorts (memory)

             0 sorts (disk)

             1  rowsprocessed

 

这个COUNT(*),SUM,AVG连续三个聚合语句写在一起，逻辑读和单个SUM运算性能一样都是221个逻辑读。因为一次扫描索引块可以同时解决三个问题。