19.读书笔记收获不止Oracle之 索引MAX和MIN优化

19.读书笔记收获不止Oracle之 索引MAX和MIN优化

1.  示例一

SQL> drop table t purge;

SQL> create table t as select * fromdba_objects;

SQL> create index idx1_object_id on t(object_id);

使用MAX看看，能不能用到索引。

set autotrace traceonly;

set timing on;

select max(object_id) from t;

Elapsed: 00:00:00.02

 

Execution Plan

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

Plan hash value: 692082706

 

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

| Id | Operation                    | Name             | Rows | Bytes | Cost (%CPU)| Time     |

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

|   0| SELECT STATEMENT        |                          |           1 |            5 |            2  (0)| 00:00:01 |

|   1|  SORT AGGREGATE       |                          |          1 |            5 |                     |         |

|   2|   INDEX FULL SCAN (MIN/MAX)| IDX1_OBJECT_ID |    1 |           5 |            2  (0)| 00:00:01 |

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

 

 

Statistics

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

             1 recursive calls

             0  dbblock gets

             2 consistent gets

             1 physical reads

             0  redosize

           550  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

 

使用了索引。

其实SUM/AVG等聚合查询必须要列为空方可用到索引；MAX/MIN时无论列是否为空都可以用到索引。

 

2.  MAX/MIN

在INDEX FULL SCAN(MIN/MAX)时，MAX取值只需要往最右边的叶子块去瞧一瞧就行了。

MAX取值一定在最右边的块上。

MIN取值，往最左边的块里去看一眼就好了，块里的第一行记录就是。

INDEX FULL SCAN就是这个思路。

无论记录如何增大，INDEX FULL SCAN (MIN/MAX)速度都基本不变。如果对查询的列没有做索引，那么该速度将会非常慢。

3.  MAX/MIN性能陷阱

Selectmin(object_id),max(object_id) from t;

Elapsed:00:00:00.22

 

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

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

            42 recursive calls

            0  db block gets

       1564 consistent gets

       1539 physical reads

            0  redo size

           629 bytes sent via SQL*Net to client

           551 bytes received via SQL*Net from client

            2  SQL*Net roundtrips to/fromclient

            3  sorts (memory)

            0  sorts (disk)

            1  rows processed

 

进行了全表扫描。

增加is not null看看

SQL> Select min(object_id),max(object_id) from t where object_idis not null;

Elapsed: 00:00:00.05

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

           202  physical reads

             0  redosize

           629  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

 

走的是索引是INDEX FAST FULL SCAN 而非INDEX FULL SCAN(MIN/MAX)。

ORACLE无法用INDEX FULL SCAN(MIN/MAX)同时在最左边和最右边读取。

 

执行如下：

SQL> select max,min from (select max(object_id) max from t)a,(select min(object_id) min from t) b;

 

Elapsed: 00:00:00.00

 

Execution Plan

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

Plan hash value: 251798682

 

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

| Id | Operation                      | Name          | Rows | Bytes | Cost (%CPU)| Time     |

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

|   0| SELECT STATEMENT          |                       |    1 |    26 |     4    (0)|00:00:01 |

|   1|  NESTED LOOPS                           |                       |    1 |    26 |     4    (0)|00:00:01 |

|   2|   VIEW                                 |                       |    1 |    13 |     2    (0)|00:00:01 |

|   3|    SORT AGGREGATE               |                       |    1 |     5 |          |                |

|   4|     INDEX FULL SCAN (MIN/MAX)|IDX1_OBJECT_ID |     1 |     5 |    2        (0)| 00:00:01 |

|   5|   VIEW                                 |                       |    1 |    13 |     2    (0)|00:00:01 |

|   6|    SORT AGGREGATE               |                       |    1 |     5 |          |                |

|   7|     INDEX FULL SCAN (MIN/MAX)|IDX1_OBJECT_ID |     1 |     5 |    2        (0)| 00:00:01 |

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

 

 

Statistics

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

             1 recursive calls

             0  dbblock gets

             4  consistent gets

             0 physical reads

             0  redosize

           607  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

 

逻辑读总数是4. 所以，对于SQL语句，需要足够的优化。