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;

whats the explain plan

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

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

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.

Quote:

---

Originally Posted by 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.

---

Sorry didnt get it ...

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

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

---

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

Code:

---

(
        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.