| Query Taking Alot of time ..Help in Tuning [message #674702] |
Tue, 12 February 2019 00:43  |
 |
rrcr
Messages: 18
Registered: January 2019
|
Junior Member |
|
|
Hi
I have the following query.
The requirement is to identify distinct ORDER_ID from table A based on Data from C and the joining conditions to join A and C.
I have attached execution plan too. Can you please suggest a solution.
Cant alter DDLs of A,B,C,D.
SELECT DISTINCT A.ORDER_ID,1,0,1,0,TO_DATE(SYSDATE)
FROM A,B,C,D
WHERE A.ORDER_ID=D.ORDER_ID
AND D.ORDER_ID=B.ORDER_ID
AND B.TRADE_ORDER_ID=C.col
AND C.col IN (13,14)
AND C.C_TYPE IN (1,2,92)
AND C.C_DT < ADD_MONTHS(SYSDATE, -40)
AND NOT EXISTS ( SELECT 1 FROM E WHERE C1='ABC' AND C2='XYZ' AND C3.=A.ORDER_ID);
------------------------------------------------------------------------------------------------------------------------------------- -----------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time | Pstart| Pstop |
------------------------------------------------------------------------------------------------------------------------------------- -----------
| 0 | SELECT STATEMENT | | 3513K| 2063M| | 1560K (1)| 05:12:02 | | |
| 1 | HASH UNIQUE | | 3513K| 2063M| 2111M| 1560K (1)| 05:12:02 | | |
|* 2 | HASH JOIN RIGHT ANTI | | 3513K| 2063M| 10M| 1103K (1)| 03:40:43 | | |
|* 3 | TABLE ACCESS STORAGE FULL | E | 18550 | 9M| | 329K (1)| 01:05:50 | | |
|* 4 | HASH JOIN | | 3513K| 201M| 90M| 763K (1)| 02:32:40 | | |
|* 5 | HASH JOIN | | 1604K| 71M| 79M| 635K (1)| 02:07:02 | | |
|* 6 | HASH JOIN | | 1600K| 61M| 31M| 619K (1)| 02:03:53 | | |
| 7 | PARTITION LIST ALL | | 1045K| 19M| | 557K (1)| 01:51:30 | 1 | 7 |
| 8 | PARTITION LIST ALL | | 1045K| 19M| | 557K (1)| 01:51:30 | 1 | LAST |
| 9 | INLIST ITERATOR | | | | | | | | |
|* 10 | TABLE ACCESS BY LOCAL INDEX ROWID| C | 1045K| 19M| | 557K (1)| 01:51:30 | 1 | 39 |
|* 11 | INDEX RANGE SCAN | C_IX13 | 4073K| | | 12594 (1)| 00:02:32 | 1 | 39 |
| 12 | TABLE ACCESS STORAGE FULL | B | 20M| 386M| | 32956 (1)| 00:06:36 | | |
| 13 | INDEX STORAGE FAST FULL SCAN | D_PK | 9365K| 62M| | 4631 (1)| 00:00:56 | | |
| 14 | PARTITION LIST ALL | | 20M| 253M| | 102K (1)| 00:20:29 | 1 | 7 |
| 15 | PARTITION LIST ALL | | 20M| 253M| | 102K (1)| 00:20:29 | 1 | LAST |
| 16 | TABLE ACCESS STORAGE FULL | A | 20M| 253M| | 102K (1)| 00:20:29 | 1 | 39 |
------------------------------------------------------------------------------------------------------------------------------------- -----------
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
