1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

Internal query by Oracle performing ROWID based deletes

Discussion in 'Server Administration and Options' started by prashant, Jul 20, 2011.

  1. prashant

    prashant Forum Advisor

    Messages:
    85
    Likes Received:
    7
    Trophy Points:
    160
    Location:
    New Delhi, India
    Hi,

    I need to understand, under what circumstances Oracle will perform a rowID based delete on a table.

    There is no query in our application which performs "delete from table_name where ROWID='<ROWID>';

    Initially i thought, Oracle might perform this in case we have Foreign key constraints with "On delete cascade".

    But then simple employee,dept test case ruled this out:-

    Code (Text):
    SQL ID: 86yysp2npjrfq
    Plan Hash: 2980558250
    delete from dept
    where
     deptno=20


    call     count       cpu    elapsed       disk      query    current        rows
    ------- ------  -------- ---------- ---------- ---------- ----------  ----------
    Parse        1      0.00       0.00          0          0          0           0
    Execute      1      0.00       0.00          0          1          7           1
    Fetch        0      0.00       0.00          0          0          0           0
    ------- ------  -------- ---------- ---------- ---------- ----------  ----------
    total        2      0.00       0.00          0          1          7           1

    Misses in library cache during parse: 1
    Optimizer mode: ALL_ROWS
    Parsing user id: SYS

    Rows     Row Source Operation
    -------  ---------------------------------------------------
          0  DELETE  DEPT (cr=4 pr=0 pw=0 time=0 us)
          1   INDEX UNIQUE SCAN SYS_C007417 (cr=1 pr=0 pw=0 time=0 us cost=1 size=13 card=1)(object id 67630)


    Elapsed times include waiting on following events:
      Event waited on                             Times   Max. Wait  Total Waited
      ----------------------------------------   Waited  ----------  ------------
      SQL*Net message to client                       1        0.00          0.00
      SQL*Net message from client                     1        1.90          1.90
    ********************************************************************************

    SQL ID: bpjqaz14m0abf
    Plan Hash: 161811703
    delete from "SYS"."EMP"
    where
     "DEPTID" = :1


    call     count       cpu    elapsed       disk      query    current        rows
    ------- ------  -------- ---------- ---------- ---------- ----------  ----------
    Parse        1      0.00       0.00          0          0          0           0
    Execute      1      0.00       0.00          0          3          3           1
    Fetch        0      0.00       0.00          0          0          0           0
    ------- ------  -------- ---------- ---------- ---------- ----------  ----------
    total        2      0.00       0.00          0          3          3           1

    Misses in library cache during parse: 0
    Optimizer mode: CHOOSE
    Parsing user id: SYS   (recursive depth: 1)

    Rows     Row Source Operation
    -------  ---------------------------------------------------
          0  DELETE  EMP (cr=3 pr=0 pw=0 time=0 us)
          1   TABLE ACCESS FULL EMP (cr=3 pr=0 pw=0 time=0 us)

    ********************************************************************************
    Oracle does "delete from "SYS"."EMP" where "DEPTID" = :1" in case of ON DELETE CASCADE and does not do a ROWID based delete.

    Does anyone know of a scenario where Oracle will internally issue a ROWID based delete on a table?

    This is Oracle 11gR2 (11.2.0.1) On Solaris Sparc 64-bit 10.5

    Thanks in advance.

    Best Regards,
    Prashant
     
  2. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,351
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    Database recovery will execute such commands if transactions need to be 'replayed' after a 'shutdown abort' or a database crash. DBMS_LOGMNR will reveal such statements in the SQL_REDO and SQL_UNDO columns in the V$LOGMNR_CONTENTS view. See here:

    http://oratips-ddf.blogspot.com/2008/10/workin-in-mines.html