DATOS ORACLE 10g

Un índice en una base datos Oracle 11g es un objeto opcional normalmente asociado a una tabla pero que su uso es casi imprescindible. Una de las misiones de los índices es permitir que las consultas de datos sean más rápidas devolviendo su resultado, sobre todo en tablas con miles o millones de líneas. Una tabla pude tener más de un índice y estos pueden estar compuestos por una o varias columnas.

El estamento básico para crear índices en una base de datos Oracle 11g es:

1.CREATE INDEX nombre_indice ON nombre_tabla (columna, columna1,….);

En el artículo - Introducción a la creación de tablas en Oracle 11g - creamos la tabla clientes, vamos a utilizarla como base para practicar la creación de índices.

En primer lugar vamos a crear un índice sobre la columna CIF para que nuestras búsquedas por esta columna sean llo más rapidas posible:

1.CREATE INDEX factura.clientes_idx1 ON factura.clientes (CIF)

2.TABLESPACE FACTURA_IDX01;

Vamos a crear otro índice compuesto por las columnas NombreCli y DireccionCli:

1.CREATE INDEX factura.clientes_idx2 ON factura.clientes (NombreCli, DireccionCli)

2.TABLESPACE FACTURA_IDX01;

Como podéis ver he añadido la cláusula TABLESPACE para indicar que el índice se cree en el tablespace FACTURA_IDX01, si omitimos esta cláusula el índice se creará en el DEFAULT TABLESPACE que tenga definido el usuario FACTURA.

El usuario que vaya a crear el índice tiene que tener privilegio de CREATE INDEX y UNLIMITED TABLESPACE o CUOTA sobre el tablespace FACTURA_IDX01.

TIPOS DE ÍNDICES EN ORACLE

Lectura/Escritura

B-tree (árboles binarios)

Function Based

Reserve key

Sólo lectura (read only)

Bitmap

Bitmap join

Index-organized table (algunas veces usados en lectura/escritura)

Cluster y hash cluster

Domain (muy específicos en aplicaciones Oracle)

Índices en Oracle

ÍNDICES CREADOS POR ORACLE DE MANERA AUTOMÁTICA

Al crearse la tabla se crea:

Un índice UNIQUE basado en B*-tree para mantener las columnas que se hayan definido como clave primaria de una tabla utilizando el constraint PRIMARY KEY de una tabla no organizada por índice.

Un índice UNIQUE basado en B*-tree para mantener la restricción de unicidad de cada grupo de columnas que se haya declarado como único utilizando el constraint UNIQUE.

Un índice basado en B*-tree para mantener las columnas que se hayan definido como clave primaria y todas las filas de una tabla organizada por índice.

Un índice basado en hashing para mantener las filas de un grupo de tablas (“cluster”) organizado por hash.

Índices en Oracle

CONSIDERACIONES DE LOS ÍNDICES

Un índice sólo es efectivo cuando es utilizado.

El mantenimiento de un índice tiene efecto sobre el rendimiento de las operaciones de eliminación, inserción y actualización.

ORACLE impone dos restricciones:

El número máximo de columnas: Btree  32 col. Bitmap  30 col.

El espacio requerido para almacenar una clave no puede exceder la mitad del espacio disponible para almacenar datos en un bloque ORACLE

Índices en Oracle Restricciones en Oracle 8

REGLAS EN EL DISEÑO DE ÍNDICES

Indexe solamente las tablas cuando las consultas no accedan a una gran cantidad de filas de la tabla.

No indexe tablas que son actualizadas con mucha frecuencia.

Indexe aquellas tablas que no tengan muchos valores repetidos en las columnas escogidas.

Las consultas muy complejas (en la cláusula WHERE) por lo general no toman mucha ventaja de los índices.

Índices en Oracle

SINTAXIS: CREACIÓN

Básica

CREATE INDEX nombre_indice ON [esquema.] nombre_tabla (columna1 [, columna2, ...])

UNIQUE garantizan que en una tabla (o “cluster”) no puedan existir dos filas con el mismo valor.

Índices en Oracle

SINTAXIS: MODIFICACIÓN

Básica

ALTER INDEX [schema.]index options

Índices en Oracle

SINTAXIS: ELIMINACIÓN

Básica

DROP INDEX [schema.]index [FORCE]

Índices en Oracle

ESTRUCTURA: B*-TREE

Se estructura como un árbol cuya raíz contiene múltiples entradas y valores de claves que apuntan al siguiente nivel del árbol.

Nivel 0.

tablas pequeñas de datos estáticos.

Nivel 1.

Indexa tablas dinámicas con el valor único de los identificadores de columna.

Nivel 2.

Indexa largas tablas o con poca cardinalidad.

Índices en Oracle

ESTRUCTURA: B*-TREE (GRÁFICO) Índices en Oracle

ESTRUCTURA: B*-TREE (CONVENIOS)

Los siguientes convenios son utilizados para el almacenamiento en un índice basado en B*- Tree:

En caso de que un índice no sea UNIQUE, si múltiples filas poseen el mismo valor de la clave en la estructura del índice se repetirán los valores de dichas claves.

Si una fila posee para todas las columnas de la clave el valor NULL, en el índice no existirá una entrada correspondiente a dicha fila.

Si la tabla no está particionada, se utilizan ROWIDs restringidos para indicar la dirección de la fila. Esto se debe a que todas las filas de la tabla no se encuentran en un mismo segmento y con esta política el índice requiere menos espacio en disco.

Índices en Oracle

ESTRUCTURA: BITMAP

Son efectivos para columnas simples con poca cardinalidad, esto es muchos valores distintos.

Más rápidos que los B*-Tree en entornos de read-only.

Almacenan valores de 0 ó 1 en el ROWID.

Ejemplo:

create bitmap index person_region on person (region);

Índices en Oracle Row Region North East West South 1 North 1 0 0 0 2 East 0 1 0 0 3 East 0 1 0 0 4 West 0 0 1 0 5 South 0 0 0 1 6 North 1 0 0 0

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 INSERTy 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';

Relaciones

creacion de scripts en oracle

La creación de relaciones e inserción de valores en las mismas suele ser un proceso bastante tedioso, especialmente cuando se transcriben errores en las instrucciones. Para simplificar esto, es posible crear Scripts que permiten crear, modificar y eliminar relaciones, así como insertar, eliminar y buscar tuplas.

Un script no es mas que un archivo de texto, con instrucciones de SQL separadas por punto y coma. Dichos archivos deben tener permiso de lectura para el usuario (100), y para ejecutar su contenido se debe usar la instrucción START, de la siguiente manera:

SQL> START scriptname

Una relación consta de:

•Un esquema: conjunto de pares

(atributo,dominio).

•Un cuerpo: conjunto de tuplas de pares

(atributo,valor).

Definición de una Base de Datos

Relacional

•Los dominios se completan con el valor

NULL.

•Claves candidatas.

– Clave minimal.

– Clave Primaria. Criterios de selección.

•Clave foránea.

MODELO ORIENTADO A VALORES

DATOS ORACLE 10g

lunes, 18 de julio de 2011

BASE DE DATOS

INSTANCIAS

INSTALACION

ORACLE

INSTALACION

INDICES

PROPIEDAD DE TABLAS