Administration des bases de données

et des systèmes d’information

Oracle

PR. OUSSAMA AZIZ

Plan du cour :

• Session 1: Introduction à Oracle Database

• Session 2: SQL de base
• Session 3: Manipulation de données
• Session 4: Clauses avancées SQL
• Session 5: Index et optimisation des requêtes
• Session 6: Conception de bases de données
• Session 7: Procédures stockées et fonctions
• Session 8: Déclencheurs (Triggers)
• Session 9: Sécurité et gestion des utilisateurs
• Session 10: Sauvegarde et restauration
 Session 5: Index et optimisation des requêtes

Plan de la session :
• Introduction aux Index
• Types d'Index
• Conception des Index
• Création et gestion des index
• Optimisation des requêtes
• Statistiques et Collecte d’informations
Introduction aux Index

Un index dans Oracle est une structure de données qui améliore la

vitesse de récupération des lignes d'une table. Il fonctionne de manière
similaire à l'index d'un livre, permettant un accès rapide à des
informations spécifiques sans avoir à parcourir l'ensemble du contenu.

L'index stocke une copie triée ou hachée d'une partie ou de l'ensemble

des données de la table, facilitant la recherche rapide des données. Il sert
de pointeur vers les enregistrements réels dans la table.
 Avantages de
l’utilisation des Index
• Amélioration des Performances de Recherche : Les index accélèrent la recherche en permettant un
accès direct aux lignes de données, réduisant ainsi le temps nécessaire pour récupérer des
enregistrements.

• Optimisation des Requêtes : Les index facilitent l'optimisation des requêtes en fournissant des
chemins d'accès rapides aux données, réduisant ainsi le coût des opérations de recherche et de
filtrage.

• Support des Contraintes de Clé : Les index sont souvent utilisés pour définir des contraintes de clé
primaire et unique, garantissant l'intégrité des données et empêchant les doublons.

• Facilitation de la Jointure de Tables : Lors de la jointure de tables, les index peuvent améliorer les
performances en accélérant la recherche des lignes correspondantes.
 Inconvénients de
l'Utilisation des Index
• Coût de Stockage Additionnel : Les index occupent de l'espace de stockage supplémentaire, ce qui
peut devenir significatif pour de grandes bases de données.
• Impact sur les Performances d'Insertion, de Mise à Jour et de Suppression : Les opérations de
modification des données (INSERT, UPDATE, DELETE) peuvent être plus lentes en présence d'index,
car les index doivent être mis à jour pour refléter les changements.
• Maintenance Nécessaire : Les index doivent être gérés et mis à jour régulièrement pour maintenir
leur efficacité. Les statistiques sur les index doivent également être collectées.
 Types d'Index

1. Index Unique :
• Garantit l'unicité des valeurs dans la colonne indexée.

• Utilisé pour les clés primaires et les contraintes d'unicité.

• Syntaxe : ‘CREATE UNIQUE INDEX nom_index ON nom_table (colonne); ‘

2. Index Non Unique :

• Autorise des valeurs en double dans la colonne indexée.

• Utilisé pour accélérer les recherches sans garantir l'unicité.

• Syntaxe : ‘CREATE INDEX nom_index ON nom_table (colonne); ‘

 Types d'Index

3. Index Bitmap :
• Utilisé pour les colonnes avec un faible cardinality (un petit nombre de valeurs distinctes).

• Stocke les valeurs distinctes sous forme de bits, ce qui réduit l'espace nécessaire.

• Peut être très efficace pour les requêtes de type recherche par plage sur des colonnes à faible cardinalité.

• Syntaxe : ‘ CREATE BITMAP INDEX nom_index ON nom_table (colonne); ’

4. Index Cluster :
• Combine physiquement des lignes de tables étroitement liées.

• Utile lorsque des requêtes récupèrent fréquemment des données de plusieurs tables liées.

• Syntaxe : ‘CREATE CLUSTER nom_cluster (colonne); ’

 Conception d'Index
1. Sélection des Colonnes à Inclure dans l'Index :
• Identifiez les Colonnes Souvent Utilisées dans les Clauses WHERE : Les colonnes fréquemment utilisées dans
les clauses WHERE des requêtes de recherche sont de bons candidats pour l'indexation. Cela accélère les opérations
de recherche en permettant un accès plus rapide aux données.
• Considérez les Colonnes de Jointure : Si vous effectuez fréquemment des opérations de jointure sur une colonne,
il peut être judicieux de créer un index sur cette colonne. Cela améliore les performances des requêtes qui impliquent
des jointures.
• Pensez aux Colonnes Utilisées dans les Clauses ORDER BY : Si vous avez des requêtes qui trient les résultats
en fonction d'une colonne spécifique, l'indexation de cette colonne peut améliorer les performances des opérations de
tri.
• Évitez d'Indexer Toutes les Colonnes : Indexer chaque colonne peut entraîner une surcharge inutile et augmenter
les coûts de maintenance. Sélectionnez soigneusement les colonnes en fonction des besoins de la base de données.
 Conception d'Index
2. Choix du Type d'Index en Fonction du Cas d'Utilisation
• Index Unique pour les Contraintes d'Unicité : Si vous avez besoin de garantir l'unicité des valeurs dans une colonne, optez
pour un index unique. Cela est couramment utilisé pour les clés primaires.
• Index Non Unique pour les Recherches Fréquentes : Pour les colonnes où des valeurs en double sont autorisées et où des
recherches fréquentes sont effectuées, choisissez un index non unique. Cela peut améliorer les performances des requêtes de
recherche.
• Index Bitmap pour des Colonnes à Faible Cardinalité : Les index bitmap sont efficaces lorsque la cardinalité des valeurs dans
la colonne est faible. Cela signifie qu'il y a un nombre limité de valeurs distinctes.
• Index Clustered pour l'Organisation Physique : Si vous avez besoin de regrouper physiquement les lignes de données
similaires, envisagez un index cluster. Cela peut être bénéfique pour les requêtes qui accèdent fréquemment à des lignes
similaires.
• Index Non-clustered pour la Flexibilité : Pour une flexibilité maximale et une gestion plus simple, choisissez un index non-
clustered. Cela convient à la plupart des cas d'utilisation et permet une manipulation plus flexible des données.
 Création et Gestion des Index
1. Syntaxe de Création d'Index

• UNIQUE : Spécifie que les valeurs de la colonne indexée doivent être uniques.
• nom_index : Nom de l'index.
• nom_table : Nom de la table sur laquelle l'index est créé.
• nom_colonne : Colonnes sur lesquelles l'index est créé.
• ASC|DESC : Facultatif, spécifie l'ordre de tri (ascendant ou descendant).
 Création et Gestion des Index
2. Modification et Suppression d'Index :
• Modification et suppression d'Index :
La modification d'un index existant peut être réalisée en le supprimant d'abord, puis en le recréant
avec les modifications nécessaires.
Exemple de suppression d'un index :
 Création et Gestion des Index
3. Utilisation d'Index Virtuels :
• Index Virtuel (Invisible Index) :Un index virtuel est un index qui n'est pas utilisé par l'optimiseur de
requêtes lors de l'évaluation du plan d'exécution des requêtes.
• Utile pour évaluer l'impact des nouveaux index sans affecter directement l'optimiseur.
• Syntaxe pour créer un index virtuel :

• Pour rendre un index visible à nouveau :

 Optimisation des Requêtes
L'optimisation des requêtes vise à améliorer les performances des requêtes SQL en minimisant le
temps d'exécution et en utilisant efficacement les ressources du système. Voici quelques points clés pour
optimiser les requêtes dans Oracle Database :

1. Compréhension du Plan d'Exécution :

Utilisez la commande EXPLAIN PLAN pour comprendre comment Oracle exécute une requête.
Analysez le plan d'exécution pour identifier les opérations coûteuses et les indices utilisés.
Exemple :
 Optimisation des Requêtes
2. Collecte de Statistiques :
Les statistiques sur les objets de la base de données aident l'optimiseur à prendre des décisions éclairées.
Utilisez la commande ‘ DBMS_STATS ‘ pour collecter des statistiques.
Exemple :
 Optimisation des Requêtes
3. Utilisation des Index :
• Assurez-vous que les colonnes utilisées dans les clauses WHERE, ORDER BY et JOIN sont indexées.
• Évitez les index inutiles, car ils peuvent augmenter les coûts de maintenance.

4. Optimisation des Clauses WHERE :

• Utilisez des opérateurs efficaces (IN, BETWEEN) plutôt que des opérateurs coûteux (LIKE).
• Évitez les fonctions dans les clauses WHERE, car elles peuvent empêcher l'utilisation des index.

5. Gestion des Jointures :

• Utilisez les types de jointures appropriés (INNER JOIN, LEFT JOIN) en fonction des besoins.
• Vérifiez que les colonnes de jointure sont indexées.
 Optimisation des Requêtes
6. Utilisation des Hints :
• Les hints permettent de guider l'optimiseur dans le choix de la meilleure stratégie d'exécution.
• Utilisez-les avec précaution, car ils peuvent rendre la base de données moins flexible.

7. Partitionnement de Tables :
• Le partitionnement peut améliorer les performances des requêtes en répartissant les données sur plusieurs
segments.
• Exemple de création de table partitionnée :
 Optimisation des Requêtes
8. Utilisation de l'Index Inversé (Reverse Key Index) :
L'index inversé peut être utilisé pour réduire les problèmes de contention sur les index générés de
manière séquentielle.
Exemple de création d'un index inversé :

9. Optimisation des Agrégations et des GROUP BY :

Utilisez les index pour accélérer les opérations d'agrégation.
Évitez d'agréger des colonnes inutiles.
10. Utilisation des Vues Materialisées :
Les vues materialisées précalculent les résultats des requêtes, améliorant ainsi les performances des requêtes
fréquemment exécutées.
Exemple de création d'une vue materialisée :
Statistiques et Collecte d'Informations
La collecte de statistiques est une étape essentielle pour permettre à l'optimiseur de requêtes
d'Oracle de prendre des décisions éclairées sur la meilleure façon d'exécuter les requêtes. Voici quelques
concepts et commandes liés à la collecte de statistiques dans Oracle Database :

1. Collecte de Statistiques sur une Table :

La commande DBMS_STATS.gather_table_stats est utilisée pour collecter des statistiques sur une table. Cela
inclut des informations telles que le nombre de lignes, la distribution des valeurs, la densité, etc.

Exemple :
Statistiques et Collecte d'Informations
2. Collecte de Statistiques sur un Schéma :
La commande DBMS_STATS.gather_table_stats est utilisée pour collecter des statistiques sur une table. Cela
inclut des informations telles que le nombre de lignes, la distribution des valeurs, la densité, etc.

Exemple :

3. Collecte de Statistiques sur l'Instance :

La commande DBMS_STATS.gather_database_stats permet de collecter des statistiques au niveau de

l'ensemble de l'instance.

Exemple :
Statistiques et Collecte d'Informations
4. Collecte de Statistiques sur l'Instance :

La commande DBMS_STATS.gather_database_stats permet de collecter des statistiques au niveau de

l'ensemble de l'instance.

Exemple :

5. Automatisation de la Collecte de Statistiques :

Oracle peut être configuré pour collecter automatiquement des statistiques à intervalles réguliers. Cela peut
être activé en utilisant le paramètre AUTO_SAMPLE_SIZE dans DBMS_STATS.SET_PARAM.

Exemple :
Statistiques et Collecte d'Informations
6. Visualisation des Statistiques Collectées :

Pour visualiser les statistiques collectées pour une table, vous pouvez interroger la vue USER_TABLES ou
ALL_TABLES.

Exemple :
TP 5 : Index et optimisation des
requêtes
Ressources Supplémentaires pour
en Savoir Plus sur Oracle Database
Documentation Officielle Technologies d'Oracle Database

Guide de l'Administrateur Oracle Database Cloud