Results 1 to 6 of 6
  1. #1
    Join Date
    Jan 2004
    Posts
    99

    Smile Unanswered: delete's taking forever!!!

    Hi,

    I'm trying to run the SQL below which is taking forver to run, I have analyzed the table currently we have 16million rows in the child table.

    The first sql delete statement only takes minutes whilst the second takes forever.

    Basically we are deleting data which is older than X date from the parent table.

    1) [ no probs with this statement, due to number of rows ]

    delete At_search_parent where searchdate < to_date(20040101,'yyyymmdd');


    2) this is the problamatic SQL.

    delete At_search_child
    where searchid not in (select distinct searchId from At_search_parent);


    explain plan
    ***********


    Operation Object Name Rows Bytes Cost Object Node In/Out PStart PStop

    DELETE STATEMENT Hint=CHOOSE 823 K 3343
    DELETE AT_SEARCH_CHILD
    FILTER
    TABLE ACCESS FULL AT_SEARCH_CHILD 823 K 10 M 3343
    INDEX FULL SCAN PK_AT_SEARCH_PARENT 1 K 5 K 59


    any suggestions?

    thanks.

  2. #2
    Join Date
    Jan 2004
    Location
    Hyderabad, India
    Posts
    37
    Hi,

    You can try using the following query instead of your query as NOT IN is not fast compared to NOT EXISTS.

    delete At_search_child A
    where not exists ( select 1 from (select distinct searchId from At_search_parent ) B where a.searchid = b.searchId);

    This query should run faster.

    Please verify the results..
    Regards
    Suneel

  3. #3
    Join Date
    Jan 2004
    Posts
    99
    Iv'e looked at the explain plan.....the query seems more efficent

    see explain plans below:

    BEFORE
    ********

    Operation Object Name Rows Bytes Cost Object Node In/Out PStart PStop

    DELETE STATEMENT Hint=CHOOSE 823 K 3343
    DELETE AT_SEARCH_CHILD
    FILTER
    TABLE ACCESS FULL AT_SEARCH_CHILD 823 K 10 M 3343
    INDEX FULL SCAN PK_AT_SEARCH_PARENT 1 K 5 K 59



    AFTER
    ********


    Operation Object Name Rows Bytes Cost Object Node In/Out PStart PStop

    DELETE STATEMENT Hint=CHOOSE 823 K 3343
    DELETE AT_SEARCH_CHILD
    FILTER
    TABLE ACCESS FULL AT_SEARCH_CHILD 823 K 10 M 3343
    INDEX UNIQUE SCAN PK_AT_SEARCH_PARENT 1 4 1



    the second explain plan is now doing a INDEX UNIQUE SCAN rather than a full index scan.


    I'm not sure how much this will impact the execution time for the delete script, ???

  4. #4
    Join Date
    Jan 2004
    Location
    North Haven, CT
    Posts
    110
    Originally posted by zaki_mtk
    Iv'e looked at the explain plan.....the query seems more efficent

    see explain plans below:

    BEFORE
    ********

    Operation Object Name Rows Bytes Cost Object Node In/Out PStart PStop

    DELETE STATEMENT Hint=CHOOSE 823 K 3343
    DELETE AT_SEARCH_CHILD
    FILTER
    TABLE ACCESS FULL AT_SEARCH_CHILD 823 K 10 M 3343
    INDEX FULL SCAN PK_AT_SEARCH_PARENT 1 K 5 K 59



    AFTER
    ********


    Operation Object Name Rows Bytes Cost Object Node In/Out PStart PStop

    DELETE STATEMENT Hint=CHOOSE 823 K 3343
    DELETE AT_SEARCH_CHILD
    FILTER
    TABLE ACCESS FULL AT_SEARCH_CHILD 823 K 10 M 3343
    INDEX UNIQUE SCAN PK_AT_SEARCH_PARENT 1 4 1



    the second explain plan is now doing a INDEX UNIQUE SCAN rather than a full index scan.


    I'm not sure how much this will impact the execution time for the delete script, ???
    use create table as select with joins of parent table and child table.
    Then replace the child table with the new table created.

  5. #5
    Join Date
    Jan 2004
    Posts
    99
    can you explain further? does this apply to large tables? [non partitioned]?

    thanks.

  6. #6
    Join Date
    Jan 2004
    Location
    North Haven, CT
    Posts
    110
    Originally posted by zaki_mtk
    can you explain further? does this apply to large tables? [non partitioned]?

    thanks.
    This is especially great for very large tables. It will get rid of logging, which takes a lot of time.
    Use join between parent and child table is also much faster than not in, not exist, anti outer join etc.

Posting Permissions

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