Preparing Delete Script

Hi friends ,

i am preparing delete script

if i pass quest_id's hard coded is this process going to execute in a faster way ....

DELETE FROM SCORE_SYN0406F WHERE grp_id > 1 and QUEST_ID IN ( SELECT QUEST_ID FROM VERSION_ITEM WHERE VERSION_ID = 28831 AND RESP_SCALE_ID = 1 ) AND POINT_NBR > 5 ;

and explain plan is
--------------------

DELETE STATEMENT Optimizer=CHOOSE (Cost=24 Card=6 Bytes=306)
DELETE OF SCORE_SYN0406F
NESTED LOOPS (Cost=24 Card=6 Bytes=306)
TABLE ACCESS (BY INDEX ROWID) OF VERSION_ITEM (Cost=22 Card=1 Bytes=12)
INDEX (RANGE SCAN) OF XIF127VERSION_ITEM (NON-UNIQUE) (Cost=3 Card=1)
INDEX (RANGE SCAN) OF SCORE_PK_SYN0406F (UNIQUE) (Cost=2 Card=598 Bytes=23322)


primary key is grp_id , quest_id and point_nbr ..

Why don't you try it yourself? Hardcode values. Look at the new plan. Trace it. See for yourself.

It would've taken you less time to figure it yourself than to post the question here.

Hi ,

i have tested this with gard coded values in in clause it appears to be significantly fast ...

but afraid how to generate the IN CLAUSE SCRIPT any idea ...

thanks a ton in advance

It would probably be faster to supply a hard-coded list, but where are you going to get the list from? By running that subquery first, then using it to generate the list and then building the new SQL statement from it? That approach would definetely be worse than the all-in-one query.

THANKS SLIMDAVE ..


appreciate if anyone has such a script . badly need that .

create a temp table with all the quest_id to be deleted in it.
create table quest_temp as select quest_id from VERSION_ITEM WHERE VERSION_ID = 28831 AND RESP_SCALE_ID = 1 and then selecting from this table in your delete statement.

regards
anandkl