Preparing Delete Script
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 6 of 6

Thread: Preparing Delete Script

  1. #1
    Join Date
    May 2002
    Location
    USA
    Posts
    462

    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 ..
    Last edited by prakashs43; 09-17-2004 at 11:51 AM.

  2. #2
    Join Date
    Oct 2002
    Posts
    807
    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.

  3. #3
    Join Date
    May 2002
    Location
    USA
    Posts
    462
    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

  4. #4
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    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.
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

  5. #5
    Join Date
    May 2002
    Location
    USA
    Posts
    462
    THANKS SLIMDAVE ..


    appreciate if anyone has such a script . badly need that .
    Last edited by prakashs43; 09-17-2004 at 05:41 PM.

  6. #6
    Join Date
    Aug 2001
    Location
    chennai,bangalore
    Posts
    840
    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
    anandkl

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width