Extract(Read) > Transform(Test&Apply) > Load(Learn) Blog

January 3, 2007

There are more than one INDEX Type Part 1

Filed under: Oracle How To,Oracle Performance — H.Tonguç Yılmaz @ 8:35 am

Oracle uses B-trees to store indexes to speed up data access. With no indexes, you have to do a sequential scan on the data to find a value. For n rows, the average number of rows searched is n/2. This does not scale very well as data volumes increase.

When you create an index, Oracle fetches and sorts the columns to be indexed and stores the rowid along with the index value for each row. Then Oracle loads the index from the bottom up. For example, consider the statement:

CREATE INDEX employees_last_name ON employees(last_name);

Oracle sorts the employees table on the last_name column. It then loads the index with the last_name and corresponding rowid values in this sorted order. When it uses the index, Oracle does a quick search through the sorted last_name values and then uses the associated rowid values to locate the rows having the sought last_name value.

B*Tree Indexes are default indexing feature in Oracle. They are;

– Based on modified B*Tree algorithm,
– Contain branch blocks and leaf blocks,
– Blocks contain keys and data,
– Keys maintained in sorted order within blocks,
– All leaf blocks are at the same depth,
– All blocks are on average 75% full,
– Maximum number of B*tree levels is 24
– Maximum number of columns is 16 in 7.3 and below; 32 in 8.0 and above

This figure describes the Internal Structure of a B-tree Index.

There are several recent variants of B*tree indexes including;

Type Introduced
Bitmap Indexes 7.3.2
Index Organised Table 8.0
Partitioned Indexes 8.0
Reverse Key 8.0
LOB Index 8.0
Compressed 8.1.5
Function-Based Indexes 8.1.5
Descending 8.1.5
Virtual Indexes 8.1.5
Bitmap Join Indexes 9.0.1

1- Virtual / NoSegment Indexes

An undocumented “fake” index type. A virtual index is a non-physical (no-segments) index useful for evaluating whether the optimizer will benefit from index creation prior to creating a physical index;

DROP TABLE tbl_virtual PURGE ;
CREATE TABLE tbl_virtual AS
SELECT table_name, tablespace_name FROM all_tables;

CREATE INDEX vix_tbl_virtual
ON tbl_virtual(table_name) NOSEGMENT;

SELECT segment_name FROM user_segments
WHERE segment_name like ‘%VIRTUAL%’ ;

TBL_VIRTUAL -- only table

SELECT index_name, index_type FROM user_indexes
WHERE index_name = ‘VIX_TBL_VIRTUAL’; -- Virtual indexes have a data dictionary definition, but no associated segment

no rows selected

SELECT column_name FROM user_ind_columns
WHERE index_name = ‘VIX_TBL_VIRTUAL’;


SELECT object_name FROM user_objects
WHERE object_name = ‘VIX_TBL_VIRTUAL’ ;


exec dbms_stats.gather_table_stats(’HR’, ‘TBL_VIRTUAL’, CASCADE=>TRUE);

alter session set “_use_nosegment_indexes” = TRUE;

SELECT * FROM tbl_virtual
WHERE table_name = ‘T1';


ERROR at line 1:
ORA-08114: can not alter a fake index :)

Continue reading with Part 2

Testing Information : the scripts mentioned are tested on Oracle Database 10g Express Edition Release

References Used :
Index Internals presentation by Mr.Julian Dyke



  1. […] There are more than one INDEX Type Series Like Unique and Nonunique Indexes, Composite Indexes, Function-Based Indexes, Reverse Key Indexes, […]

    Pingback by H.Tonguç YILMAZ Blog » Last warning, I moved my blog - Son uyarı, taşındım — January 3, 2007 @ 8:39 am | Reply

  2. […] Part 2 Filed under: Oracle Performance, Oracle How To — H.Tonguç Yılmaz @ 8:02 am In Part 1 after a brief introduction on default indexing feature B*Tree Indexes in Oracle I mentioned Virtual […]

    Pingback by There are more than one INDEX Type Part 2 « H.Tonguç YILMAZ Oracle Blog — January 16, 2007 @ 8:11 am | Reply

  3. […] Filed under: Oracle How To, Oracle Performance — H.Tonguç Yılmaz @ 11:42 am In Part 1 after a brief introduction on default indexing feature B*Tree Indexes in Oracle I mentioned […]

    Pingback by There are more than one INDEX Type Part 3 « H.Tonguç YILMAZ Oracle Blog — April 8, 2007 @ 11:42 am | Reply

  4. A well studied article by Richard Foote on “rebuild your index when ….” type of mytes.

    Oracle B-Tree Index Internals : Rebuilding The Truth

    Comment by H.Tonguç Yılmaz — November 22, 2007 @ 9:52 am | Reply

  5. […] also wrote several Index related posts before, it was a good chance to ask Richard the question: Why Indexes? Not Tables, Clusters or […]

    Pingback by Two Days Oracle Index Internals Seminar by Richard Foote « H.Tonguç Yılmaz - Oracle Blog — November 18, 2008 @ 7:34 pm | Reply

RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s

Create a free website or blog at WordPress.com.

%d bloggers like this: