¿Que es un INDEX?

Los indices se usan para mejorar el rendimiento de las operaciones sobre una tabla.

En general mejoran el rendimiento las SELECT y empeoran (minimamente) el rendimiento de los INSERT y los DELETE.

Una vez creados no es necesario nada más, oracle los usa cuando es posible (ver EXPLAIN PLAN).

En oracle existen tres tipos de indices:

1)Table Index:

CREATE [UNIQUE|BITMAP] INDEX [esquema.]index_name
     ON [esquema.]table_name [tbl_alias]
        (col [ASC | DESC]) index_clause index_attribs

2)Bitmap Join Index:

CREATE [UNIQUE|BITMAP] INDEX [esquema.]index_name
     ON [esquema.]table_name [tbl_alias]
        (col_expression [ASC | DESC])
           FROM [esquema.]table_name [tbl_alias]
              WHERE condition [index_clause] index_attribs

3)Cluster Index:

CREATE [UNIQUE|BITMAP] INDEX [esquema.]index_name
     ON CLUSTER [esquema.]cluster_name index_attribs  

las index_clauses posibles son:

LOCAL STORE IN (tablespace)

LOCAL STORE IN (tablespace)
 (PARTITION [partition
      [LOGGING|NOLOGGING]
      [TABLESPACE {tablespace|DEFAULT}]
      [PCTFREE int]
      [PCTUSED int]
      [INITRANS int]
      [MAXTRANS int]
      [STORAGE storage_clause]
      [STORE IN {tablespace_name|DEFAULT]
      [SUBPARTITION [subpartition [TABLESPACE tablespace]]]])

LOCAL (PARTITION [partition
      [LOGGING|NOLOGGING]
      [TABLESPACE {tablespace|DEFAULT}]
      [PCTFREE int]
      [PCTUSED int]
      [INITRANS int]
      [MAXTRANS int]
      [STORAGE storage_clause]
      [STORE IN {tablespace_name|DEFAULT]
      [SUBPARTITION [subpartition [TABLESPACE tablespace]]]])

GLOBAL PARTITION BY RANGE (col_list)
  ( PARTITION partition VALUES LESS THAN (value_list)
      [LOGGING|NOLOGGING]
      [TABLESPACE {tablespace|DEFAULT}]
      [PCTFREE int]
      [PCTUSED int]
      [INITRANS int]
      [MAXTRANS int]
      [STORAGE storage_clause] )

INDEXTYPE IS indextype [PARALLEL int|NOPARALLEL] [PARAMETERS ('ODCI_Params')]
{Esto es solo para table index, no para bitmap join Index}

Y además index_attribs puede ser cualquier combinación de los siguientes:

NOSORT|SORT
REVERSE
COMPRESS int
NOCOMPRESS
COMPUTE STATISTICS
[NO]LOGGING
ONLINE
TABLESPACE {tablespace|DEFAULT}
PCTFREE int
PCTUSED int
INITRANS int
MAXTRANS int
STORAGE storage_clause
PARALLEL parallel_clause

Si usamos la opcion PARALLEL esta debe estar al final.

create index es una de las pocas sentencias que pueden usar nologging option.

create index requiere un segmento temporal si no hay espacio en memoria suficiente.

Crear indices basados en funciones require que query_rewrite_enabled este a true y query_rewrite_integrity este a trusted.

Un ejemplo de indices basados en funciones para busquedas en mayusculas:

CREATE INDEX idx_case_ins ON my_table(UPPER(empname));

SELECT * FROM my_table WHERE UPPER(empname) = 'KARL';