valores valor significado restriccion nulos nulo not indexes index example campos buscar all oracle optimization null

significado - valor nulo en oracle



Oracle 10g: optimice DONDE NO ES NULO (9)

Crea un índice en esa columna.

Para asegurarse de que se usa el índice, debe estar en el índice y otras columnas en el where.

ocdecio respondió:

Si está seleccionando *, entonces tendría sentido hacer un escaneo de tabla en lugar de usar el índice.

Eso no es estrictamente cierto; se usará un índice si hay un índice que se ajuste a su cláusula where, y el optimizador de consultas decida que usar ese índice sería más rápido que hacer un escaneo de tabla. Si no hay índice, o no hay índice adecuado, solo entonces se debe realizar un escaneo de tabla.

Tenemos Oracle 10g y necesitamos consultar 1 tabla (sin uniones) y filtrar filas donde 1 de las columnas es nula. Cuando hacemos esto - DONDE OurColumn IS NOT NULL - obtenemos un escaneo de tabla completo en una mesa muy grande - MAL MALO MALO. La columna tiene un índice pero se ignora en esta instancia. ¿Hay alguna solución para esto?

Gracias

El optimizador cree que el escaneo completo de la tabla será mejor.

Si solo hay unas pocas filas NULL , el optimizador es correcto.

Si está absolutamente seguro de que el acceso al índice será más rápido (es decir, tiene más de 75% filas con col1 IS NULL ), insinúe su consulta:

SELECT /*+ INDEX (t index_name_on_col1) */ * FROM mytable t WHERE col1 IS NOT NULL

¿Por qué el 75% ?

Debido a que el uso de INDEX SCAN para recuperar valores no cubiertos por el índice implica una unión oculta en ROWID , que cuesta aproximadamente 4 veces más que el escaneo de tablas.

Si el rango de índice incluye más del 25% de las filas, el escaneo de la tabla suele ser más rápido.

Como mencionó Tony Andrews , el factor de agrupamiento es un método más preciso para medir este valor, pero el 25% sigue siendo una buena regla empírica.

El optimizador tomará su decisión basándose en el costo relativo del escaneo completo de la tabla y utilizando el índice. Esto se reduce principalmente a cuántos bloques tendrán que leerse para satisfacer la consulta. La regla de 25% / 75% mencionada en otra respuesta es simplista: en algunos casos, una exploración completa de la tabla tendrá sentido incluso para obtener el 1% de las filas, es decir, si esas filas se distribuyen en muchos bloques.

Por ejemplo, considere esta tabla:

SQL> create table t1 as select object_id, object_name from all_objects; Table created. SQL> alter table t1 modify object_id null; Table altered. SQL> update t1 set object_id = null 2 where mod(object_id,100) != 0 3 / 84558 rows updated. SQL> analyze table t1 compute statistics; Table analyzed. SQL> select count(*) from t1 where object_id is not null; COUNT(*) ---------- 861

Como puede ver, solo aproximadamente el 1% de las filas en T1 tienen un object_id no nulo. Pero debido a la forma en que construí la tabla, estas 861 filas se distribuirán más o menos uniformemente alrededor de la mesa. Por lo tanto, la consulta:

select * from t1 where object_id is not null;

Es probable que visite casi todos los bloques en T1 para obtener datos, incluso si el optimizador usó el índice. ¡Tiene sentido entonces prescindir del índice y realizar un escaneo completo de la tabla!

Una estadística clave para ayudar a identificar esta situación es el factor de agrupamiento del índice:

SQL> select clustering_factor from user_indexes where index_name=''T1_IDX''; CLUSTERING_FACTOR ----------------- 460

Este valor 460 es bastante alto (en comparación con las 861 filas en el índice), y sugiere que se usará un escaneo de tabla completo. Vea este artículo de DBAZine sobre factores de agrupamiento .

El uso de pistas debe hacerse solo como una solución en lugar de una solución.

Como se menciona en otras respuestas, el valor nulo no está disponible en los índices B-TREE.

Como sabe que tiene valores nulos en su mayoría en esta columna, podría reemplazar el valor nulo por un rango, por ejemplo.

Eso realmente depende de su columna y la naturaleza de sus datos, pero normalmente, si su columna es un tipo de fecha, por ejemplo:

where mydatecolumn is not null Se puede traducir en una regla que dice: Quiero todas las filas que tienen una fecha.

Entonces definitivamente puede hacer esto: donde mydatecolumn <= sysdate (en oráculo)

Esto devolverá todas las filas con una fecha y omitirá los valores nulos mientras se aprovecha el índice en esa columna sin utilizar ninguna sugerencia.

La base de datos Oracle no indexa valores nulos en índices regulares (b-tree), por lo que no puede usarlo ni tampoco puede obligar a la base de datos Oracle a usarlo.

BR

Puede depender del tipo de índice que tenga sobre la mesa.

La mayoría de los índices B-tree no almacenan entradas nulas. Los índices de mapa de bits almacenan entradas nulas.

Entonces, si tienes:

select * from mytable donde mycolumn es nulo

y tiene un índice B-tree estándar en mycolumn , entonces la consulta no puede usar el índice ya que "null" no está en el índice.

(Si el índice está en contra de varias columnas, y una de las columnas indexadas no es nula, entonces habrá una entrada en el índice).

Si está seleccionando *, entonces tendría sentido hacer un escaneo de tabla en lugar de usar el índice. Si sabe en qué columnas está interesado, puede crear un índice cubierto con esas columnas más el que está aplicando la condición IS NOT NULL.

También vale la pena verificar si las estadísticas de Oracle en la tabla están actualizadas. Es posible que no sepa que una exploración de tabla completa será más lenta.