Forum Stats

  • 3,815,078 Users
  • 2,258,959 Discussions
  • 7,892,939 Comments

Discussions

Categories

What does an Index exactly contan?

AB312062
AB312062 Member Posts: 25
edited Apr 9, 2013 1:56PM in SQL & PL/SQL
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
Tagged:
· Share on TwitterShare on Facebook

Best Answer

  • Karthick2003
    Karthick2003 Member Posts: 13,711 Bronze Badge
    Answer ✓
    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.
    · Share on TwitterShare on Facebook

Answers

  • jeneesh
    jeneesh Member Posts: 7,168
    edited Apr 3, 2013 2:29AM
    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
    jeneesh
    · Share on TwitterShare on Facebook
  • Karthick2003
    Karthick2003 Member Posts: 13,711 Bronze Badge
    Answer ✓
    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.
    · Share on TwitterShare on Facebook
  • AB312062
    AB312062 Member Posts: 25
    Thanks Both. its helped :)
    · Share on TwitterShare on Facebook
This discussion has been closed.