mantenimiento de Índices en 11g -...

5a. Ave. 5-55 Zona14, Edificio Euro Plaza Torre II, Nivel 12

Teléfono: (502)2364-5300 Fax: (502)2364-5311

Email. [email protected]

Mantenimiento de Índices en 11g

Por: Ing. Juan Luis Rodríguez

[email protected]

Un índice de base de datos Oracle provee una vía rápida de acceso a la información de las tablas. Oracle dispone de varios esquemas de indización para complementar la funcionalidad de desempeño. Estos son:

Índices B-tree: los más usados.

Índices B-tree cluster: definidos específicamente para clusters.

Índices Hash cluster: definidos específicamente para un hash cluster.

Índices globales y locales: relacionados a tablas e índices particionados.

Índices bitmap: para trabajar mejor con columnas que contienen un pequeño conjunto de valores.

Índices basados en funciones: contienen el valor precalculado de una función o expresión.

Índices de dominio: específicos para una aplicación.

Los índices son lógica y físicamente independientes de los datos en la tabla asociada. Por ser estructuras independientes, requieren espacio para su almacenamiento. Se puede crear o borrar un índice sin afectar las tablas base, aplicaciones en la base de datos, ni otros índices. La base de datos automáticamente mantiene los índices cuando se inserta, actualiza o borra registros de la tabla base. Si se elimina un índice, todas las aplicaciones continúan funcionando. Sin embargo, el acceso a los datos anteriormente indexados será más lento. A continuación se discute las guías para administrar índices.

Año 2 Volumen 10 – Febrero 2011

Pagina 1/10

Contenido Página

1 Mantenimiento de

índices en 11g

7 Redefinición de tablas en

línea

11 Modificar un reporte en

Reports 10g Editores Generales

Karlo Espinoza

Luis Cordón

Gerber Bautista

Debbie Moran

Francisco Barrundia

Autores Contribuyentes

Juan Luis Rodríguez

Augusto López

Francisco Barrundia


Teléfono: (502)2364-5300 Fax: (502)2364-5311


Crear índices después insertar datos en la tabla A menudo, los datos son insertados o cargados en una tabla utilizando el SQL*Loader o la utilería import. Es más eficiente crear un índice para una tabla después de insertar o cargar los datos. Si se crea uno o más índices antes de cargar los datos, la base de datos debe actualizar cada índice para cada registro que se agrega. Crear un índice en una tabla que actualmente tiene datos requiere espacio para ordenamiento. Algunos espacios para ordenamiento provienen de la memoria asignada por el creador del índice. El monto para cada usuario es determinado por el parámetro de inicialización SORT_AREA_SIZE. La base de datos también graba temporalmente información ordenada desde y hacia segmentos temporales que solamente son alojados durante la creación del índice en el tablespace temporal del usuario.

Indexar las tablas y columnas correctas Utilice las siguientes guías para determinar cuándo crear un índice:

Crear un índice si frecuentemente se requiere obtener menos del 15% de las filas de una taba grande. El porcentaje varía grandemente de acuerdo a la velocidad relativa de una búsqueda en tabla y de cómo está la distribución de los registros de datos en relación a la llave del índice.

Para mejorar el desempeño en joins de múltiples tablas, indexar las columnas utilizadas en el join.

Tablas pequeñas no requieren índices. Si la consulta toma mucho tiempo, entonces la tabla podría tener un crecimiento de mediana a grande.

Columnas que son candidatas para indexar Algunas columnas son fuertes candidatas para indexar. Columnas con una o más de las siguientes características son candidatas para indexar:

Valores que son relativamente únicos en la columna.

Si hay un amplio rango de valores (bueno para índices regulares B-tree).

Si hay un pequeño rango de valores (bueno para índices bitmap).

La columna contiene muchos nulos, pero las consultas menudo seleccionan todas las filas que contienen un valor. En este caso, utilizar la siguiente cláusula: WHERE COL_X > -9.99 * power (10,125) Utilizar la cláusula anterior es preferible a: WHERE COL_X IS NOT NULL Esto se debe a que la primera utiliza un índice en la columna COL_X (asumiendo que COL_X es una columna numérica).

Columnas que no son candidatas para indexar

Si hay muchos valores nulos en la columna y no se hacen búsquedas en los valores no nulos.

Columnas de tipo LONG y LONG RAW no pueden ser indexadas.


Teléfono: (502)2364-5300 Fax: (502)2364-5311


Columnas virtuales Se puede crear índices únicos y no-únicos en columnas virtuales.

Indexar las tablas y columnas correctas El orden de las columnas en la instrucción CREATE INDEX puede afectar el desempeño de las consultas. En general, especificar de primero las columnas más frecuentemente utilizadas. Si se crea un índice para acelerar el acceso por medio de consultas, por ejemplo, col1, col2 y col3; entonces los consultas que accesan solo la columna col1, o que accedan solo col2 y col3, se acelerarán.

Limitar el número de índices para cada tabla Una tabla puede tener cualquier número de índices. Sin embargo, si hay más índices, se incurre en más sobrecarga cuando la tabla es modificada. Específicamente, cuando se insertan nuevas filas o se eliminan, todos los índices de la taba serán actualizados también. Asimismo, cuando una columna es actualizada, todos los índices que contengan la columna deben ser actualizados. Por lo tanto, hay una brecha entre la velocidad de obtener información de una tabla y la velocidad de hacer actualizaciones en la tabla. Por ejemplo, si una tabla es principalmente solo para lectura, tener más índices puede ser útil; pero si una tabla es intensamente actualizada, tener pocos índices podría ser preferible.

Borrar índices que ya no son necesarios Considerar lo siguiente para borrar un índice:

El índice no acelera las consultas. La tabla podría ser muy pequeña, o podría haber muchas filas en la tabla pero muy pocas entradas en el índice.

Las consultas de las aplicaciones no usan índices.

El índice deberías ser borrado antes de rehacerse.

Estimar el tamaño del índice y sus parámetros de almacenamiento Estimar el tamaño de un índice antes de crearlo puede facilitar la planeación y administración del espacio en disco. Cuando se crea un índice se puede asignar apropiadamente los parámetros de almacenamiento para obtener mejora en el desempeño del I/O para las aplicaciones que utilizan el índice. Al asignar los parámetros de almacenamiento todos los datos del índice estarán relativamente contiguos en una sección de espacio en el disco. Esto disminuye el tiempo necesario en las operaciones que involucran al índice. El tamaño máximo de una simple entrada en el índice es aproximadamente la mitad del tamaño del bloque de datos.

Especificar el tablespace para cada índice Los índices pueden ser creados en cualquier tablespace. Un índice puede ser creado en el mismo u otro tablespace al que pertenece la tabla para la cual se crea el índice. Usar diferentes tablespaces (en discos diferentes) para la tabla y el índice produce mejor desempeño que almacenar la tabla y el índice en el mismo tablespace. Se reduce la contención en el disco.


Teléfono: (502)2364-5300 Fax: (502)2364-5311


Considerar creación del índice en paralelo Se puede paralelizar la creación del índice. Debido que múltiples procesos trabajan juntos para crear el índice, la base de datos puede crear el índice más rápidamente que si un simple proceso del servidor crea el índice de manera secuencial. Cuando se crea un índice en paralelo, los parámetros de almacenamiento pueden ser usados separadamente para cada proceso de servidor. Sin embargo, un índice creado un valor INITIAL de 5M y un grado de paralelismo de 12 consumirá hasta 60M de almacenamiento durante la creación del índice.

Considerar la creación del índice con la cláusula NOLOGGING Se puede crear un índice y generar un mínimo de registros de redo log especificando NOLOGGING en el comando CREATE INDEX. Crear el índice con la cláusula NOLOGGING da los siguientes beneficios:

Se ahorra espacio en los archivos redo log.

El tiempo que se necesita para crear el índice disminuye.

El desempeño mejora para la creación de grandes discos en paralelo.

En general, la mejora relativa en el desempeño es grande para grandes índices creados con la cláusula LOGGING.

Entendiendo cuando usar índices inusables o invisibles Utilizar índices inusables o invisibles puede mejorar el performance cuando se realizan cargas masivas.

Índices Inusables Un índice inusable es ignorado por el optimizador. Una razón para volver un índice inusable es para mejorar el performance de cargas masivas. (Cargas masivas de datos son más rápidas si la base de datos no tiene que mantener índices cuando se insertan filas). En vez de borrar el índice y después volver a crearlo, lo que requiere recargar los parámetros exactos del comando CREATE INDEX, se puede volver el índice inusable, y después recrearlo. Se pude crear un índice en el estado inusable, o se puede marcar un índice o una partición de índice como inusable. En algunos casos la base de datos puede marcar un índice como inusable cuando ocurre una falla mientras se está construyendo el índice. Cuando una partición, de un índice particionado es marcada como inusable, las otras particiones del índice se mantienen válidas. Un índice o partición de índice debe ser reconstruido, o borrado y re-creado, antes de que pueda ser usado. Truncar una tabla cambia un índice en estado inusable a válido. Desde la versión de base de datos Oracle 11g Release 2, cuando se vuelve un índice al estado inusable, el segmento del índice es borrado. La funcionalidad de los índices inusables depende del valor configurado en el parámetro de inicialización SKIP_UNUSABLE_INDEXES. Cuando el valor de SKIP_UNUSABLE_INDEXES es TRUE (el valor por defecto), entonces:


Teléfono: (502)2364-5300 Fax: (502)2364-5311


Las instrucciones DML aplicadas a la tabla son procesadas, pero los índices inusables no son mantenidos.

Las instrucciones DML terminan con un error si hay índices inusables que son usados para forzar un constraint UNIQUE.

Para índices no particionados, el optimizador no considera ningún índice inusable cuando se está creando un plan de acceso para instrucciones SELECT. La única excepción es cuando un índice es explícitamente especificado con el hint INDEX ().

Cuando SKIP_UNUSABLE_INDEXES es FALSE, entonces:

Si algún índice o índice particionado está presente en cualquier instrucción DML terminará con un error.

Para instrucciones SELECT, si un índice o índice particionado inusable está presente pero el optimizador no lo elige para el plan de acceso, la instrucción procede. Sin embargo, si el optimizador escoge el índice o índice particionado inusable, la instrucción termina con un error.

Índices invisibles Desde Oracle Database 11g Release 1, se pueden crear índices invisibles o volver invisible un índice existente. Un índice invisible es ignorado por el optimizador a menos que explícitamente se asigne TRUE al parámetro de inicialización OPTIMIZER_USE_INVISIBLE_INDEXES en la sesión o a nivel del sistema. A diferencia de los índices inusables, un índice invisible es mantenido durante la aplicación de instrucciones DML. Aunque se pude hacer un índice particionado invisible, no se puede hacer una partición individual de un índice particionado invisible mientras se dejen visibles las otras particiones. Al utilizar índices invisibles se puede hacer lo siguiente:

Probar el removeré un índice antes de borrarlo.

Utilizar estructuras de índices temporales para ciertas operaciones o módulos de una aplicación sin afectar toda la aplicación.

Considerar costos y beneficios de desfragmentar o reconstruir Índices Tamaño inadecuado o crecimiento del índice puede causar fragmentación. Para eliminar o reducir la fragmentación, se puede reconstruir o desfragmentar un índice. Pero antes de realizar cualquiera de estas tareas se debe considerar el costo y beneficio cada opción y escoger la que mejor trabaje para la situación. La siguiente tabla muestra una comparación de los costos y beneficios asociados cuando se reconstruyen y desfragmentan índices.

RECONSTRUIR EL ÍNDICE DESFRAGMENTAR EL ÍNDICE

Rápidamente mueve índices a otro tablespace No se puede mover índices a otro tablespace.

Alto costo: requiere más espacio en disco Bajo costo: no requiere más espacio en disco.

Crea nuevas ramificaciones, reduce a lo ancho si es posible.

Se une a bloques en la misma nivel del ramal.

Permite rápidamente cambiar parámetros de almacenamiento y de tablespace sin tener que borrar el índice original.

Rápidamente libera bloques para su uso.


Teléfono: (502)2364-5300 Fax: (502)2364-5311


En situaciones en donde se tienen índices B-tree los bloques pueden ser liberados para reusarse, se pueden combinar estos bloques usando el siguiente comando:

ALTER INDEX vmoore COALESCE;

La siguiente figura ilustra el efecto de ALTER INDEX COLESCE sobre el índice vmoore. Antes de realizar la operación, los primeros dos bloques están llenos hasta un 59%. Esto presenta una oportunidad para reducir la fragmentación y completamente rellenar el primer bloque, dejando libre el segundo. .

Antes de ALTER INDE vmoore COALESCE;

Después de ALTER INDEX vmoore COALESCE;

Considerar el costo antes de deshabilitar o borrar constraints Debido a que las llaves únicas y primarias estás asociadas a índices, se debe considerar el costo de borrar y crear índices cuando de deshabilite o se borre un constraint UNIQUE o de LLAVE PRIMARIA. Si el índice asociado al constraint es extremadamente largo, se puede economizar tiempo dejando el constraint habilitado a cambio de borrar y recrear un índice largo. También se dispone de la opción de explícitamente especificar que se desea mantener o borrar el índice cuando se borre o se deshabilite un constraint UNIQUE o de LLAVE PRIMARIA.


Teléfono: (502)2364-5300 Fax: (502)2364-5311


Redefinición de tablas en línea

Por: Ing. Augusto Lopéz

[email protected]

La redefinición de tablas en línea (OTR por sus siglas en inglés) es una característica de la edición Enterprise de Oracle Database que permite modificar la estructura física o lógica de una tabla sin interrumpir el acceso a la misma. Algunas de las operaciones que nos permite realizar esta funcionalidad son:

Particionar una tabla que actualmente no está particionada.

Cambiar el criterio de particionamiento de una tabla ya particionada.

Recrear la tabla para eliminar la fragmentación.

Modificar una o más columnas de la tabla.

A continuación se muestra un ejemplo de cómo particionar una tabla ya existente que no está particionada. -- 00. Se muestra la estructura de la tabla empleados y departamentos ya que esta última es referenciada por la primera. CREATE TABLE departamentos ( id NUMBER(5) PRIMARY KEY, nombre VARCHAR2(50) NOT NULL ); CREATE TABLE empleados ( id NUMBER(5) CONSTRAINT emp_id_nn NOT NULL, nombre VARCHAR2(50) CONSTRAINT emp_nombre_nn NOT NULL, id_dep NUMBER(5) ); ALTER TABLE empleados ADD (CONSTRAINT emp_pk PRIMARY KEY(id)); CREATE INDEX emp_ix ON empleados (id_dep); ALTER TABLE empleados ADD ( CONSTRAINT emp_fk FOREIGN KEY (id_dep) REFERENCES departamentos (id) ); -- 01. Confirmar que la tabla puede ser redefinida en línea BEGIN DBMS_REDEFINITION.CAN_REDEF_TABLE('RRHH','EMPLEADOS',DBMS_REDEFINITION.CONS_USE_PK); END; /


Teléfono: (502)2364-5300 Fax: (502)2364-5311


-- 02. Se crea la tabla particionada con nombre RRHH.EMPLEADOS2 CREATE TABLE EMPLEADOS2 ( id NUMBER(5) CONSTRAINT emp_id_nn2 NOT NULL, nombre VARCHAR2(50) CONSTRAINT emp_nombre_nn2 NOT NULL, id_dep NUMBER(5) ) PARTITION BY HASH (id_dep) PARTITIONS 8; -- 03. Crear constraints de llave primaria/llave única y su respectivo índice usando nuevos nombres ALTER TABLE empleados2 ADD (CONSTRAINT emp_pk2 PRIMARY KEY(id)); -- 04. Iniciar la redefinición de la tabla BEGIN DBMS_REDEFINITION.START_REDEF_TABLE( uname => 'RRHH', orig_table => 'EMPLEADOS', int_table => 'EMPLEADOS2', options_flag => DBMS_REDEFINITION.CONS_USE_PK); END; / -- 05. Crear los índices en la nueva tabla, con nuevos nombres. CREATE INDEX emp_ix2 ON empleados2 (id_dep); -- 06. Registrar, con REGISTER_DEPENDENT_OBJECT, los constraints PK/UK/NN/CK que se hayan creado. El objetivo es indicar la correspondencia entre los objetos de la tabla origen y la destino. Por ejemplo, en la primer instrucción se indica que el constraint EMP_PK en la tabla EMPLEADOS corresponde al constraint EMP_PK2 en la tabla EMPLEADOS2. BEGIN DBMS_REDEFINITION.REGISTER_DEPENDENT_OBJECT ('RRHH','EMPLEADOS','EMPLEADOS2',DBMS_REDEFINITION.CONS_CONSTRAINT,'RRHH', 'EMP_PK','EMP_PK2'); DBMS_REDEFINITION.REGISTER_DEPENDENT_OBJECT ('RRHH','EMPLEADOS','EMPLEADOS2',DBMS_REDEFINITION.CONS_CONSTRAINT,'RRHH', 'EMP_ID_NN','EMP_ID_NN2'); DBMS_REDEFINITION.REGISTER_DEPENDENT_OBJECT ('RRHH','EMPLEADOS','EMPLEADOS2',DBMS_REDEFINITION.CONS_CONSTRAINT,'RRHH', 'EMP_NOMBRE_NN','EMP_NOMBRE_NN2'); END; / -- 07. Registrar índices con REGISTER_DEPENDENT_OBJECT. En este paso EMP_PK se refiere al índice del constraint de llave primaria. En el paso anterior EMP_PK se refiere al constraint de llave primaria.


Teléfono: (502)2364-5300 Fax: (502)2364-5311


BEGIN DBMS_REDEFINITION.REGISTER_DEPENDENT_OBJECT ('RRHH','EMPLEADOS','EMPLEADOS2',DBMS_REDEFINITION.CONS_INDEX,'RRHH', 'EMP_PK','EMP_PK2'); DBMS_REDEFINITION.REGISTER_DEPENDENT_OBJECT ('RRHH','EMPLEADOS','EMPLEADOS2',DBMS_REDEFINITION.CONS_INDEX,'RRHH', 'EMP_IX','EMP_IX2'); END; / -- 08. Copiar objetos dependientes con COPY_TABLE_DEPENDENTS. Esto copiará los objetos de la tabla origen que no hayan sido mapeados de forma explícita en la tabla particionada, por ejemplo, el constraint EMP_FK. DECLARE v_num_errors PLS_INTEGER; BEGIN DBMS_REDEFINITION.COPY_TABLE_DEPENDENTS( uname => 'RRHH', orig_table => 'EMPLEADOS', int_table => 'EMPLEADOS2', copy_indexes => DBMS_REDEFINITION.CONS_ORIG_PARAMS, copy_triggers => TRUE, copy_constraints => TRUE, copy_privileges => TRUE, ignore_errors => TRUE, num_errors => v_num_errors, copy_statistics => FALSE); END; / -- 09. Verificar en DBA_REDEFINITION_ERRORS que no existan errores SELECT * FROM DBA_REDEFINITION_ERRORS; -- 10. Hacer una sincronización de la nueva tabla. Esto se puede hacer de forma regular si la tabla origen recibe constantemente nuevos DMLs. Sirve para minimizar el tiempo que tome hacer la operación de finalización (paso 12). BEGIN DBMS_REDEFINITION.SYNC_INTERIM_TABLE('RRHH', 'EMPLEADOS', 'EMPLEADOS2'); END; /


Teléfono: (502)2364-5300 Fax: (502)2364-5311


-- 11. Tomar estadísticas de la tabla interina BEGIN dbms_stats.gather_table_stats( ownname=>'RRHH',tabname=>'EMPLEADOS2',estimate_percent=>dbms_stats.auto_sample_size, cascade=>true); END; / -- 12. Finalizar la redefinición. Después de este punto, la tabla EMPLEADOS será la particionada y la tabla EMPLEADOS2 será la NO particionada. BEGIN DBMS_REDEFINITION.FINISH_REDEF_TABLE('RRHH', 'EMPLEADOS', 'EMPLEADOS2'); END; / -- 13. Validar constraints FK. ALTER TABLE EMPLEADOS MODIFY CONSTRAINT EMP_FK VALIDATE; -- 14. Borrar la tabla original. DROP TABLE EMPLEADOS2;

Tip técnico del día:

ALTER SYSTEM DISCONNECT SESSION :

Es un método alternativo para matar las sesiones de Oracle, A diferencia del comando KILL SESSION que pide a la sesión matarse sola ,el comando DISCONNECT SESSION mata el proceso del servidor dedicado (o circuitovirtual cuando se utiliza shared server), lo que equivale a matar el proceso a nivel del sistema operativo. Ejemplo: SQL> ALTER SYSTEM DISCONNECT SESSION 'sid,serial#' POST_TRANSACTION; SQL> ALTER SYSTEM DISCONNECT SESSION 'sid,serial#' IMMEDIATE; La cláusula POST_TRANSACTION espera a que las transacciones en curso se completen antes de desconectar la sesión, mientras que la cláusula IMMEDIATE desconecta la sesión y las transacciones en curso se hacen rollback. Por Lic. Francisco Barrundia [email protected]


Teléfono: (502)2364-5300 Fax: (502)2364-5311


Modificar un reporte en Reports 10g

Por: Lic. Francisco Barrundia [email protected]

Para modificar la apariencia y formateo de un reporte que generamos con el asistente podemos seguir los siguientes ejemplos: Para modificar la máscara de una cantidad de moneda

Por ejemplo: Queremos modifica la columna de salario en la tabla de empleados para que se muestre en quetzales , presionamos el simbolo de currency ($) y automaticamente deberia seleccionar el quetzal si tenemos configurada las propiedades de region de nuestro sistema operativo.


Teléfono: (502)2364-5300 Fax: (502)2364-5311


El resultado quedaría así:

También puede agregar comas para separar los miles:


Teléfono: (502)2364-5300 Fax: (502)2364-5311


Agregar o quitar decimales:

Como modificar atributos visuales:

Seleccionamos la columna a a la cual queremos poner un color de relleno, luego hacemos clic en la paleta de colores de la parte izquierda del editor y seleccionamos el color que queremos.


Teléfono: (502)2364-5300 Fax: (502)2364-5311


De igual manera podemos acceder a la paleta de colores para cambiar el color del texto:

Si queremos cambiar alguna propiedad visual basándonos en una condición que al cumplirse ejecuta ese cambio, utilizamos el formateo condicional: Seleccionamos en el menú: formato > formateo condicional :

Después seleccionamos nuevo y procedemos a definir la condición y la acción a realizarse:


Teléfono: (502)2364-5300 Fax: (502)2364-5311


En este caso especificamos que queremos que se marquen en rojo los los salarios de los empleados comprendidos entre 10,000 y 14,000 quetzales, esto no quedara “quemado” en el reporte si no será calculado cada vez que el reporte sea ejecutado con distintos salarios:


Teléfono: (502)2364-5300 Fax: (502)2364-5311


Nuevo Web Site

Le invitamos a visitar nuestro totalmente nuevo sitio web, una nueva herramienta de contacto al servicio de nuestros clientes. Ingrese a www.datum.com.gt para conocer más sobre nuestros servicios, productos, noticias, etc.


Teléfono: (502)2364-5300 Fax: (502)2364-5311


Gracias a la retroalimentación de nuestros clientes, Datum – Educacional estará impartiendo el siguiente curso:

Oracle Database 10g: Backup and Recovery

Qué aprenderá:

Después de completar este curso, los estudiantes podrán evaluar sus propios requerimientos de recuperación y desarrollar una estrategia adecuada para procedimientos de respaldo y recuperación. Ellos aprenderán cómo utilizar RMAN y Enterprise Manager para realizar operaciones de respaldo y recuperación. Características de Flashback son descritas como una forma adicional de recuperarse de un error humano.

Este curso incluye Oracle Secure Backup, el cual provee un respaldo a cinta confiable de la base de datos a través de integración directa con RMAN y también provee respaldo general de sistema de archivos a la cinta. Los estudiantes instalan Oracle Secure Backup y lo utilizan para respaldar archivos de la base de datos Oracle y del sistema de archivos.

Aprenda a:

Desarrollar un respaldo y una estrategia de recuperación efectiva

Realizar operaciones de respaldo y recuperación utilizando RMAN

Instalar y configurar Oracle Secure Backup para respaldos a cinta

Utilizar Oracle Secure Backup para respaldar la base de datos y el sistema de archivos.

Retroalimentación, comentarios, temas de interés y sugerencias para hands-on sessions:

[email protected]

Comentarios y Sugerencias: Su opinión es muy importante; si desea hacernos algún comentario o sugerencia, por favor escríbanos al correo electrónico: [email protected].

mailto:[email protected]

mailto:[email protected]

mantenimiento de Índices en 11g -...

Documents