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语句，需要足够的优化。
原文链接：https://www.f2er.com/oracle/207068.html