Tuning of delete stmt [message #344695] |
Mon, 01 September 2008 01:10 ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
srihari.gurram
Messages: 13 Registered: May 2008
|
Junior Member |
|
|
Hi,
I have the below delete statement which is taking more time. Can any one help me in tuning this?
DELETE FROM T_PRC_MASTER a
WHERE a.ROLLED_FLG = 'Y'
AND a.END_ROLL_FLG != 'Y'
AND a.PRC_DT < del_dt
and (exists (select 1 from t_prc_master b
where a.asset_id = b.asset_id
and a.pricing_pt_id = b.pricing_pt_id
and b.prc_dt > = del_dt
and a.price <> b.price)
or exists (select 1 from t_asset b
where b.asset_typ_cd = 16
and a.asset_id = b.asset_id
and a.price in (0.00000001,0.00000002)));
Attached is the explain plan output for the same...
-
Attachment: test2.csv
(Size: 1.09KB, Downloaded 1254 times)
|
|
|
|
Re: Tuning of delete stmt [message #345021 is a reply to message #344695] |
Tue, 02 September 2008 02:45 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
michael_bialik
Messages: 621 Registered: July 2006
|
Senior Member |
|
|
1. What indexes do you have (and what columns are used in theses indexes)?
2. How many rows are in T_PRC_MASTER table?
3. How many rows are deleted?
4. How many rows have PRC_DT < del_dt?
|
|
|
Re: Tuning of delete stmt [message #345073 is a reply to message #345021] |
Tue, 02 September 2008 04:30 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
srihari.gurram
Messages: 13 Registered: May 2008
|
Junior Member |
|
|
Hi,
1.There are 4 indexes on t_prc_master table and these columns use these indexes ASSET_ID,PRC_DT,PRICING_PT_ID,ACCEPT_FLG.
2.There are 4 crore rows in t_prc_master table.
3. Around 1 crore rows gets deleted.
4.Around 6lakh records.
|
|
|
|
|
|
Re: Tuning of delete stmt [message #346035 is a reply to message #344695] |
Fri, 05 September 2008 12:32 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
![](http://www.orafaq.com/forum/images/custom_avatars/136107.jpg) |
BlackSwan
Messages: 26766 Registered: January 2009 Location: SoCal
|
Senior Member |
|
|
http://www.orafaq.com/forum/t/88153/0/
Please read & follow posting guidelines as stated in URL above
Is not below improved over what you posted?
DELETE FROM T_PRC_MASTER A
WHERE A.ROLLED_FLG = 'Y'
AND A.END_ROLL_FLG != 'Y'
AND A.PRC_DT < '27-May-08'
AND (EXISTS (SELECT 1
FROM T_PRC_MASTER B
WHERE A.ASSET_ID = B.ASSET_ID
AND A.PRICING_PT_ID = B.PRICING_PT_ID
AND B.PRC_DT > = '27-May-08'
AND A.PRICE <> B.PRICE)
OR EXISTS (SELECT 1
FROM T_ASSET B
WHERE B.ASSET_TYP_CD = 16
AND A.ASSET_ID = B.ASSET_ID
AND A.PRICE IN (0.00000001,
0.00000002)));
You should have indexes on all fields in WHERE clause
& use TO_DATE() when comparing against PRC_DT
[Updated on: Fri, 05 September 2008 12:34] by Moderator Report message to a moderator
|
|
|
Re: Tuning of delete stmt [message #346556 is a reply to message #346035] |
Mon, 08 September 2008 19:34 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
harrysmall3
Messages: 109 Registered: April 2008 Location: Massachusetts
|
Senior Member |
|
|
First, - Kevin, good approach in separating the costs which
I have made a mental tatoo for my own future reference.
I too would like to know as Michael asked, how many rows are expected to be deleted, and furthermore, which condition
contributes to the most hits.
For now, let me take Kevin's separation approach and go a step further and see if we can get figures on which operation
in the query is the most troublesome.
Ahha! - The full table scan? Let me play public defendant
for the table scan until proven guilty. So far we just
know a table scan exists.
And until Michel's advice is taken and we do comparisson
of proper indexing and incoporate his suggestion for using
to_date, the table scan can not be called the villian.
I offer to the jury some alternatives weight:
First, a full hash join:
(I apologize that once again I am not at an Oracle terminal
so I may have fat fingered something here. This will change tomorrow when I get my remote access lap top issued!)
Like to see first what the SELECT portion for the self-join
criteria performs as with:
SELECT /*+ USE_HASH(A,B) */
A.ROWID RID FROM T_PRC_MASTER A,
T_PRC_MASTER B
WHERE ( A.ROLLED_FLG = 'Y'
AND A.END_ROLL_FLAG != 'Y'
AND A.PRC_DT < TO_DATE('05/27/08','MM/DD/YY')
AND B.PRC_DT >= TO_DATE('05/27/08','MM/DD/YY')
AND A.PRICING_PT_ID = B.PRICING_PT_ID
AND A.PRICE <> B.PRICE)
In first passes to decipher SQL I like breaking the criteria into individual select components. The below DELETE will
use a materialized view of such components
WITH TA AS
(SELECT /*+ MATERIALIZE */ C.ASSET_ID FROM T_ASSET C
WHERE A.PRICE IN (0.00000001,0.00000002)
AND C.ASSET_TYP_CD = 16 ),
M1 AS
(SELECT /*+ MATERIALIZE */ A.ROWID RID,
A.PRICING_PT_ID,
A.PRICE,
A.ASSET_ID
FROM T_PRC_MASTER A
WHERE A.PRC_DT < TO_DATE('05/27/08','MM/DD/YY')
AND A.ROLLED_FLG = 'Y'
AND A.END_ROLL_FLAG != 'Y'),
M2 AS
(SELECT /*+ MATERIALIZE */ B.PRICING_PT_ID, B.PRICE
FROM T_PRC_MASTER B
WHERE B.PRC_DT >= TO_DATE('05/27/08','MM/DD/YY'))
DELETE T_PRC_MASTER A
WHERE ROWID IN (
SELECT M1.RID FROM M1,M2
WHERE M1.PRICING_PT_ID =
M2.PRICING_PT_ID
AND M1.PRICE <> M2.PRICE
)
OR
A.ASSET_ID IN
(SELECT ASSET_ID FROM TA);
Final candidate I would weight:
DELETE T_PRC_MASTER TPM
WHERE TPM.ROLLED_FLG = 'Y'
AND TPM.END_ROLL_FLAG != 'Y'
AND TPM.PRC_DT < TO_DATE('05/27/08','MM/DD/YY')
AND (
TPM.ROWID IN
(SELECT /*+ USE_HASH(M1 M2) */ M1.RID FROM
(SELECT /*+ PUSH_SUBQ */
A.ROWID RID,
A.PRICING_PT_ID,
A.PRICE
FROM T_PRC_MASTER A
WHERE A.PRC_DT < TO_DATE('05/27/08','MM/DD/YY')
) M1,
(SELECT /*+ PUSH_SUBQ */
B.PRICING_PT_ID,
B.PRICE
FROM T_PRC_MASTER B
WHERE B.PRC_DT >= TO_DATE('05/27/08','MM/DD/YY')) M2
WHERE M1.PRICING_PT_ID = M2.PRICING_PT_ID AND
M1.PRICE != M2.PRICE
)
OR TPM.ASSET_ID IN --OR THE WHERE EXIST VERSION
(SELECT C.ASSET_ID FROM T_ASSET C
WHERE C.PRICE IN (0.00000001,0.00000002)
AND C.ASSET_TYP_CD = 16)
);
Just throwing out things to just compare performance until
further information as requested.
Again, sorry if I missed syntax; from here out I will
be connected to Oracle at the same time I post but
I just could not resist my duty to defend my
good friend the full table scan.!
Best Regards,
Harry
|
|
|
Re: Tuning of delete stmt [message #346557 is a reply to message #346556] |
Mon, 08 September 2008 19:36 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) |
harrysmall3
Messages: 109 Registered: April 2008 Location: Massachusetts
|
Senior Member |
|
|
oops - blatant typo i caught post-click!
Quote: |
WITH TA AS
(SELECT /*+ MATERIALIZE */ C.ASSET_ID FROM T_ASSET C
WHERE A.PRICE IN (0.00000001,0.00000002)
AND C.ASSET_TYP_CD = 16 ),
|
All fields should be prefaced with C. A.PRICE was my bad!
sorry
-H
|
|
|