Indices Oracle

[h2swider]

Tengo una tabla con 12 millones de registros. los usuarios finales del sistema son pocos, 2 o 3. Pero al realizar una búsqueda simple por un campo numérico (numero de expediente) como es de esperar tarda muchísimo, siempre el resultado tiene aproximadamente 20 registros, y en el where solo existen 2 condiciones, el numero de expediente, y el estado (1 habilitado, 0 deshabitado)

Mi pregunta es, que indices o recomendaciones me pueden dar para optimizar los tiempos de consulta lo mas posible?

La consulta es algo así

Código SQL:

SELECT * FROM expedientes WHERE expediente= :param_expediente AND estado = 1
 

Y la tabla esta compuesta por. Expediente number, estado number, descripcion varchar 500.

[huesos52]

Yo te recomendaría un indice compuesto por los campos expediente y estado.
Una vez hayas creado el indice, debes actualizar las estadisticas de la tabla.

Nos cuentas como te va.

saludos

[h2swider]

Tenia pensado algo asi, cuando lo implemente les digo como me fue. Gracias

[matanga]

Esto depende de varios factores, además de las condiciones del WHERE, también está la cardinalidad (cantidad de valores diferentes en una columna), las columnas en el ORDER BY y las columnas en el SELECT.

Según lo entiendo, la columna expediente supone una cardinalidad alta y la columna estado tiene una cardinalidad=2 (valores 0 y 1) que se considera muy baja, en estos casos, el optimizador resolverá el WHERE primero por expediente y después por estado, lo que sugiere:

1. Si el subconjunto de filas resultantes de filtrar por expediente es bajo, se recomienda crear un solo índice sobre la columna expediente, y dejar que el optimizador haga un fullscan sobre el subconjunto para filtrar el estado, ya que esto puede ser más óptimo que mantener un segundo índice en una tabla con 12 millones de registros.

2. Si el subconjunto de filas resultantes de filtrar por expediente es alto y detectas que el fullscan sobre el subconjunto para filtrar el estado es lento, se recomienda crear un índice sobre la columna expediente y un segundo índice pero de tipo bitmap (diseñados para valores True|False, 0|1, etc) sobre la columna estado.

3. Y se recomienda crear un solo índice compuesto por los dos campos cuando, además de formar la condición del WHERE, los campos son los únicos en el SELECT (ej: select expediente, estado from expedientes...) porque evita el acceso a la tabla para leer los datos, y/o cuando son los únicos en el ORDER BY (ej: select * from expedientes where .... order by expediente, estado) ya que la ordenación de la consulta no se ejecuta si coincide con la ordenación del índice.

De todos modos, estas recomendaciones son en general, siempre es mejor evaluar el rendimiento ejecutando las consultas comparando los tiempos y planes de ejecución.

Saludos