Cours SQL

Cours Concepts avancés SQL


Télécharger Cours Concepts avancés SQL

★★★★★★★★★★3.5 étoiles sur 5 basé sur 1 votes.
Votez ce document:

Télécharger aussi :


Cours Concepts avancés SQL

...

Introduction

n En plus des concepts de base du model relationnel, les SGBD relationnels implantent des concepts avancés

n Concepts présentés

– Vues

– Optimisation de requêtes

La notion de vue

n Généralités

– Les vues sont pour le relationnel ce que sont les niveaux externes pour les modèles légués

– Garantissent l'indépendance logique entre les applications et la base de données

– Renforcent la sécurité

n Exemple

Vente (Numv, Nump, Numf, Date, Qte)

Produit (Nump, Des, Type, Prix)

Fournisseur (Numf, Nom, Region, Tel)

– les produits vendus par le fournisseur FLUX

– les ventes du produit numéro 63755 par région

– les fournisseurs de l'île de Montréal

La notion de vue

n Définition

– Table virtuelle dont le schéma et le contenu sont dérivés de la base de données par une requête

n Syntaxe SQL

CREATE VIEW <une_vue> [ <liste_alias> ]

AS <une_requête>

[WITH CHECK OPTION]

n Exemple

– les produits vendus par le fournisseur FLUX

– les ventes par région

– les fournisseurs de l'île de Montréal

La notion de vue

n Syntaxe SQL (suite)

DROP VIEW <une_vue>

La notion de vue

n Mise à jour des données à travers une vue

– Problèmes : traduire toute mise à jour, insertion ou suppression portant sur une vue en une mise

à jour des tables de base

– Exemples

  • Modifier un produit du fournisseur FLUX
  • Rajouter un fournisseur à la liste des fournisseurs de l'île de Montréal

– Solution

  • En pratique, mise à jour réservé aux vues monotable avec obligation de contenir la clé

La notion de vue

n Mise à jour des données à travers une vue

– Règle pour la mise à jour avec SQL

  1. La vue est définie comme une sélection (pas d'union par exemple)
  2. Pas de DISTINCT dans le SELECT
  3. Tout élément du SELECT est une référence à un attribut de la table sous-jacente
  4. La clause FROM contient une référence à une seule table
  5. Cette table peut être soit une table de base soit une vue qui peut être mise à jour
  6. Pas de clause WHERE qui contient une sous-requête dont la clause FROM fait référence à la table en question
  7. Pas de GROUP BY
  8. Pas de HAVING

La notion de vue

n Cas particulier: Vue concrète

– Une vue calculée à partir des tables de base et stockée physiquement sur disque



– Elle est mise à jour dès qu'une transaction modifie les tables sous-jacentes par différentiel quand c'est possible

– Particulièrement intéressante pour mémoriser les agrégats

La notion de vue

n Cas particulier: Vue concrète

– Syntaxe SQL

CREATE SNAPSHOT <une_vue_concrete>

AS <une_requête>

[ REFRESH [FAST | COMPLETE | FORCE] [START WITH date] [NEXT date]]

–Exemple

  • ventes de produits par jour

Optimisation de requêtes

n Objectif

– Maintenir des performances acceptables lors de l’exécution des requêtes

n Exemple :

– Requête : Nom et prénoms des buveurs

Montréalais de Volnay depuis 1999

– Contenu de la base :

  • 10 millions de buveurs dont 1 m à Montréal
  • 10 millions d'abus dont 10000 de Volnay et 1m depuis 1999 (2000 de Volnay depuis 1999)
  • 1000 vins

– Coût d’exécution :

10m * 10m + …

B.NOM, B.PRENOM

A.NV V.NV

= V

Résultat

B.NB A.NB

B.VILLE = ‘Montréal’

B = A

A.DATE >= 01-01-1999

V.CRU = ‘Volnay’

Optimisation de requêtes

n Autre solution

– Coût d’exécution :

10m + 10m + 10000 +

+ 1m * 1 m + 2000 + …

B.NOM, B.PRENOM

A.DATE

V.CRU

01-01-1999

‘Volnay’ >= = A.NV V.NV = V

Résultat =

B.NB A.NB

B.VILLE ‘Montréal’

B = A

B.NB, B.NOM, B.PRENOM A.NB, A.NV

V.NV

B.NOM, B.PRENOM,A.NV

Optimisation de requêtes

n Problème :

– suivant l'ordre des opérateurs algébriques dans un arbre, le coût d'exécution est diffèrent

n Pourquoi?

– le coût des opérateurs varient en fonction du volume des données traitées. Plus le nombre de

n-uplets des relations traitées est petit, plus les coûts cpu et d'E/S sont minimisés

– certains opérateurs diminuent le volume des données

e.g., restriction et projection

Optimisation de requêtes

n Processus

1.Transformer la requête dans un format interne

2.Trouver une forme canonique

3.Décider de la stratégie d’exécution de bas niveau



4.Générer les plans de requêtes et choisir le meilleur

Optimisation de requêtes

n Transformer la requête dans un format interne

– Arbre de requête en algèbre relationnelle

n Exemple :

– Requête : Noms et prénoms des buveurs

Montréalais de Volnay depuis 1999

SELECT NOM, PRENOM

FROM BUVEURS B, ABUS A, VINS V

WHERE B.NB = A.NB

AND A.NV = V.NV

AND CRU = ‘Volnay’

AND DATE >= 01-01-1999

AND VILLE = ‘Montréal’

B.NOM, B.PRENOM

A.NV V.NV = V

Résultat

B.NB A.NB

B.VILLE = ‘Montréal’

B = A

A.DATE >= 01-01-1999

V.CRU = ‘Volnay’

Optimisation de requêtes

n Trouver une forme canonique

– Définitions

– Soit Q un ensemble de requêtes et R une relation d’équivalence entre ces requêtes (deux requêtes sont équivalentes si elles produisent le même résultats), un sous ensembles C de Q est un ensemble de formes canoniques de Q pour R si et seulement si toute requête q de Q est équivalente à exactement une requête c de C

– c est la forme canonique de q

Optimisation de requêtes

n Trouver une forme canonique

– Règles de transformation

  1. Une suite restrictions sur une relation donnée peut être transformer en une restriction conjonctive sur cette relation
  2. Dans une suite de projections sur une relation donnée, on peut garder uniquement la dernière
  3. Une restriction d’une projection peut être transformée en une projection sur une restriction
  4. Distributivité : f(A ° B) º f(A) ° f(B) ou f est une opération sur une table (restriction, projection) et ° une opération binaire (union, intersection, différence, jointure)

Optimisation de requêtes

n Trouver une forme canonique

– Règles de transformation

  1. Distributivité : f(A ° B) º f(A) ° f(B)

Si les attributs de jointure inclus dans la projection

Projection Oui Oui Non

Si 2 cond., une pour chaque table

Restriction Oui Oui Oui

Union Intersection Différence Jointure

Optimisation de requêtes

n Trouver une forme canonique

– Règles de transformation

  1. Commutativité : A ° B º B ° A

union, intersection, jointure (pas différence, division)

  1. Associativité : (A ° B) ° C º A ° (B ° C)

union, intersection, jointure (pas différence, division)

  1. Idempotence : A ° A º A

union, intersection, jointure (pas différence, division)

  1. Simplification d’expressions arithmétiques

Exemple : a * b + a * c º a * (b + c)



  1. Transformation d’expressions logiques

Exemple : a > b AND b > 3 º a > b AND b > 3 AND a > 3

Optimisation de requêtes

n Trouver une forme canonique

– Règles de transformation

  1. Transformations sémantiques

– Dépendent de la sémantique du domaine

– Reliés aux contraintes d’intégrité

– Exemple : suppression de jointure dans certains cas

Project(Join(Abus, Vins), NV) º Project(Abus, NV)

Optimisation de requêtes

n Trouver une forme canonique

B.NOM, B.PRENOM

A.DATE

V.CRU

01-01-1999

‘Volnay’ >= =

A.NV V.NV = V

Résultat = B.NB A.NB

B.VILLE ‘Montréal’

B = A

B.NB, B.NOM, B.PRENOM A.NB, A.NV

V.NV

B.NOM, B.PRENOM,A.NV

B.NOM, B.PRENOM

A.NV V.NV = V

Résultat

B.NB A.NB

B.VILLE = ‘Montréal’

B = A

A.DATE >= 01-01-1999

V.CRU = ‘Volnay’

Optimisation de requêtes

n Décider de la stratégie d’exécution de bas niveau

– Tient compte des facteurs suivants

– Les indexes

– La distribution des valeurs de données mémorisées

– L’emplacement des physique des données mémorisées

– Etc.

Optimisation de requêtes

n Générer les plans de requêtes et choisir le meilleur

– Chaque opérateur algébrique est implanté par plusieurs procédures

– Un plan d’exécution est une combinaison de procédures pour une requête donnée

– Il existe plusieurs plans possibles pour une requête

– Le coût de chaque plan est la somme des coûts des procédures qu’il contient

– Le coût de chaque procédure est estimé en fonction des résultats possibles de la procédure précédente

– Tous les plans ne sont pas générés. Des techniques de réduction de l’espace de recherche sont utilisées



990