Where is the ROWID fetched from when queried?

Question

For example, if I do

SELECT ROWID, name from emp where age > 30;

As ROWID does not take any storage space, is it calculated every time a query like this runs?

---

From here,

Each table in an Oracle database internally has a pseudocolumn named ROWID. This pseudocolumn is not evident when listing the structure of a table by executing a SELECT * FROM ... statement, or a DESCRIBE ... statement using SQL*Plus, nor does the pseudocolumn take up space in the table.

---

Taking clue from the comments,

ROWID is stored when you create an index. Suppose I have no other index than the implicit one for the primary key (emp_id). In this case, will the above query go to this implicit index? How will the ROWID calculation happen?

Please note that name and age columns are not a part of the index.

Answer 1

Hi Primx,

the rowid is an attribute of the row. It contains information relative to the location of the block (file number, block number) and location of the row within this block.

This information is not really "calculated" when queried, but merely assembled from the information in the block header of the row.

Answer 2

"Suppose I have no other index than the implicit one for the primary key (emp_id). In this case, will the above query go to this implicit index? How will the ROWID calculation happen?"

Firstly, the "implicit index" is a real index. If we create a primary or unique key on a table and no index exists on the key column(s) Oracle we create an index, with the same name as the constraint.

SQL> create table t72
  2      ( emp_id number not null primary key
  3        , name varchar2(10) not null
  4        , age number(3,0) )
  5  /

Table created.

SQL> select constraint_name from user_constraints
  2  where table_name = 'T72'
  3  and constraint_type='P'
  4  /

CONSTRAINT_NAME
------------------------------
SYS_C001145039

1 row selected.

SQL> select index_type, uniqueness
  2  from user_indexes
  3  where index_name = 'SYS_C001145039'
  4  /

INDEX_TYPE                  UNIQUENES
--------------------------- ---------
NORMAL                      UNIQUE

1 row selected.

SQL>

Secondly, the query filters on the AGE column. So the optimizer would ignore any index on EMP_ID. On that case the database will do a full table scan of EMP, evaluating the value of each AGE column it retrieves. For each record where AGE < 30 it will concatenate the table's object number, the block number, the slot number and the file number into a ROWID.

If you want to understand more about ROWID have a play around with the DBMS_ROWID package. René Nyffenegger has a useful tutorial on his website. Find out more.

---

"Suppose it was SELECT ROWID, name from emp where emp_id > 100;. Would the query fetch the ROWID from the emp_id index? "

There's one easy way to tell: experimentation. First we create an index on a table with a lot of records, and freshen the statistics:

SQL> create unique index big_i on big_emp (empno)
  2  /

Index created.

SQL> exec dbms_stats.gather_table_stats(user, 'BIG_EMP', cascade=>true)

PL/SQL procedure successfully completed.

SQL>

Then we see how Oracle tackles the query:

SQL> explain plan for
  2      select empno, rowid from big_emp
  3      where empno > 10000;

Explained.

SQL> select * from table(dbms_xplan.display)
  2  /

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------
Plan hash value: 3238483832

------------------------------------------------------------------------------
| Id  | Operation            | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |       | 24319 |   403K|    16   (0)| 00:00:01 |
|*  1 |  INDEX FAST FULL SCAN| BIG_I | 24319 |   403K|    16   (0)| 00:00:01 |
------------------------------------------------------------------------------

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

   1 - filter("EMPNO">10000)

13 rows selected.

SQL>

If Oracle can satisfy the query with just indexed columns it doesn't touch the table. Clearly here it is retreiving the ROWID from the index.