Oracle 为什么优化器不选择具有KEEP功能的索引全/范围扫描 (最小/最大)

你好，

我有一个函数MAX(b) 保持 (DENSE_RANK最后一个顺序由a)，并希望获得这样的执行计划:

----------------------------------------------------
| Id  | Operation                   | Name | Rows  |
----------------------------------------------------
|   0 | SELECT STATEMENT            |      |     1 |
|   1 |  TABLE ACCESS BY USER ROWID | T    |     1 |
|   2 |   SORT AGGREGATE            |      |     1 |
|   4 |    INDEX FULL SCAN (MIN/MAX)| PK_T |     1 |
----------------------------------------------------
复制

可以玩的脚本:

CREATE TABLE t(a NUMBER CONSTRAINT PK_T PRIMARY KEY, b NUMBER)
/
EXPLAIN PLAN FOR
  SELECT MAX(b) KEEP(DENSE_RANK LAST ORDER BY a)
    FROM t
/
SELECT * FROM TABLE(Dbms_Xplan.Display(format => 'basic +rows'))
/
EXPLAIN PLAN FOR
  SELECT b
    FROM t
   WHERE a = (SELECT MAX(a) FROM t)
/
SELECT * FROM TABLE(Dbms_Xplan.Display(format => 'basic +rows'))
/
EXPLAIN PLAN FOR
  SELECT b
    FROM t
   WHERE ROWID = (SELECT MAX(ROWID) KEEP(DENSE_RANK LAST ORDER BY a) FROM t)
/
SELECT * FROM TABLE(Dbms_Xplan.Display(format => 'basic +rows'))
/
DROP TABLE t PURGE
/
复制

正如您在第一个查询优化器中看到的，根本不使用索引。

第二次尝试几乎可以，但是索引有两次扫描。
应该没有必要。

第三次尝试应该扫描索引一次，然后通过rowid获取记录。
但是优化器拒绝使用索引。
所有必要的数据都在索引中，以查找最后一个条目并返回rowid。
但它不起作用。为什么？

我已经尝试了索引中的多个列。
然后，优化器选择索引范围扫描，由谓词在第一个索引列，但不使用索引范围扫描 (最小/最大)
Sript:

CREATE TABLE t(a NUMBER, b NUMBER, c NUMBER, CONSTRAINT PK_T PRIMARY KEY(a, b))
/
EXPLAIN PLAN FOR
  SELECT MAX(c) KEEP(DENSE_RANK LAST ORDER BY a, b)
    FROM t
   WHERE a = 1
/
SELECT * FROM TABLE(Dbms_Xplan.Display(format => 'basic +rows'))
/
EXPLAIN PLAN FOR
  SELECT c
    FROM t
   WHERE a = 1
     AND b = (SELECT MAX(b) FROM t WHERE a = 1)
/
SELECT * FROM TABLE(Dbms_Xplan.Display(format => 'basic +rows'))
/
EXPLAIN PLAN FOR
  SELECT b
    FROM t
   WHERE ROWID = (SELECT MAX(ROWID) KEEP(DENSE_RANK LAST ORDER BY a, b) FROM t WHERE a = 1)
/
SELECT * FROM TABLE(Dbms_Xplan.Display(format => 'basic +rows'))
/
DROP TABLE t PURGE
/
复制

在你的第一个例子中，主键索引只包含列a。所以为了执行:

  SELECT MAX(b) KEEP(DENSE_RANK LAST ORDER BY a)
    FROM t
复制

Oracle数据库必须访问表以获取列b的值。因此，如果使用索引，则必须读取表和索引。您没有where子句，因此它无法在索引本身中进行任何过滤。所以它必须全面扫描它:

EXPLAIN PLAN FOR
  SELECT /*+ index (t PK_T) */MAX(b) KEEP(DENSE_RANK LAST ORDER BY a)
    FROM t
/
SELECT * FROM TABLE(Dbms_Xplan.Display(format => 'basic +rows'))
/

-------------------------------------------------------------  
| Id  | Operation                            | Name | Rows  |  
-------------------------------------------------------------  
|   0 | SELECT STATEMENT                     |      |     1 |  
|   1 |  SORT AGGREGATE                      |      |     1 |  
|   2 |   TABLE ACCESS BY INDEX ROWID BATCHED| T    |     1 |  
|   3 |    INDEX FULL SCAN                   | PK_T |     1 |  
------------------------------------------------------------- 
复制

因此，您已经将全表扫描换成了rowid的全索引扫描表访问。在大多数情况下，这将是更多的工作，因为我们可以看到我们是否加载了数据表并获得了执行计划。注意缓冲区列:

insert into t 
with rws as (
  select level a, level b from dual connect by level <= 10000
)
  select a, b from rws;
commit;
exec dbms_stats.gather_table_stats(user, 't');


SELECT /*+ gather_plan_statistics index (t PK_T) */MAX(b) KEEP(DENSE_RANK LAST ORDER BY a)
FROM t
/
select * from table(dbms_xplan.display_cursor(null, null, 'IOSTATS LAST'));
/

-------------------------------------------------------------------------------------------------------  
| Id  | Operation                            | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  
-------------------------------------------------------------------------------------------------------  
|   0 | SELECT STATEMENT                     |      |      1 |        |      1 |00:00:00.01 |      37 |  
|   1 |  SORT AGGREGATE                      |      |      1 |      1 |      1 |00:00:00.01 |      37 |  
|   2 |   TABLE ACCESS BY INDEX ROWID BATCHED| T    |      1 |  10000 |  10000 |00:00:00.04 |      37 |  
|   3 |    INDEX FULL SCAN                   | PK_T |      1 |  10000 |  10000 |00:00:00.01 |      19 |  
-------------------------------------------------------------------------------------------------------

SELECT /*+ gather_plan_statistics */MAX(b) KEEP(DENSE_RANK LAST ORDER BY a)
FROM t
/
select * from table(dbms_xplan.display_cursor(null, null, 'LAST BASIC IOSTATS'));
/
----------------------------------------------------------------------------------------------  
| Id  | Operation          | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads  |  
----------------------------------------------------------------------------------------------  
|   0 | SELECT STATEMENT   |      |      1 |        |      1 |00:00:00.01 |      23 |      1 |  
|   1 |  SORT AGGREGATE    |      |      1 |      1 |      1 |00:00:00.01 |      23 |      1 |  
|   2 |   TABLE ACCESS FULL| T    |      1 |  10000 |  10000 |00:00:00.01 |      23 |      1 |  
---------------------------------------------------------------------------------------------- 
复制

请注意，索引表访问使用37个缓冲区。但是全表扫描只能访问23个。所以它又做了14次IO操作。

在你的第二个例子中:

  SELECT b
    FROM t
   WHERE a = (SELECT MAX(a) FROM t)
复制

表t出现两次。所以数据库会在计划中访问它两次!它必须先读取索引才能找到A的最大值。然后访问表 (使用索引) 以查找b的值。

在你的第三个例子中，优化器选择全表扫描可能是由于你的表是空的。如果没有行，无论您扫描索引还是表都没有任何区别: 没有什么可读的!

保持表加载数据从我以前的测试和优化器确实选择索引全扫描:

EXPLAIN PLAN FOR
  SELECT b
    FROM t
   WHERE ROWID = (SELECT MAX(ROWID) KEEP(DENSE_RANK LAST ORDER BY a) FROM t)
/
SELECT * FROM TABLE(Dbms_Xplan.Display(format => 'basic +rows'))
/

---------------------------------------------------  
| Id  | Operation                  | Name | Rows  |  
---------------------------------------------------  
|   0 | SELECT STATEMENT           |      |     1 |  
|   1 |  TABLE ACCESS BY USER ROWID| T    |     1 |  
|   2 |   SORT AGGREGATE           |      |     1 |  
|   3 |    INDEX FAST FULL SCAN    | PK_T | 10000 |  
--------------------------------------------------- 

SELECT /*+ gather_plan_statistics */b
FROM t
WHERE ROWID = (SELECT MAX(ROWID) KEEP(DENSE_RANK LAST ORDER BY a) FROM t);

select * from table(dbms_xplan.display_cursor(null, null, 'LAST BASIC IOSTATS'));

---------------------------------------------------------------------------------------------  
| Id  | Operation                  | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  
---------------------------------------------------------------------------------------------  
|   0 | SELECT STATEMENT           |      |      1 |        |      1 |00:00:00.01 |      25 |  
|   1 |  TABLE ACCESS BY USER ROWID| T    |      1 |      1 |      1 |00:00:00.01 |      25 |  
|   2 |   SORT AGGREGATE           |      |      1 |      1 |      1 |00:00:00.01 |      24 |  
|   3 |    INDEX FAST FULL SCAN    | PK_T |      1 |  10000 |  10000 |00:00:00.01 |      24 |  
---------------------------------------------------------------------------------------------
复制

你在第二组查询中遇到了同样的问题。是的，您在PK中添加了另一列。但是您还在表中添加了另一列，该列不是索引的一部分!而且您仍然没有数据，这很可能会导致 “常规” 计划。

还要注意，在一般情况下，你的查询是不等价的。功能:

MAX(b) KEEP(DENSE_RANK LAST ORDER BY a)
复制

按A对行进行排序，并找到最大值。然后，它检查具有此值的所有行，并返回其中B的最大值。

当你这样做的时候:

  SELECT b
    FROM t
   WHERE a = (SELECT MAX(a) FROM t)
复制

您找到A的最大值，然后为所有具有该值的行返回B。

并与:

  SELECT b
    FROM t
   WHERE ROWID = (SELECT MAX(ROWID) KEEP(DENSE_RANK LAST ORDER BY a) FROM t)
复制

您可以找到具有最大值的所有行。然后返回B，其中任何一个具有最高的rowid。

在A不是唯一的情况下，这些都可以返回不同的结果:

CREATE TABLE t(a NUMBER , b NUMBER)
/
insert into t values (1, 2);
insert into t values (1, 3);
insert into t values (1, 1);
commit;

  SELECT MAX(b) KEEP(DENSE_RANK LAST ORDER BY a)
    FROM t
/

MAX(B)KEEP(DENSE_RANKLASTORDERBYA)  
3 

  SELECT b
    FROM t
   WHERE a = (SELECT MAX(a) FROM t)
/

B  
2  
3  
1 

  SELECT b
    FROM t
   WHERE ROWID = (SELECT MAX(ROWID) KEEP(DENSE_RANK LAST ORDER BY a) FROM t)
/

B  
1  
复制