让“select min(xxx),max(xxx) from xxx”走索引

联系:QQ(5163721)

标题:让“select min(xxx),max(xxx) from xxx”走索引

作者:Lunar©版权所有[文章允许转载,但必须以链接方式注明源地址,否则追究法律责任.]

首先,做一个测试表:

[oracle@lunar /]$ ss

SQL*Plus: Release 11.2.0.3.0 Production on Sat Jun 7 21:29:52 2014

Copyright (c) 1982, 2011, Oracle.  All rights reserved.

Welcome Lunar's oracle world!

Love you , baby !

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning and Real Application Testing options

SYS@lunar>conn lunar/lunar
Welcome Lunar's oracle world!

Love you , baby !

Connected.
LUNAR@lunar>create table lunar as select * from dba_extents;

Table created.

Elapsed: 00:00:04.61

在extent_id列上创建非唯一索引:

LUNAR@lunar>create index idx_lunar_extent_id on lunar(extent_id) ;

Index created.

Elapsed: 00:00:00.02

LUNAR@lunar>EXEC DBMS_STATS.GATHER_TABLE_STATS(OWNNAME => 'LUNAR',TABNAME => 'LUNAR',DEGREE => 5,
 2  CASCADE => TRUE,ESTIMATE_PERCENT => DBMS_STATS.AUTO_SAMPLE_SIZE);

PL/SQL procedure successfully completed.

Elapsed: 00:00:01.45

下面的语句没有走索引:

LUNAR@lunar>select min(extent_id),max(extent_id) from lunar;

MIN(EXTENT_ID) MAX(EXTENT_ID)
-------------- --------------
             0             61

Elapsed: 00:00:00.01

Execution Plan
----------------------------------------------------------
Plan hash value: 291688323

----------------------------------------------------------------------------
| Id  | Operation          | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |       |     1 |     3 |    16   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE    |       |     1 |     3 |            |          |
|   2 |   TABLE ACCESS FULL| LUNAR |  5223 | 15669 |    16   (0)| 00:00:01 |
----------------------------------------------------------------------------


Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
         51  consistent gets
          0  physical reads
          0  redo size
        610  bytes sent via SQL*Net to client
        524  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

LUNAR@lunar>

手工指定IDX_LUNAR_EXTENT_ID时,也不走索引:

LUNAR@lunar>select /*+ index(lunar IDX_LUNAR_EXTENT_ID) */ min(extent_id),max(extent_id) from lunar;

MIN(EXTENT_ID) MAX(EXTENT_ID)
-------------- --------------
             0             61

Elapsed: 00:00:00.01

Execution Plan
----------------------------------------------------------
Plan hash value: 291688323

----------------------------------------------------------------------------
| Id  | Operation          | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |       |     1 |     3 |    16   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE    |       |     1 |     3 |            |          |
|   2 |   TABLE ACCESS FULL| LUNAR |  5223 | 15669 |    16   (0)| 00:00:01 |
----------------------------------------------------------------------------


Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
         50  consistent gets
          0  physical reads
          0  redo size
        610  bytes sent via SQL*Net to client
        524  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

LUNAR@lunar>

指定该列为非空时,可以走索引(Oracle的索引不保存空值):

LUNAR@lunar>alter table lunar modify extent_id not null;

Table altered.

Elapsed: 00:00:00.24
LUNAR@lunar>

可以看到,无论是否使用hint都走索引了:

LUNAR@lunar>set autotrace on
LUNAR@lunar>select /*+ index(lunar IDX_LUNAR_EXTENT_ID) */ min(extent_id),max(extent_id) from lunar;

MIN(EXTENT_ID) MAX(EXTENT_ID)
-------------- --------------
             0             61

Elapsed: 00:00:00.00

Execution Plan
----------------------------------------------------------
Plan hash value: 1897601393

----------------------------------------------------------------------------------------
| Id  | Operation        | Name                | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT |                     |     1 |     3 |    11   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE  |                     |     1 |     3 |            |          |
|   2 |   INDEX FULL SCAN| IDX_LUNAR_EXTENT_ID |  5223 | 15669 |    11   (0)| 00:00:01 |
----------------------------------------------------------------------------------------
注意: 使用hint时,走的是“INDEX FULL SCAN”

Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
         11  consistent gets
          0  physical reads
          0  redo size
        610  bytes sent via SQL*Net to client
        524  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

LUNAR@lunar>select min(extent_id),max(extent_id) from lunar;

MIN(EXTENT_ID) MAX(EXTENT_ID)
-------------- --------------
             0             61

Elapsed: 00:00:00.01

Execution Plan
----------------------------------------------------------
Plan hash value: 2695027278

---------------------------------------------------------------------------------------------
| Id  | Operation             | Name                | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |                     |     1 |     3 |     4   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE       |                     |     1 |     3 |            |          |
|   2 |   INDEX FAST FULL SCAN| IDX_LUNAR_EXTENT_ID |  5223 | 15669 |     4   (0)| 00:00:01 |
---------------------------------------------------------------------------------------------
注意: 不使用hint时,走的是“INDEX FAST FULL SCAN”


Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
         15  consistent gets
          0  physical reads
          0  redo size
        610  bytes sent via SQL*Net to client
        524  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

LUNAR@lunar>

再次验证,依然这个结果:
LUNAR@lunar>alter table lunar modify extent_id null;

Table altered.

Elapsed: 00:00:00.01

LUNAR@lunar>select min(extent_id),max(extent_id) from lunar;

MIN(EXTENT_ID) MAX(EXTENT_ID)
-------------- --------------
             0             61

Elapsed: 00:00:00.01

Execution Plan
----------------------------------------------------------
Plan hash value: 291688323

----------------------------------------------------------------------------
| Id  | Operation          | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |       |     1 |     3 |    16   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE    |       |     1 |     3 |            |          |
|   2 |   TABLE ACCESS FULL| LUNAR |  5223 | 15669 |    16   (0)| 00:00:01 |
----------------------------------------------------------------------------


Statistics
----------------------------------------------------------
         22  recursive calls
          0  db block gets
         75  consistent gets
          0  physical reads
          0  redo size
        610  bytes sent via SQL*Net to client
        524  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          6  sorts (memory)
          0  sorts (disk)
          1  rows processed

LUNAR@lunar>

单独查询最小值或者最大值时,也可以走索引,采用“INDEX FULL SCAN (MIN/MAX)”:

LUNAR@lunar>select min(extent_id) from lunar;

MIN(EXTENT_ID)
--------------
             0

Elapsed: 00:00:00.01

Execution Plan
----------------------------------------------------------
Plan hash value: 4158629481

--------------------------------------------------------------------------------------------------
| Id  | Operation                  | Name                | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT           |                     |     1 |     3 |     2   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE            |                     |     1 |     3 |            |          |
|   2 |   INDEX FULL SCAN (MIN/MAX)| IDX_LUNAR_EXTENT_ID |     1 |     3 |     2   (0)| 00:00:01 |
--------------------------------------------------------------------------------------------------


Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
          2  consistent gets
          0  physical reads
          0  redo size
        531  bytes sent via SQL*Net to client
        524  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

LUNAR@lunar>select max(extent_id) from lunar;

MAX(EXTENT_ID)
--------------
            61

Elapsed: 00:00:00.01

Execution Plan
----------------------------------------------------------
Plan hash value: 4158629481

--------------------------------------------------------------------------------------------------
| Id  | Operation                  | Name                | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT           |                     |     1 |     3 |     2   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE            |                     |     1 |     3 |            |          |
|   2 |   INDEX FULL SCAN (MIN/MAX)| IDX_LUNAR_EXTENT_ID |     1 |     3 |     2   (0)| 00:00:01 |
--------------------------------------------------------------------------------------------------


Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
          2  consistent gets
          0  physical reads
          0  redo size
        532  bytes sent via SQL*Net to client
        524  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

LUNAR@lunar>

假设,在不能修改表结构的情况下,怎么才能让“select min(extent_id),max(extent_id) from lunar”走索引呢?
参考了网上的一些建议,改写sql如下:

LUNAR@lunar>select 
  2  (select min(extent_id) from lunar) as min_extent_id,
  3  (select max(extent_id) from lunar) as max_extent_id
  4  from dual;

MIN_EXTENT_ID MAX_EXTENT_ID
------------- -------------
            0            61

Elapsed: 00:00:00.01

Execution Plan
----------------------------------------------------------
Plan hash value: 778639039

--------------------------------------------------------------------------------------------------
| Id  | Operation                  | Name                | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT           |                     |     1 |       |     2   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE            |                     |     1 |     3 |            |          |
|   2 |   INDEX FULL SCAN (MIN/MAX)| IDX_LUNAR_EXTENT_ID |     1 |     3 |     2   (0)| 00:00:01 |
|   3 |  SORT AGGREGATE            |                     |     1 |     3 |            |          |
|   4 |   INDEX FULL SCAN (MIN/MAX)| IDX_LUNAR_EXTENT_ID |     1 |     3 |     2   (0)| 00:00:01 |
|   5 |  FAST DUAL                 |                     |     1 |       |     2   (0)| 00:00:01 |
--------------------------------------------------------------------------------------------------


Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
          4  consistent gets
          0  physical reads
          0  redo size
        608  bytes sent via SQL*Net to client
        524  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

LUNAR@lunar>
此条目发表在 Performence Tuning 分类目录,贴了 标签。将固定链接加入收藏夹。

发表评论

电子邮件地址不会被公开。 必填项已用 * 标注

您可以使用这些 HTML 标签和属性: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>