时间戳

首页 > 解决方案 > 表访问 rowid 在此查询中不起作用

sql - 表访问 rowid 在此查询中不起作用

问题描述

我想在执行计划中避免 TABLE ACCESS FULL ，但即使我在此查询中强制索引 /* index( ) */ 它也不起作用：

SELECT 
    af.ID, af.nom_flux, st.chemin_stockage, af.hash_flux 
FROM 
    stockage st 
INNER JOIN 
    allotissement_flux af  ON EXISTS (SELECT *
                                      FROM signature sig
                                      WHERE st.id_flux = sig.id_flux
                                        AND af.ID = sig.id_flux
                                        AND sig.statut_signature = 'SIGNE'
                                        AND sig.nb_appel_service_signature < 4 
                                        AND sig.date_statut_signature >= sysdate - 1000)
WHERE 
    st.statut_stockage = 'OUI'
    AND st.date_statut_stockage >= sysdate - 1000

索引是在表的每个属性上创建的。

Plan hash value: 2782848463
 
---------------------------------------------------------------------------------------------------
| Id  | Operation            | Name               | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |                    |    40M|  8376M|       |  1594K  (1)| 00:01:03 |
|*  1 |  HASH JOIN           |                    |    40M|  8376M|  4284M|  1594K  (1)| 00:01:03 |
|*  2 |   HASH JOIN          |                    |    40M|  3821M|  1505M|   543K  (1)| 00:00:22 |
|   3 |    SORT UNIQUE       |                    |    40M|  1042M|       |   146K  (1)| 00:00:06 |
|*  4 |     TABLE ACCESS FULL| SIGNATURE          |    40M|  1042M|       |   146K  (1)| 00:00:06 |
|*  5 |    TABLE ACCESS FULL | STOCKAGE           |    48M|  3322M|       |   130K  (2)| 00:00:06 |
|   6 |   TABLE ACCESS FULL  | ALLOTISSEMENT_FLUX |    49M|  5527M|       |   536K  (1)| 00:00:21 |
---------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   1 - access("AF"."ID"="SIG"."ID_FLUX")
   2 - access("ST"."ID_FLUX"="SIG"."ID_FLUX")
   4 - filter("SIG"."NB_APPEL_SERVICE_SIGNATURE"<4 AND "SIG"."STATUT_SIGNATURE"='SIGNE' 
              AND "SIG"."DATE_STATUT_SIGNATURE">=SYSDATE@!-1000)
   5 - filter("ST"."STATUT_STOCKAGE"='OUI' AND "ST"."DATE_STATUT_STOCKAGE">=SYSDATE@!-1000)

标签： sqloracle

解决方案

我首先将其表述为正常join：

SELECT af.ID, af.nom_flux, st.chemin_stockage, af.hash_flux 
FROM signature sig JOIN
     stockage st 
     ON st.id_flux = sig.id_flux JOIN
     allotissement_flux af 
     ON af.ID = sig.id_flux
WHERE sig.statut_signature = 'SIGNE' AND
      sig.nb_appel_service_signature < 4 
      sig.date_statut_signature >= sysdate - 1000 AND
      st.statut_stockage = 'OUI' AND
      st.date_statut_stockage >= sysdate - 1000;

目前尚不清楚哪些索引最有效，但应考虑以下几点：

  • signature(statut_signature, date_statut_signature, nb_appel_service_signature, id_flux)
  • stockage(statut_stockage, date_statut_stockage, id_flux)
  • allotissement_flux(id)——你可能已经有了这个

推荐阅读