Oracle: оптимизация условий даты и времени, TRUNC и TO_CHAR занимают слишком много времени
У меня есть заявление SQL, это занимает слишком много времени.
так что я копался в этом в последнее время, и обнаружил, что проблема была в одном столбце типа DATE
это миниатюра первого утверждения:
SELECT
--needed values --
FROM X_Product pr
INNER JOIN Z_COST co ON co.x_product_id = pr.x_product_id
WHERE pr.isValid = 'Y'
AND TRUNC(sysdate, 'MM') - 1 = co.dateto;
с этим набором условий условия для выполнения оригинала (длинного оператора) требуется около 37 секунд.
когда я удаляю последнее условие, мы получаем:
SELECT
--needed values --
FROM X_Product pr
INNER JOIN Z_COST co ON co.x_product_id = pr.x_product_id
WHERE pr.isValid = 'Y';
это займет всего 2 секунды.
я пытался использовать различные виды функций, как TO_CHAR, и получать только месяцы, я пытался добавить "TRUNC(sysdate, 'MM') - 1" и "co.dateto" в выборки, а затем в другой выбор проверки если они равны
я создал индексы для столбца, а также для функции в столбце.
но это всегда занимает слишком много времени.
поэтому у меня заканчиваются идеи, и я не могу найти другого пути. и ценю любую помощь.
Редактировать:
полный запрос: (длинный lol)
SELECT mp.AD_CLIENT_ID,
w.AD_ORG_ID,
mp.CREATED,
mp.CREATEDBY,
mp.UPDATED,
mp.UPDATEDBY,
(SELECT MAX(mpl.WINF_LOCATION)
FROM m_productlocator mpl
WHERE mpl.M_PRODUCT_ID =mp.M_PRODUCT_ID
AND mpl.AD_ORG_ID =w.AD_ORG_ID
) AS WINF_LOCATION,
mp.m_product_id,
mp.value,
mpc.m_product_category_id,
mpc.name AS Product_Category ,
w.m_warehouse_id ,
winf_getproduct_stock( mp.m_product_id, w.m_warehouse_id ) AS stock_qty ,
z_getproduct_customstock( mp.m_product_id, w.m_warehouse_id ) AS customStockQty,
winf_get_po_price_org_f( mp.m_product_id, w.ad_org_id ) AS po_price ,
ROUND( winf_getproduct_stock( mp.m_product_id, w.m_warehouse_id ) * winf_get_po_price_org_f( mp.m_product_id, w.ad_org_id ), 2 ) AS po_value ,
acsch.C_AcctSchema_id ,
ROUND(
-- new cost mc.currentcostprice
mc.nextmonthcost -- new cost
,2) AS Costing_AveragePoPrice ,
ROUND (winf_getproduct_stock( mp.m_product_id, w.m_warehouse_id ) *
-- new cost mc.currentcostprice
mc.nextmonthcost -- new cost
,2) AS Costing_AveragePOValue,
mp.discontinued,
Winf_GetOSGGroup(1000052,mp.m_product_category_id) AS OsgGroup,
po.C_BPartner_ID
FROM m_product mp
-- CROSS JOIN m_warehouse w
-- put as comment by Laurent on 25/09/14 because speed issue
--modif Laurent 25/09/14 because speed issue
inner join m_storage_v s on mp.m_product_id=s.m_product_id
inner join m_locator l on l.m_locator_id=s.m_locator_id
inner join m_warehouse w on w.m_warehouse_id=l.m_warehouse_id
--fin de modif
INNER JOIN C_AcctSchema acsch ON acsch.ad_orgonly_id = w."AD_ORG_ID"
INNER JOIN m_product_category mpc ON mpc.m_product_category_id = mp.m_product_category_id
LEFT JOIN z_costing_product mc ON mc.m_product_id = mp.m_product_id
LEFT JOIN Z_COSTING_CALCUL cc ON CC.Z_COSTING_CALCUL_ID = mc.Z_COSTING_CALCUL_ID AND cc.c_acctschema_id = acsch.c_acctschema_id
left outer join m_product_po po on po.m_product_id = mp.m_product_id and po.ad_org_id = w.ad_org_id and po.iscurrentvendor = 'Y'
WHERE winf_getproduct_stock( mp.m_product_id, w.m_warehouse_id ) <> 0
AND TRUNC(sysdate, 'MM') - 1 = cc.dateto;
Изменить 2:
Изменить: полный план объяснения
Plan hash value: 2645911395
----------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 5786K| 1743M| | 1210K (2)| 04:02:04 |
| 1 | SORT AGGREGATE | | 1 | 24 | | | |
|* 2 | TABLE ACCESS BY INDEX ROWID | M_PRODUCTLOCATOR | 1 | 24 | | 3 (0)| 00:00:01 |
|* 3 | INDEX RANGE SCAN | FK915_16182 | 2 | | | 1 (0)| 00:00:01 |
|* 4 | HASH JOIN RIGHT OUTER | | 5786K| 1743M| 45M| 1210K (2)| 04:02:04 |
|* 5 | TABLE ACCESS FULL | M_PRODUCT_PO | 1483K| 28M| | 7707 (1)| 00:01:33 |
|* 6 | HASH JOIN | | 5786K| 1633M| | 1116K (2)| 03:43:13 |
| 7 | TABLE ACCESS BY INDEX ROWID | Z_COSTING_CALCUL | 9 | 252 | | 4 (0)| 00:00:01 |
|* 8 | INDEX SKIP SCAN | Z_COSTING_CALCUL_DATETO | 1 | | | 3 (0)| 00:00:01 |
|* 9 | VIEW | | 364M| 90G| | 1114K (2)| 03:42:52 |
|* 10 | HASH JOIN | | 364M| 64G| | 1114K (2)| 03:42:52 |
| 11 | TABLE ACCESS FULL | M_PRODUCT_CATEGORY | 1957 | 76323 | | 29 (0)| 00:00:01 |
|* 12 | HASH JOIN OUTER | | 364M| 51G| 139M| 1112K (1)| 03:42:31 |
|* 13 | HASH JOIN | | 986K| 127M| | 57859 (1)| 00:11:35 |
| 14 | VIEW | index$_join$_009 | 20 | 240 | | 3 (34)| 00:00:01 |
|* 15 | HASH JOIN | | | | | | |
|* 16 | INDEX FAST FULL SCAN | FK265_14205 | 20 | 240 | | 1 (0)| 00:00:01 |
| 17 | INDEX FAST FULL SCAN | PK265 | 20 | 240 | | 1 (0)| 00:00:01 |
|* 18 | HASH JOIN | | 1281K| 151M| | 57849 (1)| 00:11:35 |
| 19 | INDEX FULL SCAN | Z_WAREHOUSE_10 | 291 | 3492 | | 1 (0)| 00:00:01 |
|* 20 | HASH JOIN | | 1281K| 136M| | 57842 (1)| 00:11:35 |
| 21 | INDEX FULL SCAN | Z_LOCATOR_30 | 283 | 3396 | | 1 (0)| 00:00:01 |
|* 22 | HASH JOIN | | 1291K| 123M| 46M| 57834 (1)| 00:11:35 |
| 23 | VIEW | M_STORAGE_V | 1291K| 32M| | 41216 (1)| 00:08:15 |
| 24 | HASH GROUP BY | | 1291K| 91M| 104M| 41216 (1)| 00:08:15 |
|* 25 | HASH JOIN | | 1291K| 91M| | 18710 (1)| 00:03:45 |
| 26 | VIEW | index$_join$_020 | 283 | 3396 | | 3 (34)| 00:00:01 |
|* 27 | HASH JOIN | | | | | | |
| 28 | INDEX FAST FULL SCAN| FK207_1390 | 283 | 3396 | | 1 (0)| 00:00:01 |
| 29 | INDEX FAST FULL SCAN| PK207 | 283 | 3396 | | 1 (0)| 00:00:01 |
|* 30 | HASH JOIN RIGHT OUTER | | 1301K| 76M| 33M| 18701 (1)| 00:03:45 |
| 31 | TABLE ACCESS FULL | M_PRODUCT | 679K| 25M| | 11466 (1)| 00:02:18 |
| 32 | TABLE ACCESS FULL | M_STORAGEDETAIL | 1301K| 27M| | 3453 (1)| 00:00:42 |
| 33 | TABLE ACCESS FULL | M_PRODUCT | 679K| 47M| | 11514 (1)| 00:02:19 |
| 34 | TABLE ACCESS FULL | Z_COSTING_PRODUCT | 251M| 3598M| | 723K (2)| 02:24:46 |
----------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("MPL"."AD_ORG_ID"=:B1)
3 - access("MPL"."M_PRODUCT_ID"=:B1)
4 - access("PO"."AD_ORG_ID"(+)="from$_subquery$_014"."QCSJ_C000000000800003" AND
"PO"."M_PRODUCT_ID"(+)="from$_subquery$_014"."QCSJ_C000000000400008")
5 - filter("PO"."ISCURRENTVENDOR"(+)='Y')
6 - access("CC"."C_ACCTSCHEMA_ID"="ACSCH"."C_ACCTSCHEMA_ID" AND
"CC"."Z_COSTING_CALCUL_ID"="MC"."Z_COSTING_CALCUL_ID")
8 - access("CC"."DATETO"=TRUNC(SYSDATE@!,'fmmm')-1)
filter("CC"."DATETO"=TRUNC(SYSDATE@!,'fmmm')-1 AND
TRUNC(INTERNAL_FUNCTION("DATETO"),'fmmm')=TRUNC(TRUNC(SYSDATE@!,'fmmm')-1,'fmmm'))
9 - filter("WINF_GETPRODUCT_STOCK"("from$_subquery$_014"."QCSJ_C000000000400008","from$_subquery$_014"."QCS
J_C000000000800007")<>0)
10 - access("MPC"."M_PRODUCT_CATEGORY_ID"="MP"."M_PRODUCT_CATEGORY_ID")
12 - access("MC"."M_PRODUCT_ID"(+)="MP"."M_PRODUCT_ID")
13 - access("ACSCH"."AD_ORGONLY_ID"="W"."AD_ORG_ID")
15 - access(ROWID=ROWID)
16 - filter("ACSCH"."AD_ORGONLY_ID" IS NOT NULL)
18 - access("W"."M_WAREHOUSE_ID"="L"."M_WAREHOUSE_ID")
20 - access("L"."M_LOCATOR_ID"="S"."M_LOCATOR_ID")
22 - access("MP"."M_PRODUCT_ID"="S"."M_PRODUCT_ID")
25 - access("L"."M_LOCATOR_ID"="ST"."M_LOCATOR_ID")
27 - access(ROWID=ROWID)
30 - access("P"."M_PRODUCT_ID"(+)="ST"."M_PRODUCT_ID")
после удаления последнего условия:
AND TRUNC(sysdate, 'MM') - 1 = cc.dateto;
План объяснения для этого:
Значение хэша плана: 1172089290
-----------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 364M| 101G| | 5950K (1)| 19:50:06 |
| 1 | SORT AGGREGATE | | 1 | 24 | | | |
|* 2 | TABLE ACCESS BY INDEX ROWID | M_PRODUCTLOCATOR | 1 | 24 | | 3 (0)| 00:00:01 |
|* 3 | INDEX RANGE SCAN | FK915_16182 | 2 | | | 1 (0)| 00:00:01 |
|* 4 | HASH JOIN RIGHT OUTER | | 364M| 101G| | 5950K (1)| 19:50:06 |
| 5 | TABLE ACCESS FULL | Z_COSTING_CALCUL | 535 | 6420 | | 6 (0)| 00:00:01 |
|* 6 | HASH JOIN RIGHT OUTER | | 364M| 97G| 45M| 5948K (1)| 19:49:45 |
|* 7 | TABLE ACCESS FULL | M_PRODUCT_PO | 1483K| 28M| | 7707 (1)| 00:01:33 |
|* 8 | VIEW | | 364M| 90G| | 1114K (2)| 03:42:52 |
|* 9 | HASH JOIN | | 364M| 64G| | 1114K (2)| 03:42:52 |
| 10 | TABLE ACCESS FULL | M_PRODUCT_CATEGORY | 1957 | 76323 | | 29 (0)| 00:00:01 |
|* 11 | HASH JOIN OUTER | | 364M| 51G| 139M| 1112K (1)| 03:42:31 |
|* 12 | HASH JOIN | | 986K| 127M| | 57859 (1)| 00:11:35 |
| 13 | VIEW | index$_join$_009 | 20 | 240 | | 3 (34)| 00:00:01 |
|* 14 | HASH JOIN | | | | | | |
|* 15 | INDEX FAST FULL SCAN | FK265_14205 | 20 | 240 | | 1 (0)| 00:00:01 |
| 16 | INDEX FAST FULL SCAN | PK265 | 20 | 240 | | 1 (0)| 00:00:01 |
|* 17 | HASH JOIN | | 1281K| 151M| | 57849 (1)| 00:11:35 |
| 18 | INDEX FULL SCAN | Z_WAREHOUSE_10 | 291 | 3492 | | 1 (0)| 00:00:01 |
|* 19 | HASH JOIN | | 1281K| 136M| | 57842 (1)| 00:11:35 |
| 20 | INDEX FULL SCAN | Z_LOCATOR_30 | 283 | 3396 | | 1 (0)| 00:00:01 |
|* 21 | HASH JOIN | | 1291K| 123M| 46M| 57834 (1)| 00:11:35 |
| 22 | VIEW | M_STORAGE_V | 1291K| 32M| | 41216 (1)| 00:08:15 |
| 23 | HASH GROUP BY | | 1291K| 91M| 104M| 41216 (1)| 00:08:15 |
|* 24 | HASH JOIN | | 1291K| 91M| | 18710 (1)| 00:03:45 |
| 25 | VIEW | index$_join$_020 | 283 | 3396 | | 3 (34)| 00:00:01 |
|* 26 | HASH JOIN | | | | | | |
| 27 | INDEX FAST FULL SCAN| FK207_1390 | 283 | 3396 | | 1 (0)| 00:00:01 |
| 28 | INDEX FAST FULL SCAN| PK207 | 283 | 3396 | | 1 (0)| 00:00:01 |
|* 29 | HASH JOIN RIGHT OUTER | | 1301K| 76M| 33M| 18701 (1)| 00:03:45 |
| 30 | TABLE ACCESS FULL | M_PRODUCT | 679K| 25M| | 11466 (1)| 00:02:18 |
| 31 | TABLE ACCESS FULL | M_STORAGEDETAIL | 1301K| 27M| | 3453 (1)| 00:00:42 |
| 32 | TABLE ACCESS FULL | M_PRODUCT | 679K| 47M| | 11514 (1)| 00:02:19 |
| 33 | TABLE ACCESS FULL | Z_COSTING_PRODUCT | 251M| 3598M| | 723K (2)| 02:24:46 |
-----------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("MPL"."AD_ORG_ID"=:B1)
3 - access("MPL"."M_PRODUCT_ID"=:B1)
4 - access("CC"."C_ACCTSCHEMA_ID"(+)="ACSCH"."C_ACCTSCHEMA_ID" AND
"CC"."Z_COSTING_CALCUL_ID"(+)="MC"."Z_COSTING_CALCUL_ID")
6 - access("PO"."AD_ORG_ID"(+)="from$_subquery$_014"."QCSJ_C000000000800003" AND
"PO"."M_PRODUCT_ID"(+)="from$_subquery$_014"."QCSJ_C000000000400008")
7 - filter("PO"."ISCURRENTVENDOR"(+)='Y')
8 - filter("WINF_GETPRODUCT_STOCK"("from$_subquery$_014"."QCSJ_C000000000400008","from$_subquery$_014"
."QCSJ_C000000000800007")<>0)
9 - access("MPC"."M_PRODUCT_CATEGORY_ID"="MP"."M_PRODUCT_CATEGORY_ID")
11 - access("MC"."M_PRODUCT_ID"(+)="MP"."M_PRODUCT_ID")
12 - access("ACSCH"."AD_ORGONLY_ID"="W"."AD_ORG_ID")
14 - access(ROWID=ROWID)
15 - filter("ACSCH"."AD_ORGONLY_ID" IS NOT NULL)
17 - access("W"."M_WAREHOUSE_ID"="L"."M_WAREHOUSE_ID")
19 - access("L"."M_LOCATOR_ID"="S"."M_LOCATOR_ID")
21 - access("MP"."M_PRODUCT_ID"="S"."M_PRODUCT_ID")
24 - access("L"."M_LOCATOR_ID"="ST"."M_LOCATOR_ID")
26 - access(ROWID=ROWID)
29 - access("P"."M_PRODUCT_ID"(+)="ST"."M_PRODUCT_ID")