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

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

1recursive calls

0 dbblock gets

2consistent gets

1physical reads

0 redosize

550 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

使用了索引。

其实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

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

42recursive calls

0 db block gets

1564consistent gets

1539physical reads

0 redo size

629bytes sent via SQL*Net to client

551bytes 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

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

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

2SQL*Net roundtrips to/from client

0sorts (memory)

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

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

1recursive calls

0 dbblock gets

4 consistent gets

0physical reads

0 redosize

607 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

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

（编辑：李大同）

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