| Query is really taking a long time. Please help [message #175531] |
Fri, 02 June 2006 11:51  |
pzlj6x
Messages: 107
Registered: May 2005
Location: Louisville
|
Senior Member |
|
|
Here is the SQL and explan plan for it. Can you please share your expert knowledge to let me know if this can be improved.
SQL
SELECT
DSS.T_D1_CA_ICN.DTE_PAID,
DSS.T_D1_CA_CLAIM_KEY.CDE_ACCOUNTING,
sum(DSS.T_D1_CA_ICN.AMT_PAID),
DSS.T_D1_CA_ICN.CDE_NDC
FROM
DSS.T_D1_CA_ICN,
DSS.T_D1_CA_CLAIM_KEY,
DSS.T_D1_CA_PROV_KEY
WHERE
( DSS.T_D1_CA_ICN.BILL_PROV_KEY=DSS.T_D1_CA_PROV_KEY.PROV_KEY )
AND ( DSS.T_D1_CA_CLAIM_KEY.CLAIM_KEY=DSS.T_D1_CA_ICN.CLAIM_KEY )
AND (
( ( DSS.T_D1_CA_ICN.CDE_CLAIM_STATUS ) IN ('I','N') AND ( DSS.T_D1_CA_CLAIM_KEY.CDE_DTL_STATUS ) NOT IN ('P','K') )
AND ( ( DSS.T_D1_CA_CLAIM_KEY.IND_CLAIM ) = 'F' )
AND DSS.T_D1_CA_ICN.DTE_PAID BETWEEN '10-01-2002' AND '09-30-2004'
AND DSS.T_D1_CA_PROV_KEY.CDE_PROV_COS = '64'
AND DSS.T_D1_CA_CLAIM_KEY.CDE_ACCOUNTING NOT IN ('G', 'H', 'I', 'J', 'K', 'L', '6', '7', '9')
)
GROUP BY
DSS.T_D1_CA_ICN.DTE_PAID,
DSS.T_D1_CA_CLAIM_KEY.CDE_ACCOUNTING,
DSS.T_D1_CA_ICN.CDE_NDC
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------- -------------------------------------------------------------------
---------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Pstart| Pstop |
---------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1244 | 128K| | 130K (1)| | |
| 1 | SORT GROUP BY | | 1244 | 128K| 336K| 130K (1)| | |
| 2 | FILTER | | | | | | | |
| 3 | NESTED LOOPS OUTER | | 1244 | 128K| | 130K (1)| | |
| 4 | HASH JOIN | | 1244 | 89568 | | 130K (1)| | |
| 5 | VIEW | index$_join$_004 | 10049 | 107K| | 425 (3)| | |
| 6 | HASH JOIN | | | | | | | |
| 7 | HASH JOIN | | | | | | | |
| 8 | HASH JOIN | | | | | | | |
| 9 | BITMAP CONVERSION TO ROWIDS | | 10049 | 107K| | 1 (0)| | |
| 10 | BITMAP INDEX FULL SCAN | B_D1_CA_CLAIM_KEY_01 | | | | | | |
| 11 | BITMAP CONVERSION TO ROWIDS | | 10049 | 107K| | 1 (0)| | |
| 12 | BITMAP INDEX FULL SCAN | B_D1_CA_CLAIM_KEY_04 | | | | | | |
| 13 | BITMAP CONVERSION TO ROWIDS | | 10049 | 107K| | 1 (0)| | |
| 14 | BITMAP INDEX FULL SCAN | B_D1_CA_CLAIM_KEY_06 | | | | | | |
| 15 | INDEX FAST FULL SCAN | I_D1_CA_CLAIM_KEY | 10049 | 107K| | 13 (8)| | |
| 16 | TABLE ACCESS BY LOCAL INDEX ROWID| T_D1_CA_ICN | 39 | 2067 | | 129K (1)| | |
| 17 | NESTED LOOPS | | 3744 | 223K| | 129K (1)| | |
| 18 | INDEX RANGE SCAN | X_D1_CA_PROV_KEY_01 | 95 | 760 | | 2 (0)| | |
| 19 | PARTITION RANGE ITERATOR | | | | | | KEY | KEY |
| 20 | BITMAP CONVERSION TO ROWIDS | | | | | | | |
| 21 | BITMAP AND | | | | | | | |
| 22 | BITMAP INDEX SINGLE VALUE | B_D1_CA_ICN_12 | | | | | KEY | KEY |
| 23 | BITMAP INDEX SINGLE VALUE | B_D1_CA_ICN_42 | | | | | KEY | KEY |
| 24 | TABLE ACCESS BY INDEX ROWID | T_D1_PRV_HFT01 | 1 | 34 | | 1 (0)| | |
| 25 | INDEX RANGE SCAN | X_D1_PRV_HFT_NDX1 | 1 | | | 0 (0)| | |
---------------------------------------------------------------------------------------------------------------------------
[Updated on: Fri, 02 June 2006 12:06] by Moderator Report message to a moderator |
|
|
|
|
|
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
