There are more than one INDEX Type Part 1

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%’ ;

SEGMENT_NAME
———————————————————————–
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’;

COLUMN_NAME
———————————————————————–
TABLE_NAME

SELECT object_name FROM user_objects
WHERE object_name = ‘VIX_TBL_VIRTUAL’ ;

OBJECT_NAME
———————————————————————–
VIX_TBL_VIRTUAL

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

alter session set “_use_nosegment_indexes” = TRUE;

SET AUTOTRACE TRACEONLY
SELECT * FROM tbl_virtual
WHERE table_name = ‘T1';
SET AUTOTRACE OFF

.. INDEX RANGE SCAN| VIX_TBL_VIRTUAL ..

ALTER INDEX VIX_TBL_VIRTUAL REBUILD
*
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 10.2.0.1.0

References Used :
Index Internals presentation by Mr.Julian Dyke