索引系列十一--索引特性之有序与存列值优化max

--MAX/MIN 的索引优化

drop table t purge;

create table t as select * from dba_objects;

update t set object_id=rownum;

alter table t add constraint pk_object_id primary key (OBJECT_ID);

set autotrace on

set linesize 1000

select max(object_id) from t;

执行计划

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

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

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

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

|   1 |  SORT AGGREGATE            |              |     1 |    13 |            |          |

|   2 |   INDEX FULL SCAN (MIN/MAX)| PK_OBJECT_ID |     1 |    13 |     2   (0)| 00:00:01 |

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

统计信息

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

          0  recursive calls

          0  db block gets

          2  consistent gets

          0  physical reads

          0  redo size

        431  bytes sent via SQL*Net to client

        415  bytes received via SQL*Net from client

          2  SQL*Net roundtrips to/from client

          0  sorts (memory)

          0  sorts (disk)

          1  rows processed

--最小值老师的试验就无需展现执行计划结果了，必然和最大值的执行计划一样！          

select min(object_id) from t;

--如果没用到索引的情况是如下,请看看执行计划有何不同，请看看代价和逻辑读的差异！

select /*+full(t)*/ max(object_id) from t;

执行计划

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

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

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

|   0 | SELECT STATEMENT   |      |     1 |    13 |   292   (1)| 00:00:04 |

|   1 |  SORT AGGREGATE    |      |     1 |    13 |            |          |

|   2 |   TABLE ACCESS FULL| T    | 92407 |  1173K|   292   (1)| 00:00:04 |

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

统计信息

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

          0  recursive calls

          0  db block gets

       1047  consistent gets

          0  physical reads

          0  redo size

        431  bytes sent via SQL*Net to client

        415  bytes received via SQL*Net from client

          2  SQL*Net roundtrips to/from client

          0  sorts (memory)

          0  sorts (disk)

          1  rows processed

---另外，可以做如下试验观察在有索引的情况下，随这记录数增加，性能差异是否明显？

set autotrace off

drop table t_max purge;

create table t_max as select * from dba_objects;

insert into t_max select * from t_max;

insert into t_max select * from t_max;

insert into t_max select * from t_max;

insert into t_max select * from t_max;

insert into t_max select * from t_max;

select count(*) from t_max;

create index idx_t_max_obj on t_max(object_id);

set autotrace on 

select max(object_id) from t_max;

执行计划

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

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

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

|   0 | SELECT STATEMENT           |               |     1 |    13 |     3   (0)| 00:00:01 |

|   1 |  SORT AGGREGATE            |               |     1 |    13 |            |          |

|   2 |   INDEX FULL SCAN (MIN/MAX)| IDX_T_MAX_OBJ |     1 |    13 |     3   (0)| 00:00:01 |

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

统计信息

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

          0  recursive calls

          0  db block gets

          3  consistent gets

          0  physical reads

          0  redo size

        431  bytes sent via SQL*Net to client

        415  bytes received via SQL*Net from client

          2  SQL*Net roundtrips to/from client

          0  sorts (memory)

          0  sorts (disk)

          1  rows processed

/*

object_id如果允许为空，加个索引后，会走INDEX FULL SCAN (MIN/MAX)高效算法吗，

当然会了！取最大最小还怕啥空值？

*/ 

drop table t purge;

create table t as select * from dba_objects ;

create index idx_object_id on t(object_id);

set autotrace on

set linesize 1000

select max(object_id) from t;