Comment utiliser une fonction de base de l'index sur une colonne contenant des valeurs Null dans Oracle 10+?

Permet simplement de dire que vous avez une table Oracle:

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

avec fonction de ces indices:

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

Maintenant, given_names a pas de valeurs NULL, mais pour la commodité du raisonnement nom ne. Si je fais ceci:

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

l'expliquer plan me raconte son à l'aide de l'index, mais le modifier:

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

il ne le fait pas. L'Oracle docs disent que l'utilisation de la fonction d'index de base ne sera utilisé que lorsque plusieurs conditions sont remplies, un est de s'assurer il n'y a pas de valeurs NULL, car ils ne sont pas indexés.

J'ai essayé ces requêtes:

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

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

Dans ce dernier cas, j'ai même ajouté un index sur le nom, mais peu importe ce que j'essaie il utilise un full table scan. En supposant que je ne peut pas se débarrasser de l de valeurs NULL, comment puis-je obtenir cette requête à utiliser l'index sur la partie SUPÉRIEURE(nom)?

Combien de lignes dans la table? Pouvez-vous poster le plan pour expliquer l'intégralité de la table d'analyse, et aussi lorsqu'il ne choisissez d'utiliser l'index (vous pourriez avoir à faire des allusions ou de modifier la colonne not NULL pour les besoins de l'exercice).

OriginalL'auteur cletus | 2008-10-07

oracle sql

L'index peut être utilisé, si l'optimiseur peut-être choisi de ne pas l'utiliser pour votre exemple particulier:

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 altered.

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 vous avez lu, c'était sans doute de souligner que, comme tout autre indice, tout-clés null ne sont pas stockées dans l'index.

OriginalL'auteur Tony Andrews

2

Dans votre exemple, vous avez créé le même index à deux reprises, ce qui donne une erreur donc je suppose que c'était une erreur dans le collage, et non le code que vous avez essayé.

Je l'ai essayé avec
```
CREATE INDEX idx_person_upper_surname ON person (UPPER(surname));

SELECT * FROM person WHERE UPPER(surname) LIKE 'P%';
```
et produits attendus du plan de requête:
```
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)
```
Pour répondre à votre question, oui, ça devrait fonctionner. Essayez de double-vérifier que vous avez le deuxième indice créé correctement.

Également faire une allusion explicite:
```
SELECT /*+INDEX(PERSON IDX_PERSON_UPPER_SURNAME)*/ * 
FROM person 
WHERE UPPER(surname) LIKE 'P%';
```
Si cela fonctionne, mais seulement avec l'indice, alors il est probablement liée à CBO statistiques mal tourné, ou CBO liées initialisation des paramètres.

Avez-vous mis les valeurs Null dans la table et obtenir que le plan de requête?

OriginalL'auteur CaptainPicard
0

Êtes-vous sûr que vous voulez l'indice à utiliser? Complète les analyses de la table ne sont pas mauvais. En fonction de la taille de la table, il pourrait être plus efficace de faire une analyse de la table que d'utiliser un index. Elle dépend aussi de la densité et de la distribution des données, qui est pourquoi les statistiques sont recueillies. Le coût de l'optimiseur peut généralement faire confiance pour faire le bon choix. Sauf si vous avez une problème de performances, je ne m'inquiéterais pas trop à ce sujet.

OriginalL'auteur
0

Oracle continuera d'utiliser une fonction d'index avec des colonnes qui contiennent la valeur null - je pense que vous avez mal interprété la documentation.

Vous avez besoin de mettre une nvl dans la fonction d'index si vous voulez vérifier ce que.

Quelque chose comme...
```
create index idx_person_upper_surname on person (nvl(upper(surname),'N/A'));
```
Vous pouvez ensuite la requête à l'aide de l'index avec
```
select * from person where nvl(upper(surname),'N/A') = 'PIERPOINT'
```
Bien que, tous un peu moche. Comme la plupart des gens ont des noms de famille, peut-être un "not null" est le cas :-).

OriginalL'auteur Nick Pierpoint
0

Vous pouvez contourner le problème des valeurs null être indexée dans ce ou d'autres situations également l'indexation basée sur une valeur littérale:
```
CREATE INDEX idx_person_upper_surname ON person (UPPER(surname),0);
```
Cela vous permet d'utiliser l'index pour de telles questions comme:
```
Select *
From   person
Where  UPPER(surname) is null;
```
Cette requête ne serait pas normalement etats-unis d'un index, à l'exception de la bitmap d'index ou index dont un n'acceptant pas cette valeur réelle de la colonne autre que le nom de famille.

David, quel type de requête peut rendre l'utilisation d'un tel indice?
Toute requête utilisée limite SUPÉRIEURE(nom de famille). David truc du assure que les valeurs nulles de la tige(nom de famille) sont indexés. Oracle n'a pas d'index si toutes les valeurs sont nulles. La valeur littérale de 0 assure que cela n'arrive jamais.

OriginalL'auteur David Aldridge

Vous devez vous connecter pour publier un commentaire.