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
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?
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.
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.
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.