This discussion is archived
1 2 Previous Next 21 Replies Latest reply: Jan 15, 2013 10:38 PM by user575115 Go to original post RSS
  • 15. Re: Delete issue
    user575115 Newbie
    Currently Being Moderated
    Hi,
    result of the query
    OWNER                          TABLE_NAME                     CONSTRAINT_NAME                REMOTE_OWNER                   REMOTE_TABLE                   
    ------------------------------ ------------------------------ ------------------------------ ------------------------------ ------------------------------ 
    IBIS                           CW_UPLOAD_QUEUE_ITEM_CAND      FK_CANDIDATE_QUEUE_ITEM        IBIS                           CW_UPLOAD_QUEUE_ITEM           
    IBIS                           CW_UPLOAD_QUEUE_ITEM_META      CW_UPLOAD_QUEUE_ITEM_META_FK   IBIS                           CW_UPLOAD_QUEUE_ITEM           
    IBIS                           CW_UPLOAD_LOG                  FK_UQFILENAME_UPLOAD_Q_ITEM    IBIS                           CW_UPLOAD_QUEUE_ITEM           
    
    3 rows selected
  • 16. Re: Delete issue
    Purvesh K Guru
    Currently Being Moderated
    user575115 wrote:
    Hi,
    I able to run this sql statement and currently it's returning 0 rows.
    Is this select statement need to be execute during delete statement?
    Yes.

    The motive is to check, while your Delete statement is running, if there is any session that is holding Locks on the rows and not firing a Commit/Rollback. If there exists such a statement, then the SELECT query will return the SID of the session holding lock and you can then further your investigation, if it is another Developer who has not committed the data or another session that is in execution.

    You might as well, parallelly, check the suggestions offered by others.
  • 17. Re: Delete issue
    user503635 Explorer
    Currently Being Moderated
    Hi,
    Are the below tables big ?
    CW_UPLOAD_QUEUE_ITEM_CAND
    CW_UPLOAD_QUEUE_ITEM_META
    CW_UPLOAD_LOG


    If you constantly get long deletion time, would suggest disable the foreign key constraints of the above tables related to CW_UPLOAD_QUEUE_ITMES, feel that should improve performance significantly. Just delete records in child tables is not enough, Oracle will still look into those tables for any child records if the constraints enabled.
  • 18. Re: Delete issue
    AlbertoFaenza Expert
    Currently Being Moderated
    user575115 wrote:
    Hi,
    result of the query
    OWNER                          TABLE_NAME                     CONSTRAINT_NAME                REMOTE_OWNER                   REMOTE_TABLE                   
    ------------------------------ ------------------------------ ------------------------------ ------------------------------ ------------------------------ 
    IBIS                           CW_UPLOAD_QUEUE_ITEM_CAND      FK_CANDIDATE_QUEUE_ITEM        IBIS                           CW_UPLOAD_QUEUE_ITEM           
    IBIS                           CW_UPLOAD_QUEUE_ITEM_META      CW_UPLOAD_QUEUE_ITEM_META_FK   IBIS                           CW_UPLOAD_QUEUE_ITEM           
    IBIS                           CW_UPLOAD_LOG                  FK_UQFILENAME_UPLOAD_Q_ITEM    IBIS                           CW_UPLOAD_QUEUE_ITEM           
    
    3 rows selected
    Hi,

    in your code I did not see any DELETE of CW_UPLOAD_LOG.

    Also in the child tables CW_UPLOAD_QUEUE_ITEM_CAND, CW_UPLOAD_QUEUE_ITEM_META and CW_UPLOAD_LOG for columns referenced you should have a unique index to avoid child tables lock while deleting records in the parent table CW_UPLOAD_QUEUE_ITEM.

    Just keep in mind that if you have a reference constraint, when you delete a record on a parent table, before deletion Oracle has to scan in all child tables that this record does not exists. If child tables don't have a unique index on column(s) referenced this is going to take longtime and is going to lock the child table (see No Index on the Foreign Key).

    Unless your child tables are empty this might be the reason of poor performance. It might be worth in some cases disable the referential integrity while performing a delete of many record and re-enable the constraint with VALIDATE later on.

    Regards.
    Al
  • 19. Re: Delete issue
    marcusafs Journeyer
    Currently Being Moderated
    You may want to check out this article on indexing FK columns in light of your specific circumstances. In general, make sure all FK columns are indexed.
    http://www.dba-oracle.com/t_foreign_key_indexing.htm
  • 20. Re: Delete issue
    user575115 Newbie
    Currently Being Moderated
    Hi,
    cw_upload_log table is newly created and it's not having any records as of now.Remaining two tables having uinique keys(_cand & _meta).                                                                                                                                                                                                                                                                                       
  • 21. Re: Delete issue
    user575115 Newbie
    Currently Being Moderated
    Thanks.
1 2 Previous Next

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points