Cher(e) ami(e)s en oracle
Vous venez de créer un index sur une colonne , malgré cela requête est toujours lente.
L’utilisation des fonctions sur une colonne indexé bloque l’utilisation de l’index. un cas d’exemple
1 : structure de la table cust
CREATE TABLE cust(
cust_id NUMBER
,last_name VARCHAR2(30)
,first_name VARCHAR2(30))
TABLESPACE users;
2.1 : Sans index
requete :
set autotrace on;
select last_name, first_name
from cust
where last_name = ‘SMITH’
and first_name = ‘JOHN’;
LAST_NAME FIRST_NAME
—————————— ——————————
SMITH JOHN
Plan d’exÚcution
———————————————————-
Plan hash value: 260468903
————————————————————————–
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
————————————————————————–
| 0 | SELECT STATEMENT | | 1 | 34 | 3 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| CUST | 1 | 34 | 3 (0)| 00:00:01 |
————————————————————————–
On voit un full scan sur la table CUST
2.2: structure de l’index sur les deux colonnes
create index cust_idx1 on cust(last_name, first_name);
3) calcule des stats
exec dbms_stats.gather_table_stats(ownname=>user,-
tabname=>’CUST’,cascade=>true);
3.1)
——————————————————————————
| Id | Operation | Name | Rows | Bytes | Cost (%CPU) | Time |
——————————————————————————
| 0 | SELECT STATEMENT | | 13 | 143 | 1 (0) | 00:00:01 |
|* 1 | INDEX RANGE SCAN| CUST_IDX1 | 13 | 143 | 1 (0) | 00:00:01 |
——————————————————————————
le résultat est fourni en utilisant l’ indexe cust_IDX1 sans passer par la table
4) utilisation d’une fonction :
LAST_NAME FIRST_NAME
—————————— ——————————
SMITH JOHN
Plan d’exÚcution
———————————————————-
Plan hash value: 289943709
——————————————————————————
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
——————————————————————————
| 0 | SELECT STATEMENT | | 1 | 11 | 1 (0)| 00:00:01 |
|* 1 | INDEX SKIP SCAN | CUST_IDX1 | 1 | 11 | 1 (0)| 00:00:01 |
——————————————————————————
Predicate Information (identified by operation id):
—————————————————
Deux solutions :
1 – Créer un index basé sur une fonction.
Vous ne pouvez pas modifier une colonne sur laquelle un index basé sur une fonction est appliqué. Vous devrez supprimer l'index, modifier la colonne, puis recréez l'index
2 – Si vous utilisez Oracle Database 11g ou supérieur, créez une colonne virtuelle indexée
1)
create index cust_fidx1 on cust(UPPER(first_name));
résultat :
Plan hash value: 1835763432
——————————————————————————–
——————
| Id | Operation | Name | Rows | Bytes | Cost
(%CPU)| Time |
——————————————————————————–
——————
| 0 | SELECT STATEMENT | | 1 | 14 | 2
(0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID BATCHED| CUST | 1 | 14 | 2
(0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | CUST_FIDX1 | 1 | | 1
(0)| 00:00:01 |
——————————————————————————–
2) Index virtuel :
On ajoute une colonne virtuelle à la table qui encapsule la requête SQL
alter table cust add(up_name generated always as (UPPER(first_name)) virtual);
L’étape suivante est de créer l’index sur la colonne
create index cust_vidx1 on cust(up_name);
Conclusion :
Vous posez peut-être cette question: « Qui fonctionne le mieux, un index basé sur la fonction ou une colonne virtuelle indexée? »
Dans mes tests, j’ai créé plusieurs scénarios où la colonne virtuelle a mieux performé que la fonction .
Les résultats peuvent varier en fonction de vos données.
Le but de cette recette n’est pas de vous convaincre de remplacer immédiatement tous les index fonctionnels de votre système avec
des colonnes virtuelles;
je vous présent plutôt des autres solutions pour que vous soyez au courant d’une méthode alternative pour résoudre un problème.
de performance. Une colonne virtuelle n’est pas gratuite. Si vous avez une table existante,
vous devez créer et maintenir le DDL requis pour créer la colonne virtuelle,
tandis qu’un index basé sur la fonction peut être ajouté, modifié et supprimé indépendamment de la table
Comme toujours ce cas doit être tester en preproduction avant toute modification de la production .
Consulter la doc d’oracle pour les conditions de création d’une colonne virtuelle
OraclementVotre