Fahad Khan's Oracle Blog : Chapter 7 : MANAGING INDEXES

Chapter 7 : MANAGING INDEXES

CHAPTER # 7

MANAGING INDEXES

An index is a tree structure that allows direct access to a row in a table.

Indexes can be classified based on their logical design or on their physical implementation

LOGICAL: The logical classification group indexes from an application perspective.

1. Single column or concatenated

2. Unique or nonunique

3. Function-based

4. Domain

PHYSICAL: The physical classification is derived from the way the indexes are stored

partitioned or nonpartitioned
B-tree : Normal or reverse key
Bitmap

SINGLE COLUMN OR CONCATENATED:

A single column index has only one column in the index key.

For example: empno column in employee table.

A concatenated index also known as composite index is created on multiple columns in a table.

Columns in a concatenated index do not need to be in the same order as the column in the table, nor do they not need to bee adjacent.

For example: an index on deptno and job column in employee table.

The maximum no of column n a composite index key is 32.

UNIQUE AND NONUNIQUE INDEX:

Index can be unique or nonunique,

Unique indexes guarantee that no two row of a table have duplicate value in the key column

Nonunique index do not impose this restriction on the column values.

FUNCTION BASES INDEXES:

A function based index is created when using functions or expressions that involve one or more column in a table that is being indexed.

Function based index can be created as either a B-tree or a Bitmap index.

DOMAIN INDEX:

A domain index is an application-specific (text, spatial) index that is created managed and accessed by routine supplied by an index type.

PARTITIONED AND NONPARTITIONED INDEX:

Partitioned indexes are used for large tables to store index entries corresponding to an index in several segments.

Partitioning allows an index to be spread across many tablespaces, decreasing contention for the index lookup, and increasing manageability.

Partitioned indexes are often used with partitioned tables to improve scalability and manageability.

An index partition can be created for each table partition.

B-TREE INDEX

Although all the indexes use a B-tree structure, the term B-tree index is usually associated with an index that stores a list of ROWIDs for each key.

STRUCTURE OF B-TREE INDEX:

B-tree index has 3 parts

THE ROOT: at the top of the index the root which contain entries that point to the next level in the index.

THE BRANCH: at the next level are branch block, which in turn to block at the next level in the index.

THE LEAF: at the lowest level the leaf nodes, which contain the index entries that point to row in the table.

The leaf blocks are doubly linked to facilitate the scanning of the index in an ascending as well as descending order of the key values.

FORMAT OF INDEX LEAF ENTRIES:

An entry is made up of the following components:

· An entry header

· Key column length-value pairs

· ROWID of a column

EFFECT ON DML OPERATION IN INDEX:

INSERT: Insert work normally

DELETE: deleting a row result only a logical deletion. The spaces used by deleted row are not available for new entries until all the entries in the blocks are deleted.

UPDATE: Update to the key column result in a logical delete and on insert to the index

BITMAP INDEX:

Bitmap index are more advantageous than B-tree indexes in certain situations:

When a table has million of rows and key column have low-cardinality (duplicate value) that there are very few distinct value for the column.

For example: for Gender column and marital status column in a passport table.

When queries often use a combination of multiple

WHERE condition involving the OR operator

When there is read-only or low update activity on the key column.

STRUCTURE OF BITMAP INDEX:

A bitmap index also organized as a B-tree but the leaf nodes stores a bitmap for each key column value instead of list of ROWID.

USING A BITMAP INDEX:

The B-tree is used to locate the leaf nodes that contain bitmap segments for a given value of the key.

START ROWID and the bitmap segments are used to locate the row that contain the key value.

When changes are made to the key column in the table, bitmap must be modified. This result in locking of the relevant bitmap segments.

Because locks are acquired on the whole bitmap segment, a row that is covered by the bitmap cannot be updated by other transactions until the first transaction ends.

COMPARING B-TREE AND BITMAP INDEX

B-TREE BITMAP

Suitable for high-cardinality columns suitable for low-cardinality column

Update on keys relatively inexpensive update on key column very expensive

Inefficient for queries using OR predicates Efficient for queries using OR predicates

Useful for OLTP Useful for Data warehousing

CREATING B-TREE INDEXES:

An index can be created either in the account of the user who owns the table or in a different account, although it is generally created in the same account as the table

PRACTICAL #

SQL> create index scott.empno_idx

2 on scott.emp2(empno)

3 pctfree 30

4 storage(initial 200k next 200k

5 pctincrease 0 maxextents 50)

6 tablespace indx;

Index created.

SYNTAX OPTION: See detail Page 12-11

CREATING INDEX GUIDELINES:

Consider the following while creating an index

Balance query and DML needs
Place in separate tablespace.
Use uniform extent size:
Consider NOLOGGING for large indexes
INITRANS should generally be higher.

CREATING BITMAP INDEX:

PRACTICAL #

SQL> create BITMAP index scott.deptno_idx

2 on scott.emp2(deptno)

3 pctfree 30

4 storage(initial 200k next 200k

5 pctincrease 0 maxextents 50)

6 tablespace indx;

Index created.

CREATE_BITMAP_AREA_SIZE parameter

This initialization parameter determines the amount of space that will be used for storing bitmap segments in memory.

The default value is 8MB

A larger value may lead to a faster index creation.

If cardinality is very small, this value can set to a small value.

For high cardinality more memory is needed for optimal performance.

CHANGING STORAGE PARAMETERS FOR INDEXES

PRACTICAL #

Practical related to

Create table having 2 or 3 different value

Delete rows from table

Update rows from table

How to rebuild index

How to create index

How to analyze index

SQL> CREATE TABLE TAB4

(R NUMBER(2));

TABLE CREATED

SQL > INSERT INTO TAB4

VALUES

(2);

1 ROW CREATED

INSER UPTO 500 ROWS HAVING SAME VALUE

SQL > INSERT INTO TAB4

VALUES

(3);

1 ROW CREATED

INSER UPTO 100 ROWS HAVING VALUE 3

SQL > INSERT INTO TAB4

VALUES

(4);

1 ROW CREATED

INSER UPTO 50 ROWS HAVING VALUE 4

SQL > CREATE INDEX IND_TAB4

ON TAB4 (R) ;

INDEX CREATED