TP6 : Optimisation des requetes et du modele physique de données 1. Pour chaque table du restaurant afficher le numéro de table et le chiffre d’affaires réalisé (Numtable, montanttotalfacture) SELECT Facture.Numtable , SUM(Consommation.PrixCons * Comprend.Qte) AS MontantTotalFacture FROM Consommation INNER JOIN Comprend USING(NumCons) INNER JOIN Facture USING(NumFacture) GROUP BY Tables.NumTable ; 2 A partir de la requête précédente on souhaite afficher le numéro de table qui a le chiffre d'affaires le plus élevé (Numtable, montanttotalfacture) SELECT Facture.Numtable , SUM(Consommation.PrixCons * Comprend.Qte) AS MontantTotalFacture FROM Consommation JOIN Comprend USING(NumCons) JOIN Facture USING(NumFacture) HAVING SUM(Consommation.PrixCons * Comprend.Qte) = (SELECT MAX(SUM(Consommation.PrixCons * Comprend.Qte)) FROM Consommation JOIN Comprend USING(NumCons) JOIN Facture USING(NumFacture) GROUP BY Facture.NumTable) GROUP BY Facture.NumTable ; 3 A partir de la requête précédente on souhaite afficher le numéro de table qui a le chiffre d'affaires le plus élevé et le numéro de table qui a le chiffre d'affaires le moins élevé (Numtable, montanttotalfacture) SELECT Facture.Numtable , SUM(Consommation.PrixCons * Comprend.Qte) AS MontantTotalFacture FROM Consommation JOIN Comprend USING(NumCons) JOIN Facture USING(NumFacture) HAVING SUM(Consommation.PrixCons * Comprend.Qte) IN ((SELECT MAX(SUM(Consommation.PrixCons * Comprend.Qte)) FROM Consommation JOIN Comprend USING(NumCons) JOIN Facture USING(NumFacture) GROUP BY Facture.NumTable) , (SELECT MIN(SUM(Consommation.PrixCons * Comprend.Qte)) FROM Consommation JOIN Comprend USING(NumCons) JOIN Facture USING(NumFacture) GROUP BY Facture.NumTable)) GROUP BY Facture.NumTable ; 4 Réécriture de la requête et mise en place d'une optimisation en n'exécutant qu'une seule fois la sous-requête à l'aide de la la clause WITH (9i et 10G) WITH MontantTotal AS ( SELECT Facture.Numtable , SUM(Consommation.PrixCons * Comprend.Qte) AS MontantTotalFacture FROM Consommation JOIN Comprend USING(NumCons) JOIN Facture USING(NumFacture) GROUP BY Facture.NumTable ) SELECT 'Mini' AS Calc , MontantTotalFacture , NumTable FROM MontantTotal WHERE MontantTotalFacture = (SELECT MIN(MontantTotalFacture) FROM MontantTotal) UNION SELECT 'Maxi' AS Calc , MontantTotalFacture , NumTable FROM MontantTotal WHERE MontantTotalFacture = (SELECT MAX(MontantTotalFacture) FROM MontantTotal) ; Execution de la sous-requete 3 fois ? 5 A partir de la requête précédente on souhaite afficher le numéro de table qui a le chiffre d'affaires le plus élevé et le numéro de table qui a le chiffre d'affaires le moins élevé et le chiffre d'affaire moyen sans afficher le numéro de table. WITH MontantTotal AS ( SELECT Facture.Numtable , SUM(Consommation.PrixCons * Comprend.Qte) AS MontantTotalFacture FROM Consommation JOIN Comprend USING(NumCons) JOIN Facture USING(NumFacture) GROUP BY Facture.NumTable ) SELECT 'avg' AS Calc , AVG(MontantTotalFacture) , NULL AS NumTable FROM MontantTotal UNION SELECT 'Mini' AS Calc , MontantTotalFacture , NumTable FROM MontantTotal WHERE MontantTotalFacture = (SELECT MIN(MontantTotalFacture) FROM MontantTotal) UNION SELECT 'Maxi' AS Calc , MontantTotalFacture , NumTable FROM MontantTotal WHERE MontantTotalFacture = (SELECT MAX(MontantTotalFacture) FROM MontantTotal) ; Activation mode d'affichage du plan d'exécution SQL> set autotrace on explain; 3.1. Liste de tous les coureurs SQL> SELECT * FROM COUREUR; Plan d'exécution ---------------------------------------------------------- 0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=3 Card=9 Bytes=207 ) 1 0 TABLE ACCESS (FULL) OF 'COUREUR' (TABLE) (Cost=3 Card=9 By tes=207) 3.2. Liste des coureurs entrainés par l'entraîneur numéro 3 SQL> SELECT * FROM COUREUR WHERE NumEntr = 3; Plan d'exécution ---------------------------------------------------------- 0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=2 Card=3 Bytes=69) 1 0 TABLE ACCESS (BY INDEX ROWID) OF 'COUREUR' (TABLE) (Cost=2 Card=3 Bytes=69) 2 1 INDEX (RANGE SCAN) OF 'I_FK_COUREUR_ENTRAINEUR' (INDEX) (Cost=1 Card=3) 3.3. Liste des coureurs entrainés par l'entraîneur dont le numéro est > 1 ou différent de 1 SQL> SELECT * FROM COUREUR WHERE NumEntr > 1 OR NumEntr != 1; Plan d'exécution ---------------------------------------------------------- 0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=3 Card=9 Bytes=207 ) 1 0 TABLE ACCESS (FULL) OF 'COUREUR' (TABLE) (Cost=3 Card=9 By tes=207) 3.4. Liste des coureurs dont le nom commence par D SQL> SELECT * FROM COUREUR WHERE NomCoureur LIKE 'D%'; Plan d'exécution ---------------------------------------------------------- 0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=3 Card=2 Bytes=46) 1 0 TABLE ACCESS (FULL) OF 'COUREUR' (TABLE) (Cost=3 Card=2 By tes=46) 3.5. Liste des coureurs dont le nom est Albert en respectant la casse avec la fonction Initcap. SQL> SELECT * FROM COUREUR WHERE NomCoureur LIKE INITCAP('ALBERT'); Plan d'exécution ---------------------------------------------------------- 0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=3 Card=1 Bytes=23) 1 0 TABLE ACCESS (FULL) OF 'COUREUR' (TABLE) (Cost=3 Card=1 By tes=23) Requete 3.1, et 3.2 : requetes assez "atomiques", je ne voies pas vraiment comment les optimiser 3.3 : Le numéro d'entraineur est un identifiant entier > 0. Une des conditions dans la requete est inutile du fait de la premiere (>1). On peut donc simplifier en : SQL> SELECT * FROM COUREUR WHERE NumEntr > 1; Plan d'exécution ---------------------------------------------------------- 0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=2 Card=7 Bytes=161 ) 1 0 TABLE ACCESS (BY INDEX ROWID) OF 'COUREUR' (TABLE) (Cost=2 Card=7 Bytes=161) 2 1 INDEX (RANGE SCAN) OF 'I_FK_COUREUR_ENTRAINEUR' (INDEX) (Cost=1 Card=7) 3.4 : Améliorations ? 3.5 : en connaissant le format de stockage des noms, l'appel de la fonction INITCAP devient inutile : Qui plus est, la recherche portant sur une chaine dont on peut être sur de l'égalité, le LIKE peut être remplacé. SQL> SELECT * FROM COUREUR WHERE NomCoureur = 'Albert'; Plan d'exécution ---------------------------------------------------------- 0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=3 Card=1 Bytes=23) 1 0 TABLE ACCESS (FULL) OF 'COUREUR' (TABLE) (Cost=3 Card=1 By tes=23) On remarque pourtant que cela n'apporte pas d'amélioration par rapport à l'ancienne requête. Pour que cela fonctionne il est nécessaire de créer des indexes : SQL> CREATE INDEX NomEmplIdx ON Coureur (NomCoureur); SQL> SELECT * FROM COUREUR WHERE NomCoureur = 'Albert'; aucune ligne sélectionnée Plan d'exécution ---------------------------------------------------------- 0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=2 Card=1 Bytes=23) 1 0 TABLE ACCESS (BY INDEX ROWID) OF 'COUREUR' (TABLE) (Cost=2 Card=1 Bytes=23) 2 1 INDEX (RANGE SCAN) OF 'NOMEMPLIDX' (INDEX) (Cost=1 Card= 1) 4. Vérification du plan d'exécution des requêtes de jointures 4.1. Liste des coureurs entraînés par WEBER. (num Coureur, nom coureur, ville) SELECT NumCoureur , NomCoureur , VilleCoureur FROM Coureur JOIN Entraineur USING(NumEntr) WHERE NomEntr = 'Weber'; Apres Ajout d'un index : Plan d'exécution ---------------------------------------------------------- 0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=3 Card=2 Bytes=66) 1 0 TABLE ACCESS (BY INDEX ROWID) OF 'COUREUR' (TABLE) (Cost=1 Card=2 Bytes=46) 2 1 NESTED LOOPS (Cost=3 Card=2 Bytes=66) 3 2 TABLE ACCESS (BY INDEX ROWID) OF 'ENTRAINEUR' (TABLE) (Cost=2 Card=1 Bytes=10) 4 3 INDEX (RANGE SCAN) OF 'NOMENTRIDX' (INDEX) (Cost=1 C ard=1) 5 2 INDEX (RANGE SCAN) OF 'I_FK_COUREUR_ENTRAINEUR' (INDEX ) (Cost=0 Card=2) 4.2. Qui a gagné la "course du Lion" du 12/09/05 ? (nom coureur, ville) SELECT NomCoureur , VilleCoureur FROM Coureur JOIN Participe USING(NumCoureur) JOIN Course USING(NumCourse) WHERE Ordre = 1 AND LibCourse = 'Course du Lion' AND DateCourse = '12/09/05' ; Plan d'exécution ---------------------------------------------------------- 0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=3 Card=1 Bytes=54) 1 0 NESTED LOOPS (Cost=3 Card=1 Bytes=54) 2 1 NESTED LOOPS (Cost=2 Card=1 Bytes=27) 3 2 TABLE ACCESS (BY INDEX ROWID) OF 'COUREUR' (TABLE) (Co st=1 Card=1 Bytes=21) 4 3 INDEX (UNIQUE SCAN) OF 'PK_COUREUR' (INDEX (UNIQUE)) (Cost=0 Card=1) 5 2 TABLE ACCESS (BY INDEX ROWID) OF 'PARTICIPE' (TABLE) ( Cost=1 Card=1 Bytes=6) 6 5 INDEX (RANGE SCAN) OF 'I_FK_PARTICIPE_COUREUR' (INDE X) (Cost=0 Card=1) 7 1 TABLE ACCESS (BY INDEX ROWID) OF 'COURSE' (TABLE) (Cost= 1 Card=1 Bytes=27) 8 7 INDEX (UNIQUE SCAN) OF 'PK_COURSE' (INDEX (UNIQUE)) (C ost=0 Card=1) Refaites le test en supprimant les index sur les colonnes de clé étrangère. Les plans d'exécution sont-ils identiques ; Quelle stratégie d'indexation pouvez-vous envisager pour optimiser ces deux requêtes. Mettez en oeuvre vos stratégies et vérifier le nouveau plan d'exécution. Plan d'exécution ---------------------------------------------------------- 0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=5 Card=1 Bytes=57) 1 0 NESTED LOOPS (Cost=5 Card=1 Bytes=57) 2 1 NESTED LOOPS (Cost=4 Card=1 Bytes=36) 3 2 TABLE ACCESS (FULL) OF 'PARTICIPE' (TABLE) (Cost=3 Car d=2 Bytes=18) 4 2 TABLE ACCESS (BY INDEX ROWID) OF 'COURSE' (TABLE) (Cos t=1 Card=1 Bytes=27) 5 4 INDEX (UNIQUE SCAN) OF 'PK_COURSE' (INDEX (UNIQUE)) (Cost=0 Card=1) 6 1 TABLE ACCESS (BY INDEX ROWID) OF 'COUREUR' (TABLE) (Cost =1 Card=1 Bytes=21) 7 6 INDEX (UNIQUE SCAN) OF 'PK_COUREUR' (INDEX (UNIQUE)) ( Cost=0 Card=1) -> plus lourd ! 5. Validation des requêtes de l'exercice K du tp 5 1. Affichez la liste des clients (numéro et nom) de la catégorie « Détaillant » ayant commandé au cours du mois de mars. SELECT Cli_Num , Cli_Nom , Cde_Date FROM Client JOIN Categorie USING (Cat_Code) JOIN Commande USING (Cli_Num) WHERE Cat_Design = 'DETAILLANT' AND Cde_Date LIKE '%/03/%' ; 2. Affichez la liste des produits (référence et désignation) commandés par le client numéro 41103 depuis le début de l’année. SELECT Prod_Ref , Prod_Des , Cde_Date FROM Produit JOIN Ligne_Commande USING(Prod_Ref) JOIN Commande USING(Cde_Num) WHERE Cli_Num = '41103' AND Cde_Date >= '01/01/97' ; 2. Optimisation du modèle physique de données MCD MLR SQL : création de l'utilisateur sur la base oracle Optimisation MCD / MLR Contrainte "Consommable vendu que par 2 fournisseurs différents" : ne change rien au niveau du passage au logique Rédaction des requetes 1. 2. 3.