ROWIDs from an Index

Earlier today there was a question : "can i use rowid as a column in index columns ?"

It's clear that you cannot index the ROWID :

SQL> connect hemant/hemant
Connected.
SQL> create table obj_list_tbl as select * from dba_objects;

Table created.

SQL> select rowid from obj_list_tbl where object_id between 1001 and 1005;

ROWID
------------------
AAAU1bAAEAAAEU+ABG
AAAU1bAAEAAAEU+ABH
AAAU1bAAEAAAEU+ABI
AAAU1bAAEAAAEU+ABJ
AAAU1bAAEAAAEU+ABK

SQL> create index obj_list_tbl_rid_ndx on obj_list_tbl(rowid);
create index obj_list_tbl_rid_ndx on obj_list_tbl(rowid)
                                                  *
ERROR at line 1:
ORA-00904: : invalid identifier


SQL> create index obj_list_tbl_oid_ndx on obj_list_tbl(object_id);

Index created.

SQL> 

Although we can retrieve the ROWID for each row in the table, we cannot index it as it is a pseudo-column.

However, can we use an Index to retrieve *all* the ROWIDs of a table, without having to do a FullTableScan ?
Yes  Since an Index is essentially a list of KeyValues and matching ROWIDs, we can read ROWIDs from an Index.

SQL> explain plan for select rowid from obj_list_tbl where object_id between 1001 and 1005;

Explained.

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 2458170317

-----------------------------------------------------------------------------------------
| Id  | Operation        | Name                 | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT |                      |     5 |   125 |     2   (0)| 00:00:01 |
|*  1 |  INDEX RANGE SCAN| OBJ_LIST_TBL_OID_NDX |     5 |   125 |     2   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------

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

   1 - access("OBJECT_ID">=1001 AND "OBJECT_ID"<=1005)

Note
-----
   - dynamic sampling used for this statement (level=2)

17 rows selected.

SQL> 
SQL> exec dbms_stats.gather_table_stats('HEMANT','OBJ_LIST_TBL',estimate_percent=>100,cascade=>TRUE);

PL/SQL procedure successfully completed.

SQL> explain plan for select rowid from obj_list_tbl where object_id between 1001 and 1011;

Explained.

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 2458170317

-----------------------------------------------------------------------------------------
| Id  | Operation        | Name                 | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT |                      |    11 |   187 |     2   (0)| 00:00:01 |
|*  1 |  INDEX RANGE SCAN| OBJ_LIST_TBL_OID_NDX |    11 |   187 |     2   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------

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

   1 - access("OBJECT_ID">=1001 AND "OBJECT_ID"<=1011)

13 rows selected.

SQL> 

Thus, Oracle could do an Index Range Scan to retrieve ROWIDs. Why would we want ROWIDs ? Probably to identify the datafiles that contain the rows of interest :

SQL> l
  1  select file_name from dba_data_files
  2  where tablespace_name = 'USERS'
  3  and relative_fno in
  4  (select distinct row_file_relative_no
  5  from (select dbms_rowid.rowid_relative_fno(rowid) as row_file_relative_no
  6        from obj_list_tbl where object_id between 1001 and 1011)
  7* )
SQL> /

FILE_NAME
----------------------------------------------------------------
/home/oracle/app/oracle/oradata/orcl/users01.dbf

SQL>

Can we retrieve all the ROWIDs of the table from the Index ?

SQL> explain plan for
  2  select /*+ INDEX (O OBJ_LIST_OID_NDX) */  rowid from obj_list_tbl O;

Explained.

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 4249882908

----------------------------------------------------------------------------------
| Id  | Operation         | Name         | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |              | 76967 |   901K|   307   (1)| 00:00:04 |
|   1 |  TABLE ACCESS FULL| OBJ_LIST_TBL | 76967 |   901K|   307   (1)| 00:00:04 |
----------------------------------------------------------------------------------

8 rows selected.

SQL> 

Why did Oracle choose to do a FullTableScan ? In the absence of a WHERE clause, the requirement is for all the rows of the table. However, an Index can be used only if it is known to have captured all the rows of the table. If the Index is on a column that is NULLABLE (i.e. is not a NOT NULL), the Optimizer cannot be sure that it captures all the rows as NULLs are not indexed (in a regular B-Tree index). I need to confirm that all the rows are indexed.

SQL> alter table obj_list_tbl modify (object_id not null);

Table altered.

SQL> explain plan for
  2  select /*+ INDEX (O OBJ_LIST_OID_NDX) */  rowid from obj_list_tbl O;

Explained.

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 2780527016

---------------------------------------------------------------------------------------------
| Id  | Operation            | Name                 | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |                      | 76967 |   901K|    48   (0)| 00:00:01 |
|   1 |  INDEX FAST FULL SCAN| OBJ_LIST_TBL_OID_NDX | 76967 |   901K|    48   (0)| 00:00:01 |
---------------------------------------------------------------------------------------------

8 rows selected.

SQL> 

Now that the column has been validated to be a NOT NULL, the Optimizer can be certain that every row has been indexed by an Index on this column. So, it can now use the Index.

.
.
.