Indexer QUE la valeur NULL d'une colonne

Version imprimable

06/12/2018, 23h57
Ikebukuro

Indexer QUE la valeur NULL d'une colonne

Bonjour,

Pour des raisons d'étude sur les index Oracle, je voudrais créer un index MAIS qui n'indexe QUE la valeur NULL d'une colonne.
Quel intérêt? Imaginons une table de 10 000 000 de rows avec seulement 100 rows avec Col10 à NULL et le reste des rows avec la valeur 1 (c'est juste un cas d'école).

Si je fais un SELECT avec la clause WHERE Col10 IS NULL, Oracle fera un Full Table Scan extrêmement coûteux. Pour éviter cela, j'indexe non pas Col10, car les rows avec NULL seront absents, mais je crée un indexe composite sur (Col1, 1).
Et là, cool, le plan d'exécution montre un prédicat d'accès IS NULL à ces rows via l'index et non pas un prédicat de filtre.

Maintenant je voudrais indexer uniquement la valeur NULL. Pourquoi? Faire sauter un ou deux niveaux de branches donc rendre l'index plus désirable pour le CBO car plus rapide à parcourir et puis avoir un index plus léger pour RMAN et Datapump car réduit à 100 rows.

J'ai donc voulu créer un index sur fonction : si col10 is null then return col10,1 sinon return null, null;
Pb : ça ne passe pas, je me heurte à des contraintes PL/SQL; pour qu'une fonction retourne N valeurs il faut créer un type objet ou utiliser un array de deux valeurs (ce que j'arrive à faire). MAIS, ensuite, impossible de créer un index en se basant sur cette fonction...

Voilà, si vous avez des idées je suis preneur ;)
Dans le "pire" des cas, où cela n'est pas possible, peut-être que créer une partition pour mes 100 rows serait une solution... à voir

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
 
create table demo as select rownum id, case when rownum>2 then 'X' end flag from xmltable('1 to 100000');
 
 
alter table demo add flag_is_null char generated always as (case when flag is null then 'Y' end) virtual;
create index demo_flag_is_null on demo(flag_is_null);
 
 
exec dbms_stats.gather_table_stats('','DEMO');
 
 
explain plan for select * from demo where flag_is_null='Y';
 
 
select * from dbms_xplan.display();

07/12/2018, 09h49
Ikebukuro

Qu'est-ce que nous ferions sans toi Franck?
Merci pour le retour, je regarde ça très vite ;)

Ca marche pareil avec un index de fonction

Code:

1
2
3
4
5
6
7
8
9
10
11
12
CREATE OR REPLACE FUNCTION wmc_demo (p_flag IN VARCHAR2) RETURN VARCHAR2 DETERMINISTIC
IS
BEGIN
	IF p_flag IS NULL THEN RETURN 'Y'; END IF;
  RETURN NULL;
END;
 
CREATE INDEX demo_flag_is_null2 ON demo(wmc_demo(flag));
 
exec dbms_stats.gather_table_stats('','DEMO');
 
SELECT * FROM demo WHERE wmc_demo(flag) = 'Y';

Code:

1
2
3
4
5
SELECT index_name, leaf_blocks, distinct_keys, num_rows FROM user_indexes WHERE table_name = 'DEMO'
 
INDEX_NAME	LEAF_BLOCKS	DISTINCT_KEYS	NUM_ROWS
DEMO_FLAG_IS_NULL	1	1	2
DEMO_FLAG_IS_NULL2	1	1	2

07/12/2018, 16h44
Ikebukuro

Merci McM, j'avais essayé de faire un index sur fonction mais... échec :calim2:
11/12/2018, 10h06
McM

Il faut déclarer la fonction en DETERMINISTIC obligatoirement.
11/12/2018, 12h06
Ikebukuro

Oui, c'est ce que j'avais fait mais je voulais indexer la valeur NULL et pas Y.
Résultat, je voulais créer une fonction qui retournait 2 valeurs : NULL et 1 pour les rows avec Col10 à NULL et, pour les rows avec col10 <> NULL, je retourne NULL, NULL ==> résultat, je pensais qu'Oracle me créerait un index composite sur fonction, mais échec, il ne veut pas.
11/12/2018, 15h14
mnitu

Citation:

Envoyé par McM

Ca marche pareil avec un index de fonction

...

La solution proposé par Pachot implique un index de fonction en fait.
11/12/2018, 16h46
pachot

Citation:

Envoyé par mnitu

La solution proposé par Pachot implique un index de fonction en fait.

Exact. Et l'avantage de la colonne virtuelle, c'est qu'on l'utilise dans la where clause sans le risque que le prédicat soit différent de ce qui est indexé.
En fait, les index sur fonction créent une colonne virtuelle, donc autant la créer nous-mêmes avec un nom parlant et utilisable dans les requêtes.