Re: Delete based on rowid

From: Harel Safra <harel.safra_at_gmail.com>
Date: Mon, 05 Oct 2009 14:44:54 +0200
Message-ID: <4AC9EA46.5000107_at_gmail.com>

Oracle does not need to scan the whole index to delete the indexed values. This can be tested easily:

SQL> select * from v$version;

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Prod
PL/SQL Release 10.2.0.3.0 - Production
CORE    10.2.0.3.0      Production
TNS for 32-bit Windows: Version 10.2.0.3.0 - Production
NLSRTL Version 10.2.0.3.0 - Production

SQL> _at_z
SQL> DROP TABLE TEST;

Table dropped.

SQL> CREATE TABLE TEST (idx_col1 NUMBER,idx_col2 NUMBER, padding VARCHAR2(1000));

Table created.

SQL>
SQL> INSERT INTO TEST
  2     SELECT     LEVEL, LEVEL, RPAD ('*', 1000, '*')
  3           FROM DUAL
  4     CONNECT BY LEVEL <= 10000;

10000 rows created.

SQL>
SQL> CREATE INDEX test_idx1 ON TEST(idx_col1);

Index created.

SQL> CREATE INDEX test_idx2 ON TEST(idx_col2);

Index created.

SQL>
SQL> EXEC dbms_Stats.gather_table_stats('test','test',estimate_percent=>100,degree=>4,cascade=>true);

PL/SQL procedure successfully completed.

SQL>
SQL> COL row_id new_value row_id
SQL>
SQL> SELECT row_id
  2    FROM (SELECT      ROWID row_id
  3              FROM TEST
  4          ORDER BY DBMS_RANDOM.VALUE)
  5   WHERE ROWNUM < 2;

ROW_ID
------------------
AAAK2xAAGAAACZCAAC

SQL>
SQL> SELECT idx_col1, idx_col2
  2    FROM TEST
  3   WHERE ROWID = '&row_id';
old   3:  WHERE ROWID = '&row_id'
new   3:  WHERE ROWID = 'AAAK2xAAGAAACZCAAC'

  IDX_COL1   IDX_COL2
---------- ----------
       129        129

SQL>
SQL> DELETE FROM TEST
  2        WHERE ROWID = '&row_id';
old   2:       WHERE ROWID = '&row_id'
new   2:       WHERE ROWID = 'AAAK2xAAGAAACZCAAC'

1 row deleted.

SQL>
SQL> SELECT *
  2    FROM TABLE (DBMS_XPLAN.display_cursor ());

PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------------------------

SQL_ID  3szgts95ndbj9, child number 0
-------------------------------------
DELETE FROM TEST       WHERE ROWID = 'AAAK2xAAGAAACZCAAC'

Plan hash value: 2251118889

------------------------------------------------------------------------------------
| Id  | Operation                   | Name | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------
|   0 | DELETE STATEMENT            |      |       |       |     1 (100)|          |
|   1 |  DELETE                     | TEST |       |       |            |          |
|   2 |   TABLE ACCESS BY USER ROWID| TEST |     1 |    20 |     1   (0)| 00:00:01 |
------------------------------------------------------------------------------------


14 rows selected.

SQL>

And the relevant part of the trace file:
PARSING IN CURSOR #35 len=57 dep=0 uid=177 oct=7 lid=177 tim=484572684976 hv=1989778350 ad='1e9e0c2c'
DELETE FROM TEST
      WHERE ROWID = 'AAAK2uAAGAAACjMAAE'
END OF STMT
PARSE #35:c=0,e=653,p=0,cr=0,cu=0,mis=1,r=0,dep=0,og=1,tim=484572684970
EXEC #35:c=0,e=161,p=0,cr=1,cu=6,mis=0,r=1,dep=0,og=1,tim=484572685301
STAT #35 id=1 cnt=1 pid=0 pos=1 obj=0 op='DELETE  TEST (cr=1 pr=0 pw=0 time=122 us)'
STAT #35 id=2 cnt=1 pid=1 pos=1 obj=44462 op='TABLE ACCESS BY USER ROWID TEST (cr=1 pr=0 pw=0 time=21 us)'

Harel Safra

troach_at_gmail.com wrote:

Since a btree index is organized by columns, not rowid (assuming its a btree index) oracle need to full scan it for that rowid. Since rowid points to the row in the table and not the index, it has no choice but to full scan the index. Someone please correct me if I'm wrong?

Sent from my Verizon Wireless BlackBerry

---

From: DBA Deepak <oracle.tutorials_at_gmail.com>

Date: Mon, 5 Oct 2009 16:04:09 +0530

To: Oracle-L<oracle-l_at_freelists.org>

Subject: Delete based on rowid

Have a simple question.

 

When we delete a row based on rowid from an indexed table, how the index entry gets deleted?

 

DELETE t WHERE rowid='<some rowid>';

 

Am asking this because in my case Oracle is doing a full index scan. Can we say deleting an indexed table based on rowid may not be the fastest way of deleting a row?

 

Please comment...

-- http://www.freelists.org/webpage/oracle-l Received on Mon Oct 05 2009 - 07:44:54 CDT