INDEX

CREAR INDICES

Validar selectividad de las tablas en donde la selectividad debe ser aproximada a 1:

A = SELECT count(*) FROM ESQUEMA.NOMBRE_TABLA;

B =SELECT count(DISTINCT TABLA.COLUMNADETABLA) FROM ESQUEMA.NOMBRE_TABLA;

SELECTIVIDAD = B / A

CREAR INDEX

DROP INDEX NOM_ESQUEMA.INDEX_NOM_TABLE1;

CREATE UNIQUE INDEX NOM_ESQUEMA.INDEX_NOM_TABLE1 ON NOM_ESQUEMA.NOM_TABLE1

(ID_CAMPO_TABLE)

LOGGING

TABLESPACE INDICES

PCTFREE 10

INITRANS 2....................................................................numero minimo de conexiones simultaneas

MAXTRANS 255..............................................................numero maximo de conexiones simultaneas

STORAGE (

INITIAL 64K

NEXT 1M

MINEXTENTS 1

MAXEXTENTS UNLIMITED

PCTINCREASE 0

BUFFER_POOL DEFAULT

)

NOPARALLEL;

OPERACIONES CON INDEX

ALTER INDEX ESQUEMA.NOMBRE_INDICE UNUSABLE;

ALTER INDEX ESQUEMA.NOMBRE_INDICE REBUILD;

RECONSTRUIR INDICES

ALTER INDEX ESQUEMA.NOM_INDICE REBUILD;

Consulta Oracle SQL para conocer Tamaño ocupado por una tabla concreta incluyendo los índices de la misma

select sum(bytes)/1024/1024 Table_Allocation_MB from user_segments

where segment_type in ('TABLE','INDEX') and

(segment_name='NOMBRETABLA' or segment_name in

(select index_name from user_indexes where table_name='NOMBRETABLA'));

Antes de crear los indices es importante identificar la selectividad que debe ser idealmente 1 o aproximada

SELECT COUNT (*) FROM ESQUEMA.TABLA;

SELECT COUNT (DISTINCT ESQUEMA.TABLA.CAMPO_PARA_CREAR_INDEX) FROM ESQUEMA.TABLA;

Reindexar todo un esquema generar Script

select 'ALTER INDEX '||owner||'.'||index_name ||' REBUILD ONLINE NOLOGGING;' from dba_indexes where owner=upper('NOM_ESQUEMA');

Revisar todas las tablas de un esquema para correr estadisticas y reindexar

spool /directorio/indexa.sql

set pagesize 20000;

SELECT 'ALTER INDEX'||' '||I.OWNER||'.'||I.INDEX_NAME||' '||'REBUILD;' FROM ALL_INDEXES I WHERE OWNER = 'NOM_ESQUEMA';

SELECT 'ANALYZE TABLE'||' '||T.OWNER||'.'||T.TABLE_NAME||' '||'COMPUTE STATISTICS;' FROM DBA_TABLES T WHERE OWNER = 'NOM_ESQUEMA';

spool off;

Otro método es utilizar las funciones del motor de base de datos

Para saber si las estadísticas se están lanzando correctamente podemos hacer una consulta sobre la tabla dba_indexes y ver el campo last_analyzed para observar cuando se ejecutaron sobre ese índice las estadísticas.

Lanzar consultas con el usuario SYS

SQL> SELECT index_name, last_analyzed

FROM dba_indexes

WHERE table_owner=’ESQUEMA’;

Para actualizar las estadísticas utilizamos el paquete DBM_STATS.

SQL> Execute DBMS_STATS.gather_schema_stats(‘ESQUEMA’);

Una vez actualizadas las estadísticas de los índices de la base de datos lanzamos la siguiente consulta:

SQL> SELECT index_name, blevel,

DECODE(blevel,0,'OK BLEVEL',1,'OK BLEVEL',2,

'OK BLEVEL',3,'OK BLEVEL',4,'OK BLEVEL','BLEVEL HIGH') OK

FROM dba_indexes where table_owner='ESQUEMA';

Con esta sentencia obtendremos el nombre nombre del índice, el blevel y si es correcto.

INDEX_NAME BLEVEL OK

INX_CUENTA 1 OK BLEVEL

INX_TRABAJO 0 OK BLEVEL

INX_DINERO BLEVELHIGH

Los índices que deberíamos de reconstruir son los que en la columna ok aparecen como BLEVEL HIGH.

Blevel (branch level) es parte del formato del B-tree del índice e indica el número de veces que ORACLE ha tenido que reducir la búsqueda en ese índice.

Si este valor está por encima de 4 el índice debería de ser reconstruido.

Para reconstruir un índice bastaría con lazar la siguiente sentencia

SQL> ALTER INDEX <index_name> REBUILD;

Para reconstruir una partición de un índice podríamos hacer lo siguiente

SQL> ALTER INDEX <index_name> REBUILD PARTITION <nb_partition> NOLOGGING;

Nota: En algunos casos cuando alguno de los índices tiene algún tipo de corrupción no es posible reconstruirlo. La solución en este caso es borrar el índice y recrearlo.

Tomado del sitio web: https://orasite.com/tutoriales/administracion/reconstruccion-de-indices