hello ,

Quelqu'un a des idée pour optimiser cette requête ? Elle prend trop de temps : plus de 6 heures sans résultat, avant il était d'environ quelques minutes

Code : Sélectionner tout - Visualiser dans une fenêtre à part
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
SELECT*
NVL(PT.CREATED_DTTM,OO.CREATED_DTTM) "Crié DTTM"
,EXTRACT(MONTH FROM NVL(PT.CREATED_DTTM,OO.CREATED_DTTM) )*
,EXTRACT(YEAR FROM NVL(PT.CREATED_DTTM,OO.CREATED_DTTM) )*
,o.tc_order_id "ASN/DO"
,'Préparation' "Category"
,'Full case picking Type 1' "Sub-Category"
,COUNT( DISTINCT O.ORDER_ID) "Nombre de DOs"
,COUNT(DISTINCT *OLI.ITEM_ID) "Nobre de Produit"
*
,WSC.CODE_DESC "Division"
,'Case picking line' UOM
 
,CASE WHEN SUM(floor(nvl(TD.QTY_PULLD,0)/NULLIF(ipc.quantity,0))) > 0 THEN COUNT( DISTINCT OLI.LINE_ITEM_ID) *END AS "Qte"
 
FROM ORDERS O
INNER JOIN ORDER_LINE_ITEM OLI ON O.ORDER_ID = OLI.ORDER_ID
INNER JOIN ITEM_CBO ITC ON COMPANY_ID =1026 AND *ITC.ITEM_ID = OLI.ITEM_ID
INNER JOIN WHSE_SYS_CODE WSC ON WSC.CODE_ID = PROD_TYPE AND WHSE='MA1' AND CODE_TYPE ='665'*
INNER JOIN TASK_DTL TD ON TD.TC_ORDER_ID = O.TC_ORDER_ID AND TD.ITEM_ID = OLI.ITEM_ID AND TD.INVN_NEED_TYPE =50 AND TD.LINE_ITEM_ID = OLI.LINE_ITEM_ID
 
 
LEFT JOIN (SELECT TC_ORDER_ID, MIN(CREATED_DTTM) CREATED_DTTM
 
* * * * * * FROM
* * * * * * OUTPT_ORDERS OO
* * * * * * WHERE
* * * * * * TC_COMPANY_ID =1026
* * * * * * AND PRE_BILL_STATUS =1
* * * * * * AND *CREATED_DTTM >= SYSDATE -7
* * * * * * GROUP BY TC_ORDER_ID) *OO ON OO.TC_ORDER_ID =O.TC_ORDER_ID*
 
LEFT JOIN (SELECT REF_FIELD_1, TC_ORDER_ID, MIN(DATE_PROC) CREATED_DTTM
* * * * * * FROM
* * * * * * PIX_TRAN PT
* * * * * * WHERE
* * * * * * TC_COMPANY_ID =1026
* * * * * * AND REF_FIELD_2 ='150'
* * * * * * AND DATE_PROC >= SYSDATE -7
* * * * * * GROUP BY REF_FIELD_1) PT ON PT.TC_ORDER_ID = O.TC_ORDER_ID
 
* * * * * **
 
LEFT JOIN *item_package_cbo IPC on ITC.item_id = IPC.item_id*
* * * * * * AND IPC.package_uom_id=374*
* * * * * * AND O.TC_COMPANY_ID =1026
* * * * * * AND IPC.mark_for_deletion=0*
* * * * * * AND IPC.IS_STD=1
* * * * * * AND O.DO_STATUS BETWEEN 150 AND 190
* * * * * * AND NVL(PT.CREATED_DTTM,OO.CREATED_DTTM) >= SYSDATE -7
* * * * * **
GROUP BY WSC.CODE_DESC*
,NVL(PT.CREATED_DTTM,OO.CREATED_DTTM)*
,o.tc_order_id , EXTRACT(MONTH FROM NVL(PT.CREATED_DTTM,OO.CREATED_DTTM) )*
,EXTRACT(YEAR FROM NVL(PT.CREATED_DTTM,OO.CREATED_DTTM) )*
Thanks