sql - 基于综合指数获取最新值(value)的更有效方法？

Question

我有一个名为 SAMPLE_TABLE 的表，它具有以下列，CAR_TYPE、COLOR 和 CAR_BRAND 构成复合索引。

 VALUE_ID      VALUE      CAR_TYPE     COLOR     SUBMIT_DT      CAR_BRAND
   1            10        Sedan        Blue      3/7/2019       Ford
   2            70        Sedan        Blue      3/6/2019       Ford
   3            20        Sedan        Blue      3/5/2019       Ford
   4            77         SUV         Red       3/7/2019       Volvo
   5           100         SUV         Red       3/1/2019       Volvo

有没有一种方法可以编写一种更有效的方法来查询与 LATEST SUBMIT_DT 相关的值？将来，TABLE 将有数百万行数据，因此我需要找到可以查询的运行时间/成本最低的查询。

例如，以下是我在查询 Blue Ford Sedan 时希望在结果集中显示的内容:

 VALUE
 10

以下是我迄今为止所拥有的:

 SELECT value 
   FROM (
          SELECT *
            FROM TABLE
           WHERE CAR_TYPE = rCar_Type
             AND COLOR = rColor
             AND CAR_BRAND = rCar_Brand
            ORDER by submit_dt desc
        )
   WHERE rownum = 1;

这是低效的吗？

提前致谢

Answer 1

哇...已经有很多答案了，但我认为其中一些错过了我认为您的问题的重点。

您的表中将有数百万行，并且 (CAR_TYPE, COLOR, CAR_BRAND) 上的复合索引将不是很有选择性。您正在寻找一种方法来获取复合索引中给定条目的最后一个 SUBMIT_DT 的行，而无需通读该索引中的所有匹配项。

答案:将 SUBMIT_DT DESC 添加到您的复合索引

让我们设置一个测试:

create table matt_objects as select * from dba_objects;

-- This is our analog of your composite index
create index matt_objects_n1 on matt_objects ( object_type, owner );

exec dbms_stats.gather_table_stats(user,'MATT_OBJECTS');

现在，让我们自动跟踪这个语句:

select object_name
from   matt_objects
where  object_type = 'TABLE'
and    owner = 'INV'
order by last_ddl_time desc
fetch first 1 row only;

---------------------------------------------------------------------------------------------------------
| Id  | Operation                             | Name            | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                      |                 |     1 |    88 |    17   (6)| 00:00:01 |
|*  1 |  VIEW                                 |                 |     1 |    88 |    17   (6)| 00:00:01 |
|*  2 |   WINDOW SORT PUSHED RANK             |                 |   162 |  7290 |    17   (6)| 00:00:01 |
|   3 |    TABLE ACCESS BY INDEX ROWID BATCHED| MATT_OBJECTS    |   162 |  7290 |    16   (0)| 00:00:01 |
|*  4 |     INDEX RANGE SCAN                  | MATT_OBJECTS_N1 |   162 |       |     3   (0)| 00:00:01 |
---------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("from$_subquery$_002"."rowlimit_$$_rownumber"<=1)
   2 - filter(ROW_NUMBER() OVER ( ORDER BY INTERNAL_FUNCTION("LAST_DDL_TIME") DESC )<=1)
   4 - access("OBJECT_TYPE"='TABLE' AND "OWNER"='INV')

结果(来自自动跟踪):72 一致读取缓冲区获得

现在，让我们用一个可以帮助我们更多的索引替换您的复合索引:

drop index matt_objects_n1;

create index matt_objects_n1 on matt_objects ( object_type, owner, last_ddl_time desc );

exec dbms_stats.gather_table_stats(user,'MATT_OBJECTS');

.. 让我们再次自动跟踪相同的语句:

--------------------------------------------------------------------------------------------------
| Id  | Operation                      | Name            | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT               |                 |     1 |    88 |    54   (2)| 00:00:01 |
|   1 |  SORT ORDER BY                 |                 |     1 |    88 |    54   (2)| 00:00:01 |
|*  2 |   VIEW                         |                 |     1 |    88 |    53   (0)| 00:00:01 |
|*  3 |    WINDOW NOSORT STOPKEY       |                 |   162 |  7290 |    53   (0)| 00:00:01 |
|   4 |     TABLE ACCESS BY INDEX ROWID| MATT_OBJECTS    |   162 |  7290 |    53   (0)| 00:00:01 |
|*  5 |      INDEX RANGE SCAN          | MATT_OBJECTS_N1 |   162 |       |     3   (0)| 00:00:01 |
--------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter("from$_subquery$_002"."rowlimit_$$_rownumber"<=1)
   3 - filter(ROW_NUMBER() OVER ( ORDER BY SYS_OP_DESCEND("LAST_DDL_TIME"))<=1)
   5 - access("OBJECT_TYPE"='TABLE' AND "OWNER"='INV')

结果(来自自动跟踪):5 次一致读取获得

该指数帮助很大。注意到计划不同了吗？ “WINDOW SORT PUSHED RANK”已被“WINDOW NOSORT STOPKEY”取代。由于索引已经按您想要的方式排序(按降序)，Oracle 知道它可以按顺序读取索引行并在第一个行之后停止——以更少的努力完成查询。

有趣的是，尽管第二次查询的性能好 10 倍以上，但第二次查询的成本高于第一次查询的成本。它只是向您展示“成本”是一个估计值，有时应该有所保留。