1 2 Previous Next 21 Replies Latest reply: Jan 16, 2013 12:38 AM by user575115 Go to original post RSS
      • 15. Re: Delete issue
        user575115
        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
          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
            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
              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
                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
                  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
                    Thanks.
                    1 2 Previous Next