¿Cómo usar un índice basado en funciones en una columna que contiene NULLs en Oracle 10+?

Digamos que tienes una table en Oracle:

CREATE TABLE person ( id NUMBER PRIMARY KEY, given_names VARCHAR2(50), surname VARCHAR2(50) ); 

con estos índices basados ​​en funciones:

 CREATE INDEX idx_person_upper_given_names ON person (UPPER(given_names)); CREATE INDEX idx_person_upper_last_name ON person (UPPER(last_name)); 

Ahora, given_names no tiene valores NULL, pero por el bien del argumento last_name lo hace. Si hago esto:

 SELECT * FROM person WHERE UPPER(given_names) LIKE 'P%' 

el plan de explicación me dice que usa el índice pero cámbialo a:

 SELECT * FROM person WHERE UPPER(last_name) LIKE 'P%' 

no es así Los documentos de Oracle dicen que usar el índice basado en funciones solo se usará cuando se cumplan varias condiciones, una de las cuales es garantizar que no haya valores NULOS ya que no están indexadas.

He intentado estas consultas:

 SELECT * FROM person WHERE UPPER(last_name) LIKE 'P%' AND UPPER(last_name) IS NOT NULL 

y

 SELECT * FROM person WHERE UPPER(last_name) LIKE 'P%' AND last_name IS NOT NULL 

En este último caso, incluso agregué un índice en last_name, pero no importa lo que intente, utiliza un escaneo de tabla completo. Suponiendo que no puedo deshacerme de los valores NULL, ¿cómo obtengo esta consulta para usar el índice en UPPER (last_name)?

El índice se puede usar, aunque el optimizador puede haber elegido no usarlo para su ejemplo en particular:

 SQL> create table my_objects 2 as select object_id, object_name 3 from all_objects; Table created. SQL> select count(*) from my_objects; 2 / COUNT(*) ---------- 83783 SQL> alter table my_objects modify object_name null; Table altenetworking. SQL> update my_objects 2 set object_name=null 3 where object_name like 'T%'; 1305 rows updated. SQL> create index my_objects_name on my_objects (lower(object_name)); Index created. SQL> set autotrace traceonly SQL> select * from my_objects 2 where lower(object_name) like 'emp%'; 29 rows selected. Execution Plan ---------------------------------------------------------- ------------------------------------------------------------------------------------ | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| ------------------------------------------------------------------------------------ | 0 | SELECT STATEMENT | | 17 | 510 | 355 (1)| | 1 | TABLE ACCESS BY INDEX ROWID| MY_OBJECTS | 17 | 510 | 355 (1)| |* 2 | INDEX RANGE SCAN | MY_OBJECTS_NAME | 671 | | 6 (0)| ------------------------------------------------------------------------------------ 

La documentation que leyó presumiblemente señaló que, al igual que cualquier otro índice, las keys que no son nulas no están almacenadas en el índice.

En su ejemplo, ha creado el mismo índice dos veces; esto daría un error, así que supongo que fue un error al pegar, no el código real que probó.

Lo intenté con

 CREATE INDEX idx_person_upper_surname ON person (UPPER(surname)); SELECT * FROM person WHERE UPPER(surname) LIKE 'P%'; 

y produjo el plan de consulta esperado:

 Execution Plan ---------------------------------------------------------- 0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=1 Card=1 Bytes=67) 1 0 TABLE ACCESS (BY INDEX ROWID) OF 'PERSON' (TABLE) (Cost=1 Card=1 Bytes=67) 2 1 INDEX (RANGE SCAN) OF 'IDX_PERSON_UPPER_SURNAME' (INDEX) (Cost=1 Card=1) 

Para responder a su pregunta, sí debería funcionar. Intente verificar dos veces si tiene el segundo índice creado correctamente.

Pruebe también una sugerencia explícita:

 SELECT /*+INDEX(PERSON IDX_PERSON_UPPER_SURNAME)*/ * FROM person WHERE UPPER(surname) LIKE 'P%'; 

Si eso funciona, pero solo con la pista, entonces es probable que esté relacionado con las statistics de CBO que fallaron, o con los parameters de init relacionados con CBO.

¿Estás seguro de que quieres que se use el índice? Los escaneos completos de tabla no son malos. Dependiendo del tamaño de la tabla, puede ser más eficiente hacer un escaneo de tabla que usar un índice. También depende de la densidad y la distribución de los datos, razón por la cual se recostackn las statistics. El optimizador basado en el costo generalmente se puede confiar para tomar la decisión correcta. A less que tengas un problema de performance específico, no me preocuparía demasiado por eso.

Oracle aún usará índices basados ​​en funciones con columnas que contengan nulo; creo que malinterpretaste la documentation.

Sin embargo, debe poner un nvl en el índice de function si desea verificar esto.

Algo como…

 create index idx_person_upper_surname on person (nvl(upper(surname),'N/A')); 

A continuación, puede consultar usando el índice con

 select * from person where nvl(upper(surname),'N/A') = 'PIERPOINT' 

Aunque, todo un poco feo. Como la mayoría de las personas tiene apellidos, tal vez sea apropiado un "no nulo" :-).

Puede eludir el problema de los valores nulos que no están indexados en esta u otras situaciones al indexar también en function de un valor literal:

 CREATE INDEX idx_person_upper_surname ON person (UPPER(surname),0); 

Esto le permite usar el índice para consultas tales como:

 Select * From person Where UPPER(surname) is null; 

Esta consulta normalmente no usa un índice, a exception de los índices o índices de bitmap que incluyen una columna real no anulable que no sea apellido.