1.Classification of Indexes
Logical
- Single column or concatenated
The maximum number of columns in a composite key index is 32.However,the combined size of all the columns cannot exceed roughly one-third of the data block size.

- Unique or nonunique
A unique index guarantees that no rows of a table have duplicate values in the column that defines the index.
- Function-based
Function-based indexes can be created as either a B-tree or a bitmap index.
- Domain
A domain index is an application-specific (Text, Spatial) index that is created, managed, and accessed by routines supplied by an indextype.
It is called a domain index because it indexes data in application-specific domains.Only single-column domain indexes are supported.
You can build single-column domain indexes on columns having scalar, object, or LOB datatypes.

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

In a B-tree index on a nonpartitioned table:
. Key values are repeated if there are multiple rows that have the same key value.
. There is no index entry corresponding to a row that has all key columns that are NULL.Therefore a WHERE clause specifying NULL will always result in a full table scan.
. Restricted ROWID is used to point to the rows of the table, since all rows belong to the same segment.

- Bitmap
The advantages of Bitmap index:
.When a table has millions of rows and the key columns have low cardinality.
.When queries often use a combination of multiple WHERE conditions involving the OR operator
.When there is read-only or low update activity on the key columns.

2.Creating Indexes:Guidelines
Because index entries are smaller compared to the rows they index,index blocks tend to have more entries per block.For this reason,INITRANS should generally be higher
on indexes than on the corresponding tables.

Indexes and PCTFREE

The PCTFREE parameter for an index
The PCTFREE parameter for an index works differently from that of a table. This parameter is used only during creation of the index to reserve space for index entries that may need to
be inserted into the same index block. Index entries are not updated. When a key column is updated, this involves a logical delete of the index entry and an insert.

Maximum number of rows - Initial number of rows *100
—————————————————-
Maximum number of rows

PCTUSED cannot be specified for an index.Because index entries must be stored in the correct order,the user cannot control when an index block is used for inserts.

INITRANS sbould generally be higher on indexes than on the corresponding tables.

3.Create Bitmap indexes
CREATE_BITMAP_AREA_SIZE
The initialization parameter determines the amount of space taht will be used for storing bitmap segments in menory.The default value is 8M.
A larger value may lead to a faster index creation.If cardinality is vary small,this value can be set to a small value.

4.Allocating and Deallocating Index Space

1
2
3
4

ALTER INDEX [schema.]INDEX
{ALLOCATE EXTENT ([SIZE integer [K|M]]
[ DATAFILE ‘filename’ ])
| DEALLOCATE UNUSED [KEEP integer [ K|M ] ] }

Note: Index space is deallocated when the table on which the index built is truncated.Truncating a table results in truncation of the associated index.

5.Rebuilding Indexes
6.Drop Indexes
7.Identifiying Unused Indexes

1
2

ALTER INDEX summit.orders_id_idx
MONOTORING[NOMONITORING] USAGE

V$OBJECT_USAGE
Beginning with Oracle9i,statstics about the usage of an index can be gathered and displayed in V$OBJECT_USAGE.Each time the MONITORING USAGE caluse
is specified,V$OBJECT_USAGE will be reset for the specified index.The previous information is cleared or reset.

8.Obtaing Index Information
.DBA_INDEXES
.DBA_IND_COLUMNS
.DBA_IND_EXPRESSIONS:Provides information on function based indexes
.V$OBJECT_USAGE

Popularity: 33% [?]