¿Qué es un índice?

Un índice (o KEY, o INDEX) es un grupo de datos que MySQL asocia con una o varias columnas de la tabla. En este grupo de datos aparece la relación entre el contenido y el número de fila donde está ubicado.

Los índices -como los índices de los libros- sirven para agilizar las consultas a las tablas, evitando que mysql tenga que revisar todos los datos disponibles para devolver el resultado.

Podemos crear el índice a la vez que creamos la tabla, usando la palabra INDEX seguida del nombre del índice a crear y columnas a indexar (que pueden ser varias):
INDEX nombre_indice (columna_indexada, columna_indexada2...)

La sintaxis es ligeramente distinta segun la clase de índice:

PRIMARY KEY (nombre_columna_1 [,nombre_columna2...])

UNIQUE INDEX nombre_indice (columna_indexada1 [,columna_indexada2 ...])

INDEX nombre_index (columna_indexada1 [,columna_indexada2...])

Podemos también añadirlos a una tabla después de creada:
ALTER TABLE nombre_tabla ADD INDEX nombre_indice (columna_indexada);

Si queremos eliminar un índice: ALTER TABLE tabla_nombre DROP INDEX nombre_indice¿para que sirven ?

LOs index permiten mayor rápidez en la ejecución de las consultas a la base de datos tipo SELECT ... WHERE

La regla básica es pues crear tus índices sobre aquellas columnas que vayas a usar con una cláusula WHERE, y no crearlos con aquellas columnas que vayan a ser objeto de un SELECT: SELECT texto from tabla_libros WHERE autor = Vazquez; En este ejemplo, la de autor es una columna buena candidata a un indice; la de texto, no.

Otra regla básica es que son mejores candidatas a indexar aquellas columnas que presentan muchos valores distintos, mientras que no son buenas candidatas las que tienen muchos valores idénticos, como por ejemplo sexo (masculino y femenino) porque cada consulta implicará siempre recorrer practicamente la mitad del indice.

13.1.4. Sintaxis de CREATE INDEX

CREATE [UNIQUE|FULLTEXT|SPATIAL] INDEX index_name

[USING index_type]

ON tbl_name (index_col_name,...)

index_col_name:

col_name [(length)] [ASC | DESC]

En MySQL 5.0, CREATE INDEX se mapea a un comando ALTER TABLE para crear índices. Consulte Sección 13.1.2, “Sintaxis de ALTER TABLE”.

Normalmente, crea todos los índices en una tabla cuando se crea la propia tabla con CREATE TABLE. Consulte Sección 13.1.5, “Sintaxis de CREATE TABLE”. CREATE INDEX le permite añadir índices a tablas existentes.

Una lista de columnas de la forma (col1,col2,...) crea un índice de múltiples columnas. Los valores de índice se forman al concatenar los valores de las columnas dadas.

Para columnas CHAR y VARCHAR, los índices pueden crearse para que usen sólo parte de una columna, usando col_name(length) para indexar un prefijo consistente en los primeros length caracteres de cada valor de la columna. BLOB t TEXT pueden indexarse, pero se debe dar una longitud de prefijo.

El comando mostrado aquí crea un índice usando los primeros 10 caracteres de la columna name :

CREATE INDEX part_of_name ON customer (name(10));

Como la mayoría de nombres usualmente difieren en los primeros 10 caracteres, este índice no debería ser mucho más lento que un índice creado con la columna name entera. Además, usar columnas parcialmente para índices puede hacer un fichero índice mucho menor, que puede ahorrar mucho espacio de disco y además acelarar las operaciones INSERT .

Los prefijos pueden tener una longitud de hasta 255 bytes. Para tablas MyISAM y InnoDB en MySQL 5.0, pueden tener una longitud de hasta 1000 bytes . Tenga en cuenta que los límites de los prefijos se miden en bytes, mientras que la longitud de prefijo en comandos CREATE INDEX se interpreta como el número de caracteres. Tenga esto en cuenta cuando especifique una longitud de prefijo para una columna que use un conjunto de caracteres de múltiples bytes.

En MySQL 5.0:

Puede añadir un índice en una columna que puede tener valores NULL sólo si está usando MyISAM, InnoDB, o BDB .
Puede añadir un índice en una columna BLOB o TEXT sólo si está usando el tipo de tabla MyISAM, BDB, o InnoDB .

Una especificación index_col_name puede acabar con ASC o DESC. Estas palabras se permiten para extensiones futuras para especificar almacenamiento de índice ascendente o descendente. Actualmente se parsean pero se ignoran; los valores de índice siempre se almacenan en orden ascendente.

En MySQL 5.0, algunos motores le permiten especificar un tipo de índice cuando se crea un índice. La sintaxis para el especificador index_type es USING type_name. Los valores type_name posibles soportados por distintos motores se muestran en la siguiente tabla. Donde se muestran múltiples tipos de índice , el primero es el tipo por defecto cuando no se especifica index_type .

Motor de almacenamiento	Tipos de índice permitidos
MyISAM	BTREE
InnoDB	BTREE
MEMORY/HEAP	HASH, BTREE

TIPOS DE INDICES:

En MySQL hay cinco tipos de índices:

· PRIMARY KEY: Este índice se ha creado para generar consultas especialmente rápidas, debe ser único y no se admite el almacenamiento de NULL.

· KEY o INDEX: Son usados indistintamente por MySQL, permite crear indices sobre una columna, sobre varias columnas o sobre partes de una columna.

· UNIQUE: Este tipo de índice no permite el almacenamiento de valores iguales.

· FULLTEXT: Permiten realizar búsquedas de palabras. Sólo pueden usarse sobre columnas CHAR, VARCHAR o TEXT

· SPATIAL: Este tipo de índices solo puede usarse sobre columnas de datos geométricos (spatial) y en el motor MyISAM

http://dev.mysql.com/doc/refman/5.0/es/create-index.html

ORACLE

Crear índices

En esta sección se describe cómo crear índices. Para crear un índice en su propio esquema, por lo menos una de las siguientes condiciones deben ser verdaderas:

· La tabla o clúster para ser indexados en su propio esquema.

· Usted tiene ÍNDICE privilegio sobre la mesa para ser indexado.

· Usted tiene CREAR CUALQUIER ÍNDICE privilegio del sistema.

Para crear un índice en otro esquema, todas de las siguientes condiciones deben ser ciertas:

· Usted tiene CREAR CUALQUIER ÍNDICE privilegio del sistema.

· El propietario del esquema que no tiene un cupo para los espacios de tabla para contener las particiones de índice o un índice, o UNLIMITED TABLESPACE privilegios del sistema.

Crear un índice explícitamente

Puede crear índices de forma explícita (fuera de las restricciones de integridad) utilizando la sentencia SQL

CREATE
INDEX

. La siguiente sentencia crea un índice llamado emp_ename para la Ename columna de la emp tabla:

CREAR emp_ename ÍNDICE DE LA emp (Ename)

      Usuarios TABLESPACE

      Almacenamiento (inicial 20K

      SIGUIENTE 20k

      PCTINCREASE 75);

Observe que la configuración de varios almacenes y un espacio de tabla se especifican explícitamente para el índice. Si no se especifican opciones de almacenamiento (como INICIAL y SIG ) para un índice, las opciones de almacenamiento por defecto del defecto o espacio de tabla especificado se utiliza automáticamente.

La creación de un índice único de forma explícita

Los índices pueden ser únicas o no únicas. Los índices únicos garantizar que no hay dos filas de una tabla tienen valores duplicados en la columna de clave (o columnas). Índices no únicos no imponen esta restricción en los valores de columna.

Utilice el CREATE UNIQUE INDEX instrucción para crear un índice único. En el ejemplo siguiente, se crea un índice único:

CREAR dept_unique_index índice único en departamento (dname)

      TABLESPACE INDX;

Como alternativa, puede definir UNIQUE restricciones de integridad en las columnas deseadas. La base de datos exige UNIQUE restricciones de integridad de forma automática la definición de un índice único en la clave única. Esto se discute en la siguiente sección. Sin embargo, es aconsejable que cualquier índice que existe para el rendimiento de consulta, incluyendo índices únicos, ser creado explícitamente.

Creación de un índice asociado a una restricción

Oracle Database impone un ÚNICO clave o PRIMARY KEY restricción de integridad en una mesa junto a la creación de un índice único en la clave única o clave primaria. Este índice se crea automáticamente la base de datos cuando la restricción está habilitada. Ninguna acción es requerida por usted cuando se emite la sentencia CREATE TABLE o ALTER TABLE instrucción para crear el índice, pero si lo desea, puede especificar un índice con cláusula de ejercer control sobre su creación. Esto incluye tanto una restricción cuando se define y se habilita, y cuando una restricción definida pero deshabilitado está habilitado.

Para habilitar un UNIQUE o PRIMARY KEY restricción, creando así un índice asociado, el propietario de la tabla debe tener una cuota para el espacio de tablas destinado a contener el índice, o la UNLIMITED TABLESPACE privilegio del sistema. El índice asociado a una restricción siempre toma el nombre de la restricción, a menos que se especifique lo contrario opcionalmente.

Nota:

Un procedimiento eficaz para permitir una restricción que puede hacer uso de paralelismo se describe en "Uso Eficiente de restricciones de integridad: un procedimiento" .

Especificación de las opciones de almacenamiento para un índice asociado a una restricción

Puede configurar las opciones de almacenamiento para los índices asociados con UNIQUE y PRIMARY KEY restricciones utilizando el índice mediantecláusula. El siguiente CREATE TABLE declaración permite una PRIMARY KEY restricción y especifica las opciones de almacenamiento de los índices asociados:

CREATE TABLE emp (

     NÚMERO empno (5) PRIMARY KEY, INTEGER edad)

     HABILITAR PRIMARY KEY se utiliza el índice

     Usuarios TABLESPACE;

Especificación del índice asociado a una restricción

Si usted requiere un control más explícito sobre los índices asociados con UNIQUE y PRIMARY KEY limitaciones, la base de datos le permite:

· Especifica un índice existente de que la base de datos se va a utilizar para hacer cumplir la restricción

· Especifique un CREATE ÍNDICE declaración de que la base de datos se va a utilizar para crear el índice y hacer cumplir la restricción

Estas opciones se especifica mediante la USO ÍNDICE cláusula. Las declaraciones siguientes se presentan algunos ejemplos.

Ejemplo 1:

CREATE TABLE a (

     a1 INT PRIMARY KEY se utiliza el índice (Índice AI en crear una (a1)));

Ejemplo 2:

CREATE TABLE b (

     b1 INT,

     INT b2,

     RESTRICCIÓN bu1 UNIQUE (b1, b2)

                    USO ÍNDICE (crear bi índice único en b (b1, b2)),

     RESTRICCIÓN bu2 UNIQUE (b1 b2), utilizando el índice bi);

Ejemplo 3:

CREATE TABLE C (C1 INT, C2 INT);

CREATE INDEX EN ci c (c1, c2);

C ALTER TABLE ADD CONSTRAINT cpk PRIMARY KEY (c1) utilizando el índice de ci;

Si una sola sentencia crea un índice con una restricción y también utiliza este índice para otra restricción, el sistema intentará reorganizar las cláusulas para crear el índice antes de volver a usarlo.

Recopilación de estadísticas en el fondo al crear un índice

Base de Datos Oracle le ofrece la oportunidad de recopilar estadísticas a un costo recurso muy poco durante la creación o reconstrucción de un índice. Estas estadísticas se almacenan en el diccionario de datos para su uso continuo por el optimizador en la elección de un plan para la ejecución de sentencias SQL. La siguiente afirmación se calcula índice, tabla y estadísticas de columnas mientras que la construcción e índice mp_ename en columna Ename de la tabla emp :

CREAR emp_ename ÍNDICE DE LA emp (Ename)

     Calcular las estadísticas;

Creación de un índice grande

Al crear un índice extremadamente grande, considerar la asignación de un espacio de tabla temporal mayor para la creación de índices usando el siguiente procedimiento:

1. Crear un nuevo espacio de tabla temporal utilizando el CREATE TABLESPACE o CREATE TABLESPACE TEMPORAL comunicado.

2. Utilice el espacio de tablas temporal de la opción ALTER USER declaración para convertirlo en el nuevo espacio de tablas temporal.

3. Cree el índice mediante el CREATE INDEX comunicado.

4. Deja este espacio de tablas utilizando el DROP TABLESPACE comunicado. A continuación, utilice el USUARIO ALTER declaración para restablecer el espacio de tablas temporal a su espacio de tablas temporal original.

El uso de este procedimiento puede evitar el problema de la expansión de su costumbre, y por lo general compartido, tablespace temporal a un tamaño excesivamente grande que puede afectar al rendimiento futuro.

La creación de un Index Online

Puede crear y volver a generar índices en línea. Esto le permite actualizar las tablas base, al mismo tiempo que usted está construyendo o reconstruyendo los índices de esa tabla. Puede realizar operaciones DML, mientras que el índice de construcción se lleva a cabo, pero las operaciones de DDL no están permitidos. La ejecución paralela no se admite al crear o volver a generar un índice en línea.

Las siguientes declaraciones ilustran las operaciones de índice en línea de compilación:

CREAR emp_name ÍNDICE DE LA emp (mgr, Emp1, emp2, EMP3) ONLINE;

Nota:

Tenga en cuenta que el tiempo que se tarda en índice en línea para completar es proporcional al tamaño de la tabla y el número de estados que se ejecutan simultáneamente DML. Por lo tanto, lo mejor es empezar de índices en línea cuando la actividad DML es baja.

Creación de un índice basado en funciones

Función de base de índices de facilitar las consultas que califican a un valor devuelto por una función o expresión. El valor de la función o expresión se calcula previamente y almacena en el índice.

Además de los requisitos previos para crear un índice convencional, si el índice está basado en funciones definidas por el usuario, entonces esas funciones deben estar marcados DETERMINISTIC . También, usted apenas tiene el EXECUTE privilegio de objeto en cualquier función definida por el usuario (s) que se utiliza en el índice basado en las funciones si dichas funciones son propiedad de otro usuario.

Además, para utilizar un índice basado en las funciones:

· La tabla debe ser analizado después se crea el índice.

· La consulta debe ser garantizado para no necesitar ningún NULL los valores de la expresión indexada, ya que NULL valores no se almacenan en los índices.

Nota:

CREATE INDEX tiendas de la marca de tiempo de la función más reciente utilizado en el índice basado en las funciones.Esta marca de tiempo se actualiza cuando el índice se valida. Al realizar tablas de punto en el tiempo de recuperación de un índice basado en las funciones, si la marca de tiempo de la función más reciente utilizado en el índice es más reciente que la fecha y hora almacenada en el índice, el índice se marcarán como no válidos. Debe utilizar el ÍNDICE DE ANALIZAR ... VALIDAR LA ESTRUCTURA declaración para validar este índice.

Para ilustrar un índice basado en las funciones, considere la siguiente declaración que define un índice basado en funciones ( area_index ) definida en función de la zona (geo) :

CREAR area_index ÍNDICE DE ríos (área (geo));

En la siguiente sentencia SQL, cuando area (geo) se hace referencia en el DONDE cláusula, el optimizador considera que mediante el índice de area_index .

SELECT id, geo, área (geo), desc

     De los ríos

     DONDE Area (geo)> 5000;

Propietarios tabla debe tener EXECUTE privilegios en las funciones utilizadas en los índices basados en funciones.

Debido a que un índice basado en funciones depende de cualquier función que está utilizando, puede ser invalidada cuando cambia de función. Si la función es válida, puede utilizar un ALTER INDEX ... ACTIVAR declaración para que un índice basado en las funciones que se ha desactivado. El ALTER INDEX ... DISABLE permite deshabilitar declaración el uso de un índice basado en las funciones. Considere la posibilidad de hacer esto si usted está trabajando en el cuerpo de la función.

Nota:

Una alternativa a la creación de un índice basado en funciones es añadir una columna virtual a la tabla de destino y el índice de la columna virtual. Ver "Acerca de las tablas" para obtener más información.

Creación de un índice de clave-comprimido

Creación de un índice mediante compresión tecla le permite eliminar las ocurrencias repetidas de los principales valores de la columna de prefijo.

Compresión Key rompe una clave de índice en un prefijo y un sufijo entrada. La compresión se consigue mediante el intercambio de las entradas de prefijo entre todas las entradas de sufijos en un bloque de índice. Este intercambio puede llevar a un gran ahorro en el espacio, lo que le permite almacenar más claves para cada bloque índice al tiempo que mejora el rendimiento.

Compresión de clave puede ser útil en las situaciones siguientes:

· Usted tiene un índice no único en el ROWID se añade para hacer la llave única. Si utiliza compresión clave aquí, el duplicado de la llave se almacena como una entrada de prefijo en el bloque de índice sin el ROWID . Las filas restantes convertido entradas sufijo que consiste en sólo el ROWID .

· Usted tiene un único índice de varias columnas.

Para habilitar la compresión de claves mediante la COMPRESS cláusula. La longitud del prefijo (como el número de columnas de la clave) también se pueden especificar para identificar cómo las columnas de clave se divide en un prefijo y un sufijo de entrada. Por ejemplo, la siguiente sentencia comprime ocurrencias duplicadas de una llave en el bloque de la hoja de índice:

CREAR emp_ename ÍNDICE DE LA emp (Ename)

   Usuarios TABLESPACE

   COMPRIMIR 1;

El COMPRESS cláusula también se puede especificar durante la reconstrucción. Por ejemplo, durante la reconstrucción puede deshabilitar la compresión de la siguiente manera:

Emp_ename ALTER INDEX REBUILD nocompress;

Crear un índice Invisible

A partir de la versión 11 g , puede crear índices invisibles. Un índice invisible es un índice que es ignorado por el optimizador menos que establezca explícitamente la OPTIMIZER_USE_INVISIBLE_INDEXES parámetro de inicialización al VERDADERO en la sesión o nivel del sistema. Cómo hacer una invisible índice es una alternativa a lo que es inutilizable o se caiga. El uso de índices invisibles, puede hacer lo siguiente:

· Pruebe la eliminación de un índice antes de que se caiga.

· Usar estructuras temporales de índice para ciertas operaciones o módulos de la aplicación, sin perjuicio de la aplicación general.

A diferencia de los índices inutilizables, un índice invisible se mantiene durante DML.

Para crear un índice invisible, utilice la sentencia SQL CREATE INDEX con la INVISIBLE cláusula. La siguiente sentencia crea un índice invisible llamadoemp_ename para la Ename columna de la emp tabla:

CREAR emp_ename ÍNDICE DE LA emp (Ename)

      Usuarios TABLESPACE

      Almacenamiento (inicial 20K

      SIGUIENTE 20k

      PCTINCREASE 75)

      INVISIBLE ;

http://docs.oracle.com/cd/B28359_01/server.111/b28310/indexes003.htm

extras:

http://www.slideshare.net/calejero/indices-en-oracle

http://ora.u440.com/ddl/create%20index.HTML

CREACION DE INDICES.

Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.5.27 MySQL Community Server (GPL)

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> use veterinaria;
Database changed

mysql> create index name ON cliente (nombre(10));
Query OK, 8 rows affected (0.09 sec)
Records: 8 Duplicates: 0 Warnings: 0

mysql> create index idname ON general (id_nombre(1));
Query OK, 0 rows affected (0.10 sec)
Records: 0 Duplicates: 0 Warnings: 0

mysql> create index name1 ON visita (nombre(10));
Query OK, 0 rows affected (0.08 sec)
Records: 0 Duplicates: 0 Warnings: 0

mysql> create index tabmascota ON mascota (raza(11));
Query OK, 3 rows affected (0.03 sec)
Records: 3 Duplicates: 0 Warnings: 0

mysql> create index tabcolsulta ON mascota (motivoconsulta(15));
Query OK, 3 rows affected (0.04 sec)
Records: 3 Duplicates: 0 Warnings: 0

mysql> create index tabname ON general1 (nombre(15));
Query OK, 0 rows affected (0.11 sec)
Records: 0 Duplicates: 0 Warnings: 0

mysql> create index tabdireccion ON general1 (direccion(15));
Query OK, 0 rows affected (0.18 sec)
Records: 0 Duplicates: 0 Warnings: 0

kevinluna

lunes, 15 de abril de 2013

Manejo de índices ACTIVIDAD #22

Crear índices

Crear un índice explícitamente

La creación de un índice único de forma explícita

Creación de un índice asociado a una restricción

Especificación de las opciones de almacenamiento para un índice asociado a una restricción

Especificación del índice asociado a una restricción

Recopilación de estadísticas en el fondo al crear un índice

Creación de un índice grande

La creación de un Index Online

Creación de un índice basado en funciones

Creación de un índice de clave-comprimido

Crear un índice Invisible

No hay comentarios:

Publicar un comentario

Datos personales