sql不走索引案例

sql不走索引案例

查询视图

select count(1)
  from VI_ALL_ORDER vi
 where vi.DECLARATIONAPPLY_ACTUAL between to_date('2020-11-01', 'yyyy-mm-dd') and
       to_date('2020-12-21', 'yyyy-mm-dd');

该视图sql如下，此查询时间维护如下就不走索引

--不走索引写法
SELECT  so.*, (SELECT A.CUST_DATE FROM ACT_CUST_ORDER A
                  WHERE A.ORDER_CODE = SO.ORDER_CODE
                  AND A.CUST_DATE IS NOT NULL
                  AND A.CUST_NUM IS NOT NULL) AS DECLARATIONAPPLY_ACTUAL, --实际完成
                (SELECT A.DECLARATIONAPPLY_CREATED FROM ACT_CUST_ORDER A
                  WHERE A.ORDER_CODE = SO.ORDER_CODE
                    AND A.CUST_DATE IS NOT NULL
                    AND A.CUST_NUM IS NOT NULL) AS DECLARATIONAPPLY_CREATED, --首次时间
                (SELECT A.DECLARATIONAPPLY_LAS FROM ACT_CUST_ORDER A WHERE A.ORDER_CODE = SO.ORDER_CODE
                    AND A.CUST_DATE IS NOT NULL
                    AND A.CUST_NUM IS NOT NULL) AS DECLARATIONAPPLY_LAST, --最新时间
                (SELECT A.ACTUAL_FINISH_DATE FROM SO_ACT A  WHERE A.ORDER_NUM = SO.ORDER_CODE
                    AND A.ACT_ID = 'getPackageInfo') AS SCAN_DATE, --扫时间
                SO.CREATED
  FROM SO_SALES_ORDER SO,
       SO_SALES_ORDER_ITEM SOI
 WHERE SOI.ORDER_CODE = SO.ORDER_CODE
   AND SOI.PROD_QUANTITY > 0
   --走索引写法
SELECT  so.*,ac.CUST_DATE AS DECLARATIONAPPLY_ACTUAL, --实际时间
                (SELECT A.DECLARATIONAPPLY_CREATED  FROM ACT_CUST_ORDER A
                  WHERE A.ORDER_CODE = SO.ORDER_CODE AND A.CUST_DATE IS NOT NULL
                    AND A.CUST_NUM IS NOT NULL) AS DECLARATIONAPPLY_CREATED, --首次时间
                (SELECT A.DECLARATIONAPPLY_LAST FROM ACT_CUST_ORDER A WHERE A.ORDER_CODE = SO.ORDER_CODE
                    AND A.CUST_DATE IS NOT NULL
                    AND A.CUST_NUM IS NOT NULL) AS DECLARATIONAPPLY_LAST, --最新时间               
                (SELECT A.ACTUAL_FINISH_DATE  FROM SO_ACT A WHERE A.ORDER_NUM = SO.ORDER_CODE
                    AND A.ACT_ID = 'getPackageInfo') AS SCAN_DATE, --时间
                SO.CREATED
  FROM SO_SALES_ORDER SO,
       SO_SALES_ORDER_ITEM SOI,
       ACT_CUST_ORDER AC,
 WHERE SOI.ORDER_CODE = SO.ORDER_CODE
   AND SOI.PROD_QUANTITY > 0
      AND AC.ORDER_CODE = SO.ORDER_CODE
   AND AC.CUST_DATE IS NOT NULL
   AND AC.CUST_NUM IS NOT NULL