Discussions
Categories
- 196.7K All Categories
- 2.2K Data
- 235 Big Data Appliance
- 1.9K Data Science
- 449.8K Databases
- 221.5K General Database Discussions
- 3.8K Java and JavaScript in the Database
- 31 Multilingual Engine
- 549 MySQL Community Space
- 477 NoSQL Database
- 7.9K Oracle Database Express Edition (XE)
- 3K ORDS, SODA & JSON in the Database
- 532 SQLcl
- 4K SQL Developer Data Modeler
- 186.8K SQL & PL/SQL
- 21.2K SQL Developer
- 295.4K Development
- 17 Developer Projects
- 138 Programming Languages
- 292K Development Tools
- 104 DevOps
- 3.1K QA/Testing
- 645.9K Java
- 27 Java Learning Subscription
- 37K Database Connectivity
- 153 Java Community Process
- 105 Java 25
- 22.1K Java APIs
- 138.1K Java Development Tools
- 165.3K Java EE (Java Enterprise Edition)
- 17 Java Essentials
- 158 Java 8 Questions
- 85.9K Java Programming
- 79 Java Puzzle Ball
- 65.1K New To Java
- 1.7K Training / Learning / Certification
- 13.8K Java HotSpot Virtual Machine
- 94.2K Java SE
- 13.8K Java Security
- 203 Java User Groups
- 24 JavaScript - Nashorn
- Programs
- 390 LiveLabs
- 37 Workshops
- 10.2K Software
- 6.7K Berkeley DB Family
- 3.5K JHeadstart
- 5.6K Other Languages
- 2.3K Chinese
- 170 Deutsche Oracle Community
- 1K Español
- 1.9K Japanese
- 230 Portuguese
What does an Index exactly contan?

AB312062
Member Posts: 25
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
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:
Best 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 thisSQL> 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.
Answers
-
AB312062 wrote: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..
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?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 -
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 thisSQL> 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. -
Thanks Both. its helped
This discussion has been closed.