Both seem almost identical plans. But it looks that the first will
execute faster.
You could also try this (if you have an option
Create table tmp1 as
Select * from table_a left join TABLE_B on a.trep_id=b.trep_id where
b.trep_id is null and bea_id=1;
Truncate table table_a;
Insert into table_a select * from tmp1;
Commit;
Deepak
-----Original Message-----
From: Amrish Kothari (RBIN/EDM1)
[mailto:oracledba-ezmlmshield-x23736342.[Email address protected]
Sent: Monday, June 12, 2006 11:20 PM
To: LazyDBA Discussion
Subject: Performance Tuning
Hi,
Can any body tell which one will be faster:
1 delete from TABLE_A where TPREP_ID in
2* (select TPREP_ID from TABLE_B where BEA_ID = 1)
Execution Plan
----------------------------------------------------------
0 DELETE STATEMENT Optimizer=CHOOSE (Cost=5 Card=723 Bytes=122
91)
1 0 DELETE OF 'TABLE_A'
2 1 NESTED LOOPS (Cost=5 Card=723 Bytes=12291)
3 2 TABLE ACCESS (FULL) OF 'TABLE_B' (Cost=2 Card=1
Bytes=10)
4 2 INDEX (RANGE SCAN) OF 'TPREP_ID_PLACED_I' (NON-UNIQUE)
(Cost=3 Card=723 Bytes=5061)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
3 consistent gets
0 physical reads
0 redo size
874 bytes sent via SQL*Net to client
728 bytes received via SQL*Net from client
3 SQL*Net roundtrips to/from client
2 sorts (memory)
0 sorts (disk)
0 rows processed
************************************************************************
**********************
1 delete from TABLE_A
2 where EXISTS (select 'x'
3 FROM TABLE_B
4 WHERE TPREP_ID = TABLE_A.TPREP_ID
5* AND BEA_ID = 1)
Execution Plan
----------------------------------------------------------
0 DELETE STATEMENT Optimizer=CHOOSE (Cost=7 Card=723 Bytes=122
91)
1 0 DELETE OF 'TABLE_A'
2 1 NESTED LOOPS (Cost=7 Card=723 Bytes=12291)
3 2 SORT (UNIQUE)
4 3 TABLE ACCESS (FULL) OF 'TABLE_B' (Cost=2 Card=
1 Bytes=10)
5 2 INDEX (RANGE SCAN) OF 'TPREP_ID_PLACED_I' (NON-UNIQUE)
(Cost=3 Card=723 Bytes=5061)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
3 consistent gets
0 physical reads
0 redo size
879 bytes sent via SQL*Net to client
801 bytes received via SQL*Net from client
3 SQL*Net roundtrips to/from client
3 sorts (memory)
0 sorts (disk)
Regds,
Amrish
--------
website: http://www.LazyDBA.com
Please don't reply to RTFM questions
Oracle documentation is here: http://tahiti.oracle.com
To unsubscribe: see http://www.lazydba.com/unsubscribe.html
To subscribe: see http://www.lazydba.com
By using this list you agree to these
terms:http://www.lazydba.com/legal.html
Oracle LazyDBA home page