Urgent: this oracle query is taking too much time for running, how to optimize

[janki_mehta]

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;

[davey23uk]

whats the explain plan

[janki_mehta]

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

[davey23uk]

that doesnt help, hwt does it say the cardinalities are, how many rows are coming back from each operation?

[PAVB]

Your query is executing in-line view for each row in main query.

I would seriously consider creating a temp table with the query you are using to populate in-line view, query is creating once and again the same list no matter row selected on main query.

Once you have your temp table you can take out in-line view and replace is for a single additional where clause.

Depending on temp table size an index on temp.wfraud_data.id wouldn't hurt.

[janki_mehta]

Your query is executing in-line view for each row in main query.

I would seriously consider creating a temp table with the query you are using to populate in-line view, query is creating once and again the same list no matter row selected on main query.

Once you have your temp table you can take out in-line view and replace is for a single additional where clause.

Depending on temp table size an index on temp.wfraud_data.id wouldn't hurt.

Sorry didnt get it ...

[PAVB]

-- Create TEMP_FRAUD table
-- For PROD deployment you might want to create
-- a true temp table
--
create table TEMP_FRAUD as
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 ) ;
--
-- CREATE PK on TEMP_FRAUD.ID if needed
--
...
--
-- NOW RUN YOUR QUERY
--
SELECT fraud_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 = TEMP_FRAUD.ID
ORDER by wfraud_data.score;

[pando]

try this

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

[janki_mehta]

try this

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??

[tamilselvan]

(
	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

First check, would oracle materialize the in-line view?
Or post the execution plan.