Page 1 of 2 12 LastLast
Results 1 to 15 of 16
  1. #1
    Join Date
    May 2003
    Location
    France
    Posts
    112

    Question Unanswered: full scan on delete statment

    Hi,

    I have a delete statment:
    DELETE FROM VALEURS_CARTE WHERE ICCID IN (
    SELECT ICCID FROM CARTE WHERE NUMERO_OF IN
    (SELECT NUMERO_OF FROM ORDRE_FABRICATION
    WHERE trunc(DATE_FIN) < trunc(SYSDATE) - 90
    AND ORDRE_FABRICATION.CODE_ETAT_OF IN (11,13))
    );

    It use FULL SCAN on all the table, but when i try the same query with a select, it use indexes; i've tried hints, but it doesn't affect the explain plan.

    How to force using indexes in delete statment ?

    Thanks in advance

  2. #2
    Join Date
    Jan 2004
    Location
    North Haven, CT
    Posts
    110

    Re: full scan on delete statment

    can you post both explain plans?

  3. #3
    Join Date
    May 2003
    Location
    France
    Posts
    112

    Re: full scan on delete statment

    Originally posted by lynden.zhang
    can you post both explain plans?
    i attach explain plans in a text file
    Attached Files Attached Files

  4. #4
    Join Date
    Feb 2004
    Location
    Viet Nam
    Posts
    6

    Re: full scan on delete statment

    if you use oracle built in function like trunc(), index will be invalid

  5. #5
    Join Date
    Mar 2002
    Location
    Reading, UK
    Posts
    1,137
    try

    WHERE DATE_FIN < trunc(SYSDATE) - 89

    Alan

  6. #6
    Join Date
    May 2003
    Location
    France
    Posts
    112
    Originally posted by AlanP
    try

    WHERE DATE_FIN < trunc(SYSDATE) - 89

    Alan
    DATE_FIN is not index column , only CODE_ETAT and NUMERO_OF.

    I found that rewrite the select with a join between CARTE and ORDRE_FABRICATION use indexes on both tables. But, the table VALEURS_CARTE is always access with fullSCAN.

    VALEURS_CARTE (iccid,code_variable, valeur) is partionned on iccid with PK=(iccid,code_variable)
    Parallel access is used on CARTE and VALEURS_CARTE.

    VALEURS_CARTE is 60 000 000 rows, carte is 3 000 000 and ordre_fabrication is 3 000.

    Why oracle is not using PK on valeurs_carte to access the table ?

  7. #7
    Join Date
    Jan 2004
    Location
    North Haven, CT
    Posts
    110
    Originally posted by ndu35
    DATE_FIN is not index column , only CODE_ETAT and NUMERO_OF.

    I found that rewrite the select with a join between CARTE and ORDRE_FABRICATION use indexes on both tables. But, the table VALEURS_CARTE is always access with fullSCAN.

    VALEURS_CARTE (iccid,code_variable, valeur) is partionned on iccid with PK=(iccid,code_variable)
    Parallel access is used on CARTE and VALEURS_CARTE.

    VALEURS_CARTE is 60 000 000 rows, carte is 3 000 000 and ordre_fabrication is 3 000.

    Why oracle is not using PK on valeurs_carte to access the table ?
    check what's the plan for
    delete from VALEURS_CARTE where iccid = -1;
    and
    delete from VALEURS_CARTE where iccid = (select ... subquery);

    Does VALEURS_CARTE have child table?

  8. #8
    Join Date
    May 2003
    Location
    France
    Posts
    112
    delete from VALEURS_CARTE where iccid = -1
    => full scan

    delete from VALEURS_CARTE where iccid = (select ... subquery);
    => full scan

    VALEURS_CARTE have FK on CODE_VARIABLE

    Originally posted by lynden.zhang
    check what's the plan for
    delete from VALEURS_CARTE where iccid = -1;
    and
    delete from VALEURS_CARTE where iccid = (select ... subquery);

    Does VALEURS_CARTE have child table?

  9. #9
    Join Date
    Jan 2004
    Location
    North Haven, CT
    Posts
    110
    Originally posted by ndu35
    delete from VALEURS_CARTE where iccid = -1
    => full scan

    delete from VALEURS_CARTE where iccid = (select ... subquery);
    => full scan

    VALEURS_CARTE have FK on CODE_VARIABLE
    If another table (child) references VALEURS_CARTE, you got to make sure the key column is indexed on the child table.

    Similar post here:

    http://www.dbforums.com/showthread.p...silly+question

  10. #10
    Join Date
    Jan 2004
    Location
    North Haven, CT
    Posts
    110
    is iccid a number field? otherwise
    delete from VALEURS_CARTE where iccid = -1 would be misleading.
    In that case try
    delete from VALEURS_CARTE where iccid = '-1' see what's the plan.

  11. #11
    Join Date
    May 2003
    Location
    France
    Posts
    112
    iccid is a varchar2; so, explain plan of
    delete from VALEURS_CARTE where iccid = '-1' use the correct index (PK_VALEURS_CARTE(iccid,code_variable))

    the FK is on CODE_VARIABLE => table VARIABLE (PK=Code_Variable); so the columns on both are indexed.

    I trie to disable FK but no effect.

  12. #12
    Join Date
    Jan 2004
    Location
    Scottsdale, AZ
    Posts
    106
    DELETE FROM VALEURS_CARTE WHERE ICCID IN (
    SELECT ICCID FROM CARTE WHERE NUMERO_OF IN
    (SELECT NUMERO_OF FROM ORDRE_FABRICATION
    WHERE trunc(DATE_FIN) < trunc(SYSDATE) - 90
    AND ORDRE_FABRICATION.CODE_ETAT_OF IN (11,13))
    )
    AND code_variable is not null ;

    Adding the 2nd part of the key may produce a range scan on the index. Your optimizer mode is first_rows, which is good for finding information and returning to the user asap for an online application, but not necessarily the best for deleting. Try hinting the delete with a choose or rule hint and see what the explain plan shows.
    "Take Control!
    Make The Choice!
    Accept The Consequences!
    Deal With The Results! "

  13. #13
    Join Date
    May 2003
    Location
    France
    Posts
    112

    Unhappy

    It use Full access also

  14. #14
    Join Date
    Jan 2004
    Location
    North Haven, CT
    Posts
    110
    Originally posted by ndu35
    It use Full access also
    i did some test.

    delete from TMP_INTRSCH_PARAM_VALUES where TIPV_SESSION_ID in (
    select rownum from INTRSCH_ESP_EPRTGS where rownum < 7)
    index used

    delete from TMP_INTRSCH_PARAM_VALUES where TIPV_SESSION_ID in (
    select rownum from INTRSCH_ESP_EPRTGS where rownum < 8)
    full table scan

    It sounds like the optimizer really thinks full table scan is the best path in your case. Depending on how many rows returned in your subquery and how many distinct values in your main table, optimizer will use table scan or index scan based on what ever he thinks the best.

    The only reason why the plan for delete and for select is different is probably delete will ignore first_rows, but i am not sure about this.

    Can you put a distinct in the subquery to see whether it changes the plan.

  15. #15
    Join Date
    Jan 2004
    Location
    North Haven, CT
    Posts
    110
    I put an hint and the index is used in my case.

    delete /*+ index (TMP_INTRSCH_PARAM_VALUES INTRSCH_TIPV_NUIDX) */
    from TMP_INTRSCH_PARAM_VALUES where TIPV_SESSION_ID in (
    select rownum from INTRSCH_ESP_EPRTGS where rownum < 8)

    index used

Posting Permissions

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