Determination of ROWID Based on Index

SQL> select rowid from; Rowid ------------------ Aaaq/laacaaabacaaa Aaaq/laacaaabacaab SQL> DESC; 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; 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 cballs 0 db block gets 1 consistent gets 0 physical reads 0 redo size 824 bytes sent via SQL * Net to client 739 bytes encoded ed 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; 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; 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 CILS 0 dB block gets 5 consistent gets 0 physical reads 0 redo size 826 bytes sent via SQL * Net to client 739 bytes encoded ed via SQL * Net from client 3 SQL * Net roundtrips to/from client 2 sorts (memory) 0 sorts (disk) 0 rows processed

10053. the following part of the trace file is indexed.
***********************
Table Stats ::
Table: T Alias: T
# Rows: 0 # Blks: 1 AvgRowLen: 0.00
Index Stats ::
Index: I _T Col #: 1
LVLS: 0 # LB: 0 # DK: 0 LB/K: 0.00 DB/K: 0.00 CLUF: 0.00
***************************************
1-row tables: T [T] #0
***************************************
SINGLE TABLE ACCESS PATH
Column (#1001): ROWID (ROWID) no statistics (using defaults)
AvgLen: 10.00 Newcastle: 0 Nulls: 0 Density: 0.20.e + 000
Table: T Alias: T
Card: Original: 0 rounded: 1 computed: 0.00 non adjusted: 0.00
Access path: tablescan
Cost: 2.00 resp: 2.00 degree: 0
Cost_io: 2.00 cost_cpu: 7121
Resp_io: 2.00 resp_cpus: 7121
Access path: rowid
Cost: 1.00 resp: 1.00 degree: 0
Cost_io: 1.00 cost_cpu: 7271
Resp_io: 1.00 resp_cpus: 7271
Kkofmx: Index filter: "T". rowid = 'aaaq9uaacaaabacaab'
Access path: Index (fullscan)
Index: I _t
Resc_io: 0.00 resc_cpu: 200
Ix_sel: 1 ix_sel_with_filters: 1
Cost: 0.00 Resp: 0.00 Degree: 1
Best: AccessPath: IndexRange Index: I _T
Cost: 0.00 Degree: 1 Resp: 0.00 Card: 1.00 Bytes: 0

Table walking

 

INGLE TABLE ACCESS PATH
Column (#1001): ROWID (ROWID) no statistics (using defaults)
AvgLen: 10.00 Newcastle: 3 Nulls: 0 Density: 0.33333
Table: A Alias:
Card: Original: 3 Rounded: 1 Computed: 0.03 Non Adjusted: 0.03
Access Path: TableScan
Cost: 3.00 resp: 3.00 degree: 0
Cost_io: 3.00 cost_cpu: 36167
Resp_io: 3.00 resp_cpus: 36167
Access path: rowid
Cost: 1.00 resp: 1.00 degree: 0
Cost_io: 1.00 cost_cpu: 7271
Resp_io: 1.00 resp_cpus: 7271
Kkofmx: Index filter: "A". rowid = 'aaaq/laacaaabacaad'
Access path: Index (fullscan)
Index: idx_a_id
Resc_io: 1.00 resc_cpu: 7721
Ix_sel: 1 ix_sel_with_filters: 1
Cost: 1.00 resp: 1.00 degree: 1
One row card: 1.00
Best: accesspath: rowid
Cost: 1.00 degree: 1 resp: 1.00 card: 1.00 Bytes: 0

If the constraint is null and the PK constraint is used ~ Table access full is required, because the result must be accurate first.

If not null and the data bit is 0, the index block is 0. If the table is at least 1, the index cost is 0, and the CBO will choose a lower cost for execution.
The not null constraint ensures the accuracy of the results before selecting a low-cost index.

This article describes how to analyze and collect table information.