Results 1 to 8 of 8
  1. #1
    Join Date
    Feb 2003
    Location
    Romania
    Posts
    10

    Unanswered: Problem with delete statement

    Hi, this is the first post on this forum.
    I have a question for you:

    I have a table with 2500000 records. This table(PICTURE) has the following description:

    Id number PK
    Name varchar2(24)
    Description varchar2(128)
    ……………………………..


    My problem is with delete statement. For example “delete from PICTURE where id = 10” is time expensive, almost 10 seconds. The insert and select statement is very fast.
    What can I do to optimize the delete statement? Way the execution of the delete statement is so slow?

    Thanks a lot

  2. #2
    Join Date
    Apr 2003
    Location
    Greenville, SC (USA)
    Posts
    1,155
    Is the table indexed ?
    Have the statistics been updated on the index and the table?
    Are there any child tables (foreign keys) referencing the PICTURE
    table and they are not indexed ?

    Post an explain plan for the delete statement and let's see....

    HTH
    Gregg

  3. #3
    Join Date
    Mar 2004
    Location
    Colorado
    Posts
    49

    Re: Problem with delete statement

    Also check to make sure your data types match that id is a number field and your id=10 is a number. If they do not match oracle will convert the colum to the data type you used and this will bypass the index and do a full table scan. This will show up on the explain plan when you get it.

  4. #4
    Join Date
    Feb 2003
    Location
    Romania
    Posts
    10
    The explain plan for statement "delete from psi_usr_photo where pht_id=10" is:
    1.DELETE STATEMENT Cost=1
    2.1 DELETE- PSI_USR_PHOTO
    3.1 TABLE ACCESS(BY INDEX ROWID) - PSI_USR_PHOTO
    4.1 INDEX(UNIQUE SCAN) - PK_PSI_USR_PHOTO(UNIQUE)

    The real name for table PICTURE and PSI_USR_PHOTO.
    PSI_USR_PHOTO is in "many-to-many" relation with table PSI_USR_ALBUM. The relation is representing by PSI_PHOTO_ALBUM. The structure of this table is:

    album_id number FK from PSI_USR_ALBUM (PK1)
    photo_id number FK from PSI_USR_PHOTO (PK2)

    These two columns is PK in this table in the same order.

    Thanks in advance.

  5. #5
    Join Date
    Apr 2003
    Location
    Greenville, SC (USA)
    Posts
    1,155
    Do you have indexes on the child table (foreign keys) ???
    and are the statistics updated ???

    Gregg

  6. #6
    Join Date
    Feb 2003
    Location
    Romania
    Posts
    10
    Originally posted by gbrabham
    Do you have indexes on the child table (foreign keys) ???
    and are the statistics updated ???

    Gregg
    But the PK creates by default UNIQUE index on columns included on it.
    I don't know what do you mean about "are the statistics updated?"

  7. #7
    Join Date
    Mar 2004
    Location
    Colorado
    Posts
    49
    Do you have an on delete cascade on the main table? If you do your time is being spent cleaning up the album and phot tables. you need to check the delete command on these tables to make sure they are properly indexed.

  8. #8
    Join Date
    Apr 2003
    Location
    Greenville, SC (USA)
    Posts
    1,155
    analyze table tablename estimate statistics sample 20 percent;
    analyze index indexname estimate statistics sample 20 percent;

    Be sure your statistics are updated (if you are using the cost based optimizer) periodically...

    Gregg

Posting Permissions

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