Results 1 to 7 of 7

Thread: Delete tuning

  1. #1
    Join Date
    Nov 2003
    Location
    India
    Posts
    114

    Unanswered: Delete tuning

    Hello Friends

    I am writing a automated job for housekeeping of 1 table .

    Name of table :csx_update

    Number of rows more tahn 6 months old : 32000000
    (This is my purging criteria)

    Additional Info :

    PHP Code:

    SQL
    desc CSX_UPDATE_LIST
     Name                                                              Null
    ?    Type
     
    ----------------------------------------------------------------- -------- --------------------------------------------
     
    CID                                                               NOT NULL NUMBER(12)
     
    SOURCE_CD                                                         NOT NULL VARCHAR2(10)
     
    PROC_STS_CD                                                       NOT NULL VARCHAR2(10)
     
    BUILD_DT                                                          NOT NULL DATE
     QUEUE_DT                                                                   DATE
     PROC_PRIORITY_CD                                                  NOT NULL NUMBER
    (1)
     
    PROC_RQST_DT                                                      NOT NULL DATE
     CURR_CARD_NO                                                               VARCHAR2
    (20)
     
    MEMB_STAT                                                                  VARCHAR2(1)
     
    UPDATE_MSG                                                                 VARCHAR2(3000)


    Indexes defined:
    1.   X_CSX_UPDATE_LIST01 Index PROC_STS_CDPROC_PRIORITY_CDPROC_RQST_DTCID
    2.   X_CSX_UPDATE_LIST02 
    Index CIDPROC_STS_CD 

    I want to best optimized delete to be used in script. I have tried

    PHP Code:
    DELETE FROM csx_update_list
    where  proc_sts_cd  
    =  'P'
          
    and  proc_rqst_dt <  ADD_MONTHS(SYSDATE,-6
          and 
    rownum <= 25000;

    Explain Plan  :  

    ACCESS_PATH
    ------------------------------------------------------------------------------------------------------------------------
    DELETE STATEMENT   Cost=13839
      DELETE  CSX_UPDATE_LIST
        COUNT STOPKEY
          TABLE ACCESS FULL CSX_UPDATE_LIST


    and


    DELETE /*+ PARALLEL ( cul, 4) */
    FROM csx_update_list cul
    where  proc_sts_cd  
    =  'P'
          
    and  proc_rqst_dt <  ADD_MONTHS(SYSDATE,-6)
          and 
    rownum <= 25000;


    EXPLAIN PLAN

    ACCESS_PATH
    ------------------------------------------------------------------------------------------------------------------------
    DELETE STATEMENT   Cost=3460
      DELETE  CSX_UPDATE_LIST
        COUNT STOPKEY
          TABLE ACCESS FULL CSX_UPDATE_LIST 
    Kindlt suggest which is the best and fastest way to delete the data. If i use parallel hint are there any drawbacks in this.. (Opening parallel sessions)..

    Kindly have a look and suggest.

  2. #2
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    This won't answer your question, but - just an idea: how many records would you like to keep in the table? Would it be faster (better?) if you use the CTAS approach:

    CREATE TABLE remaining_records AS SELECT
    * FROM original_table
    WHERE criteria;

    Now drop the original table and rename newly created one into an old one.

  3. #3
    Join Date
    Mar 2002
    Location
    Reading, UK
    Posts
    1,137
    If your index had proc_sts_cd, proc_rqst_dt as the first columns it may run a lot quicker. Alternatively create a new index with just proc_sts_cd, proc_rqst_dt.

    Alan

  4. #4
    Join Date
    Mar 2002
    Location
    Reading, UK
    Posts
    1,137
    The other possibility is to list partition by proc_rqst_dt and the delete from the partition required.

    Alan
    Last edited by AlanP; 09-10-06 at 04:39.

  5. #5
    Join Date
    Nov 2003
    Location
    India
    Posts
    114

    Production env limitations

    Hello

    Thanks for the help you have povided. But due to limitations while working in Production system I cant follow the CTAS approch as there is no permission to drop and recreate database objects.
    Also requesting for new indexes will be difficult.
    I would like to see in partition option but as i have not much prior knowledge of partitioning it can take some time.

    With given sources and indexes what is the approch I should follow in DELETE statement. As mentioned delete with parallel hint has low cost rather than simple delete. Shuld i go with this approch or there are better ways to write DELETES.
    Last edited by varun_751980; 09-10-06 at 14:44.

  6. #6
    Join Date
    Apr 2004
    Posts
    246
    the cost is meaningless. from the oracle doc:
    "The cost is an estimated value proportional to the expected resource use needed to execute the statement with a particular plan. The optimizer calculates the cost of access paths and join orders based on the estimated computer resources, which includes I/O, CPU, and memory."

    when you give a hint, the CBO adjusts the costs of possible operations to make the cost of the hinted operation (parallel in this case) to be lower, and therefore more attractive, so that at run time, the hint will be used.

    want to know which is faster? run them both on a test system.

    will parallel be faster? most likely. is the expense of starting extra process threads to run the parallel deletes too much? I don't think so, but only you would know for certain with your system.
    Give a man a fish, you feed him for a day. Club him over the head with a fish, he'll leave you alone.

  7. #7
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    When you use CTAS, do NOT drop the original table. That has the potential to mess up a lot of dependecies. Do something like the following (if foreign keys allow)

    create table csx_update_list_temp as
    select * from
    FROM csx_update_list
    cul
    where (proc_sts_cd
    <> 'P' )
    or (proc_sts_cd = 'P'
    and proc_rqst_dt >=ADD_MONTHS(SYSDATE,-6) );

    truncate table csv_update_list_temp;

    insert into csx_update_list
    select * from
    csx_update_list_temp;

    drop table
    csx_update_list;


    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

Posting Permissions

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