我有一个庞大的遗留查询,它是我的程序核心, 这个查询需要太长时间,有什么方法可以让它运行得更快? 我使用的是 Oracle 11g。
SELECT *
FROM ( SELECT COUNT(*) AS countme,
string_value ,
name ,
property_id ,
category_id
FROM ( SELECT DISTINCT a.string_value,
a.name ,
a.property_id ,
b.product_id ,
a.category_id
FROM filter_criterias a
JOIN product_properties b
ON (
a.property_id = b.property_id
AND
(
(
isnumber(b.value) IS NOT NULL
AND isnumber(a.range_bottom) IS NOT NULL
AND isnumber(a.range_top) IS NOT NULL
AND
(
a.range_bottom >a.range_top
AND b.value >= a.range_bottom
OR a.range_bottom<=a.range_top
AND b.value >= a.range_bottom
AND b.value <=a.range_top
)
)
)
)
JOIN PRODUCT_CATEGORY prc
ON (
prc.sku = b.product_id
AND prc.category_id = a.category_id
)
JOIN PRODUCT pr
ON (
b.product_id = pr.SKU
AND pr.visible = '1'
)
)
GROUP BY (string_value, name, property_id,category_id)
UNION
SELECT COUNT(*) AS countme,
string_value ,
name ,
property_id ,
category_id
FROM ( SELECT DISTINCT a.string_value,
a.name ,
a.property_id ,
b.product_id ,
a.category_id
FROM filter_criterias a
JOIN product_properties b
ON (
a.property_id = b.property_id
AND
(
(
a.name= b.value
)
)
)
JOIN PRODUCT_CATEGORY prc
ON (
prc.sku = b.product_id
AND prc.category_id = a.category_id
)
JOIN PRODUCT pr
ON (
b.product_id = pr.SKU
AND pr.visible = '1'
)
)
GROUP BY (string_value, name, property_id,category_id)
)
ORDER BY 5,4,3,2
这是执行计划。
"Optimizer" "Cost" "Cardinality" "Bytes" "Partition Start" "Partition Stop" "Partition Id" "ACCESS PREDICATES" "FILTER PREDICATES"
"SELECT STATEMENT" "ALL_ROWS" "1298" "2" "542" "" "" "" "" ""
"SORT(ORDER BY)" "" "1298" "2" "542" "" "" "" "" ""
"VIEW" "" "1297" "2" "542" "" "" "" "" ""
"SORT(UNIQUE)" "" "1297" "2" "74" "" "" "" "" ""
"UNION-ALL" "" "" "" "" "" "" "" "" ""
"HASH(GROUP BY)" "" "661" "1" "37" "" "" "" "" ""
"VIEW" "" "659" "1" "37" "" "" "" "" ""
"HASH(UNIQUE)" "" "659" "1" "95" "" "" "" "" ""
"NESTED LOOPS" "" "" "" "" "" "" "" "" ""
"NESTED LOOPS" "" "658" "1" "95" "" "" "" "" ""
"HASH JOIN" "" "493" "1" "81" "" "" "" ""B"."PRODUCT_ID"=TO_NUMBER("PRC"."SKU") AND "A"."CATEGORY_ID"=SYS_OP_C2C("PRC"."CATEGORY_ID")" ""
"HASH JOIN" "" "369" "2" "128" "" "" "" ""B"."PROPERTY_ID"=TO_NUMBER("A"."PROPERTY_ID")" ""A"."RANGE_BOTTOM">"A"."RANGE_TOP" AND "A"."RANGE_BOTTOM"<=TO_NUMBER("B"."VALUE") OR "A"."RANGE_BOTTOM"<="A"."RANGE_TOP" AND "A"."RANGE_BOTTOM"<=TO_NUMBER("B"."VALUE") AND "A"."RANGE_TOP">=TO_NUMBER("B"."VALUE")"
"TABLE ACCESS(FULL) BNET.B_FILTER_CRITERIAS" "ANALYZED" "36" "28" "1148" "" "" "" "" ""ISNUMBER"(TO_CHAR("A"."RANGE_BOTTOM")) IS NOT NULL AND "ISNUMBER"(TO_CHAR("A"."RANGE_TOP")) IS NOT NULL"
"TABLE ACCESS(FULL) BNET.B_PRODUCT_PROPERTIES" "ANALYZED" "332" "12566" "289018" "" "" "" "" ""ISNUMBER"("B"."VALUE") IS NOT NULL"
"TABLE ACCESS(FULL) BNET.WLCS_PRODUCT_CATEGORY" "ANALYZED" "124" "129762" "2205954" "" "" "" "" ""
"INDEX(RANGE SCAN) BNET.WLCS_PROD_VISIBLE_IDX" "ANALYZED" "12" "6208" "" "" "" "" ""PR"."VISIBLE"='1'" ""
"TABLE ACCESS(BY INDEX ROWID) BNET.WLCS_PRODUCT" "ANALYZED" "164" "1" "14" "" "" "" "" ""B"."PRODUCT_ID"=TO_NUMBER("PR"."SKU")"
"HASH(GROUP BY)" "" "637" "1" "37" "" "" "" "" ""
"VIEW" "" "635" "1" "37" "" "" "" "" ""
"HASH(UNIQUE)" "" "635" "1" "91" "" "" "" "" ""
"HASH JOIN" "" "634" "1" "91" "" "" "" ""B"."PRODUCT_ID"=TO_NUMBER("PRC"."SKU") AND "A"."CATEGORY_ID"=SYS_OP_C2C("PRC"."CATEGORY_ID")" ""
"NESTED LOOPS" "" "" "" "" "" "" "" "" ""
"NESTED LOOPS" "" "509" "1" "74" "" "" "" "" ""
"HASH JOIN" "" "345" "1" "60" "" "" "" ""B"."PROPERTY_ID"=TO_NUMBER("A"."PROPERTY_ID") AND "A"."NAME"="B"."VALUE"" ""
"TABLE ACCESS(FULL) BNET.B_FILTER_CRITERIAS" "ANALYZED" "35" "11257" "416509" "" "" "" "" ""
"TABLE ACCESS(FULL) BNET.B_PRODUCT_PROPERTIES" "ANALYZED" "309" "251319" "5780337" "" "" "" "" ""
"INDEX(RANGE SCAN) BNET.WLCS_PROD_VISIBLE_IDX" "ANALYZED" "12" "6208" "" "" "" "" ""PR"."VISIBLE"='1'" ""
"TABLE ACCESS(BY INDEX ROWID) BNET.WLCS_PRODUCT" "ANALYZED" "164" "1" "14" "" "" "" "" ""B"."PRODUCT_ID"=TO_NUMBER("PR"."SKU")"
"TABLE ACCESS(FULL) BNET.WLCS_PRODUCT_CATEGORY" "ANALYZED" "124" "129762" "2205954" "" "" "" "" ""