Optimisation de Requtes 1 2 3 4 5

Optimisation de Requêtes Ø 1. Ø 2. Ø 3. Ø 4. Ø 5. Ø 6. Introduction Arbres relationnels Restructuration algébrique Modèle de coût Choix du meilleur plan Conclusion G. Gardarin 1

1. ARCHITECTURE TYPE SGBD META-BASE G. Gardarin ANALYSEUR SYNTAXE SEMANTIQUE SCHEMA CONTROLE VUES INTEGRITE AUTORISATIONS OPTIMISEUR ORDONNANCEMENT ELABORATION D'UN PLAN EXECUTABLE EXECUTION METHODES D'ACCES 2

ETAPES DE L'OPTIMISATION Ø (1) Obtention d’une représentation canonique Ø (2) Réécriture = transformation par : • simplification • ordonnancement des opérations élémentaires Ø (3) Planning = construction des plans d'exécution candidats • choix des algorithmes pour chaque opérateur, • calcul du coût de chaque plan, • choix du meilleur plan. Ø Etapes 1 et 2 : indépendantes données Ø Etape 3 : dépendante des données G. Gardarin 3

2. ARBRES RELATIONNELS G. Gardarin 4

EXEMPLE D'ARBRE Ø Coût d'exécution: RESULTAT • 10 millions de buveurs dont 1 m à Paris • 10 millions d'abus dont 10000 de Volnay • 1000 vins • 10 m + 10 m * 1 m + 10 m * 1000 • + 10 m + 10000 + … B. NB = • de l'ordre de 10 ** 13 • comparaisons de tuples !!! B. VILLE = "PARIS" B. NOM, B. PRENOM A. DATE > 01 -01 -90 V. CRU = "VOLNAY" A. NV = V. NV VINS V A. NB ABUS A BUVEURS B G. Gardarin 5

Arbre linéaire droit SELECT V. CRU FROM PRODUCTEURS P, VINS V, PRODUIT R WHERE V. MILLESIME = 1976 AND V. DEGRE £ 14 V. MILLESIME = 1976 AND P. REGION = « BORDELAIS » AND P. NP = R. NP V. DEGRE £ 14 AND R. NV = V. NV. = « Bordelais » P. REGION P. NP = R. NP P R. NV R G. Gardarin = V. NV V 6

Typologie des arbres Arbre linéaire droit G. Gardarin Arbre linéaire gauche Arbre ramifié 7

Autre exemple RECETTE SELECT P. NOM, SUM(L. PRIX * (1 L. DISCOUNT)) FROM CLIENTS C, COMMANDES O, LIGNES L, FOURNISSEUR F, PAYS P, CONTINENTS T P. NOM, RECETTE P. NOM WHERE C. NUMCLI = O. NUMCLI AND O. NUMCOM = L. NUMCO AND L. NUMFOU = F. NUMFOU AND C. NUMPAYS = F. NUMPAYS AND F. NUMPAYS = P. NUMPAYS AND P. NUMCONT = T. NUMCONT AND T. NOM = « EUROPE » AND O. DATE < $D 1 + INTERVAL 1 YEAR L. NUMFOU = F. NUMFOU L C. NUMPAYS = F. NUMPAYS C F. NUMPAYS = P. NUMPAYS F P. NUMCONT = T. NUMCONT $D 1 £ O. DATE <$D 1+1 P O G. Gardarin ³ $D 1 GROUP BY P. NOM ORDER BY RECETTE DESC ; T. NOM = « EUROPE » T 8

3. RESTRUCTURATION ALGEBRIQUE Ø Problème : • suivant l'ordre des opérateurs algébriques dans un arbre, le coût d'exécution est diffèrent Ø Pourquoi? • 1. le coût des opérateurs varient en fonction du volume des données traitées i. e. , plus le nombre de tuple des relations traitées est petit, plus les coûts cpu et d'E/S sont minimisés • 2. certains opérateurs diminuent le volume des données e. g. , restriction et projection G. Gardarin 9

Commutativité des Jointures R G. Gardarin S S R 10

Associativité des jointures Ø Il existe N!/2 arbre de jointure de N relations. Ø Parmi les jointures, certaines sont des produits cartésiens. T R G. Gardarin S R S T 11

Groupage des Restrictions Ai = a et Aj = b G. Gardarin 12

Semi-commutativité des Projections Ø Il est possible de descendre les projections, mais les attributs utilisés dans la suite doivent être conservés !!! A 1, … Ap Ai = a Ai, A 1, … Ap G. Gardarin 13

Règles de Restructuration Ø (1) Commutativité des jointures Ø (2) Associativité des jointures Ø (3) Groupabilité des restrictions Ø (4) Semi-commutativité des projections et restrictions Ø (5) Semi-commutativité des restrictions et jointures Ø (6) Semi-distributivité des projections / jointures Ø (7) Distributivité des restrictions / unions ou différences Ø (8) Distributivité des projections / unions G. Gardarin 14

Heuristique d'Optimisation Ø Appliquer d'abord les opérations réductrices (restrictions et projections) en les groupant sur chaque relation. • • 1. 2. 3. 4. Dégrouper les restrictions (Règle 3') Descendre les restrictions (Règles 4, 5 et 7) Grouper les restrictions aux feuilles (Règle 3) Descendre les projections (Règles 4, 6 et 8) Ø L'ordre des unions, différences et jointures reste inchangé !!! G. Gardarin 15

Exemple d'Arbre Optimisé Ø Coût d'exécution: Résultat B. NOM, B. PRENOM A. NV = Ø 10 m + 1 m * 100000 + 1 m * 1000 + … V. NV B. NOM, B. PRENOM, A. NV B. NB, B. NOM, B. PRENOM B. VILLE = "PARIS" B G. Gardarin = A. NB V. NV Ø de l'ordre de 10 ** 11 comparaisons de tuples ! A. NB, A. NV V. CRU = "VOLNAY" A. DATE > 01 -01 -83 A V 16

Ordonnancement des Jointures Ø HEURISTIQUES : • Choix des relations de taille minimum • Jointures pré-calculés d ’abord (indexes) • Semi-jointures plus réductrices Ø ORDONNANCEMENT DES AGREGATS • Permutations difficiles • Profiter des tris des jointures, dédoublement, etc. . • Gains importants pour MIN et MAX G. Gardarin 17

4. MODELE DE COUT Ø Facteur de sélectivité • Proportion de tuples du produit cartésien des relations touchées qui satisfont une condition. Ø Exemple: SELECT * FROM R 1, R 2 ==> s =1 SELECT * FROM R 1 WHERE A = valeur ==> s = 1/NDIST(A) avec un modèle uniforme G. Gardarin 18

Sélectivité des Restrictions Ø TAILLE (s(R)) = s * TAILLE(R) avec: s (A = valeur) = 1 / NDIST(A) s(A > valeur) = (max(A) - valeur) / (max(A) - min(A)) s(A < valeur) = (valeur - min(A)) / (max(A) - min(A)) s (A IN liste valeurs) = (1/NDIST(A)) * CARD(liste valeurs) s(P et Q) = s(P) * s(Q) s(P ou Q) = s(P) + s(Q) - s(P) * s(Q) s( not P) = 1 - s(P) Ø Le coût dépend de l'algorithme (index, hachage ou balayage). G. Gardarin 19

Sélectivité des Projections Ø TAILLE(px(R)) = p(x) * (1 -d) * TAILLE(R) • avec p(x) = Larg(x) / Larg(R) • d = probabilité de doubles • CARD(X) / CARD(DOM(X))**2 G. Gardarin 20

Sélectivité des Jointures Ø TAILE( R 1 |><| R 2) = p * TAILLE(R 1) * TAILLE(R 2) • p dépend du type de jointure et de la corrélation des colonnes : § p = 0 si aucun tuple ne joint § p = 1 / MAX(NDIST(A), NDIST(B)) si distribution uniforme équiprobable des attributs A et B sur un même domaine § p = 1 si produit cartésien Ø L'algorithme change radicalement les coûts • linéaire si index, • produit des tailles si boucles imbriquées. G. Gardarin 21

Le calcul des tailles Ø Taille des tables de base dans le catalogue Ø Calcul des tailles à la compilation • application du coefficient de sélectivité • hypothèse d ’uniformité Ø Possibilité d’histogrammes • Run. Stat(<Table>, <attribut>) • Stockage dans le catalogue de l’histogramme de distribution de l ’attribut • Utilisation par le modèle de coût G. Gardarin 22

5. CHOIX DU MEILLEUR PLAN Schéma interne Arbre d'opérations Plans d'exécution Générateur de Plans Stratégie de Recherche Bibliothèque de transformations Heuristiques de choix Modèle de coût Plan d'exécution Optimal G. Gardarin 23

Sélectivité minimum Rel = liste des relations à joindre ; p = plus petite relation ; Tant que Rel non vide { R = relation de selectivité minimum de Rel ; p = join(R, p) ; Relations = Relations - R ; } ; Return(p) ; G. Gardarin 24

Programmation Dynamique Plan. Ouverts = liste de tous les plans mono-relation possible ; Eliminer tous les plans équivalents excepté le moins coûteux ; Pour chaque Plan. Ouverts p { Pour chaque opérateur n’appartenant pas au plan p { Etendre le plan en ajoutant cet opérateur ; Calculer le coût du nouveau plan ; Insérer le nouveau plan dans la liste Nouveaux ; } Eliminer tous les plans équivalents excepté le moins coûteux ; Transférer les plans Nouveaux dans Plan. Ouverts ; } Retourner le plan optimal ; G. Gardarin 25

Illustration DP Scan. R 1 Join. S R 3 Join. H R 2 Join. S R 2 Join. H R 3 G. Gardarin Join. H R 3 Join. S R 3 Join. H R 2 Join. S R 2 26

Différentes Stratégie de recherche Enumérative Exhaustive G. Gardarin Augmentation Aléatoire Amélioration itérative Recuit simulé Génétique 27

Amélioration itérative Function Iterative(Query) p: = Parse(Query) ; // Set the initial plan S : = {} ; // S is the set of locally optimum plans while not Stop. Cond() { nmoves : = 0; while nmoves < Max. Moves(Query) and Transformable(p) { p' : = Transform (p) ; // Apply a transformation rule if Cost(p') < Cost(p) then { p : : = p'; nmoves : : = nmoves + 1; } Insert (S, p') ; // Maintain the set of interesting plans p : = Random(Parse(Query)); // Generate a new initial plan at random } } return Optimal(S) ; // Select best plan among all generated ones } G. Gardarin 28

Illustration II Parse(Query) Profitable r 1 Rand(Parse(Query)) Profitable r'1 Rand((Parse(Query))) Profitable r"1 Profitable r"2 Profitable r 2 SELECT MINIMAL COST PLAN G. Gardarin 29

6. CONCLUSION Ø Problème essentiel des SGBD Ø Nécessité d’un modèle de coût Ø Approches par compilation dans un langage d’accès (opérateurs avec annotations) Ø Stratégies de choix aléatoires G. Gardarin 30