Cours Optimisation Et Qualité BDD PDF

COURS OPTIMISATION ET QUALITÉ BDD PLAN Voici un plan pour maîtriser le contenu de ce fichier en moins de 3 heures, étant donné que vous êtes déjà familière avec le cours et les concepts : 1. Organisation et Priorisation (10 minutes)...

COURS OPTIMISATION ET QUALITÉ BDD PLAN Voici un plan pour maîtriser le contenu de ce fichier en moins de 3 heures, étant donné que vous êtes déjà familière avec le cours et les concepts : 1. Organisation et Priorisation (10 minutes) Objectif : Identifier les sections critiques pour votre apprentissage rapide. Passez en revue rapidement le sommaire et les titres des sections pour évaluer les parties essentielles, telles que : Optimisation du schéma interne et logique. Indexation et partitionnement. Vues matérialisées. Méthodes d’accès aux données. Clustering et tables externes. Notez les sections moins importantes pour une lecture ultérieure. 2. Lecture Active des Concepts Clés (90 minutes) 1. Première Partie : Organisation des Données et Concepts Structurants (20 minutes) Concentrez-vous sur la différence entre schémas physique et logique. Apprenez les rôles des composants clés : tablespaces, segments, extents, blocs, et index. 2. Deuxième Partie : Optimisation et Indexation (30 minutes) Maîtrisez les types d’index : dense, non-dense, primaire, secondaire, bitmap, composite. COURS OPTIMISATION ET QUALITÉ BDD 1  Étudiez les meilleures pratiques pour créer et maintenir les index. Comprenez l'impact de la sélectivité sur les performances. 3. Troisième Partie : Partitionnement (20 minutes) Analysez les types de partitionnement (par liste, plage, intervalle, hachage, combiné). Notez les cas d’utilisation et les avantages spécifiques à chaque type. 4. Quatrième Partie : Vues Matérialisées et Clustering (20 minutes) Étudiez la création et la gestion des vues matérialisées. Apprenez comment les clusters améliorent les performances pour des requêtes fréquentes. 3. Révision et Consolidation (40 minutes) 1. Synthèse des Points Essentiels (20 minutes) Élaborez un résumé de chaque section clé en listant les concepts, commandes SQL importantes, et cas pratiques. Identifiez les concepts que vous trouvez encore flous. 2. Exercices de Réflexion (20 minutes) Imaginez des scénarios où vous appliqueriez les concepts (par exemple : "Comment optimiser une table volumineuse avec des accès fréquents par date ?"). Faites une simulation mentale ou écrite des commandes SQL importantes. 4. Questions et Validation (20 minutes) Répondez à des questions ciblées sur le cours, ou posez-les à vous- même. Relisez les parties cruciales qui semblent encore complexes. Ce plan vous permettra d'optimiser votre temps et de solidifier vos connaissances sur les sections importantes. Si vous souhaitez un focus COURS OPTIMISATION ET QUALITÉ BDD 2  spécifique sur un sujet, indiquez-le ! Schéma de Base de Données 1. Introduction au Schéma de Base de Données Définition : Un schéma de base de données est une description formelle qui définit : Comment les données sont organisées. Les relations entre ces données. Les contraintes à respecter. En d'autres termes, c'est le plan ou la structure logique de la base de données. 2. Composants du Schéma de Base de Données 2.1 Organisation des Données Les bases de données sont organisées en différents niveaux : a) Niveau physique : Correspond au stockage réel des données sur le disque. Les données sont enregistrées dans des datafiles, qui sont les fichiers physiques contenant les informations. Ces fichiers sont divisés en plus petites unités appelées data blocks. b) Niveau logique : C’est ce que les utilisateurs et les administrateurs perçoivent. Ce niveau regroupe les objets comme : Tables (structure logique des données). Index (pour optimiser les recherches). Tablespaces (zones logiques regroupant les datafiles). COURS OPTIMISATION ET QUALITÉ BDD 3  2.2 Principaux Composants Logiques Composant Description Exemple Zone logique regroupant les Tablespace_DATA regroupant deux objets (tables, index, etc.) et Tablespace fichiers datafile1.dbf et connectée aux fichiers datafile2.dbf. physiques. Fichiers physiques sur le datafile1.dbf (500 Mo), Datafiles disque contenant les données datafile2.dbf (1 Go). de la base. Structure logique organisée en Table Clients avec colonnes : Table colonnes et lignes. Nom , Adresse. Zone logique réservée pour un Segment dédié aux données de la Segment objet, comme une table ou un table Clients. index. Si un segment nécessite plus Groupe contigu de blocs de d’espace, un nouvel extent est Extent données alloué à un segment. ajouté (exemple : 10 blocs supplémentaires). Plus petite unité de stockage Data Block dans un datafile. Chaque bloc Taille typique : 8 Ko. contient les données réelles. Index sur Nom dans la table Structure utilisée pour Index Clients pour les recherches accélérer l’accès aux données. rapides. 3. Fonctionnement d'un Schéma 3.1 Organisation Physique et Logique 1. Stockage Physique : Les datafiles contiennent les données divisées en data blocks. Ces fichiers sont organisés en tablespaces, regroupant les objets logiques. 2. Organisation Logique : COURS OPTIMISATION ET QUALITÉ BDD 4  Les tables stockent les données sous forme de lignes et colonnes. Chaque table est associée à un segment, composé de plusieurs extents. 3. Requête et accès : Lorsqu’une requête est exécutée, le SGBDR (par exemple Oracle ou MySQL) utilise les index pour trouver rapidement les données nécessaires. Les données sont ensuite récupérées à partir des blocs dans les datafiles. 3.2 Exemple Pratique Scénario : Une entreprise souhaite gérer des informations sur ses clients et leurs commandes. 1. Création de la table Clients : CREATE TABLE Clients ( ID_Client NUMBER PRIMARY KEY, Nom VARCHAR2(50), Adresse VARCHAR2(100), Téléphone VARCHAR2(15) ); 2. Association de la table à un Tablespace : La table Clients est associée au tablespace Tablespace_DATA. Ce tablespace est lié à deux fichiers physiques : datafile1.dbf (500 Mo). datafile2.dbf (1 Go). 3. Stockage des données : COURS OPTIMISATION ET QUALITÉ BDD 5  Les données insérées dans Clients sont stockées dans des blocs libres du tablespace. Par exemple, la ligne suivante est insérée dans un bloc disponible : INSERT INTO Clients (ID_Client, Nom, Adresse, Télépho ne) VALUES (1, 'Jean Dupont', 'Paris', '0123456789'); 4. Concepts Structurants 4.1 Schéma Physique vs Schéma Logique Aspect Schéma Physique Schéma Logique Définit l’organisation des Définit le stockage des données Définition données et leurs relations sur le disque (datafiles, blocs). logiques. Datafiles, data blocks, extents, Tables, index, vues, clés Composants segments. primaires/étrangères. Invisible pour les utilisateurs Perception Visible et manipulable via SQL. finaux. 4.2 Rôle des Composants 1. Data Blocks : Contiennent les données réelles insérées dans les tables. 2. Extents : Ajoutés lorsque l’espace dans un segment devient insuffisant. 3. Segments : Zones logiques associées à un objet comme une table ou un index. 4. Tablespaces : Regroupent les segments et permettent une gestion logique des fichiers physiques. Conception du Schéma Interne 1. Définition COURS OPTIMISATION ET QUALITÉ BDD 6  La conception du schéma interne se concentre sur la façon dont les données sont physiquement stockées et organisées dans la base de données pour garantir des performances optimales. Contrairement au schéma logique, qui définit les relations entre les objets (tables, index), le schéma interne vise à optimiser les opérations d'accès, de lecture et d'écriture des données sur le stockage physique. 2. Objectifs de la Conception du Schéma Interne 1. Optimisation de l'accès aux données : Réduire le temps nécessaire pour lire ou modifier des données. 2. Réduction des coûts d'entrées/sorties (I/O) : Minimiser le nombre d'opérations sur disque pour améliorer les performances. 3. Gestion efficace des ressources : Utiliser efficacement le stockage physique, la mémoire cache, et le processeur. 4. Anticipation de la croissance des données : Prévoir les besoins futurs pour éviter les interruptions ou ralentissements. 3. Facteurs à Prendre en Compte 3.1 Fréquence des Requêtes Analyse des requêtes courantes : Déterminer les tables ou colonnes les plus souvent consultées ou modifiées. Prioriser les optimisations pour les requêtes fréquemment exécutées. Exemple : Si une table Clients est fréquemment interrogée pour les informations de contact, un index sur la colonne Nom peut accélérer ces requêtes. 3.2 Types de Requêtes Requêtes de sélection ( SELECT ) : COURS OPTIMISATION ET QUALITÉ BDD 7  Optimiser les requêtes en lecture avec des index appropriés et des structures adaptées (ex. : B-Tree, Bitmap). Requêtes d'écriture ( INSERT , UPDATE , DELETE ): Minimiser les index sur des tables avec des écritures fréquentes pour réduire la surcharge liée à leur mise à jour. Exemple : Dans une base OLTP, une table de journalisation ( Logs ) contenant des millions d'enregistrements doit être optimisée pour des insertions rapides. On peut éviter les index inutiles pour maximiser les performances. 3.3 Critères de Répétition Détection des modèles de requêtes répétées : Si certaines requêtes sont exécutées très fréquemment, les données correspondantes doivent être conservées dans la mémoire cache. Exemple : Une application de gestion de stock exécute souvent une requête pour afficher les produits avec un stock faible : SELECT * FROM Produits WHERE Stock < 10; Une vue matérialisée ou un index fonctionnel peut être créé pour optimiser cette requête. 3.4 Traitement dans le Cache Les bases de données modernes utilisent des mécanismes de cache pour stocker temporairement les données fréquemment consultées en mémoire vive (RAM). Optimisation : Ajuster la taille des caches comme le Buffer Cache (SGA dans Oracle) pour inclure les blocs les plus utilisés. COURS OPTIMISATION ET QUALITÉ BDD 8  Éviter les requêtes entraînant un balayage complet des tables si elles ne tiennent pas en cache. Exemple : Si une table Produits contient une petite quantité de données fréquemment consultées, configurez les caches pour que ses blocs restent en mémoire vive. 3.5 Coût des Entrées/Sorties (I/O) Les opérations I/O sont les plus coûteuses en termes de performances dans une base de données, car elles nécessitent des lectures ou écritures sur disque. Optimisation : Réduire les accès disque en utilisant des index et des techniques de partitionnement. Préférer les accès séquentiels (lecture de blocs contigus) aux accès aléatoires (lecture de blocs dispersés). Exemple : Une table Ventes avec des millions de lignes peut être partitionnée par date pour limiter la quantité de données à lire lors de l'exécution d'une requête : SELECT * FROM Ventes WHERE Date_Vente BETWEEN '2023-01-0 1' AND '2023-12-31'; 4. Cas Pratique Contexte : Une université souhaite gérer efficacement les informations sur ses étudiants et leurs résultats aux examens. 1. Tables impliquées : Étudiants : Stocke les informations personnelles. COURS OPTIMISATION ET QUALITÉ BDD 9  Résultats : Enregistre les notes des étudiants pour chaque matière. 2. Analyse des Requêtes : Les requêtes les plus fréquentes sont : Trouver les détails d’un étudiant par son ID. Afficher les résultats pour un étudiant spécifique. Les écritures concernent l’ajout de nouveaux étudiants ou de nouvelles notes. 3. Conception Optimisée : Index sur Étudiants.ID_Étudiant : Accélère la recherche d’un étudiant par son ID. Partitionnement de la table Résultats par année académique : Réduit les données à parcourir pour les analyses par année. 4. Configuration des Caches : Les données des étudiants étant fréquemment utilisées, les blocs correspondants sont priorisés pour rester en mémoire. 5. Outils et Techniques d’Optimisation 1. Indexation : Utilisez des index B-Tree pour des recherches rapides. Créez des index bitmap pour des colonnes avec peu de valeurs distinctes (ex. : sexe, région). 2. Partitionnement : Divisez une table en partitions logiques basées sur des colonnes comme les dates ou les catégories. Exemple : CREATE TABLE Résultats ( ID_Étudiant NUMBER, COURS OPTIMISATION ET QUALITÉ BDD 10  Année NUMBER, Note NUMBER ) PARTITION BY RANGE (Année) ( PARTITION P2023 VALUES LESS THAN (2024), PARTITION P2024 VALUES LESS THAN (2025) ); 3. Vues Matérialisées : Utilisez des vues matérialisées pour des agrégations ou des données calculées fréquemment. Exemple : CREATE MATERIALIZED VIEW Ventes_Totales AS SELECT Région, SUM(Montant) AS Total FROM Ventes GROUP BY Région; 4. Planification de l’Extension des Données : Configurez les datafiles pour s’étendre automatiquement avec une taille de croissance prédéfinie : ALTER DATABASE DATAFILE 'datafile1.dbf' AUTOEXTEND ON NEXT 50M MAXSIZE 2G; 6. Résumé La conception du schéma interne est essentielle pour garantir les performances et la scalabilité des bases de données. Voici les principaux points à retenir : Facteurs Techniques Associées Fréquence des Analysez les requêtes courantes pour optimiser les accès. Requêtes COURS OPTIMISATION ET QUALITÉ BDD 11  Types de Adaptez l’indexation et la structure en fonction des besoins. Requêtes Traitement dans Configurez les caches pour minimiser les lectures répétées. le Cache Coût I/O Réduisez les accès disque avec des partitions et des index. Croissance des Planifiez les extensions de tablespaces pour éviter les interruptions. Données Avec une conception bien pensée, il est possible d’atteindre des performances optimales même avec des volumes de données importants. Types d'Optimisation de Schéma Les concepts de normalisation, dénormalisation, et d'autres pratiques comme les index, vues matérialisées, ou partitionnement, font tous partie des différentes approches d'optimisation des schémas de bases de données. Ces optimisations peuvent être regroupées en deux grandes catégories principales : 1. Optimisation Logique (Modèle Conceptuel et Logique) A. Normalisation Objectif : Diviser les données en plusieurs tables pour : Éviter les redondances. Assurer l’intégrité des données. Simplifier la maintenance (modifications locales plutôt que globales). Exemple : Une table Employés non normalisée contenant des informations sur les départements peut être divisée en deux tables pour réduire la duplication et permettre un changement centralisé du nom des départements. B. Dénormalisation Objectif : Fusionner certaines tables ou conserver des redondances de données pour : COURS OPTIMISATION ET QUALITÉ BDD 12  Réduire les jointures complexes. Accélérer les requêtes analytiques (OLAP). Exemple : Une table dénormalisée dans un système OLAP pourrait regrouper des données sur les clients, les commandes, et les montants dans une seule table pour simplifier les requêtes d'agrégation. C. Modèles en Étoile ou en Flocon Objectif : Organiser les données pour des analyses rapides dans des environnements décisionnels (OLAP). Exemple : Une table fact centralise les mesures comme le montant des ventes, et les tables de dimensions décrivent les attributs contextuels comme les produits, les clients, ou les régions. 2. Optimisation Physique (Modèle Interne et Stockage) A. Index Objectif : Réduire le temps de recherche dans une table en créant des structures spécifiques pour accéder rapidement à certaines colonnes. Exemple : Un index B-Tree sur une colonne ID_Client permet une recherche rapide des clients par leur ID. B. Partitionnement Objectif : Diviser une grande table ou un index en plusieurs partitions logiques pour faciliter les accès parallèles et limiter la quantité de données à parcourir. Exemple : Une table COURS OPTIMISATION ET QUALITÉ BDD 13  Ventes peut être partitionnée par années ( 2023 , 2024 , etc.) pour optimiser les requêtes limitées à une plage de dates. C. Vues Matérialisées Objectif : Pré-calculer et stocker les résultats de requêtes complexes ou agrégées pour améliorer les performances. Exemple : Une vue matérialisée sur les ventes totales par région permet de répondre rapidement à une requête comme : SELECT Région, Total_Ventes FROM Vues_Matérialisées; D. Organisation des Tablespaces et des Datafiles Objectif : Configurer le stockage physique pour éviter les problèmes de contention et assurer une bonne répartition des données. Exemple : Un tablespace contenant des index peut être alloué à un disque rapide distinct des tables pour éviter que les écritures sur table n'affectent les performances des index. E. Gestion du Cache et des Entrées/Sorties (I/O) Objectif : Minimiser les accès disque en optimisant l’utilisation du cache mémoire. Exemple : Une petite table fréquemment consultée est priorisée pour rester en mémoire dans le Buffer Cache. Comparaison : Optimisation Logique vs Physique Aspect Optimisation Logique Optimisation Physique Conception du modèle logique Gestion du stockage physique et Niveau (tables, relations, contraintes). des mécanismes d'accès. COURS OPTIMISATION ET QUALITÉ BDD 14  Simplifier les relations entre Améliorer les performances de Objectif données et réduire les lecture/écriture et optimiser les redondances. ressources. Normalisation, Index, partitionnement, vues Exemple de dénormalisation, modèles en matérialisées, gestion des Méthode étoile. tablespaces. Synthèse : Quand Appliquer Quelle Optimisation ? 1. Normalisation : Utilisée dans les bases OLTP (transactionnelles) où les écritures sont fréquentes et où il est crucial d’éviter les redondances pour maintenir l’intégrité des données. 2. Dénormalisation : Utilisée dans les bases OLAP (analytique) pour accélérer les requêtes de lecture en limitant les jointures complexes. 3. Index et Partitionnement : Utilisés pour améliorer les performances des bases transactionnelles ou analytiques en fonction des types de requêtes et des volumes de données. 4. Vues Matérialisées : Idéales dans les systèmes OLAP où des requêtes complexes ou agrégées sont fréquemment exécutées. En conclusion, la normalisation et la dénormalisation appartiennent à l'optimisation logique, tandis que les index, vues matérialisées, et partitionnement relèvent principalement de l'optimisation physique. Chaque approche doit être choisie en fonction des besoins spécifiques de l'application et de l'environnement (OLTP ou OLAP). Méthodes d’Accès aux Données Lorsqu'on parle de méthodes d'accès dans une base de données, il s'agit des différentes façons dont le (SGBD) peut localiser et récupérer les enregistrements demandés. Cela inclut des techniques pour accéder COURS OPTIMISATION ET QUALITÉ BDD 15  directement à des enregistrements via des identifiants uniques, ou à travers des structures plus complexes comme les index. 1. Accès via le ROWID Le ROWID est un identifiant interne unique pour chaque enregistrement dans une table. Il permet un accès direct et rapide aux enregistrements sans avoir besoin de parcourir toute la table ou un index. ROWID est un pointeur de 10 octets qui représente l’emplacement physique exact de l’enregistrement dans la base de données. Ce pointeur se compose de plusieurs parties : Data Object Number : Identifie l'objet de la base de données (table ou index) auquel appartient l'enregistrement. Relative File Number : Numéro du fichier physique où est stocké l'enregistrement. Block Number : Le numéro du bloc (unité de stockage) contenant l'enregistrement. Row Number : Le numéro de la ligne au sein de ce bloc. Exemple d'utilisation de ROWID SELECT * FROM Étudiants WHERE ROWID = 'AAABBBCCC'; Dans cette requête, le SGBD accède directement à la ligne spécifique identifiée par ROWID , ce qui est extrêmement rapide car il n'a pas besoin de parcourir toute la table ou d’utiliser un index. 2. Accès via un index Un index est une structure de données utilisée pour accélérer la recherche dans une table. Il permet de localiser rapidement les enregistrements sans avoir à scanner chaque ligne de la table. Accès via index dense : Un index dense contient une entrée pour chaque valeur de clé distincte dans la table. Cela signifie qu’il contient des COURS OPTIMISATION ET QUALITÉ BDD 16  pointeurs vers chaque ligne de la table, ce qui permet une recherche rapide par clé unique ou un ensemble restreint de valeurs. Exemple : Si on a une table Étudiants (CNE, NOM, AGE), un index dense sur la colonne CNE permet d'accéder directement à un étudiant avec un CNE donné : L’index permet de trouver directement la ligne sans parcourir la table entière. SELECT * FROM Étudiants WHERE CNE = '123456'; Accès via index non-dense : Un index non-dense contient une entrée pour certaines valeurs de clé, pas toutes. Il est souvent utilisé pour les colonnes avec des valeurs répétées, comme une colonne Zone_Géographique. Un index non-dense pointe vers le premier enregistrement correspondant à une valeur, et les autres enregistrements peuvent être retrouvés séquentiellement. Opérations sur un index dense : Recherche par clé : On peut effectuer une recherche rapide si l'on connaît la clé exacte (ex. CNE dans une base d’étudiants). Recherche par intervalles : Les index permettent aussi de rechercher des plages de valeurs (ex. recherche d'étudiants dans une certaine tranche d'âge). 3. Accès séquentiel L’accès séquentiel consiste à lire chaque enregistrement d’une table un par un, sans utiliser d’index. C'est la méthode la plus simple mais aussi la plus lente, surtout si la table contient un grand nombre d'enregistrements. Résumé des méthodes d'accès Méthode Description Avantages Inconvénients d'accès COURS OPTIMISATION ET QUALITÉ BDD 17  Accès direct à un Nécessite de enregistrement à partir Très rapide, accès connaître le ROWID, ROWID de son identifiant direct. peu flexible pour physique. d'autres recherches. Contient une entrée Rapide pour les Peut nécessiter pour chaque recherches par clé beaucoup d'espace Index dense enregistrement de la exacte ou plages si la table est table. de valeurs. volumineuse. Contient des entrées pour certaines valeurs Peut être plus lent Moins d'espace Index non- seulement, souvent pour accéder à des requis qu'un index dense utilisé pour des enregistrements dense. colonnes avec des individuels. répétitions. Très lent si la table Parcours ligne par ligne Accès Simple à mettre en est grande, surtout de la table sans utiliser séquentiel œuvre. pour des recherches d’index. complexes. Ces différentes méthodes d’accès permettent au SGBD d’optimiser les performances en fonction de la taille de la table, de la nature des données, et des requêtes les plus fréquemment exécutées. Les index 1. Introduction aux Index Les index sont des structures de données spéciales utilisées pour accélérer l'accès aux données dans une table. Ils permettent de retrouver rapidement les lignes correspondant à une requête sans parcourir l'intégralité de la table. Pourquoi utiliser des index ? Amélioration des performances : Accélèrent les requêtes SELECT en réduisant le nombre d'opérations de lecture nécessaires. Optimisation des recherches : Permettent de localiser rapidement les lignes pertinentes. COURS OPTIMISATION ET QUALITÉ BDD 18  Facilité de tri et de filtrage : Améliorent les performances pour des clauses ORDER BY , GROUP BY , et WHERE. 2. Types d'Index 2.0 Index B-Tree : L'Index par Défaut Définition : Un index B-Tree (Binary Tree) est une structure arborescente équilibrée utilisée pour accélérer les recherches, les insertions, et les suppressions dans une table. Il est largement adopté par défaut dans les bases de données relationnelles comme Oracle, MySQL, et PostgreSQL. Caractéristiques Principales : Organisation équilibrée : L'index reste toujours équilibré, garantissant une recherche rapide même pour des tables volumineuses. Ordre naturel : Les données dans un index B-Tree sont stockées de manière triée, ce qui le rend idéal pour des requêtes de type ORDER BY ou BETWEEN. Support de plusieurs types de requêtes : Recherche exacte ( = ). Recherche par plage ( BETWEEN , >= , < ). Tri ( ORDER BY ). Structure d'un Index B-Tree : Un index B-Tree est divisé en trois niveaux : 1. Bloc Racine (Root Block) : Contient des pointeurs vers des blocs branches. 2. Blocs Branches (Branch Blocks) : Agissent comme des intermédiaires pour affiner la recherche. COURS OPTIMISATION ET QUALITÉ BDD 19  3. Blocs Feuilles (Leaf Blocks) : Contiennent les clés indexées et les RowIDs, qui permettent d'accéder directement aux enregistrements dans la table. Exemple : Table Employés : ID_Employé Nom Département 1001 Dupont RH 1002 Martin IT 1015 Durand RH Création d'un index B-Tree sur ID_Employé : CREATE INDEX idx_employe_id ON Employés(ID_Employé); Requête : SELECT * FROM Employés WHERE ID_Employé = 1015; Processus : 1. Le système commence par le bloc racine. 2. Il suit les pointeurs vers les blocs branches. 3. Dans le bloc feuille, il localise la clé 1015. 4. Le RowID permet d’accéder directement à l’enregistrement correspondant. 2.1 Index Dense Définition : Un index dense contient une entrée pour chaque ligne de la table, y compris pour des valeurs de clé répétées. Cela signifie qu'il indexe chaque enregistrement sans exception. Avantages : COURS OPTIMISATION ET QUALITÉ BDD 20  Permet un accès direct et rapide à chaque ligne d'une table. Utile pour les colonnes uniques ou à haute sélectivité (par exemple, une clé primaire ou une colonne avec des ID uniques). Inconvénients : Consomme plus d'espace disque. Chaque opération de mise à jour ou d'insertion dans la table doit également mettre à jour l'index, ce qui peut ralentir ces opérations. Exemple : Supposons une table Employés avec les colonnes suivantes : ID_Employé Nom Département 1 Dupont RH 2 Martin IT 3 Leclerc Finance Création d'un index dense sur ID_Employé : CREATE UNIQUE INDEX idx_employe_id ON Employés(ID_Employé); Chaque ligne de la table a une entrée dans l'index dense, ce qui permet une recherche rapide : SELECT * FROM Employés WHERE ID_Employé = 2; 2.2 Index Non-Dense Définition : Un index non-dense ne contient pas d'entrée pour chaque ligne. Il indexe des blocs ou des groupes de lignes plutôt que des lignes individuelles. Avantages : COURS OPTIMISATION ET QUALITÉ BDD 21  Réduit l'espace disque utilisé. Diminue la surcharge des opérations d'insertion, de mise à jour, ou de suppression. Inconvénients : Moins précis pour des recherches exactes. Peut nécessiter un balayage partiel de blocs pour retrouver toutes les lignes pertinentes. Exemple : Supposons une table Ventes avec les colonnes suivantes : ID_Vente Zone_Géographique Montant 1 Nord 100 2 Sud 200 3 Nord 300 Création d'un index non-dense sur Zone_Géographique : CREATE INDEX idx_zone_geographique ON Ventes(Zone_Géographique); Cet index regroupe les ventes par zone géographique. Une requête comme celle-ci sera optimisée : SELECT * FROM Ventes WHERE Zone_Géographique = 'Nord'; 3. Index Primaire et Index Secondaire 3.1 Index Primaire Définition : Créé automatiquement sur la clé primaire d'une table. COURS OPTIMISATION ET QUALITÉ BDD 22  Toujours unique et souvent dense. Généralement utilisé pour des recherches rapides sur des colonnes uniques. Exemple : CREATE TABLE Étudiants ( ID_Étudiant NUMBER PRIMARY KEY, Nom VARCHAR2(50), Date_Naissance DATE ); L'index primaire est créé automatiquement sur la colonne ID_Étudiant. 3.2 Index Secondaire Définition : Créé manuellement sur une ou plusieurs colonnes pour optimiser des recherches ou tris spécifiques. Peut être dense ou non-dense. Exemple : CREATE INDEX idx_nom_etudiant ON Étudiants(Nom); Requête optimisée par l'index secondaire : SELECT * FROM Étudiants WHERE Nom = 'Dupont'; 4. Index Concatené ou Composite Définition : COURS OPTIMISATION ET QUALITÉ BDD 23  Un index composite est basé sur plusieurs colonnes combinées. Il est utile pour des recherches ou tris impliquant plusieurs colonnes. Exemple : Supposons une table Employés : ID_Employé Nom Prénom 1 Dupont Jean 2 Dupont Marie 3 Martin Paul Création d’un index composite sur Nom et Prénom : CREATE INDEX idx_nom_prenom ON Employés(Nom, Prénom); Optimise des requêtes comme : SELECT * FROM Employés WHERE Nom = 'Dupont' AND Prénom = 'J ean'; 5. Types Avancés d'Index 5.1 Index sur Fonctions Définition : Un index sur une fonction est utile lorsque des colonnes sont souvent transformées dans des requêtes ( UPPER , LOWER , etc.). Exemple : Création d’un index insensible à la casse : CREATE INDEX idx_upper_nom COURS OPTIMISATION ET QUALITÉ BDD 24  ON Employés(UPPER(Nom)); Requête optimisée : SELECT * FROM Employés WHERE UPPER(Nom) = 'DUPONT'; 5.2 Index Bitmap Définition : Un index bitmap est conçu pour des colonnes avec peu de valeurs distinctes (par exemple, Sexe ). Exemple : Création d’un index bitmap : CREATE BITMAP INDEX idx_sexe ON Employés(Sexe); Optimise des requêtes comme : SELECT * FROM Employés WHERE Sexe = 'Homme'; 6. Création d’un Index Syntaxe Générale : CREATE [UNIQUE] INDEX nom_index ON nom_table(colonne1, colonne2,...); Options Avancées : UNIQUE : Empêche les valeurs dupliquées. PCTFREE : Réserve de l’espace libre pour des mises à jour futures. COURS OPTIMISATION ET QUALITÉ BDD 25  TABLESPACE : Spécifie l’espace de stockage. 7. Meilleures Pratiques pour les Index 1. Indexer uniquement les colonnes nécessaires : Priorisez les colonnes utilisées dans des clauses WHERE , ORDER BY , ou GROUP BY. 2. Évitez de sur-indexer : Trop d'index peuvent ralentir les opérations d'insertion, de mise à jour, et de suppression. 3. Maintenez les statistiques des index : Exécutez régulièrement : EXEC DBMS_STATS.GATHER_TABLE_STATS('SCHEMA', 'TABL E'); 4. Utilisez des index concaténés pour des recherches combinées. 5. Évaluez la sélectivité des colonnes : Les colonnes avec peu de valeurs distinctes sont souvent mieux adaptées aux index bitmap. 8. Résumé Comparatif Type d'Index Dense/Non-dense Usage Principal Index Dense Dense Recherches précises (clé primaire) Index Non-Dense Non-dense Colonnes avec valeurs répétées Index Primaire Dense Accès rapide via la clé primaire Dense ou Non- Index Secondaire Colonnes fréquemment recherchées dense Recherches combinées sur plusieurs Index Composite Dense colonnes COURS OPTIMISATION ET QUALITÉ BDD 26  Index sur Dense Colonnes transformées par des fonctions Fonctions Index Bitmap Non-dense Colonnes avec peu de valeurs distinctes Avec cette structure, vous disposez d'une documentation intuitive, complète, et prête à l'emploi pour comprendre et implémenter des index efficacement. Limite de 16 Index dans Oracle Explication : Oracle impose une limite de 16 index par table afin de : 1. Éviter la surcharge de maintenance : Chaque mise à jour d’une table entraîne la mise à jour de tous les index associés, ce qui augmente le coût des écritures. 2. Améliorer les performances de requêtes : Trop d'index ralentissent l'optimisation des requêtes, car le moteur doit choisir l'index optimal parmi ceux disponibles. 3. Préserver les ressources système : Chaque index consomme de l'espace disque et des ressources mémoire. 4. Encourager les bonnes pratiques de conception : Limiter le nombre d'index incite à une conception réfléchie où seuls les index essentiels sont créés et maintenus. La Sélectivité Le Concept de Sélectivité dans les Bases de Données La sélectivité est un concept clé utilisé pour mesurer l'efficacité des index dans une base de données. Elle décrit dans quelle mesure un attribut d'une table permet de distinguer les différentes lignes de cette table. Plus la sélectivité est élevée, plus un index sur cet attribut est efficace pour filtrer les résultats de requêtes. Définition La sélectivité d'un attribut (ou d'une colonne) est définie comme le rapport entre le nombre de valeurs distinctes non nulles dans cette colonne et le COURS OPTIMISATION ET QUALITÉ BDD 27  nombre total de lignes dans la table. S = (nbr de val - val nulles)/Nbr Total Une sélectivité faible signifie qu'une valeur de cet attribut est partagée par un grand nombre d'enregistrements (donc peu discriminant), tandis qu'une sélectivité élevée signifie qu'une valeur de cet attribut correspond à peu d'enregistrements (donc plus discriminant). Calcul de la Sélectivité Prenons l'exemple d'une colonne d'une base de données avec : 1 million de lignes, 500 000 valeurs distinctes non nulles, 100 000 valeurs nulles. La sélectivité est calculée comme suit : 500000 [S = = 0.5] 1000000 ​ Cela signifie que, statistiquement, chaque valeur distincte se retrouve dans environ deux lignes (1 / 0.5 = 2). Facteurs influençant la Sélectivité 1. Cardinalité : La cardinalité est le nombre total de valeurs distinctes d'un attribut dans une table. Elle affecte directement la sélectivité. Exemple : Si une colonne CNE (Code National de l’Étudiant) contient des valeurs uniques pour chaque étudiant, la cardinalité est égale au nombre total d'étudiants. Cela en fait une colonne hautement sélective. 2. Répartition des données : La répartition des données dans la colonne influe sur la sélectivité. Si les données sont uniformément réparties (chaque valeur apparaît à peu COURS OPTIMISATION ET QUALITÉ BDD 28  près le même nombre de fois), la sélectivité est meilleure que si certaines valeurs sont beaucoup plus fréquentes que d'autres. 3. Facteur de divisionnement (ou clustering factor) : Le clustering factor mesure dans quelle mesure les lignes de la table sont ordonnées selon les valeurs de l'attribut sur lequel l'index est construit. Un faible clustering factor indique que les lignes sont bien ordonnées, ce qui rend l'index plus efficace. Un facteur de clustering élevé peut rendre l’index moins efficace, car il y a beaucoup de sauts entre les blocs de données pour atteindre les lignes correspondantes. 4. Valeurs nulles : Les valeurs nulles ne contribuent pas à la sélectivité car elles ne sont pas prises en compte dans le nombre de valeurs distinctes. 5. Système d'indexation : Les statistiques des index (comme celles disponibles dans pour Oracle) permettent de connaître des sys.user_ind_statistics informations comme la cardinalité, le clustering factor, et d’autres paramètres qui aident le moteur de la base de données à optimiser l’utilisation des index. Exemples de Sélectivité 1. CNE (Code National de l’Étudiant) : Si chaque étudiant a un CNE unique, la sélectivité est très élevée. S = 1 / nombre total d’étudiants (par exemple, S = 1 / 1 000 000 = 0.000001), ce qui signifie qu’un CNE identifie exactement une ligne, sans confusion. 2. Université : Si la colonne Université contient des valeurs représentant seulement quelques universités (par exemple, 10 universités pour 1 million d'étudiants), la sélectivité est très faible. S = nombre d’universités / nombre total d’étudiants (S = 10 / 1 000 000 = 0.00001). Cela signifie qu'une université est partagée par COURS OPTIMISATION ET QUALITÉ BDD 29  plusieurs étudiants, donc l'index ne sera pas très utile pour distinguer des lignes individuelles. Interprétation de la Sélectivité Sélectivité élevée : Plus la sélectivité d'un attribut est élevée (proche de 1), plus cet attribut permet de distinguer les lignes. Il est donc idéal pour les index. Par exemple, un attribut comme CNE est très sélectif, car chaque étudiant a un code unique. Sélectivité faible : Une sélectivité faible (proche de 0) indique que beaucoup de lignes partagent la même valeur. Par exemple, un attribut comme Université ou Sexe aura une sélectivité faible car de nombreux étudiants peuvent appartenir à la même université ou avoir le même sexe. Impact sur la performance des requêtes 1. Index sur une colonne à haute sélectivité : L’utilisation d’un index sur une colonne à haute sélectivité permet au SGBD d’accéder rapidement à un petit nombre de lignes. Par exemple, un index sur le CNE (Code National d'Étudiant) est efficace pour retrouver rapidement un étudiant spécifique. 2. Index sur une colonne à faible sélectivité : Un index sur une colonne à faible sélectivité est moins utile. Par exemple, un index sur Sexe ne serait pas très performant puisque chaque valeur ( Homme ou Femme ) correspond à une grande partie des enregistrements. Exemple pratique Imaginons une table Étudiants avec les colonnes CNE , Nom , Université et Sexe. Voici comment la sélectivité varie : CNE : Très sélectif, car chaque CNE est unique. Nom : Moyennement sélectif, car plusieurs étudiants peuvent avoir le même nom. Université : Faible sélectivité, car plusieurs milliers d’étudiants peuvent être inscrits dans la même université. COURS OPTIMISATION ET QUALITÉ BDD 30  Sexe : Très faible sélectivité, car il y a seulement deux valeurs ( Homme ou Femme ). En résumé, la sélectivité est un facteur clé qui détermine l’efficacité d’un index. Lors de l'optimisation de base de données, il est important de choisir des colonnes à haute sélectivité pour la création d'index, afin de maximiser la performance des requêtes. La granularité La granularité des données fait référence au niveau de détail ou à la précision avec laquelle les données sont stockées et gérées dans une base de données ou un système d'information. C'est une manière de décrire la taille des unités de données en fonction de la finesse ou de la précision à laquelle elles peuvent être traitées ou analysées. Les Tables Organisées en Index (IOT) Introduction : Qu'est-ce qu'une Table Organisée en Index (IOT) ? Une table organisée en index (Index-Organized Table ou IOT) est un type spécial de table dans une base de données relationnelle où les données sont stockées physiquement dans un index B-Tree au lieu d'être organisées en blocs non ordonnés (comme dans les tables classiques, dites "heap"). Différence avec une table classique (heap table) : Dans une table classique, les données sont stockées sans ordre particulier, et les index sont créés séparément pour optimiser les recherches. Dans une table organisée en index, les données sont directement intégrées dans un index B-Tree, triées selon la clé primaire. Caractéristiques des Tables Organisées en Index 1. Stockage des Données : Les données sont stockées dans un index B-Tree, triées en fonction de la clé primaire. COURS OPTIMISATION ET QUALITÉ BDD 31  Les lignes de la table sont physiquement ordonnées dans le B-Tree. 2. Absence de RowID traditionnel : Contrairement aux tables classiques, les lignes dans une IOT n'ont pas de RowID physique. À la place, le SGBD utilise la clé primaire pour localiser les données. 3. Optimisation pour les Requêtes sur Clé Primaire : Les recherches et tris basés sur la clé primaire sont extrêmement rapides, car les données sont directement accessibles dans l'ordre. 4. Gain d'Espace : Les données de la table et l'index sur la clé primaire sont combinés, ce qui réduit l'espace de stockage. Avantages des IOT 1. Performances : Les requêtes basées sur la clé primaire sont optimisées car les données sont triées et directement accessibles. Moins d'E/S disque, car l'accès se fait directement dans l'index. 2. Gain d’Espace : Pas de duplication entre l'index et la table, contrairement aux tables classiques. 3. Efficacité des Parcours Séquentiels : Les parcours séquentiels basés sur l’ordre des clés primaires sont rapides, car les données sont physiquement triées. 4. Utilisation pour les Requêtes OLTP : Idéal pour des charges de travail transactionnelles où les recherches sur la clé primaire sont fréquentes. Inconvénients des IOT 1. Complexité des Mises à Jour : COURS OPTIMISATION ET QUALITÉ BDD 32  Les insertions et mises à jour peuvent être coûteuses, car elles nécessitent le rééquilibrage de l'index B-Tree. Une modification de la clé primaire implique le déplacement physique des données. 2. Index Secondaires Moins Performants : Les index secondaires sur une IOT nécessitent plus d'espace et de calculs, car ils doivent inclure la clé primaire pour localiser les données. 3. Limitation sur les Requêtes Non-Basées sur la Clé Primaire : Les requêtes qui ne s'appuient pas sur la clé primaire peuvent être moins performantes. Cas d'Utilisation des IOT 1. Applications OLTP : Idéal pour les systèmes transactionnels où les recherches et les tris sont principalement basés sur la clé primaire (par exemple, les systèmes bancaires ou les systèmes de gestion des commandes). 2. Chargements avec Recherche Fréquente : Les applications où les données sont souvent consultées et rarement mises à jour. 3. Bases avec Données Clé-Relation : Bases où les relations entre les données sont fortement basées sur des clés primaires et secondaires. Création d’une Table Organisée en Index Syntaxe : CREATE TABLE table_name ( column1 datatype, column2 datatype, column3 datatype, COURS OPTIMISATION ET QUALITÉ BDD 33  PRIMARY KEY (column1) ) ORGANIZATION INDEX; Exemple : Supposons une table Employés avec les colonnes suivantes : ID_Employé (clé primaire) Nom Poste Pour créer cette table comme une IOT : CREATE TABLE Employés ( ID_Employé NUMBER, Nom VARCHAR2(50), Poste VARCHAR2(50), PRIMARY KEY (ID_Employé) ) ORGANIZATION INDEX; Accès aux Données dans une IOT 1. Requête sur Clé Primaire : Les recherches basées sur la clé primaire sont extrêmement rapides, car les données sont stockées dans un index B-Tree ordonné. SELECT * FROM Employés WHERE ID_Employé = 101; 2. Requête avec Index Secondaire : Si une requête utilise un index secondaire (par exemple, sur la colonne Nom ), le SGBD doit d'abord consulter l'index secondaire, puis utiliser la clé primaire pour trouver les données dans l'IOT. CREATE INDEX idx_nom ON Employés(Nom); SELECT * FROM Employés WHERE Nom = 'Dupont'; COURS OPTIMISATION ET QUALITÉ BDD 34  3. Requêtes Non-Optimales : Les requêtes qui ne s'appuient pas sur la clé primaire ou les index secondaires peuvent être moins performantes. Différence avec une Table Classique (Heap Table) Table Classique (Heap Critères IOT (Index-Organized Table) Table) Stockage des Dans un index B-Tree, trié selon En tas (heap), sans ordre Données la clé primaire. particulier. Utilisation de la clé primaire Chaque ligne a un RowID RowID pour accéder aux données. physique. Moins d’espace (pas de Les données et l'index sont Espace de Stockage duplication entre table et stockés séparément. index). Performances des Très rapides pour les Nécessite un index pour des Recherches recherches sur clé primaire. recherches rapides. Performances des Moins efficaces (rééquilibrage Plus rapides, car les Mises à Jour de l'index nécessaire). données ne sont pas triées. Index Secondaires sur une IOT Problème des Index Secondaires : Dans une table organisée en index, les index secondaires doivent inclure la clé primaire pour localiser les données. Cela augmente leur taille et ralentit leur utilisation. Solution : Table de Mapping Oracle propose une option pour inclure une table de mapping avec les IOT. Cette table permet de stocker des informations additionnelles nécessaires pour les index secondaires. Création d'une Table avec Table de Mapping : COURS OPTIMISATION ET QUALITÉ BDD 35  CREATE TABLE Employés ( ID_Employé NUMBER, Nom VARCHAR2(50), Poste VARCHAR2(50), PRIMARY KEY (ID_Employé) ) ORGANIZATION INDEX INCLUDING (Nom, Poste) OVERFLOW TABLESPACE ts1; Meilleures Pratiques pour les IOT 1. Choisir les IOT pour les Tables avec Clés Primaires Fréquemment Consultées : Les IOT sont idéales pour des tables où les requêtes utilisent principalement la clé primaire. 2. Limiter les Index Secondaires : Évitez d’ajouter trop d’index secondaires pour minimiser leur impact sur les performances. 3. Planifier les Modifications : Les insertions et mises à jour massives peuvent être coûteuses. Prévoir des plages de maintenance ou optimiser les charges. 4. Analyser les Requêtes : Avant d’opter pour une IOT, examinez les requêtes principales pour déterminer si elles tirent parti de la clé primaire. Conclusion Les tables organisées en index (IOT) sont une solution puissante pour optimiser les performances des bases de données transactionnelles où les recherches basées sur des clés primaires sont fréquentes. Bien qu’elles offrent des avantages significatifs en termes de performances et de gestion de l’espace, leur utilisation doit être réfléchie, notamment en tenant compte des inconvénients liés aux mises à jour et aux index secondaires. COURS OPTIMISATION ET QUALITÉ BDD 36  Partitionnement 1. Introduction au Partitionnement Le partitionnement consiste à diviser une table volumineuse en segments plus petits appelés partitions, chacun pouvant être géré et accédé indépendamment. Chaque partition est physiquement stockée dans un tablespace différent ou le même. Objectifs principaux : Réduction des temps d'accès pour les requêtes filtrées. Optimisation des performances des opérations (INSERT, UPDATE, DELETE). Simplification de la gestion des grandes tables (archivage, sauvegarde). Amélioration des performances grâce au traitement parallèle. 2. Types de Partitionnement 2.1 Partitionnement par Liste Divise les données selon des valeurs spécifiques dans une colonne. Exemple : Partitionner une table de clients par pays. Quand l'utiliser : Lorsque les données se regroupent naturellement en catégories limitées, comme les pays, régions, ou statuts. CREATE TABLE pclients ( NOCLIENT NUMBER NOT NULL, NOM VARCHAR2(50) NOT NULL, PRENOM VARCHAR2(50), PAYS VARCHAR2(15) ) PARTITION BY LIST (PAYS) ( PARTITION PAUST VALUES ('Australia') TABLESPACE TS1, PARTITION PAMER VALUES ('Chile', 'Brazil', 'USA', 'Cana da') TABLESPACE TS2, COURS OPTIMISATION ET QUALITÉ BDD 37  PARTITION PAFRI VALUES ('South_Africa', 'Cameroun') TAB LESPACE TS3, PARTITION POTH VALUES (DEFAULT) TABLESPACE TS3 ); CREATE TABLESPACE TS1 DATAFILE 'C:\Oracle\oradata\ORCL\TS1.DBF' SIZE 100M AUTOEXTEND ON NEXT 32M MAXSIZE 2024M EXTENT MANAGEMENT LOCAL; 2.2 Partitionnement par Plage Les partitions sont définies selon des plages de valeurs continues sur une colonne. Exemple : Partitionner une table de commandes par date. Quand l'utiliser : Pour les données ordonnées chronologiquement ou par tranches logiques comme dates de commandes ou montants. CREATE TABLE pcmd ( NOCMD NUMBER NOT NULL, DATECOMMANDE DATE, ETATCOMMANDE CHAR(1) ) PARTITION BY RANGE (DATECOMMANDE) ( PARTITION Part1 VALUES LESS THAN (TO_DATE('2005-12-31', 'YYYY-MM-DD')), PARTITION Part2 VALUES LESS THAN (TO_DATE('2007-12-31', 'YYYY-MM-DD')), PARTITION Part3 VALUES LESS THAN (MAXVALUE) TABLESPACE COURS OPTIMISATION ET QUALITÉ BDD 38  TS3 ); 2.3 Partitionnement par Intervalle Extension du partitionnement par plage, créant automatiquement des partitions pour les valeurs au-delà des plages existantes. Quand l'utiliser : Lorsque de nouvelles données arrivent régulièrement et que les plages ne sont pas pré-définies, comme des données mensuelles ou annuelles. Exemple : Partitionner des commandes par mois. CREATE TABLE comm_ri ( NO_COMMANDE NUMBER(6), DATE_COMMANDE DATE ) PARTITION BY RANGE (DATE_COMMANDE) INTERVAL (NUMTOYMINTERVAL(1, 'MONTH')) STORE IN (ts1, ts2, ts3) ( PARTITION p_initial VALUES LESS THAN (TO_DATE('2023-01- 01', 'DD-MM-YYYY')) ); 2.4 Partitionnement par Hachage Les partitions sont définies dynamiquement à l'aide d'un hachage pour une répartition uniforme. Quand l'utiliser : Quand vous avez besoin d'un équilibre parfait entre partitions pour des données non ordonnées, comme des ID clients ou des produits. Exemple : Partitionner les produits par ID produit. CREATE TABLE hash_products ( product_id NUMBER(6), product_name VARCHAR2(50) COURS OPTIMISATION ET QUALITÉ BDD 39  ) PARTITION BY HASH (product_id) PARTITIONS 4 STORE IN (tbs_1, tbs_2, tbs_3, tbs_4); 2.5 Partitionnement Combiné Combine plusieurs méthodes (exemple : range + list). Quand l'utiliser : Pour des structures complexes nécessitant une organisation fine, comme les commandes triées par date et sous- catégorisées par statut. Exemple : Partitionner des commandes par date et état. CREATE TABLE pccmd ( NOCMD NUMBER NOT NULL, DATECOMMANDE DATE, ETATCOMMANDE CHAR(1) ) PARTITION BY RANGE (DATECOMMANDE) SUBPARTITION BY LIST (ETATCOMMANDE) ( PARTITION Part1 VALUES LESS THAN (TO_DATE('2005-12-31', 'YYYY-MM-DD')) ( SUBPARTITION Etat_C VALUES ('C') TABLESPACE TS1, SUBPARTITION Etat_Other VALUES (DEFAULT) TABLESPACE TS1 ), PARTITION Part2 VALUES LESS THAN (TO_DATE('2007-12-31', 'YYYY-MM-DD')) ( SUBPARTITION Etat_C VALUES ('C') TABLESPACE TS2, SUBPARTITION Etat_Other VALUES (DEFAULT) TABLESPACE TS2 ) ); COURS OPTIMISATION ET QUALITÉ BDD 40  2.6 Résumé Visuel Type Quand l'utiliser ? Exemple Catégories distinctes avec un Par Liste Données par pays ou région. nombre limité de valeurs. Données par trimestre ou Par Plage Plages continues et bien définies. année. Nouvelles données périodiques et Données mensuelles ajoutées Par Intervalle non prévisibles. dynamiquement. Répartition uniforme des données Répartition d'IDs clients ou de Par Hachage non ordonnées. produits. Scénarios complexes nécessitant Commandes triées par année et Combiné plusieurs dimensions. statut. 3. Analyse des Plans d'Exécution Comparaison Table Partitionnée vs Non Partitionnée : Scénario Non Partitionnée Partitionnée Requête filtrée ( WHERE TABLE ACCESS PARTITION LIST SINGLE PAYS='Australia' ) FULL TABLE ACCESS Requête sans filtre ( SELECT * ) FULL PARTITION LIST ALL Requête par date ( WHERE DATE TABLE ACCESS PARTITION RANGE BETWEEN ) FULL SINGLE 4. Commandes de Maintenance des Partitions 1. Ajouter une partition : ALTER TABLE pclients ADD PARTITION p_new VALUES ('NewCou ntry') TABLESPACE TS4; 2. Supprimer une partition : COURS OPTIMISATION ET QUALITÉ BDD 41  ALTER TABLE pclients DROP PARTITION p_old; 3. Fusionner des partitions : ALTER TABLE pclients MERGE PARTITIONS p1, p2 INTO p_merg ed; 4. Diviser une partition : ALTER TABLE pclients SPLIT PARTITION p_existing AT ('Cam eroun') INTO (PARTITION p_new1, PARTITION p_new2); 5. Différences Observées Exemple 1 : Requête filtrée par pays Table CLIENTS (non partitionnée) : TABLE ACCESS FULL coûteux. Table PCLIENTS (partitionnée) : PARTITION LIST SINGLE , accès limité à une seule partition. Exemple 2 : Requête filtrée par date Table CMD (non partitionnée) : Scanne toute la table. Table PCMD (partitionnée) : PARTITION RANGE SINGLE , accès restreint à une partition. 6. Résumé des Avantages Requêtes plus rapides grâce à l'accès limité aux partitions pertinentes. Réduction des ressources nécessaires pour les grands ensembles de données. Maintenance simplifiée (archivage, suppression). Avec ces nouvelles informations, cette documentation est complète et prête à l'usage ! COURS OPTIMISATION ET QUALITÉ BDD 42  Vue Matérialisé 1. Introduction aux Vues Matérialisées Une vue matérialisée est une structure de données qui stocke les résultats d'une requête SQL sous forme physique, contrairement aux vues classiques qui ne sont que des requêtes dynamiques exécutées à chaque appel. Concept clé : Les vues matérialisées sont des copies physiques des données et permettent de réduire les temps d'exécution des requêtes complexes en évitant leur recalcul fréquent. Elles sont particulièrement utiles pour les analyses complexes, les rapports, et les bases de données décisionnelles. 2. Différences entre Vues Simples et Matérialisées Caractéristique Vue Classique Vue Matérialisée Pas de stockage des Stockage Résultats stockés physiquement résultats, dynamique Performance Recalcul à chaque appel Recalcul évité, accès rapide Nécessite une synchronisation Mises à jour Toujours à jour manuelle ou automatique Requêtes dynamiques, cas Utilisation Rapports, analyses complexes simples 3. Utilité des Vues Matérialisées 1. Améliorer les performances : Réduction du temps d'exécution des requêtes répétées, complexes ou gourmandes. Particulièrement utile pour les agrégations ou les jointures lourdes. 2. Stockage des résultats intermédiaires : Permet de simplifier les requêtes en utilisant une structure pré- calculée. COURS OPTIMISATION ET QUALITÉ BDD 43  3. Optimisation en bases décisionnelles : Les bases décisionnelles impliquent des requêtes fréquentes sur de grandes tables pour des analyses ou des tableaux de bord. Les vues matérialisées permettent de réduire drastiquement les temps d'attente. 4. Création d'une Vue Matérialisée La commande de base pour créer une vue matérialisée est la suivante : CREATE MATERIALIZED VIEW nom_vue BUILD [IMMEDIATE | DEFERRED] REFRESH [FAST | COMPLETE | FORCE] ON [DEMAND | COMMIT] AS requête_sql; Explications : BUILD IMMEDIATE | DEFERRED : IMMEDIATE : Les données sont chargées immédiatement à la création. DEFERRED : La vue est créée mais les données sont chargées plus tard. REFRESH FAST | COMPLETE | FORCE : FAST : Rafraîchit uniquement les données modifiées, si des logs d'activité (materialized view logs) existent. COMPLETE : Recalcule complètement la vue. FORCE : Utilise FAST si possible, sinon bascule sur COMPLETE. ON DEMAND | COMMIT : DEMAND : La vue est rafraîchie manuellement via une commande. COMMIT : La vue est rafraîchie automatiquement à chaque validation de transaction. 5. Exemples Pratiques COURS OPTIMISATION ET QUALITÉ BDD 44  Exemple 1 : Vue Matérialisée pour Agrégation Calculer les ventes totales par région : CREATE MATERIALIZED VIEW ventes_par_region BUILD IMMEDIATE REFRESH FAST ON DEMAND AS SELECT region, SUM(montant) AS total_ventes FROM ventes GROUP BY region; Avantages : Les agrégations sont pré-calculées. Permet des requêtes rapides comme : SELECT * FROM ventes_par_region WHERE region = 'Europ e'; Exemple 2 : Vue Matérialisée avec Jointures Créer une vue pour accéder rapidement aux commandes et aux détails des clients : CREATE MATERIALIZED VIEW commandes_clients BUILD IMMEDIATE REFRESH COMPLETE ON COMMIT AS SELECT c.nom, c.prenom, cmd.date_commande, cmd.montant FROM clients c JOIN commandes cmd ON c.client_id = cmd.client_id; Avantages : Simplifie les requêtes complexes qui impliquent plusieurs jointures. COURS OPTIMISATION ET QUALITÉ BDD 45  La vue peut être mise à jour automatiquement à chaque insertion dans clients ou commandes. Exemple 3 : Vue Matérialisée Partitionnée Créer une vue partitionnée pour optimiser les données par année : CREATE MATERIALIZED VIEW ventes_par_annee PARTITION BY RANGE (annee) REFRESH FAST ON DEMAND AS SELECT EXTRACT(YEAR FROM date_vente) AS annee, SUM(montant) AS total FROM ventes GROUP BY EXTRACT(YEAR FROM date_vente); 6. Gestion des Vues Matérialisées 6.1 Rafraîchir une Vue Manuellement EXEC DBMS_MVIEW.REFRESH('nom_vue'); 6.2 Créer un Log pour le Rafraîchissement RAPIDE Pour utiliser l'option REFRESH FAST , un journal des modifications doit être créé sur les tables sources. CREATE MATERIALIZED VIEW LOG ON ventes WITH ROWID, PRIMARY KEY, SEQUENCE (region, montant); 7. Avantages des Vues Matérialisées Temps de réponse rapide : Les requêtes complexes deviennent quasi instantanées. COURS OPTIMISATION ET QUALITÉ BDD 46  Optimisation des ressources : Moins de calculs répétitifs pour des requêtes similaires. Support des analyses volumineuses : Idéal pour les bases de données décisionnelles. 8. Limitations Synchronisation nécessaire : Les données peuvent devenir obsolètes si la vue n'est pas rafraîchie régulièrement. Consommation de stockage : Les vues matérialisées occupent de l'espace disque car elles stockent physiquement les données. Rafraîchissement coûteux : Les rafraîchissements complets peuvent être lourds pour des vues volumineuses. 9. Scénarios d'utilisation 1. Rapports périodiques : Les vues matérialisées permettent de pré-calculer des rapports mensuels ou hebdomadaires. 2. Tableaux de bord : Les données affichées sur les tableaux de bord peuvent être alimentées par des vues matérialisées. 3. Analyses historiques : Les agrégations sur de grandes périodes sont simplifiées avec des vues matérialisées. 10. Bonnes Pratiques 1. Utiliser des logs pour le rafraîchissement FAST : Réduire les coûts de mise à jour en n'appliquant que les modifications. 2. Planifier les rafraîchissements : Automatiser les rafraîchissements à l'aide de tâches planifiées ( DBMS_SCHEDULER ). 3. Optimiser les requêtes sources : S'assurer que la requête utilisée pour créer la vue est elle-même performante. Résumé COURS OPTIMISATION ET QUALITÉ BDD 47  Les vues matérialisées sont un outil puissant pour optimiser les performances des bases de données, en particulier dans des contextes analytiques ou décisionnels. Bien utilisées, elles permettent d'améliorer la rapidité des analyses, mais nécessitent une bonne gestion des rafraîchissements pour garantir la fraîcheur des données. Besoin d'un exemple spécifique ou d'une explication sur une partie ? Je suis là pour clarifier ! Clustering dans les Bases de Données Le clustering est une technique d'optimisation dans laquelle les données liées entre elles sont physiquement stockées de manière contiguë sur le disque. Cela améliore l'accès aux données souvent consultées ensemble. Rôle du Clustering : 1. Optimisation des requêtes fréquentes : Réduit le nombre d'accès disque pour les requêtes qui nécessitent plusieurs lignes liées. Exemple : Dans une base e-commerce, regrouper les commandes d'un même client. 2. Performances accrues pour les jointures : Si deux tables jointes fréquemment sont regroupées physiquement, la jointure sera plus rapide. 3. Réduction de la fragmentation : Les données regroupées permettent d'économiser les ressources lors des scans séquentiels. Exemple de Clustering : Créer une table clusterisée basée sur une clé commune entre deux tables. 1. Créer le cluster : CREATE CLUSTER client_cluster (num_client NUMBER(10)); COURS OPTIMISATION ET QUALITÉ BDD 48  2. Associer les tables au cluster : CREATE TABLE Client ( num_client NUMBER(10), nom VARCHAR2(50) ) CLUSTER client_cluster (num_client); CREATE TABLE Compte_Bancaire ( num_client NUMBER(10), num_compte NUMBER(10), solde NUMBER ) CLUSTER client_cluster (num_client); Avantage : Les clients et leurs comptes bancaires seront physiquement stockés ensemble, accélérant les recherches et les jointures. Tables Externes dans les Bases de Données Les tables externes sont des structures permettant d'accéder à des données stockées en dehors de la base de données, comme des fichiers sur le système de fichiers (CSV, texte, etc.). Rôle des Tables Externes : 1. Accès direct aux données externes : Les tables externes permettent de lire les données sans les importer physiquement dans la base. Exemple : Lecture d'un fichier CSV sans le charger dans une table SQL. 2. Réduction de l'espace disque : Puisque les données ne sont pas stockées dans la base, elles n'occupent pas de place supplémentaire. 3. Optimisation des flux de données : COURS OPTIMISATION ET QUALITÉ BDD 49  Utile pour intégrer des pipelines de données, où les données sont transformées ou utilisées directement depuis des fichiers externes. 4. Facilité d'intégration : Les tables externes permettent de combiner des données internes et externes dans une seule requête. Exemple de Table Externe : Créer une table externe pour lire un fichier CSV. 1. Créer un répertoire externe : CREATE DIRECTORY ext_dir AS '/chemin/vers/fichiers'; 2. Créer une table externe : CREATE TABLE ventes_externes ( id_vente NUMBER, montant NUMBER, date_vente DATE ) ORGANIZATION EXTERNAL ( TYPE ORACLE_LOADER DEFAULT DIRECTORY ext_dir ACCESS PARAMETERS ( RECORDS DELIMITED BY NEWLINE FIELDS TERMINATED BY ',' (id_vente, montant, date_vente DATE 'YYYY-MM-D D') ) LOCATION ('ventes.csv') ) REJECT LIMIT UNLIMITED; Technique Rôle dans l'optimisation COURS OPTIMISATION ET QUALITÉ BDD 50  Regroupe physiquement des données liées pour accélérer les Clustering recherches et les jointures. Tables Permettent d’accéder à des données externes sans les importer dans Externes la base, économisant de l’espace et des ressources. CC 1 Voici mes réponses détaillées au CC1 "Optimisation BD" basé sur l'image fournie. Exercice 1 1. Plan d'exécution des requêtes après ajout de la contrainte d'unicité sur CIN Si une contrainte d'unicité est définie sur la colonne cin (par exemple, via une clé primaire ou un index unique), cela signifie qu'Oracle a automatiquement créé un index unique sur cette colonne. R1: select * from Personne where cin='be200051' or cin='be20 0106'; Avec la contrainte d'unicité, Oracle utilisera un Index Unique sur la colonne CIN. Le plan d'exécution inclura : Un Index Unique Scan pour localiser directement les enregistrements correspondant aux deux valeurs. R2: select * from Personne where cin > 'A125400'; Cette requête implique une recherche par plage. Avec un index sur CIN , Oracle utilisera : COURS OPTIMISATION ET QUALITÉ BDD 51  Un Index Range Scan pour récupérer les valeurs supérieures à 'A125400'. R3: select * from Personne where cin is null; Les index classiques ne stockent pas de valeurs NULL. Oracle réalisera : Un Full Table Scan pour rechercher les lignes où CIN est NULL. R4 : SELECT * FROM Personne WHERE UPPER(cin) = 'A125400'; Avec l’introduction de la fonction UPPER on ne pourra pas exploiter l’index unique crée par défaut et du coup, Oracle devra effectuer un Full Table Scan Afin d’optimiser on pourra créer un index fonctionnel CREATE INDEX idx_upper_cin ON Personne(UPPER(cin)); 2. Changements du plan d'exécution avec une clé primaire sur CIN Ajouter une clé primaire sur CIN entraîne la création implicite d'un index unique sur cette colonne. Les plans d'exécution restent les mêmes que ceux décrits ci-dessus : R1 : Index Unique Scan R2 : Index Range Scan R3 : Full Table Scan (car les index ne prennent pas en charge les valeurs NULL ). 3. Ajouter un index B+ sur la colonne NOM COURS OPTIMISATION ET QUALITÉ BDD 52  Index B+ : C'est une structure d'index équilibrée permettant un accès rapide aux données via des recherches par égalité ou par plage. Les nœuds feuilles contiennent des pointeurs vers les enregistrements correspondants dans la table. Commande SQL pour créer l'index : CREATE INDEX IND_NOM ON Personne (NOM); 4. Statistiques de l’index IND_NOM DEL_LF_ROWS=40 : Nombre de lignes supprimées (obsolètes) dans les feuilles de l'index. LF_ROWS=100 : Nombre total de lignes stockées dans les feuilles de l'index. Impact : Le ratio de fragmentation = (DEL_LF_ROWS / LF_ROWS) * 100 = (40 / 100) * 100 = 40%. Un taux de 40% indique une fragmentation importante, ce qui peut ralentir les performances. Une reconstruction de l'index est recommandée : ALTER INDEX IND_NOM REBUILD; 5. Optimisation pour la recherche sur la colonne Sexe La colonne Sexe contient peu de valeurs distin