Oracle 解释索引扫描计划

嗨，汤姆，
我已经更新了计划。我不知道如何提交有关所提出问题的更多信息，这就是为什么再次提出问题。

这是参考以下链接中提出的问题
https://asktom.oracle.com/pls/apex/asktom.search?tag=index-scan-on-timestamp-column

按照您的建议更改索引顺序后，附加我正在使用的实际查询的说明。

WITH LABEL_COUNT AS ( 
    
        select     /*+ gather_plan_statistics  */     CLPT.CLIENT_ID AS CLIENT_ID , CLPT.GROWER_ID AS  CGRW_ID,CLPT.SHIPPER_LOT_ID,SUM(CLPT.LABEL_COUNT) AS VALUE
        FROM T_CASE_LABEL_PRINT_TRNS CLPT
        WHERE  CLPT.CLIENT_ID = 3104 and CLPT.STATUS = 1 
        AND CLPT.CREATED_ON >= TO_TIMESTAMP('01/01/2018 00:00:00', 'MM/DD/YYYY HH24:MI:SS')  
        AND CLPT.CREATED_ON <= TO_TIMESTAMP('12/29/2018 23:59:59', 'MM/DD/YYYY HH24:MI:SS') 
        GROUP BY  CLPT.CLIENT_ID ,CLPT.GROWER_ID,CLPT.SHIPPER_LOT_ID
        
         ) 
         
        SELECT  NVL(SLGRWR.ID, 0) AS entityId, NVL(SLGRWR.NAME, 'Unspecified') AS label , SUM(LC.VALUE)
        FROM LABEL_COUNT LC
        INNER JOIN T_SHIPPER_LOTS SL ON LC.SHIPPER_LOT_ID = SL.ID AND LC.CLIENT_ID = SL.CLIENT_ID
        LEFT JOIN T_GROWERS SLGRWR ON   (LC.CGRW_ID = SLGRWR.ID  OR SL.GROWER_ID = SLGRWR.ID ) AND LC.CLIENT_ID=SLGRWR.CLIENT_ID
        GROUP BY NVL(SLGRWR.ID, 0), NVL(SLGRWR.NAME, 'Unspecified')
        ORDER BY SUM(LC.VALUE) DESC
        FETCH FIRST 10 ROWS ONLY;
        
    select * 
from   table(dbms_xplan.display_cursor(null, null, 'ALLSTATS LAST'));


SQL_ID  5vw2uz51qv2w9, child number 1
-------------------------------------
WITH LABEL_COUNT AS (               select     /*+ 
gather_plan_statistics  */     CLPT.CLIENT_ID AS CLIENT_ID , 
CLPT.GROWER_ID AS  CGRW_ID,CLPT.SHIPPER_LOT_ID,SUM(CLPT.LABEL_COUNT) AS 
VALUE         FROM T_CASE_LABEL_PRINT_TRNS CLPT         WHERE  
CLPT.CLIENT_ID = 3104 and CLPT.STATUS = 1          AND CLPT.CREATED_ON 
>= TO_TIMESTAMP('01/01/2018 00:00:00', 'MM/DD/YYYY HH24:MI:SS')         
  AND CLPT.CREATED_ON <= TO_TIMESTAMP('12/29/2018 23:59:59', 
'MM/DD/YYYY HH24:MI:SS')          GROUP BY  CLPT.CLIENT_ID 
,CLPT.GROWER_ID,CLPT.SHIPPER_LOT_ID           )           SELECT  
NVL(SLGRWR.ID, 0) AS entityId, NVL(SLGRWR.NAME, 'Unspecified') AS label 
, SUM(LC.VALUE)         FROM LABEL_COUNT LC         INNER JOIN 
T_SHIPPER_LOTS SL ON LC.SHIPPER_LOT_ID = SL.ID AND LC.CLIENT_ID = 
SL.CLIENT_ID         LEFT JOIN T_GROWERS SLGRWR ON   (LC.CGRW_ID = 
SLGRWR.ID  OR SL.GROWER_ID = SLGRWR.ID ) AND 
LC.CLIENT_ID=SLGRWR.CLIENT_ID         GROUP BY NVL(SLGRWR.ID, 0), 
NVL(SLGRWR.NAME, 'Unspecified')         ORDER
 
Plan hash value: 3231218950
 
-------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                           | Name                    | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads  |  OMem |  1Mem | Used-Mem |
-------------------------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |                         |      1 |        |     10 |00:00:17.98 |     139K|    122K|       |       |          |
|   1 |  SORT ORDER BY                      |                         |      1 |     10 |     10 |00:00:17.98 |     139K|    122K|  2048 |  2048 | 2048  (0)|
|*  2 |   VIEW                              |                         |      1 |     10 |     10 |00:00:17.98 |     139K|    122K|       |       |          |
|*  3 |    WINDOW SORT PUSHED RANK          |                         |      1 |      3 |     10 |00:00:17.98 |     139K|    122K|  2048 |  2048 | 2048  (0)|
|   4 |     HASH GROUP BY                   |                         |      1 |      3 |     78 |00:00:17.98 |     139K|    122K|   898K|   898K| 1361K (0)|
|   5 |      NESTED LOOPS OUTER             |                         |      1 |  16538 |  16538 |00:00:17.54 |     139K|    122K|       |       |          |
|*  6 |       HASH JOIN                     |                         |      1 |  16538 |  16538 |00:00:17.31 |     125K|    122K|  2055K|  1934K| 1768K (0)|
|   7 |        VIEW                         |                         |      1 |  16539 |  16539 |00:00:17.06 |     122K|    122K|       |       |          |
|   8 |         HASH GROUP BY               |                         |      1 |  16539 |  16539 |00:00:17.04 |     122K|    122K|  2064K|  1501K| 1594K (0)|
|*  9 |          TABLE ACCESS FULL          | T_CASE_LABEL_PRINT_TRNS |      1 |    184K|    168K|00:00:19.50 |     122K|    122K|       |       |          |
|  10 |        INDEX FAST FULL SCAN         | INDX_SLOT_ID_CLN_GID    |      1 |    741K|    741K|00:00:00.42 |    2264 |      0 |       |       |          |
|  11 |       VIEW                          | VW_LAT_A8413AFA         |  16538 |      1 |   2384 |00:00:00.29 |   14306 |      0 |       |       |          |
|  12 |        CONCATENATION                |                         |  16538 |        |   2384 |00:00:00.25 |   14306 |      0 |       |       |          |
|* 13 |         FILTER                      |                         |  16538 |        |   2384 |00:00:00.06 |    7153 |      0 |       |       |          |
|* 14 |          TABLE ACCESS BY INDEX ROWID| T_GROWERS               |   2384 |      1 |   2384 |00:00:00.03 |    7153 |      0 |       |       |          |
|* 15 |           INDEX UNIQUE SCAN         | PK_GROWER               |   2384 |      1 |   2384 |00:00:00.01 |    4769 |      0 |       |       |          |
|* 16 |         TABLE ACCESS BY INDEX ROWID | T_GROWERS               |  16538 |      1 |      0 |00:00:00.11 |    7153 |      0 |       |       |          |
|* 17 |          INDEX UNIQUE SCAN          | PK_GROWER               |  16538 |      1 |   2384 |00:00:00.04 |    4769 |      0 |       |       |          |
-------------------------------------------------------------------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   2 - filter("from$_subquery$_007"."rowlimit_$$_rownumber"<=10)
   3 - filter(ROW_NUMBER() OVER ( ORDER BY SUM("LC"."VALUE") DESC )<=10)
   6 - access("LC"."SHIPPER_LOT_ID"="SL"."ID" AND "LC"."CLIENT_ID"="SL"."CLIENT_ID")
   9 - filter(("CLPT"."CREATED_ON">=TIMESTAMP' 2018-01-01 00:00:00.000000000' AND "CLPT"."CLIENT_ID"=3104 AND "CLPT"."CREATED_ON"<=TIMESTAMP' 
              2018-12-29 23:59:59.000000000' AND "CLPT"."STATUS"=1))
  13 - filter("SL"."GROWER_ID" IS NOT NULL)
  14 - filter("LC"."CLIENT_ID"="SLGRWR"."CLIENT_ID")
  15 - access("SL"."GROWER_ID"="SLGRWR"."ID")
  16 - filter(("LC"."CLIENT_ID"="SLGRWR"."CLIENT_ID" AND (LNNVL("SL"."GROWER_ID" IS NOT NULL) OR LNNVL("SL"."GROWER_ID"="SLGRWR"."ID"))))
  17 - access("LC"."CGRW_ID"="SLGRWR"."ID")
 
Note
-----
   - dynamic statistics used: dynamic sampling (level=2)
   - statistics feedback used for this statement
   - 1 Sql Plan Directive used for this statement
复制

使用日期和状态筛选器的CLPT表中的总计数为: 168031。

-如果我在索引上使用跳过扫描: IDX_CSLBLPRNTTRX_CIDDIDCON(CLIENT_ID，DEVICE_ID，CREATED_ON) 作为提示，则在响应时间方面可以正常工作，但对于少数客户端而言，成本要高得多。我无法决定天气是否使用跳过扫描提示。

为了澄清，您创建了一个索引:

(CLIENT_ID,STATUS,CREATED_ON)
复制

对吗？

几乎所有的时间都在进行全扫描T_CASE_LABEL_PRINT_TRNS。这样做的行估计足够准确 (期望184K行并得到168K)。

所以优化器认为全表扫描是最快的方法。如果索引实际上更快，那么您希望帮助优化器发现这一点。

您可以通过以下方式使索引 (CLIENT_ID，STATUS，CREATED_ON) 对优化器更具吸引力:

-压缩前导列 (alter index... 重建压缩2)
-将TABLE_CACHED_BLOCKS参数设置为值〜16并重新统计:

执行dbms_stats.set_table_prefs (用户，'t_case_label_print_trns '，pname =>' 表 _ 缓存 _ 块 '，pvalue =>16);

如果索引是 “大部分聚集的”，这将有助于优化器发现。

I AM NOT ABLE TO DECIDE WEATHER TO USE SKIP SCAN HINT OR NOT.

你应该尽可能避免暗示。特别是如果它对某些值来说是快速的，而对其他值来说是缓慢的。