Index partiel sur NULL : comment indexer uniquement la valeur NULL d'une colonne - Partial index on NULL: how to index only NULL
Introduction
On vous a dit qu'il n'était pas possible sous Oracle d'indexer la valeur NULL. J'ai prouvé ici que c'était faux : http://dbaoraclesql.canalblog.com/archives/2018/08/05/36611094.html.
Idem pour le fait que Oracle ne gère pas d'index partiel : cette fonctionnalité est disponible en 12c mais uniquement pour des tables partitionnées. Néanmoins il existe une solution de contournement depuis plusieurs versions d'Oracle : créer un index sur fonction qui mettra à NULL les valeurs que l'on ne veut pas indexer, et, de la sorte, l'index créé via cette fonction ne retiendra pas ces valeurs annulées.
Aujourd'hui nous allons plus loin : nous voulons indexer UNIQUEMENT la valeur NULL d'une colonne contenant également des valeurs différentes de NULL. Donc créer un index partiel sur NULL :-)
Quel intérêt? Imaginons une table de 100 000 000 de rows avec seulement 100 rows avec Col10 à NULL, le reste des rows avec des valeurs non null. Les utilisateurs font majoritairement des SELECTs avec la clause WHERE col10 IS NULL pour identifier ces rows qui, fonctionnellement, leur pose problème. Dans ce cas, Oracle fera un Full Table Scan extrêmement coûteux et lira 99,99% de rows inutiles. Pour éviter cela, j'indexe non pas Col10, car les rows avec NULL seront absents, mais je crée un indexe composite sur (Col10, 8); oui, sur col10 et une constante pour être sur que la deuxième colonne soit NOT NULL. 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? Pour faire sauter un ou deux niveaux de branches donc rendre l'index plus désirable pour le CBO car pour accéder aux feuilles il lira moins de blocs. En outre cela permet d'avoir un index plus léger, sans données inutiles, rendant plus rapide les opérations RMAN et Datapump puisque l'index est devenu minuscule.
Hé bien ce fut beaucoup plus dur que prévu, à tel point que j'ai du me faire aider sur le forum developpez.com par Franck PACHOT et McM; un gros merci à vous deux :-)
Points d'attention
Aucun...
Base de tests
N'importe quelle base Oracle.
Exemples
Ma solution était de créer un index composite sur fonction : si col10 is null then return col10, 8 sinon return null, null; Le pb, c'est que ça ne passe pas, je me heurte à des contraintes PL/SQL; pour qu'une fonction retourne plus de une valeur, 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, Oracle refuse car la fonction retourne un type non pris en compte pour la création d'index...
============================================================================================
Solution un : index sur colonne virtuelle
============================================================================================
Voici la solution de Franck: utiliser une colonne virtuelle.
On crée d'abord la table de test avec 100 000 rows.
SQL> create table zztest as select rownum AS "ID", case when rownum>2 then 'X' end AS "FLAG" from xmltable('1 to 100000');
Table created.
SQL> select FLAG, count(*) from zztest group by FLAG;
F COUNT(*)
- ----------
2
X 99998
SQL> select count(*) from zztest where flag is null;
COUNT(*)
----------
2
Création de la colonne virtuelle : elle vaut Y quand la colonne flag vaut null puis on crée un index dessus.
SQL> alter table zztest add flag_is_null char generated always as (case when flag is null then 'Y' end) virtual;
SQL> create index zztest_flag_is_null on zztest(flag_is_null);
La recherche se fait avec la clause suivante : WHERE flag_is_null='Y' et non pas WHERE flag IS NULL. OK, c'est un peu complexe mais on n'a pas le choix.
SQL> select * from zztest where flag_is_null='Y';
ID F F
---------- - -
1 Y
2 Y
L'index est bien utilisé : bingo!
SQL> select * from table(dbms_xplan.display_cursor(null,null,'TYPICAL'));
PLAN_TABLE_OUTPUT
-----------------------------------------------
SQL_ID 29r24a03zt6yb, child number 0
-------------------------------------
select * from zztest where flag_is_null='Y'
Plan hash value: 828944513
--------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 2 (100)| |
| 1 | TABLE ACCESS BY INDEX ROWID BATCHED| ZZTEST | 1000 | 7000 | 2 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | ZZTEST_FLAG_IS_NULL | 2 | | 1 (0)| 00:00:01 |
--------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("FLAG_IS_NULL"='Y')
19 rows selected.
============================================================================================
Solution deux : index sur fonction
============================================================================================
La solution de McM : utiliser une fonction pour créer l'index et non pas directement une colonne virtuelle. Il faut quand même savoir que sous Oracle, un index sur fonction crée une colonne virtuelle donc on retombe sur la solution un :-)
SQL> drop index zztest_flag_is_null;
SQL> 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;
/
Function created.
SQL> CREATE INDEX zztest_flag_is_null ON zztest(wmc_demo(flag));
Index created.
SQL> exec dbms_stats.gather_table_stats('','ZZTEST');
PL/SQL procedure successfully completed.
Nous avons bien créé un index partiel, qui n'indexe QUE la valeur NULL pour le champ flag puisque seules deux lignes sont stockées.
SQL> select INDEX_TYPE, UNIQUENESS, BLEVEL, LEAF_BLOCKS, NUM_ROWS from user_indexes where lower(index_name) = 'zztest_flag_is_null';
INDEX_TYPE UNIQUENES BLEVEL LEAF_BLOCKS NUM_ROWS
--------------------------- --------- ---------- ----------- --- -----
FUNCTION-BASED NORMAL NONUNIQUE 0 1 2
La recherche se fait avec la clause suivante, où la fonction ayant servi à créer l'index doit impérativement être utilisée : WHERE wmc_demo(flag) = 'Y'. Là encore, impossible de faire une clause WHERE avec WHERE flag IS NULL.
SQL> SELECT * FROM zztest WHERE wmc_demo(flag) = 'Y';
ID F F
---------- - -
1 Y
2 Y
L'index est bien utilisé : succès total :-)
SQL> select * from table(dbms_xplan.display_cursor(null,null,'TYPICAL'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------
SQL_ID 6fuyhjjywd3j2, child number 0
-------------------------------------
SELECT * FROM zztest WHERE wmc_demo(flag) = 'Y'
Plan hash value: 828944513
------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 2 (100)| |
| 1 | TABLE ACCESS BY INDEX ROWID BATCHED| ZZTEST | 1000 | 7000 | 2 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | ZZTEST_FLAG_IS_NULL | 2 | | 1 (0)| 00:00:01 |
------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("ZZTEST"."SYS_NC00004$"='Y')
19 rows selected.
/https%3A%2F%2Fassets.over-blog.com%2Ft%2Fcedistic%2Fcamera.png)
/https%3A%2F%2Fprofilepics.canalblog.com%2Fprofilepics%2F1%2F5%2F1555182.jpg)