Urgent: this oracle query is taking too much time for running, how to optimize
Plz help me tune this query and optimize it..it is taking too much time for running...
thnx,
SELECT wfraud_data.id,wfraud_data.account_number,wfraud_data_extra.data,
wfraud_data.score,wfraud_data.time_created
FROM wfraud_data,wfraud_data_extra
WHERE wfraud_data.id=wfraud_data_extra.fraud_data_id AND wfraud_data.id
IN (SELECT * FROM
(SELECT MAX(wfraud_data.id) FROM wfraud_data WHERE wfraud_data.fraud_suspect_reason_id=1761
AND wfraud_data.time_created >= 1160463600
AND wfraud_data.status ='N'
AND (wfraud_data.expiration is null OR wfraud_data.expiration=0 OR wfraud_data.expiration>1161003844
OR wfraud_data.status != 'N' OR BITAND(wfraud_data.flags, 10) > 0)
GROUP BY wfraud_data.account_number )
WHERE rownum < 2800 )
ORDER by wfraud_data.score;
expain plan for above query is
Quote:
Originally Posted by davey23uk
whats the explain plan
QUERY_PLAN OTHER_TAG OTHER OBJECT_NAME
------------------------------------------------------- -------------------- ------------------------------ ------------------------------
SELECT STATEMENT
SORT ORDER BY
NESTED LOOPS
NESTED LOOPS
VIEW VW_NSO_1 VW_NSO_1
SORT UNIQUE
COUNT STOPKEY
VIEW
SORT GROUP BY STOPKEY
TABLE ACCESS BY INDEX ROWID WFRAUD_ WFRAUD_DATA
DATA
INDEX RANGE SCAN WFRAUD_DATA_TIME WFRAUD_DATA_TIME_CREATED
_CREATED
TABLE ACCESS BY INDEX ROWID WFRAUD_DATA WFRAUD_DATA
INDEX UNIQUE SCAN WFRAUD_DATA_NEW_PRIME WFRAUD_DATA_NEW_PRIME
TABLE ACCESS BY INDEX ROWID WFRAUD_DATA_EXTRA WFRAUD_DATA_EXTRA
INDEX RANGE SCAN WFRAUD_DATA_EXTRA_PK WFRAUD_DATA_EXTRA_PK
15 rows selected
thnx a lot, can u explain me how this query will save time then query i wrote
Quote:
Originally Posted by pando
try this
Code:
SELECT
wfraud_data.id,
wfraud_data.account_number,
wfraud_data_extra.data,
wfraud_data.score,
wfraud_data.time_created
FROM wfraud_data,
wfraud_data_extra,
(
SELECT
MAX(x.id) MAX_ID_PER_ACC
FROM wfraud_data x
WHERE x.fraud_suspect_reason_id = 1761
AND x.time_created >= 1160463600
AND x.status ='N'
AND rownum < 2800
AND (x.expiration is null
OR x.expiration = 0
OR x.expiration > 1161003844
OR x.status != 'N'
OR BITAND(x.flags, 10) > 0)
GROUP BY x.account_number
) C
WHERE wfraud_data.id = wfraud_data_extra.fraud_data_id
AND wfraud_data.id = c.max_id_per_acc
thnx a lot, can u explain me how this query will save time then query i wrote??