select max(),min()为什么要全表扫描？

wendy

版本：oracle 10.2.0.4(64bit).

SQL> set autotrace on;
SQL> select min(income_time) from AGG_QUERY_SESSION_DETAIL;

MIN(INCOME_TIME)
-------------------
2011-01-01 00:00:00


Execution Plan
----------------------------------------------------------

-----------------------------------------------------------------------------------------
| Id  | Operation                  | Name                  | Rows  | Bytes | Cost (%CPU)|
-----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT           |                       |     1 |     8 |   743K  (2)|
|   1 |  SORT AGGREGATE            |                       |     1 |     8 |            |
|   2 |   INDEX FULL SCAN (MIN/MAX)| IDX_QSD_INCOME_TIME11 |   272M|  2081M|            |
-----------------------------------------------------------------------------------------

Note
-----
   - 'PLAN_TABLE' is old version


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

SQL> select max(income_time) from AGG_QUERY_SESSION_DETAIL;

MAX(INCOME_TIME)
-------------------
2013-07-18 23:59:40


Execution Plan
----------------------------------------------------------

-----------------------------------------------------------------------------------------
| Id  | Operation                  | Name                  | Rows  | Bytes | Cost (%CPU)|
-----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT           |                       |     1 |     8 |   743K  (2)|
|   1 |  SORT AGGREGATE            |                       |     1 |     8 |            |
|   2 |   INDEX FULL SCAN (MIN/MAX)| IDX_QSD_INCOME_TIME11 |   272M|  2081M|            |
-----------------------------------------------------------------------------------------

Note
-----
   - 'PLAN_TABLE' is old version


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

SQL> select max(income_time),min(income_time) from AGG_QUERY_SESSION_DETAIL;

MAX(INCOME_TIME)    MIN(INCOME_TIME)
------------------- -------------------
2013-07-18 23:59:40 2011-01-01 00:00:00


Execution Plan
----------------------------------------------------------

------------------------------------------------------------------------------------
| Id  | Operation          | Name                     | Rows  | Bytes | Cost (%CPU)|
------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |                          |     1 |     8 |   743K  (2)|
|   1 |  SORT AGGREGATE    |                          |     1 |     8 |            |
|   2 |   TABLE ACCESS FULL| AGG_QUERY_SESSION_DETAIL |   272M|  2081M|   743K  (2)|
------------------------------------------------------------------------------------

Note
-----
   - 'PLAN_TABLE' is old version


Statistics
----------------------------------------------------------
          2  recursive calls
          3  db block gets
    4273288  consistent gets
    4273185  physical reads
        132  redo size
        610  bytes sent via SQL*Net to client
        492  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(income_time) from table; 走索引；
select max(income_time) from table; 走索引。
select max(income_time),min(income_time) from table;全表扫描。为什么会这样呢？请赐教！

wendy

环境：oracle 10.2.0.4(64bit).

Stone

问题很有意思啊 :)

可以参考这篇文章："http://richardfoote.wordpress.com/category/index-full-scan-minmax/"
主要原因摘录如下：

If you’re after either the minimum or the maximum of a column value and the column is indexed, the CBO can potentially use the Index Full Scan (Min/Max), which simply navigates to the first OR last leaf block in the index structure looking for the Min or Max value in question. Oracle can of course navigate to the first (left-most) or last (right-most) leaf blocks very easily by simply following the associated first/last pointers in the Root/Branch structure of the index. All things being equal and providing there haven’t been any subsequent deletes to empty out the index entries from these leaf blocks, Oracle can very quickly determine the minimum or maximum value of the column.

However, the Index Full Scan (Min/Max) can only visit one side of the index, not both. Therefore, if you want both the minimum and the maximum column value, an Index Full Scan (Min/Max) is not viable and the CBO is forced to look for other alternatives. It sounds like such a trivial thing to implement but that’s how it goes. I do remember way back when Oracle9i was released and the introduction of the Index Skip Scan I thought perhaps Oracle might also soon introduce an index skip scan version of Min/Max (as it basically just needs to “skip” all the index leaf blocks in the “middle” of the index via another lookup of the index), but it was not to be.

特别注意如下描述：

Then an (expensive) Full Table Scan is likely the way to go. However, if the column has a NOT NULL constraint and the index is indeed smaller than the parent table, then:

正如下面：

1. SQL> alter table t1 modify object_id not null;
   
2. 
   
3. Table altered.
   
4. 
   
5. SQL> select max(object_id),min(object_id) from t1;
   
6. 
   
7. MAX(OBJECT_ID) MIN(OBJECT_ID)
   
8. -------------- --------------
   
9.          76200                    2
   
10. 
    
11. 
    
12. Execution Plan
    
13. ----------------------------------------------------------
    
14. Plan hash value: 1447436376
    
15. 
    
16. --------------------------------------------------------------------------------
    
17. | Id  | Operation              | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
    
18. --------------------------------------------------------------------------------
    
19. |   0 | SELECT STATEMENT      |        |     1 |    13 |    50         (0)| 00:00:01 |
    
20. |   1 |  SORT AGGREGATE       |        |     1 |    13 |            |               |
    
21. |   2 |   INDEX FAST FULL SCAN| T1_IDX | 97916 |  1243K|    50         (0)| 00:00:01 |
    
22. --------------------------------------------------------------------------------
    
23. 
    
24. Note
    
25. -----
    
26.    - dynamic sampling used for this statement (level=2)
    
27. 
    
28. 
    
29. Statistics
    
30. ----------------------------------------------------------
    
31.          36  recursive calls
    
32.           0  db block gets
    
33.         290  consistent gets
    
34.         165  physical reads
    
35.           0  redo size
    
36.         500  bytes sent via SQL*Net to client
    
37.         419  bytes received via SQL*Net from client
    
38.           2  SQL*Net roundtrips to/from client
    
39.           6  sorts (memory)
    
40.           0  sorts (disk)
    
41.           1  rows processed

复制代码

Good luck.

wendy

谢谢，非常感谢，补了一课。

wind

比较有意思。