Oracle index et fonction

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

 

 

 

 

 

Post Comment

Votre adresse e-mail ne sera pas publiée. Les champs obligatoires sont indiqués avec *