Initiation à l'optimisation de requêtes SQL sous ORACLE

Jean-Philippe GUILLOUX

Les bases de données sont un élément essentiel lorsqu'il s'agit de la persistance de l'information. Oracle est un acteur majeur et beaucoup d'applications d'entreprise reposent sur leur SGBD. L'optimisation de la base en elle-même est souvent du ressort du DBA. Toutefois, le développeur qui écrit la requête connaît souvent mieux que le DBA la logique métier qui entoure un projet et sera ainsi mieux à même d"optimiser ses requêtes. L'objectif de ce document est d'introduire la notion d'optimisation de requête et les bases nécessaires pour comprendre comment améliorer les performances de ses interrogations. ♪

- Titre : Initiation à l'optimisation de requêtes SQL sous ORACLE

- Auteur : Jean-Philippe GUILLOUX

- Parution : 21 décembre 2008

- Mise à jour : 25 décembre 2008

- Licence : Ce document est issu de http://www.developpez.com et reste la propriété exclusive de son auteur. La copie, modification et/ou distribution par quelque moyen que ce soit est soumise à l'obtention préalable de l'autorisation de l'auteur.

Sommaire

- I. Objectif de ce document

- I-A. Introduction

- I-B. Public ciblé et prérequis

- I-C. Mise en garde - Limites

- II. Plan d'exécution, optimiseur, cout, chemin d'accès et statistiques

- II-A. Chemin d'accès

- II-B. Plan d'exécution

- II-C. Trouver le meilleur chemin ? On a un GPS ? (L'optimisteur)

- II-D. Les statistiques

- II-E. Les hints : « je suis grand c'est moi qui gère »

- II-F. Et concrètement comment je le sais le plan d'exécution pour ma requête ?

- III. Les chemins d'accès

- IV. Les index

- IV-A. Définition - Avantages

- IV-B. Les différents types d'index

- IV-C. Les index composés :quelques règles

- IV-D. Les balayages d'index

- IV-E. Inconvénients et limites des index

- IV-E-a. Une grosse limite : les fonctions

- IV-E-b. Trop d'index tue l'index

- IV-E-c. Quand l'index se substitue à la table…

- V. Les tables organisées en index

- VI. Clefs étrangères et index

- VII. Mais pourquoi Oracle n'utilise-t-il pas mon index ? (le méchant)

- VII-A. La fonction…

- VII-B. La fonction qu'on ne voit pas…

- VII-C. Les stats

- VII-D. Ce n'est pas le meilleur chemin

- VIII. Tables en cache

- IX. Vues matérialisées

- X. Les index ne font pas tout…

- XI. En plus de l'optimisation de requête

- XII. Conclusion

- XIII. Remerciements et liens

I - Objectif de ce document

I-A - Introduction

L'optimisation des bases de données est souvent perçue comme étant une tâche du DBA, cependant sur de gros volumes de données et un grand nombre de bases, le DBA ne peut pas connaître la logique métier de toutes ses bases ni passer réécrire les requêtes de chacun des développeurs.
La conception d'applications performantes passera nécessairement par la connaissance des différents éléments permettant d'avoir des bases répondant aux besoins et évolutives. Il est fréquent qu'une requête mal écrite coute 10 fois plus de temps et de ressources qu'elle ne le devrait.
Lorsque l'application n'est pas seule sur le serveur de base de données, les ralentissements ou la surconsommation de ressources peuvent s'avérer gênants non seulement pour l'application, mais aussi les autres projets/ applications partageant les ressources.
Pour que les développeurs puissent écrire des requêtes efficaces, il leur est nécessaire de connaître quelques-uns des mécanismes mis en œuvre lors de l'exécution de leurs requêtes, ici dans le cas d'Oracle.

I-B - Public ciblé et prérequis

Cet article s'adresse aux développeurs et à toute personne sachant exécuter des ordres SQL simples, des jointures simples, désirant rapidement améliorer les performances de ses requêtes et la conception de sa base. Le tuning de base de données est une chose complexe, cet article se veut une initiation à des opérations simples qui permettent d'améliorer les performances.
Aucune connaissance DBA ou autre n'est requise.

I-C - Mise en garde - Limites

Cet article ne se veut en aucun cas un guide exhaustif du tuning sous Oracle ! Il n'est que la retranscription écrite de connaissances que j'ai pu acquérir. Je ne détaillerai ici que les éléments concernant vraiment les développeurs et omettrai parfois par souci de simplification d'expliquer des notions pointues…

II - Plan d'exécution, optimiseur, cout, chemin d'accès et statistiques

II-A - Chemin d'accès

Un des objectifs des bases de données est de stocker l'information tout en l'organisant de manière à pouvoir y accéder rapidement. De la même manière qu'il y a plusieurs manières de se rendre de Paris à Tokyo, il y a plusieurs manières d'accéder à l'information. Pour accéder à une ligne donnée d'une table, Oracle dispose de plusieurs solutions que nous expliquerons sous peu. Citons parmi celles-ci par exemple le « parcours complet de table » ou « full table scan », le « parcours d'index » ou « Index range scan »… (promis, on va expliquer tout cela après…). Ces différentes voies pour l'accès aux données sont tout simplement appelées « chemin d'accès ».

II-B - Plan d'exécution

Il est souvent nécessaire sur des requêtes d'interroger une ou plusieurs tables qui feront donc l'objet d'un ou plusieurs chemins d'accès. L'ensemble des chemins d'accès utilisés pour une requête a pour nom « Plan d'exécution ».
On peut comparer le plan d'exécution à un itinéraire complet entre deux villes et le chemin d'accès aux indications (vous savez le « tourner à droite »…) qui le composent.

II-C - Trouver le meilleur chemin ? On a un GPS ? (L'optimisteur)

Trouver le chemin le plus intéressant pour accéder aux données, c'est le boulot de l'optimiseur Oracle. Évidemment le terme « chemin le plus intéressant » est assez subjectif (et réglable par le DBA dans le cas d'Oracle), mais on demande le plus souvent à Oracle d'utiliser le chemin qu'il estime avoir le meilleur « cout » (cost-based).
Le cout tient compte de l'utilisation des ressources (CPU, accès disques, mémoire…) pour un plan donné.
L'élément à mon avis le plus important est le nombre d'I/O Disque que nécessite la requête. Lire sur un disque c'est lent. Pire encore, devoir effectuer un tri de données sur le disque s'avère souvent extrêmement couteux…

II-D - Les statistiques

L'optimiseur choisit nous disions… d'accord, mais comment il sait par où il faut passer lui ?
Pour choisir le bon chemin entre deux villes il faut savoir les distances, les risques d'embouteillage… bref, posséder des données sur les trajets… Pour Oracle ces données sur les objets qu'il contient (métadonnées) s'appellent les statistiques. Les statistiques permettent à l'optimiseur d'estimer le cout d'accès aux données. Normalement, elles sont en général calculées automatiquement / périodiquement (Oracle 10G) ou manuellement(10G et avant…).
Pour en savoir plus sur le calcul des stats sur vos bases, demandez à votre DBA. Calculer des stats se fait en lançant une requête « ANALYZE TABLE matable COMPUTE STATISTICS; » par exemple ou mieux en utilisant le package dbms_stats comme ici :

EXEC DBMS_STATS.gather_database_stats; // pour toute la base ... argl !
EXEC DBMS_STATS.gather_database_stats(estimate_percent => 15); // pour la base, avec un échantillon de 15%

EXEC DBMS_STATS.gather_schema_stats('SCOTT'); // collecte pour le schéma SCOTT
EXEC DBMS_STATS.gather_schema_stats('SCOTT', estimate_percent => 15); // idem avec 15% d'échantillon

EXEC DBMS_STATS.gather_table_stats('SCOTT', 'EMPLOYEES'); // pour une table
EXEC DBMS_STATS.gather_table_stats('SCOTT', 'EMPLOYEES', estimate_percent => 15); // idem avec 15% ...

EXEC DBMS_STATS.gather_index_stats('SCOTT', 'EMPLOYEES_PK'); // pour un index
EXEC DBMS_STATS.gather_index_stats('SCOTT', 'EMPLOYEES_PK', estimate_percent => 15);

Le web est plein d'indications sur la chose.

II-E - Les hints : « je suis grand c'est moi qui gère »

Les hints ou suggestions sont les instructions que nous pouvons insérer dans nos ordres SQL pour influencer l'optimiseur. Dans certains cas l'optimiseur peut ne pas prendre le meilleur chemin, du moins à mon goût. On peut alors l'influencer en insérant dans l'ordre SQL un hint se présentant comme ceci : /*+ MONHINT */. Oracle le suivra si c'est possible et l'ignorera sinon.
Personnellement je ne recommande pas l'utilisation des hints tant qu'il n'y a pas de souci. L'inconvénient des hints est le fait qu'ils sont écrits en dur et donc inévitablement figés. Quand la volumétrie ou d'autres éléments changent le hint peut devenir caduc et contre-performant. L'utilisation des hints suppose donc de bonnes connaissances dans le domaine et de Oracle.
Beaucoup des éléments que je décrirai par la suite feront l'objet de hints possibles. Je tâcherai d'indiquer en vert ces hints au fur et à mesure.

II-F - Et concrètement comment je le sais le plan d'exécution pour ma requête ?

Pas mal d'outils (SQL Developer de Oracle [gratuit] et TOAD) ont un petit bouton magique qui permet de voir le plan.
Sous SQLPlus, plusieurs manières s'offrent à vous. Le plus simple à mon goût est le mode autotrace :

sqlplus hr/hr

SQL*Plus: Release 10.2.0.1.0 - Production on Mer. Nov. 19 17:40:22 2008

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

Connecté Ó :
Oracle Database 10g Express Edition Release 10.2.0.1.0 - Production

SQL> select first_name,last_name from employees where last_name like 'T%';

FIRST_NAME           LAST_NAME
-------------------- -------------------------
Jonathon             Taylor
Winston              Taylor
Sigal                Tobias
Peter                Tucker
Oliver               Tuvault

Activons le autotrace :

SQL> set autotrace on;
SQL> select first_name,last_name from employees where last_name like 'T%';

FIRST_NAME           LAST_NAME
-------------------- -------------------------
Jonathon             Taylor
Winston              Taylor
Sigal                Tobias
Peter                Tucker
Oliver               Tuvault

Plan d'exécution
----------------------------------------------------------
Plan hash value: 3085132068

--------------------------------------------------------------------------------
| Id  | Operation        | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT |             |     5 |    75 |     1   (0)| 00:00:01 |
|*  1 |  INDEX RANGE SCAN| EMP_NAME_IX |     5 |    75 |     1   (0)| 00:00:01 |
--------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

1 - access("LAST_NAME" LIKE 'T%')
filter("LAST_NAME" LIKE 'T%')

Statistiques
----------------------------------------------------------
0  recursive calls
0  db block gets
2  consistent gets
0  physical reads
0  redo size
595  bytes sent via SQL*Net to client
384  bytes received via SQL*Net from client
2  SQL*Net roundtrips to/from client
0  sorts (memory)
0  sorts (disk)
5  rows processed

SQL>

Le plan est à lire comme suit : Oracle a utilisé un scan (on y revient plus tard) sur l'index « EMP_NAME_IX » et c'est tout.
Vos pires ennemis sont dans les statistiques… Tous les physical reads seront à réduire et les sorts (disk) ou tris sur le disque également.

Une autre solution est de faire un EXPLAIN PLAN sur une commande :

SQL> explain plan for (select * from employees where last_name like 'T%');
Explicité.
SQL> select plan_table_output from table(dbms_xplan.display());

PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------------

Plan hash value: 2077747057

-------------------------------------------------------------------------------------------
| Id  | Operation                   | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |             |     5 |   340 |     2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| EMPLOYEES   |     5 |   340 |     2   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | EMP_NAME_IX |     5 |       |     1   (0)| 00:00:01 |
-------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):

PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------------

2 - access("LAST_NAME" LIKE 'T%')
filter("LAST_NAME" LIKE 'T%')

15 ligne(s) sélectionnée(s).

SQL> explain plan for (select * from employees where last_name like 'T%');

Explicité.

SQL> select plan_table_output from table(dbms_xplan.display());

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------

Plan hash value: 2077747057

-------------------------------------------------------------------------------------------
| Id  | Operation                   | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |             |     5 |   340 |     2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| EMPLOYEES   |     5 |   340 |     2   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | EMP_NAME_IX |     5 |       |     1   (0)| 00:00:01 |
-------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):

PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------------

2 - access("LAST_NAME" LIKE 'T%')
filter("LAST_NAME" LIKE 'T%')

15 ligne(s) sélectionnée(s).

Les colonnes cost et time sont précieuses… Ici on peut lire dans le plan que Oracle a effectué un scan sur l'index EMP_NAME_IX puis a accédé à la table EMPLOYEES via les ROWID (on décodera tout cela après).

N. B. Si Oracle vous insulte avec un message « SP2-0618: Impossible de trouver l'identificateur de session. Le rôle PLUSTRACE doit être activé. SP2-0611: Erreur lors de l'activation de l'état STATISTICS » lorsque vous voulez activer le autotrace demandez à votre dba de lancer une commande qui vous donnera le droit de le faire: « grant plustrace to votreUser; »

Voilà pour ces quelques définitions, passons donc maintenant à quelques notions sur les index.

III - Les chemins d'accès

Je vais ici passer assez vite sur les différents modes d'accès aux données… on pourrait aller très loin dans la technique, mais cela sortirait de l'objectif que je me suis ici fixé. Les voici :

- parcours complet de table ou Full Table Scan : là c'est simple on parcourt toute la table. En l'absence d'index approprié à la recherche, c'est ce qu'Oracle fait. Ce mode est couteux sur de grosses tables, car il génère beaucoup d'accès disque, étant donné que toute la table est lue ;

- parcours (ou balayage) d'index : un index est parcouru à la recherche des valeurs. Cette lecture donne généralement lieu à des accès par rowid aux lignes concernées dans la table, mais pas toujours, comme on le verra. Je reviendrai sur les différentes méthodes de balayage dans la partie sur les index :

- accès par ROWID ou Table Access by ROWID : le ROWID ou Id de ligne est un identifiant permettant d'accéder le plus rapidement possible à une ligne. Oracle accède aux lignes directement par leur ROWID (identifiant interne) dans la table. C'est très rapide, mais suppose de connaître le ROWID… on est donc passé par une autre étape avant typiquement un INDEX RANGE SCAN.

IV - Les index

IV-A - Définition - Avantages

On peut voir un index comme un glossaire, un sommaire. Avoir un glossaire basé sur le bon critère vous permet d'accéder rapidement à la page du livre traitant du sujet désiré. De même, un index base sur la bonne valeur permettra à Oracle de retrouver rapidement l'ensemble de lignes concernées par la requête.
Exemple : Créer un index sur le champ « date de naissance » d'un individu permettra de retrouver rapidement tous les individus nés à une date donnée. L'index représente souvent le premier niveau d'optimisation d'une requête : positionner un index là où il faut permet assez souvent de diminuer grandement le cout d'accès à une table (voir les chemins d'accès).
Un index peut être basé sur une ou plusieurs valeurs… on peut par exemple indexer au sein du même index la date de naissance et le nom de la personne, on parle alors d'index composé ou composite. Si on effectue souvent la recherche avec date de naissance et nom de la personne cet index sera probablement intéressant.
L'index permet donc à Oracle de trouver les lignes de la table qui correspondent à la requête.

IV-B - Les différents types d'index

Mettre un index c'est bien, connaître les types d'index permet d'aller plus loin. Oracle gère plusieurs types d'index, qui ont chacun leurs spécificités et sont plus avantageux dans tel ou tel cas. Les voici :

create index monIndex on individus(NOM,PRENOM)

CREATE BITMAP INDEX monIndex ON INDIVIDUS(NOM)

- B-tree : les index b-tree sont le type d'index par défaut quand on ne précise rien. Comme son nom l'indique, l'index b-tree est organisé en arbre, les racines constituées par les valeurs précisées à gauche. En clair, si je crée un index avec (NOM,PRENOM) via la commande
cela crée un index en arbre avec les personnes classées par nom puis par prenom…
Attention : les valeurs NULL ne sont pas indexées dans un index, si elles sont seules. Par exemple l'index basé sur le champ (numero) de téléphone ne sera pas utilisable pour un « where telephone is not null ». Par contre un index basé sur deux colonnes dont une ne sera pas nulle sera utilisable. L'index (nom, telephone) sera ainsi utilisable si nom n'est pas nul. En résumé une valeur nulle peut exister dans l'index à condition qu'elle soit associée à une valeur non nulle ;

- Bitmap : un mot binaire est créé, composé d'autant de bits que de possibilités de valeurs de l'index, ce type d'index est particulièrement efficace lorsque le nombre de valeurs est petit ainsi que pour les opérations AND et OR. Là je vais illustrer par un exemple cela vaut mieux…
Prenons en exemple le champ « civilité » de ma table individus. Ce champ contient un nombre très limité de valeurs possibles: 'Monsieur' ; 'Madame' ; 'Mademoiselle' et NULL (vide).
Les valeurs de l'index pourront par exemple être:
000 pour NULL, 001 pour Monsieur, 010 pour Mademoiselle, 100 pour Madame
Lors de la recherche, Oracle effectuera un simple AND sur la valeur de l'index pour comparer. Ce type d'index est très performant lorsque le nombre valeur est petit. Toutefois, les index bitmaps provoquent lors des opérations d'écriture des verrous importants qui font qu'ils sont très adaptés aux bases en lecture, mais deviennent contre-indiqués dès lors que la base devra subir un nombre important d'insert par exemple.
Le verrou se fait sur la valeur dans l'index et on se retrouvera lors d'une mise à jour avec un verrou sur toutes les lignes concernées par cette valeur de l'index et ça peut faire beaucoup…
Ces index sont particulièrement efficaces pour des requêtes avec des clauses WHERE complexes, des opérations count ou sum…
Ils sont également économiques en stockage. À utiliser en connaissant ses limites ;

- les index à clefs inversées : là c'est très simple : « 1234 » deviendra « 4321 »… La raison de cette inversion provient du fait que les index peuvent devenir un goulot d'étranglement quand un grand nombre d'insertions est fait… L'index étant ordonné, quand vous insérez par exemple beaucoup de valeurs venant d'une séquence, c'est le même bloc de l'index qui est alors systématiquement sollicité. En cassant la continuité, on enlève cet inconvénient… ce type d'index a toutefois un inconvénient, on ne peut y effectuer des « range scan » (parcours d'une partie de l'index) de par sa nature discontinue. Concrètement cet index pourra être utilisé lors d'une clause where monchamp=maValeur, mais pas avec une clause where monChamps > maValeur. Seul l'opérateur égalité peut l'utiliser. Cela peut facilement être gênant ;

- les index basés sur des fonctions : là on n'indexe plus un champ, mais le résultat d'une opération sur un champ. Par exemple, on crée un index sur UPPER(monChamp) plutôt que sur monChamp. Pour l'utilité on y vient juste après :) Ce type d'index revient donc à précalculer une valeur.

IV-C - Les index composés :quelques règles

Comme nous l'avons vu, les index composés sont des index sur plusieurs valeurs, par exemple

CREATE INDEX monIndex on invidus(nom,prenom);

Une requête portant une partie des champs indexés ne pourra utiliser au mieux l'index que si ces champs sont placés en tête d'index. Par exemple l'index (NOM, PRENOM) pourra être utilisé le plus efficacement sur une requête dont la clause ne porterait que sur NOM, l'index (NOM,PRENOM,TELEPHONE) sera toutefois utilisable pour une requête ne contenant qu'une clause sur TELEPHONE, mais via une opération moins efficace.
Il sera donc intéressant de placer les colonnes les plus interrogées en début d'index.
Il est également plus efficace d'avoir les valeurs les plus restrictives en début d'index.

IV-D - Les balayages d'index

Nous avons vu dans le chapitre sur les chemins d'accès, les balayages d'index sont une possibilité d'accès. Nous allons les détailler, simplement pour les comprendre.

- Le balayage d'intervalle d'index (index range scan): L'index est parcouru pour trouver les valeurs. Les clauses d'égalité, supériorité ou infériorité déclenchent généralement ces opérations. L'index peut servir à effectuer un tri, car il est déjà trié.

- Le balayage unique (unique index scan): il s'applique lorsque l'index repose sur une colonne possédant une contrainte d'unicité (Unique).

- Le balayage à contresens (descending index scan): L'index est parcouru à contresens. Ceci est généralement fait pour un tri. Oracle se basera sur l'index à contresens pour son tri.

- Le balayage par saut (index skip scan): l'index est parcouru en sautant les zones où les clefs ne pourraient pas se trouver. Par exemple si la première colonne d'un index composé n'est pas mentionnée dans la requête Oracle peut choisir de quand même utiliser l'index et effectuera cette opération.

IV-E - Inconvénients et limites des index

IV-E-a - Une grosse limite : les fonctions

Un index ne peut être utilisé que lorsque la recherche est faite directement sur la valeur indexée elle-même. En plus clair, pour Oracle UPPER(monChamp) et monChamp c'est deux choses complètement différentes !
Repartons dans du plus concret, imaginons que je veuille les gens de ta table individus dont le nom est 'MACHIN'. On sait qu'on va souvent faire une recherche sur ce champ, donc on va l'indexer.

CREATE INDEX INDVIDU_NOM on INDVIDUS(NOM) COMPUTE STATISTICS;

Un bel index donc ! et je quand je fais ma requête sur le nom :

SELECT * from INDVIDUS where nom='MACHIN';

on obtient le plan d'exécution :

TABLE ACCESS BY INDEX ROWID TABLE INDVIDUS Cost: 2  Bytes: 270  Cardinality: 1
1 INDEX RANGE SCAN INDEX INDVIDU_NOM Cost: 1  Cardinality: 1

Et donc on passe désormais par notre index :). Imaginons maintenant qu'on veuille faire une requête qui ne soit pas sensible à la casse. On va donc faire un upper sur notre champ et le comparer à notre paramètre ('MACHIN').

SELECT * from INDVIDUS where UPPER(nom)='MACHIN';

on obtient le plan d'exécution :

TABLE ACCESS FULL TABLE INDVIDUS Cost: 6  Bytes: 540  Cardinality: 2

Et là on dit « argl ». Et oui UPPER(NOM) et NOM ce n'est pas la même chose. Par contre avec

SELECT * from INDVIDUS where nom=UPPER('MACHIN');

on obtient le plan d'exécution :

TABLE ACCESS BY INDEX ROWID TABLE INDVIDUS Cost: 2  Bytes: 270  Cardinality: 1
1 INDEX RANGE SCAN INDEX INDVIDU_NOM Cost: 1  Cardinality: 1

C'est parce que là la fonction est bien appliquée au paramètre et non à notre champ indexé.
Pour notre problème d'index ici présent, deux solutions peuvent marcher :

CREATE INDEX INDVIDU_U_NOM on INDVIDUS(UPPER(NOM));

- Si on peut le faire, imposer que le champ de la base soit uniquement en majuscule (code, trigger…) afin de ne pas avoir à utiliser UPPER et donc arriver à NOM='MACHIN' dans la requête. Mais cela n'est pas toujours possible.

- Créer un index basé sur une fonction et avoir la clause UPPER(nom)='MACHIN'.

Là on constatera que Oracle utilise notre bel index, car on compare bien UPPER(nom) à 'MACHIN'.

Autre souci majeur des index, les NULL. Retenez que quand on peut mettre une valeur par défaut, il est intéressant au niveau performances de le faire. Dès qu'il y aura un IS NULL, IS NOT NULL dans une requête vous courrez au FULL TABLE SCAN.

IV-E-b - Trop d'index tue l'index

« Bon alors l'index ça accélère les selects donc autant en créer plein non ? » Halte-là, mon ami, si l'index ne faisait que tout accélérer pourquoi est-ce qu'on n’en aurait pas créé de tout partout ? Évidemment, si l'index nous faisait seulement gagner de la vitesse ce serait trop beau. Pour qu'il soit utile, un index doit être tenu à jour tout comme le glossaire du livre quand on rajoute des chapitres. Évidemment pour nous c'est transparent, c'est Oracle qui fait le boulot, mais ça a un prix. Une opération d'écriture sur un champ indexé est selon la doc Oracle 3 fois plus lente que sans index. Quand on fait un INSERT sur une table avec 4 index cela coute donc 4*3=12 fois plus cher que sans ces index…
La question « faut-il indexer alors ? » se pose donc et là, il n'y a pas de réponse universelle.
Il s'agira de trouver un compromis entre performances d'écritures et de lecture. Car une sélection trop longue, car non indexée sollicitera la base de manière excessive, ce qui peut aussi nuire aux performances des INSERT et UPDATE… Dans le cas d'une base quasiment en lecture seule, la question se pose moins, les index sont en général très intéressants. Sur une base qui servira autant aux INSERT qu'aux SELECT la question est moins facile à trancher.

IV-E-c - Quand l'index se substitue à la table…

Il arrive que parfois notre interrogation n'aille même pas jusqu'à la table contenant les données. Reprenons le cas où nous avons un index sur INDVIDU.NOM (pas son upper, lui-même). Regardons ce qui se passe là :

explain plan for (SELECT nom from indVidus where nom='MACHIN');
Plan:
1 INDEX RANGE SCAN INDEX OBLADI.I_CANDIDAT_NOM Cost: 1  Bytes: 8  Cardinality: 1

Oracle indique qu'il n'a rien fait d'autre qu'accéder à l'index ! Non, il va bien, c'est juste que comme toute l'information se trouve déjà dans l'index, pourquoi irait-il voir la table ?
Un bon exemple est celui donné en II-F, dans le schéma HR :

SQL> explain plan for (select last_name,first_name from employees where last_name like 'T%');
Explicité.
SQL> select plan_table_output from table(dbms_xplan.display());

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------

Plan hash value: 3085132068

--------------------------------------------------------------------------------
| Id  | Operation        | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT |             |     5 |    75 |     1   (0)| 00:00:01 |
|*  1 |  INDEX RANGE SCAN| EMP_NAME_IX |     5 |    75 |     1   (0)| 00:00:01 |
--------------------------------------------------------------------------------

V - Les tables organisées en index

Les tables organisées en index sont des tables dont la structure est celle d'un index : les données y sont classées en arbre et les valeurs stockées directement dans la structure. Sur une table classique lorsqu'un index est parcouru il génère le plus souvent un accès à la table via les ROWID. Avec les tables organisées en Index, les valeurs sont stockées directement dans la structure.
Ces structures sont souvent bien adaptées aux tables de paramètres qui changent peu. Par exemple une liste des communes…
On peut voir les choses de cette manière: dans une table normale les informations sont enregistrées à la suite les unes des autres quelques soient les valeurs, dans une table organisée en index, ces valeurs sont rangées proprement dans des cases, l'information va là où elle doit aller. Pour une information comme les précipitations météo organisées par villes et qui arrivent tous les jours cela peut être très efficace. Ces tables perdent de leurs performances lorsque trop de suppressions ont lieu. Il peut être alors nécessaire de les reconstruire.

CREATE TABLE MATABLE ( nom    VARCHAR2(30), prenom  VARCHAR2(20) ) ORGANISATION INDEX;

VI - Clefs étrangères et index

Halte aux idées reçues, NON ORACLE N'INDEXE PAS automatiquement les clefs étrangères (référentielles) de la base.
Dans la grande majorité des cas, l'indexation des clefs étrangères peut amener un gain non négligeable ! Vous vous rappelez, je disais que les index ralentissaient les insertions… et bien pas toujours.
Imaginons un cas tout simple de clef étrangère : le champ CA de la table A référence le champ CB de la table B. Nous demandons ici à Oracle de vérifier que la valeur que l'on mettra dans CA existe bien dans CB… et bien comment croyez-vous qu'il va la chercher cette valeur pour savoir si elle existe ? En accédant à la table… Et si le champ est indexé, il y a des chances que ça aille plus vite…
Une des extrémités (le champ référencé) est souvent une clef primaire, mais pas forcément, une contrainte d'unicité suffit, dans ce cas indexer sera judicieux. L'autre extrémité, le référençant est rarement indexé, pensez-y, cela accéléra les jointures et recherches qui pourraient se faire via ce champ.

VII - Mais pourquoi Oracle n'utilise-t-il pas mon index ? (le méchant)

VII-A - La fonction…

Et oui, quand on applique une fonction, l'index ne peut pas être utilisé. Solution : Soit indexer le champ calculé avec la fonction, soit s'arranger pour ne pas avoir à appliquer de fonction (typiquement, forcer les noms de famille en Majuscules par exemple afin d'éviter un upper lors des recherches).

VII-B - La fonction qu'on ne voit pas…

Si le champ MONTANT est un NUMBER, qu'il possède un INDEX BITMAP et qu'on exécute « select * from maTable where montant='1' », on effectue une conversion de type implicite. Oracle fera un TO_CHAR sur le champ, et donc une fonction et l'index bitmap ne sera pas utilisé… En 10G Oracle arrive à éviter ce piège, mais c'est une bonne pratique que de veiller à respecter les types, car on peut vite arriver sur des cas complexes, autant prendre de suite les bonnes habitudes. Solution : Surveillez les types dans les requêtes…

VII-C - Les stats

Si vous avez fait de lourds changements dans une table, n'oubliez pas de relancer l'analyse afin d'avoir des stats à jour. Avec de mauvaises stats cela ne facilite pas la tâche à Oracle pour faire les bons choix… et peut même le conduire à en faire des mauvais…

VII-D - Ce n'est pas le meilleur chemin

Oracle peut considérer au regard de ses statistiques que l'utilisation de l'index sera plus couteuse que de parcourir complètement la table. Il a souvent raison… Si vous voulez le vérifier, rien ne vous empêche de le forcer avec un hint, vous serez fixé…
Quand Oracle estime de par les statistiques et les histogrammes qu'il devra ramener un gros pourcentage de lignes, il préfèrera parfois faire un FULL TABLE SCAN, car finalement un accès par index a un cout : au moins un I/O pour l'index, un I/O pour accéder à la ligne…
Vous feriez pareil devant un livre où vous savez que vous devez lire 90 % du contenu… vous oublierez le sommaire pour allez chercher directement…

VIII - Tables en cache

Vous pouvez demander à Oracle de conserver certaines tables en mémoire cache. Ceci est particulièrement intéressant pour des petites tables de paramètres que vous accédez souvent, lors de jointures par exemple. Attention toutefois à ne pas en abuser, conserver de grosses tables en mémoire aurait des gros inconvénients au niveau occupation mémoire et performances…

IX - Vues matérialisées

Une vue est en fait le fruit d'une requête. Chez Oracle, quand on crée une vue et qu'on l'interroge Oracle ne fait ni plus ni moins qu'une réécriture de requête… Allez hop l'exemple :

CREATE VIEW maVue AS (select * from maTable where monChamp=58);

Quand on effectuera :

select * from maVue where monChamp=45

Oracle lancera implicitement la requête :

select * from (select * from maTable where monChamp=58) where monChamp=45

Oracle fait donc une simple réécriture de requête et exécute donc les requêtes imbriquées…
Les vues matérialisées sont des vues dont on stocke physiquement le résultat : le résultat de la requête est stocké comme pour une table. Mais qui dit résultat stocké dit résultat de la requête à un moment t, et quand la table change la vue matérialisée sera tenue à jour ou non selon comment on l'a paramétrée.
Le problème c'est qu'entretenir ces vues en temps réel est couteux et pas souvent mis en œuvre. Les vues matérialisées trouvent tout leur intérêt dans des cas où l'on n'a pas besoin des données en temps réel. Par exemple l'analyse des chiffres économiques de la veille. Dans ce cas, les données sont en réalité désormais figées et il peut être intéressant de stocker les résultats pour faire des sous-interrogations plus rapidement. Je ne m'étendrai pas ici sur les vues matérialisées : le web possède plein d'informations sur le sujet, mais sachez qu'elles peuvent parfois être très intéressantes dans les problématiques de performances, en particulier dans les datawarehouse.

X - Les index ne font pas tout…

Mettre des index sur une base ne résoudra que partiellement les problèmes de performances… quand on rédige une requête il faut aussi penser à ce qu'on demande à oracle…
Je vais ici donner un exemple frappant que j'ai pu constater… imaginons deux tables TA et TB, avec un nombre important de lignes…

select TA.a,TA.b,TA.c from TA,TB where TA.a=TB.a;

Une bien belle jointure que voilà… mais au fait pourquoi fait-on une jointure ici ? Alors qu'on ne ramène rien de TB ??? Finalement on ne fait que ramener de TA ce qui existe aussi dans TB selon la jointure…

Select TA.a,TA.b,TA.c from TA,TB where exists (select 1 from TB where TA.a=TB.a);

Vous constaterez lors de tables avec de nombreux enregistrements que cela est quand même bien plus rapide…

XI - En plus de l'optimisation de requête

Une base performante se conçoit dès la phase de conception d'un projet. Savoir faire les bons choix et réfléchir aux couts est primordial… Introduire de la redondance pour des raisons peut être payant, mais doit s'étudier scrupuleusement…
Avant de pouvoir mettre le bon index sur une base il est nécessaire de bien connaître le besoin, de bien savoir sur quels champs seront effectuées les recherches… Il est fréquent que les utilisateurs désirent des recherches qui ne soient pas case sensitive ou encore sans tenir compte des accents. Pour cela il convient de réfléchir si on ne préfère pas stocker le nom en majuscules directement dans la base, cela évitera un upper() systématique…

XII - Conclusion

Comme annoncé, ce document ne fait que présenter en surface l'optimisation de requêtes… Il existe beaucoup de paramètres influant sur les performances et il serait ici bien long de tous les citer… J'espère juste que ce document aura pu vous sensibiliser au fait qu'une requête même si on ne la juge pas couteuse au premier abord, peut être intéressante à développer, ne serait-ce qu'en observant son plan d'exécution.
Enfin, une base efficace passe par une architecture efficace. La conception de la base est un élément clef. Enfin, bien dimensionner le serveur, au niveau processeur, RAM est primordial, mais ce travail-là relève du boulot du DBA…

J'espère que cet article vous sera utile et aura permis une première approche du tuning de requêtes.

XIII - Remerciements et liens

Merci à Manu, mon DBA préféré pour sa relecture.
Merci à Olivier de m'avoir autrefois initié à Oracle.
Merci à Arnaud pour ses conseils et sa formation.
Un grand merci aux membres de DVP, 3DArchi pour ses relectures attentives et ses corrections orthographiques, à mnitu, orafrance, fatsora, PlaineR, Scheu pour leurs relectures, corrections, conseils, suggestions et leurs encouragements.

Enfin, je ne peux que vous encourager à regarder les multiples articles qui existent sur développez, de manière non exhaustive et dans aucun ordre précis:
https://Oracle.developpez.com/guide/tuning/tkprof/
https://jaouad.developpez.com/conference-jonathan-lewis/
https://orafrance.developpez.com/dbahelp/#L3