FAQ
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='';

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

Search Discussions

  • Toon Koppelaars at Oct 5, 2009 at 10:40 am
    Am asking this because in my case Oracle is doing a full index scan
    Can you elaborate on that? Is the explain plan (or better tracefile) for
    that delete statement showing a full index scan?

    I would expect the following to happen:
    1) Oracle fetches the row directly using the rowid supplied in the delete
    statement.
    Then,
    2) Oracle reads all indexed column values of the fetched row, and uses these
    to delete the entries in the corresponding indexes.
    3) Oracle deletes the row from the table.
    On Mon, Oct 5, 2009 at 12:34 PM, DBA Deepak wrote:

    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='';

    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...
    --
    Toon Koppelaars
    RuleGen BV
    +31-615907269
    Toon.Koppelaars_at_RuleGen.com
    www.RuleGen.com
    TheHelsinkiDeclaration.blogspot.com

    (co)Author: "Applied Mathematics for Database Professionals"
    www.RuleGen.com/pls/apex/f?p=14265:13

    --
    http://www.freelists.org/webpage/oracle-l
  • DBA Deepak at Oct 5, 2009 at 10:47 am
    Thanks for the quick response.

    Yes the trace file shows that an index full scan is done for the said DELETE
    statement.

    Ideally Oracle should not have done full index scan considering the
    steps/logic provided by you.

    Another question comes to my mind after going through your response is:

    Which method is faster/efficient?

    1> Deletion based on rowid
    or
    2> deletion based on index values

    My application can provide the index values for the deletion as well.

    After examining the explain plan I found that the 2nd method above has less
    cost associated with it.

    I had the impression till now that DML operations based on the rowids are
    the fastest.
    On Mon, Oct 5, 2009 at 4:10 PM, Toon Koppelaars wrote:

    Am asking this because in my case Oracle is doing a full index scan
    Can you elaborate on that? Is the explain plan (or better tracefile) for
    that delete statement showing a full index scan?

    I would expect the following to happen:
    1) Oracle fetches the row directly using the rowid supplied in the delete
    statement.
    Then,
    2) Oracle reads all indexed column values of the fetched row, and uses
    these to delete the entries in the corresponding indexes.
    3) Oracle deletes the row from the table.

    On Mon, Oct 5, 2009 at 12:34 PM, DBA Deepak wrote:

    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='';

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


    --
    Toon Koppelaars
    RuleGen BV
    +31-615907269
    Toon.Koppelaars_at_RuleGen.com
    www.RuleGen.com <http://www.rulegen.com/>
    TheHelsinkiDeclaration.blogspot.com<http://thehelsinkideclaration.blogspot.com/>

    (co)Author: "Applied Mathematics for Database Professionals"
    www.RuleGen.com/pls/apex/f?p=14265:13<http://www.rulegen.com/pls/apex/f?p=14265:13>
    --
    Regards,

    Deepak
    Oracle DBA

    --
    http://www.freelists.org/webpage/oracle-l
  • Troach_at_gmail.com at Oct 5, 2009 at 11:10 am
    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

    -----Original Message-----
    From: DBA Deepak
    Date: Mon, 5 Oct 2009 16:04:09
    To: Oracle-L
    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='';

    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
  • Rjamya at Oct 5, 2009 at 11:26 am
    I think you are right ... when delete happens on a (indexed) column value,
    the index deletion should be quick but since rowid is used, a index ffs is
    probably the fastest oracle can do.
    On Mon, Oct 5, 2009 at 7:10 AM, 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?
    Raj
  • Harel Safra at Oct 5, 2009 at 12:44 pm
    <!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">

    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

    Date: Mon, 5 Oct 2009 16:04:09 +0530
    To: Oracle-L
    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='';

    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...
  • Nuno Souto at Oct 5, 2009 at 1:12 pm
    Agreed, but it sounds like a bug to me.
    It would be much faster to read the row
    given the rowid and then use whatever
    column values(s) come up in indexes
    to get rid of the index entry.
    I can sort of understand a non-unique index
    maybe using an index ffs, but a unique one?
    Yet, stranger things have been seen...
    I suppose it'd also depend on the index depth
    as well?
    Plenty of ideas for some in-depth investigation,
    I reckon!

    --
    Cheers
    Nuno Souto
    in wet Sydney, Australia
    dbvision_at_iinet.net.au

    rjamya wrote,on my timestamp of 5/10/2009 9:26 PM:
    I think you are right ... when delete happens on a (indexed) column
    value, the index deletion should be quick but since rowid is used, a
    index ffs is probably the fastest oracle can do.

    On Mon, Oct 5, 2009 at 7:10 AM, > 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?
    --
    http://www.freelists.org/webpage/oracle-l
  • Timur Akhmadeev at Oct 5, 2009 at 12:30 pm
    Hi,



    No, it’s not a bug. OP has an IOT. And since IOT and ROWID are not big friends <http://download.oracle.com/docs/cd/B19306_01/server.102/b14220/schema.htm#CNCPT911>, “table” access by physical rowid can’t be accomplished, hence, IFS is performed by Oracle.



    Thanks,

    Timur Akhmadeev

    -----Original Message-----
    From: oracle-l-bounce_at_freelists.org On Behalf Of Nuno Souto
    Sent: Monday, October 05, 2009 17:12
    Cc: Oracle-L
    Subject: Re: Delete based on rowid



    Agreed, but it sounds like a bug to me.

    It would be much faster to read the row

    given the rowid and then use whatever

    column values(s) come up in indexes

    to get rid of the index entry.

    I can sort of understand a non-unique index

    maybe using an index ffs, but a unique one?

    Yet, stranger things have been seen...

    I suppose it'd also depend on the index depth

    as well?

    Plenty of ideas for some in-depth investigation,

    I reckon!



    --

    Cheers

    Nuno Souto

    in wet Sydney, Australia

    dbvision_at_iinet.net.au





    rjamya wrote,on my timestamp of 5/10/2009 9:26 PM:
    I think you are right ... when delete happens on a (indexed) column
    value, the index deletion should be quick but since rowid is used, a
    index ffs is probably the fastest oracle can do. >
    On Mon, Oct 5, 2009 at 7:10 AM, > 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?




    --

    http://www.freelists.org/webpage/oracle-l





    --
    http://www.freelists.org/webpage/oracle-l
  • DBA Deepak at Oct 6, 2009 at 2:52 pm
    Thanks Govind, for sharing your experience.

    In our case the statistics are up to date. Am still trying to find out the
    possible reason. Any help is appreciated.

    On Tue, Oct 6, 2009 at 6:39 PM, Arumugam, Govind <
    Govind.Arumugam_at_verizonwireless.com> wrote:
    Deepak,

    I don't have posting privileges yet on the oracle-l list. But I think I
    have an answer to your question. Yes, Accessing a row based on ROWID is the
    fastest way in Oracle. I ran into a similar issue with a delete on AQ
    tables. After collecting statistics at the table level, the data access
    changed from INDEX FULL SCAN to TABLE ACCESS BY USER ROWID.

    SQL Text: delete from "VZW_SCM"."QT_VM_AUDITEVENT_DATA" where rowid = :1

    SQL Profile:

    Stat Name Statement Per Execution % Snap
    ---------------------------------------- ---------- -------------- -------
    Elapsed Time (ms) 4,580,561 21.5 9.0
    CPU Time (ms) 4,255,953 20.0 11.9
    Executions 212,673 N/A N/A
    Buffer Gets ########## 1,850.2 23.4
    Disk Reads 3 0.0 0.0
    Parse Calls 271 0.0 0.0
    Rows 212,104 1.0 N/A

    SQL Execution Plan

    -------------------------------------------------------------------------------
    Id | Operation | Name | Rows | Bytes | Cost
    (%CPU)|
    -------------------------------------------------------------------------------
    0 | DELETE STATEMENT | | | | 1 (100)|
    1 | DELETE | QT_VM_AUDITEVENT_DATA | |
    2 | INDEX FULL SCAN| SYS_C0012284 | 1 | 117 | 0
    (0)|

    After collecting stats:


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

    Hope this helps.

    Take care.

    Govind


    The information contained in this message and any attachment may be
    proprietary, confidential, and privileged or subject to the work
    product doctrine and thus protected from disclosure. If the reader
    of this message is not the intended recipient, or an employee or
    agent responsible for delivering this message to the intended
    recipient, you are hereby notified that any dissemination,
    distribution or copying of this communication is strictly prohibited.
    If you have received this communication in error, please notify me
    immediately by replying to this message and deleting it and all
    copies and backups thereof. Thank you.

    --
    Regards,

    Deepak
    Oracle DBA

    --
    http://www.freelists.org/webpage/oracle-l

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
grouporacle-l @
categoriesoracle
postedOct 5, '09 at 10:34a
activeOct 6, '09 at 2:52p
posts9
users7
websiteoracle.com

People

Translate

site design / logo © 2022 Grokbase