SQL> select rowid from a; ROWID ------------------ AAAQ/LAACAAABacAAA AAAQ/LAACAAABacAAB SQL> desc a; Name Null? Type ----------------------------------------- -------- ---------------------------- ID NOT NULL NUMBER(5) NAME VARCHAR2(35) CREATE_TIME DATE SQL> select index_name from user_indexes where table_name='A'; INDEX_NAME -------------------------------------------------------------------------------- IDX_A_ID SQL> delete from a; 2 rows deleted. SQL> commit; Commit complete. SQL> exec dbms_stats.gather_table_stats(ownname=>'TEST',tabname=>'A',cascade=>true); PL/SQL procedure successfully completed. SQL> set autotrace traceonly; SQL> delete from a where rowid='AAAQ/LAACAAABacAAA'; 0 rows deleted.
Execution Plan ---------------------------------------------------------- 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='AAAQ/LAACAAABacAAA')
Statistics ---------------------------------------------------------- 1 recursive calls 0 db block gets 1 consistent gets 0 physical reads 0 redo size 824 bytes sent via SQL*Net to client 739 bytes received via SQL*Net from client 3 SQL*Net roundtrips to/from client 2 sorts (memory) 0 sorts (disk) 0 rows processed SQL> set autotrace off; SQL> desc a; Name Null? Type ----------------------------------------- -------- ---------------------------- ID NOT NULL NUMBER(5) NAME VARCHAR2(35) CREATE_TIME DATE SQL> alter table a modify id null; Table altered. SQL> desc a; Name Null? Type ----------------------------------------- -------- ---------------------------- ID NUMBER(5) NAME VARCHAR2(35) CREATE_TIME DATE SQL> set autotrace traceonly; SQL> delete from a where rowid='AAAQ/LAACAAABacAAB'; 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 | -------------------------------------------------------------------------------- ----
Statistics ---------------------------------------------------------- 34 recursive calls 0 db block gets 5 consistent gets 0 physical reads 0 redo size 826 bytes sent via SQL*Net to client 739 bytes received via SQL*Net from client 3 SQL*Net roundtrips to/from client 2 sorts (memory) 0 sorts (disk) 0 rows processed