F_ITEMS_RECPT_MONTHLY这个表是按照range分区的。按照ID_MONTH分区的。每个月一个分区。,数据量为24881514行,有个normal的联合索引名为IX_ITEM_RECPT_MONTHLY_1(ID_MONTH, SID_STR, SID_SUP)。现在问题是索引怎么也用不上。对该表一直是全表扫描。导致查询很慢。如何解决
SELECT DISTINCT to_char(D_MONTH.ID_MONTH),
(D_STR.ID_STR) || ' / ' || (D_STR.DS_STR_CN),
(D_SUPPLIER.ID_SUP) || ' / ' || (D_SUPPLIER.DS_SUP_EN) ||
' - ' || (D_SUPPLIER.DS_SUP_CN),
(D_GRP_SCD.ID_GRP) || ' / ' || (D_GRP_SCD.DS_GRP_EN) ||
' - ' || (D_GRP_SCD.DS_GRP_CN),
(substr(D_SGR_SCD.ID_SGR + 1000, 2, 3)) || ' / ' ||
(D_SGR_SCD.DS_SGR_EN) || ' - ' || (D_SGR_SCD.DS_SGR_CN),
(D_ITEMS_SCD.ID_ITEM) || ' / ' || (D_ITEMS_SCD.DS_ITEM_EN) ||
' - ' || (D_ITEMS_SCD.DS_ITEM_CN),
sum(F_ITEMS_RECPT_MONTHLY.RECPT_AMT)
FROM F_ITEMS_RECPT_MONTHLY,
D_DIV_SCD,
D_STR,
D_SEC_SCD,
D_SEC_CUR,
D_MONTH,
D_GRP_SCD,
D_GRP_CUR,
D_SGR_SCD,
D_SGR_CUR,
D_SUPPLIER,
D_ITEMS_CUR,
D_ITEMS_SCD
WHERE (D_ITEMS_CUR.ID_SGR = D_SGR_CUR.ID_SGR and
D_ITEMS_CUR.ID_GRP = D_SGR_CUR.ID_GRP)
AND (D_SGR_CUR.ID_GRP = D_GRP_CUR.ID_GRP)
AND (D_SEC_CUR.ID_SEC = D_GRP_CUR.ID_SEC)
AND (D_SGR_SCD.SID_SGR_H = D_ITEMS_SCD.SID_SGR_H)
AND (D_GRP_SCD.SID_GRP_H = D_SGR_SCD.SID_GRP_H)
AND (D_SEC_SCD.SID_SEC_H = D_GRP_SCD.SID_SEC_H)
AND (D_SEC_SCD.SID_DIV_H = D_DIV_SCD.SID_DIV_H)
AND (F_ITEMS_RECPT_MONTHLY.ID_MONTH = D_MONTH.ID_MONTH)
AND (F_ITEMS_RECPT_MONTHLY.SID_STR = D_STR.SID_STR)
AND (F_ITEMS_RECPT_MONTHLY.SID_ITEM_H = D_ITEMS_SCD.SID_ITEM_H)
AND (D_SUPPLIER.SID_SUP = F_ITEMS_RECPT_MONTHLY.SID_SUP)
AND (D_ITEMS_CUR.SID_ITEM_C = F_ITEMS_RECPT_MONTHLY.SID_ITEM_C)
AND (((to_char(D_MONTH.ID_MONTH)) >= '201108' AND
(to_char(D_MONTH.ID_MONTH)) <= '201108') AND
(((D_STR.ID_STR) || ' / ' || (D_STR.DS_STR_CN)) in
('10 / KS1-昆山店') or 'All' in ('10 / KS1-昆山店') or
('Comparable Stores' in ('10 / KS1-昆山店') and
D_STR.OPEN_DATE <= add_months(sysdate, -12))) AND
(((substr(D_SEC_CUR.ID_SEC + 1000, 2, 3)) || ' / ' ||
(D_SEC_CUR.DS_SEC_EN) || ' - ' || (D_SEC_CUR.DS_SEC_CN)) =
'061 / GROCERY - 食品杂货') AND
(((D_SUPPLIER.ID_SUP) || ' / ' || (D_SUPPLIER.DS_SUP_EN) || ' - ' ||
(D_SUPPLIER.DS_SUP_CN)) = '4273 / - 上海公司'))
GROUP BY to_char(D_MONTH.ID_MONTH),
(D_STR.ID_STR) || ' / ' || (D_STR.DS_STR_CN),
(D_SUPPLIER.ID_SUP) || ' / ' || (D_SUPPLIER.DS_SUP_EN) || ' - ' ||
(D_SUPPLIER.DS_SUP_CN),
(D_GRP_SCD.ID_GRP) || ' / ' || (D_GRP_SCD.DS_GRP_EN) || ' - ' ||
(D_GRP_SCD.DS_GRP_CN),
(substr(D_SGR_SCD.ID_SGR + 1000, 2, 3)) || ' / ' ||
(D_SGR_SCD.DS_SGR_EN) || ' - ' || (D_SGR_SCD.DS_SGR_CN),
(D_ITEMS_SCD.ID_ITEM) || ' / ' || (D_ITEMS_SCD.DS_ITEM_EN) ||
' - ' || (D_ITEMS_SCD.DS_ITEM_CN)
SELECT DISTINCT to_char(D_MONTH.ID_MONTH),
(D_STR.ID_STR) || ' / ' || (D_STR.DS_STR_CN),
(D_SUPPLIER.ID_SUP) || ' / ' || (D_SUPPLIER.DS_SUP_EN) ||
' - ' || (D_SUPPLIER.DS_SUP_CN),
(D_GRP_SCD.ID_GRP) || ' / ' || (D_GRP_SCD.DS_GRP_EN) ||
' - ' || (D_GRP_SCD.DS_GRP_CN),
(substr(D_SGR_SCD.ID_SGR + 1000, 2, 3)) || ' / ' ||
(D_SGR_SCD.DS_SGR_EN) || ' - ' || (D_SGR_SCD.DS_SGR_CN),
(D_ITEMS_SCD.ID_ITEM) || ' / ' || (D_ITEMS_SCD.DS_ITEM_EN) ||
' - ' || (D_ITEMS_SCD.DS_ITEM_CN),
sum(F_ITEMS_RECPT_MONTHLY.RECPT_AMT)
FROM F_ITEMS_RECPT_MONTHLY,
D_DIV_SCD,
D_STR,
D_SEC_SCD,
D_SEC_CUR,
D_MONTH,
D_GRP_SCD,
D_GRP_CUR,
D_SGR_SCD,
D_SGR_CUR,
D_SUPPLIER,
D_ITEMS_CUR,
D_ITEMS_SCD
WHERE (D_ITEMS_CUR.ID_SGR = D_SGR_CUR.ID_SGR and
D_ITEMS_CUR.ID_GRP = D_SGR_CUR.ID_GRP)
AND (D_SGR_CUR.ID_GRP = D_GRP_CUR.ID_GRP)
AND (D_SEC_CUR.ID_SEC = D_GRP_CUR.ID_SEC)
AND (D_SGR_SCD.SID_SGR_H = D_ITEMS_SCD.SID_SGR_H)
AND (D_GRP_SCD.SID_GRP_H = D_SGR_SCD.SID_GRP_H)
AND (D_SEC_SCD.SID_SEC_H = D_GRP_SCD.SID_SEC_H)
AND (D_SEC_SCD.SID_DIV_H = D_DIV_SCD.SID_DIV_H)
AND (F_ITEMS_RECPT_MONTHLY.ID_MONTH = D_MONTH.ID_MONTH)
AND (F_ITEMS_RECPT_MONTHLY.SID_STR = D_STR.SID_STR)
AND (F_ITEMS_RECPT_MONTHLY.SID_ITEM_H = D_ITEMS_SCD.SID_ITEM_H)
AND (D_SUPPLIER.SID_SUP = F_ITEMS_RECPT_MONTHLY.SID_SUP)
AND (D_ITEMS_CUR.SID_ITEM_C = F_ITEMS_RECPT_MONTHLY.SID_ITEM_C)
AND (((to_char(D_MONTH.ID_MONTH)) >= '201108' AND
(to_char(D_MONTH.ID_MONTH)) <= '201108') AND
(((D_STR.ID_STR) || ' / ' || (D_STR.DS_STR_CN)) in
('10 / KS1-昆山店') or 'All' in ('10 / KS1-昆山店') or
('Comparable Stores' in ('10 / KS1-昆山店') and
D_STR.OPEN_DATE <= add_months(sysdate, -12))) AND
(((substr(D_SEC_CUR.ID_SEC + 1000, 2, 3)) || ' / ' ||
(D_SEC_CUR.DS_SEC_EN) || ' - ' || (D_SEC_CUR.DS_SEC_CN)) =
'061 / GROCERY - 食品杂货') AND
(((D_SUPPLIER.ID_SUP) || ' / ' || (D_SUPPLIER.DS_SUP_EN) || ' - ' ||
(D_SUPPLIER.DS_SUP_CN)) = '4273 / - 上海公司'))
GROUP BY to_char(D_MONTH.ID_MONTH),
(D_STR.ID_STR) || ' / ' || (D_STR.DS_STR_CN),
(D_SUPPLIER.ID_SUP) || ' / ' || (D_SUPPLIER.DS_SUP_EN) || ' - ' ||
(D_SUPPLIER.DS_SUP_CN),
(D_GRP_SCD.ID_GRP) || ' / ' || (D_GRP_SCD.DS_GRP_EN) || ' - ' ||
(D_GRP_SCD.DS_GRP_CN),
(substr(D_SGR_SCD.ID_SGR + 1000, 2, 3)) || ' / ' ||
(D_SGR_SCD.DS_SGR_EN) || ' - ' || (D_SGR_SCD.DS_SGR_CN),
(D_ITEMS_SCD.ID_ITEM) || ' / ' || (D_ITEMS_SCD.DS_ITEM_EN) ||
' - ' || (D_ITEMS_SCD.DS_ITEM_CN)
对索引列使用函数或者计算
NOT IN操作
通配符位于查询字符串的第一个字符
IS NULL或者IS NOT NULL
多列索引,但它的第一个列并没有被Where子句引用
如果是varchar2,那么为啥要写“(TO_CHAR(D_MONTH.ID_MONTH)) >= '201108'”?另外,关联表太多,会影响DB选择执行计划,建议想法减少关联表数量和各表数据量。
比如对表D_MONTH,先进行条件过滤,再去和主表关联。