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