-
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
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.
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.
-
Sorry didnt get it...
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;
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.
-
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
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.
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|