What does an Index exactly contan?

AB312062

Hi

I have a doubt if index actually contains the rowid or the data values of column or both?

As per Oracle docs:
"An index is a schema object that contains an entry for each value that appears in the indexed column(s) of the table or cluster and provides direct, fast access to rows."

Can anybody explain this please? What happens when an index contains more than one column?

Also, I need some information in Index Organised tables. Is it a normal database table with index in it or something different? Any study material on this would be useful.


Thanks
AB

Karthick2003

A index contain ROWID and Column Value.

For example if you index the EMP table on EMPNO then the index will contain ROWID of each row from EMP table along with the value of column EMPNO.

A simple example using EMP table to show that Index stores column value.

I have a index on EMPNO. So when i select only EMPNO from EMP table you can see that oracle does not access the table at all.

SQL> set autotrace traceonly explain
SQL> 
SQL> select empno from emp;
 
Execution Plan
----------------------------------------------------------
Plan hash value: 1216028367
 
---------------------------------------------------------------------------
| Id  | Operation        | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT |        |    11 |    44 |     1   (0)| 00:00:01 |
|   1 |  INDEX FULL SCAN | EMP_PK |    11 |    44 |     1   (0)| 00:00:01 |
---------------------------------------------------------------------------
 

Oracle just picks up the value from the index by doing a INDEX FULL SCAN.

But at the same time if i select all the columns from EMP for a specific EMPNO like this

SQL> select * from emp where empno = 7839;
 
Execution Plan
----------------------------------------------------------
Plan hash value: 2741608761
 
--------------------------------------------------------------------------------------
| Id  | Operation                   | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |        |     1 |    38 |     1   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| EMP    |     1 |    38 |     1   (0)| 00:00:01 |
|*  2 |   INDEX UNIQUE SCAN         | EMP_PK |     1 |       |     1   (0)| 00:00:01 |
--------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   2 - access("EMPNO"=7839)

You can see that oracle scans the index for the specific EMPNO and with the ROWID it scans the EMP table.

jeneesh

AB312062 wrote:
Hi

I have a doubt if index actually contains the rowid or the data values of column or both?

As per Oracle docs:
"An index is a schema object that contains an entry for each value that appears in the indexed column(s) of the table or cluster and provides direct, fast access to rows."

Can anybody explain this please? What happens when an index contains more than one column?

An index will contain the value of the column(s) indexed and the ROWID of the corresponding row.ie., if you have an index INDEX TEST_NDX(COL1,COL2) => The index will contain the below entries (for every row in the table for which col1 or col2 is not null). The entries will be ordered..

COL1   COL2   ROWID
-------   ------    -----------
A       A           ROWID1
A       B           ROWID2
A       C           ROWID3
....

So when you have filter => WHERE col1 = 'A' and col2='B' :

Oracle check the index entries and find out the matching ROWIDs and get the rows required..

Also, I need some information in Index Organised tables. Is it a normal database table with index in it or something different? Any study material on this would be useful.

In index organized tables (IOT) data is stored similar to an index, in an ordered way, so that the data retrieval will be easy..

Read the Docs

Karthick2003

A index contain ROWID and Column Value.

For example if you index the EMP table on EMPNO then the index will contain ROWID of each row from EMP table along with the value of column EMPNO.

A simple example using EMP table to show that Index stores column value.

I have a index on EMPNO. So when i select only EMPNO from EMP table you can see that oracle does not access the table at all.

SQL> set autotrace traceonly explain
SQL> 
SQL> select empno from emp;
 
Execution Plan
----------------------------------------------------------
Plan hash value: 1216028367
 
---------------------------------------------------------------------------
| Id  | Operation        | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT |        |    11 |    44 |     1   (0)| 00:00:01 |
|   1 |  INDEX FULL SCAN | EMP_PK |    11 |    44 |     1   (0)| 00:00:01 |
---------------------------------------------------------------------------
 

Oracle just picks up the value from the index by doing a INDEX FULL SCAN.

But at the same time if i select all the columns from EMP for a specific EMPNO like this

SQL> select * from emp where empno = 7839;
 
Execution Plan
----------------------------------------------------------
Plan hash value: 2741608761
 
--------------------------------------------------------------------------------------
| Id  | Operation                   | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |        |     1 |    38 |     1   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| EMP    |     1 |    38 |     1   (0)| 00:00:01 |
|*  2 |   INDEX UNIQUE SCAN         | EMP_PK |     1 |       |     1   (0)| 00:00:01 |
--------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   2 - access("EMPNO"=7839)

You can see that oracle scans the index for the specific EMPNO and with the ROWID it scans the EMP table.

AB312062

Thanks Both. its helped