Home » RDBMS Server » Performance Tuning » Why delete a where rowid=:1 go to index? (oracle 10g 10203)
Why delete a where rowid=:1 go to index? [message #556237] Fri, 01 June 2012 02:20 Go to next message
zengmuansha
Messages: 26
Registered: April 2012
Junior Member
SQL> create table a ( id number(5) not null,name varchar(35),create_time date);

Table created

Executed in 0.172 seconds

SQL> create index idx_a_id on a(id);

Index created

Executed in 0.032 seconds

SQL> exec dbms_stats.gather_table_stats(ownname=>'OSS03',tabname=>'A',cascade=>true);

PL/SQL procedure successfully completed

Executed in 0.125 seconds

SQL> set autot traceonly
SQL> delete a where rowid='AAAXXDFFESDFA';


----------------------------------------------------------
Plan hash value: 2233874139

-----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------
| 0 | DELETE STATEMENT | | 1 | 25 | 0 (0)| 00:00:01 |
| 1 | DELETE | A | | | | |
|* 2 | INDEX FULL SCAN| IDX_A_ID | 1 | 25 | 0 (0)| 00:00:01 |
-----------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

2 - filter(ROWID=CHARTOROWID('AAAXXDFFESDFA'))

state info
----------------------------------------------------------
1 recursive calls
0 db block gets
1 consistent gets
0 physical reads
0 redo size
548 bytes sent via SQL*Net to client
483 bytes received via SQL*Net from client
4 SQL*Net roundtrips to/from client
2 sorts (memory)
0 sorts (disk)
0 rows processed

I find it is id column is not null ; Why the plan go to index?
Re: Why delete a where rowid=:1 go to index? [message #556244 is a reply to message #556237] Fri, 01 June 2012 03:06 Go to previous messageGo to next message
andy huang
Messages: 498
Registered: July 2011
Senior Member
Hi,
Can you set the id column to null and try you test?

  alter table a modity id null;
Re: Why delete a where rowid=:1 go to index? [message #556246 is a reply to message #556244] Fri, 01 June 2012 03:37 Go to previous messageGo to next message
andy huang
Messages: 498
Registered: July 2011
Senior Member
Michel,
can you explain detail?
Re: Why delete a where rowid=:1 go to index? [message #556258 is a reply to message #556246] Fri, 01 June 2012 04:47 Go to previous messageGo to next message
Michel Cadot
Messages: 67946
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
I think it is just meaningless because there is no row in the table.
When you insert some you actually get the following:
SQL> insert into a select level, 'a', sysdate from dual connect by level <100000;

99999 rows created.

SQL> commit;

Commit complete.

SQL> exec dbms_stats.gather_table_stats(ownname=>user, tabname=>'A',cascade=>true);

PL/SQL procedure successfully completed.

SQL> 
SQL> set autot traceonly
SQL> delete a where rowid='AAAXXDFFESDFA';
delete a where rowid='AAAXXDFFESDFA'
                     *
ERROR at line 1:
ORA-01410: invalid ROWID

Regards
Michel
Re: Why delete a where rowid=:1 go to index? [message #556259 is a reply to message #556258] Fri, 01 June 2012 04:48 Go to previous messageGo to next message
Michel Cadot
Messages: 67946
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
And if you give a real rowid you get:
SQL> set autotrace off
SQL> select rowid from a where rownum=1;
ROWID
------------------
AAAWJDAAEAAACyrACZ

1 row selected.

SQL> set autot traceonly
SQL> delete a where rowid='AAAWJDAAEAAACyrACZ';

1 row deleted.


Execution Plan
----------------------------------------------------------
Plan hash value: 1898483634

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

Regards
Michel
Re: Why delete a where rowid=:1 go to index? [message #556263 is a reply to message #556259] Fri, 01 June 2012 05:16 Go to previous messageGo to next message
zengmuansha
Messages: 26
Registered: April 2012
Junior Member
Thanks,
Michel,your test is ok! but in my test,it go to the index when the table A have any data,can you tell the detail?
Re: Why delete a where rowid=:1 go to index? [message #556269 is a reply to message #556263] Fri, 01 June 2012 05:50 Go to previous messageGo to next message
John Watson
Messages: 8592
Registered: January 2010
Location: Global Village
Senior Member
I get the expected result, when the table is empty and I use that impossible rowid:
orcl> create table a ( id number(5) not null,name varchar(35),create_time date);

Table created.

orcl> create index idx_a_id on a(id);

Index created.

orcl> exec dbms_stats.gather_table_stats(ownname=>'SCOTT',tabname=>'A',cascade=>true)

PL/SQL procedure successfully completed.

orcl> set autot traceonly exp
orcl> delete a where rowid='AAAXXDFFESDFA';

0 rows deleted.


Execution Plan
----------------------------------------------------------
Plan hash value: 1898483634

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

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access(CHARTOROWID('AAAXXDFFESDFA'))

Note
-----
   - automatic DOP: skipped because of IO calibrate statistics are missing

orcl>
This is 11.2.0.3.
Re: Why delete a where rowid=:1 go to index? [message #556271 is a reply to message #556269] Fri, 01 June 2012 06:09 Go to previous messageGo to next message
Michel Cadot
Messages: 67946
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
It seems a "bug" on 10.2 (not actually one as there is nothing to do).

Regards
Michel

[Updated on: Fri, 01 June 2012 06:12]

Report message to a moderator

Re: Why delete a where rowid=:1 go to index? [message #556273 is a reply to message #556263] Fri, 01 June 2012 06:10 Go to previous messageGo to next message
Michel Cadot
Messages: 67946
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
zengmuansha wrote on Fri, 01 June 2012 12:16
Thanks,
Michel,your test is ok! but in my test,it go to the index when the table A have any data,can you tell the detail?


No, it does not got to the index.
The optimizer finds there is no data and stop analyzing keeping the current state of its analyze that you see but there is NO execution of this plan.

Regards
Michel

[Updated on: Fri, 01 June 2012 06:11]

Report message to a moderator

Re: Why delete a where rowid=:1 go to index? [message #556283 is a reply to message #556271] Fri, 01 June 2012 07:35 Go to previous messageGo to next message
zengmuansha
Messages: 26
Registered: April 2012
Junior Member
Thanks,
Michel This is 10g 10203 bug . Do you know the bug number?
Re: Why delete a where rowid=:1 go to index? [message #556284 is a reply to message #556273] Fri, 01 June 2012 07:43 Go to previous messageGo to next message
zengmuansha
Messages: 26
Registered: April 2012
Junior Member
In on 10g database analyze after insert data,that plan is going index!
Re: Why delete a where rowid=:1 go to index? [message #556285 is a reply to message #556283] Fri, 01 June 2012 07:46 Go to previous messageGo to next message
Michel Cadot
Messages: 67946
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
No, I don't know, I don't know if it have been filed or not, for me it is not really a bug but anyway it is fixed in 11g.
By the way, I used 10.2.0.4.

Regards
Michel
Re: Why delete a where rowid=:1 go to index? [message #556286 is a reply to message #556284] Fri, 01 June 2012 07:47 Go to previous message
Michel Cadot
Messages: 67946
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
zengmuansha wrote on Fri, 01 June 2012 14:43
In on 10g database analyze after insert data,that plan is going index!


Not in my case, copy and paste what you did and got as I did it.

Regards
Michel
Previous Topic: Query optimize
Next Topic: block size
Goto Forum:
  


Current Time: Fri Sep 17 12:13:42 CDT 2021