LES INDEX ET LES VUES
La définition d'index et de vues relève du langage de définition de données (LDD) au même titre que la création de tables.
1. Les index
q Généralités
On crée des index sur les tables pour permettre un accès plus rapide à l'information. Un index concerne une ou plusieurs colonnes de la table et entraîne la création d'un espace comportant pour chaque valeur de la colonne ou du groupe de colonnes, le numéro du (ou des) tuple(s) correspondant(s).
Représentation schématique de la notion d'index
L'illustration présente une table Client avec un index sur le nom.
CLIENT (Num_cli, Nom_cli, Pre_Cli, Adr_cli, Cdp_cli, Vil_cli, Tel_cli)
DONNEES INDEX
Identifiant de la ligne Identifiant de la ligne
|
|
Dupont |
Alexandre….
|
A1 |
|
Armand |
A2 |
|
|
Armand |
Thomas….
|
A2 |
|
Armand |
A4 |
|
|
Duval |
Chrystelle….
|
A3 |
|
Dupont |
A1 |
|
4 |
Armand |
Cathy….
|
A4 |
|
Duval |
A3 |
|
… |
|
|
|
|
… |
|
ð les données sont insérées de façon séquentielle dans la table Client au fur et à mesure des ajouts
ð chaque ligne de la table est identifiée de façon unique par un identifiant dans la base de données.
ð lorsqu’on crée un index sur le nom du client, les valeurs de la colonne indexée sont en permanence classées ; à chacune de ces valeurs, on fait correspondre l’identifiant de la ligne de la table.
Un index va permettre d'éviter de balayer toute la table lors de la recherche d'une ligne répondant à un critère de sélection exprimé sur la colonne à indexer.
Ex : SELECT *
FROM Client
WHERE Nom_cli = 'Armand';
. Exécution sans index sur le nom de client :
Le SGBDR scrute toute la table Client (bloc par bloc et ligne par ligne) pour retrouver les clients nommés 'Armand'.
. Exécution avec index sur le nom de client :
Le SGBDR passe dans ce cas par l'index qui le renverra vers les lignes concernées dans la table. Cette seconde solution est nettement plus rapide car le nombre de blocs parcourus est beaucoup moins important.
q Création d'un index
Sous Oracle ou MySql:
CREATE [UNIQUE] INDEX NomIndex
 |
 |
ON NomTable (NomColonne ASC [, NomColonne …]);
DESC
UNIQUE est à préciser si les doublons ne sont pas acceptés.
Par défaut, le classement est croissant (ASC).
Un index peut être composé de une à 16 colonnes.
Sous MySql on peut visualiser les
index en écrivant : Show index From
nomTable
Ex : création d'un index sur le nom du client :
…………………………………………………………………………………………………………………
…………………………………………………………………………………………………………………
q Suppression d'un index
DROP INDEX NomIndex;
q Colonnes à indexer
. Les clés primaires
La création d'un index sur la clé primaire est automatique.
. Les clés étrangères
Ces clés servent pour les jointures. C'est justement pour accélérer les opérations de jointure qu'un index est nécessaire sur les clés étrangères.
. Les colonnes servant souvent de critère de recherche
Ce sont les colonnes qui sont référencées dans une clause Where.
. Les colonnes servant souvent de critère de tri
q Les colonnes à ne pas indexer
Il est recommandé de ne pas indexer :
. les colonnes présentant peu de valeurs distinctes,
. les colonnes souvent modifiées.
q Cas où l'index n'est pas utilisé
Bien qu'une colonne soit indexée, l'index ne sera pas utilisé si :
. la colonne indexée ne figure ni dans where, ni dans order by et ni dans group by,
. on applique une fonction sur la colonne indexée :
Ex : SELECT *
FROM Client
WHERE UPPER(Nom_cli) LIKE 'P%';
. on teste l'inégalité entre la colonne indexée et une valeur :
Ex : SELECT *
FROM Client
WHERE Nom_cli != 'Dupont';
. on compare la colonne indexée avec un modèle de chaîne dont on ne connaît pas le début :
Ex : SELECT *
FROM client
WHERE Nom_cli LIKE '_A%';
q Inconvénients des index
L'insertion ou la suppression dans une table indexée de même que la modification de la valeur d'une colonne indexée va entraîner une mise à jour de l'espace d'index.
Les opérations DELETE et UPDATE sont très souvent précédées par une recherche. Dans ce cas, l'index fait gagner du temps lors de la recherche et en fait perdre lors de l'opération proprement dite de modification ou suppression. Donc, globalement, l'index n'est pas vraiment pénalisant.
L'opération INSERT ne fait pas de recherche préalable : d'éventuels index ne peuvent être que pénalisants.
Dans les cas de saisie massive initiale d'une table, on recommande de saisir la table d'abord et de créer les index après ; de cette façon, l'étape de saisie est plus rapide.
2. Les vues
q Définition
Les vues sont des tables virtuelles créées à partir de requêtes SQL. Seule la définition de la vue (c'est à dire le texte de la requête) est stockée, l'ensemble de tuples correspondant n'est pas stocké contrairement aux tuples d'une table.
Une vue dans un SGBDR est assimilable à la notion de requête sous Access mais offre plus de possibilités qu'une requête Access notamment au niveau de l'attribution des privilèges.
q Création d'une vue
CREATE VIEW NomVue [(NomColonne1Vue [,NomColonne2Vue…]) ]
AS
Requête
[WITH CHECK OPTION] ou sous MySql [WITH LOCAL CHECK OPTION]
Ex 1: CREATE VIEW V_CLI_PAR
AS
SELECT *
FROM Client
WHERE Vil_cli = 'PARIS';
Pour utiliser la vue afin de consulter les clients parisiens, il suffit d'écrire :
SELECT *
FROM V_CLI_PAR;
Ex2 : CREATE VIEW V_CLI_VILLE (V_ville, V_nb)
AS
SELECT Vil_cli, count(*)
FROM Client
GROUP BY Vil_cli;
Remarques :
. On n’est pas obligé de spécifier les noms des colonnes de la vue si la liste de sélection de la requête ne comporte ni expression non nommée, ni fonction non nommée, ni colonnes en double dans le select (ex : client.code et produit.code) et non renommées. Si on ne spécifie pas de nom, les colonnes de la vue héritent des noms des champs indiqués dans la liste de sélection (sans le nom de la table en préfixe). Il faut soit ne préciser aucun des noms de colonnes de la vue, soit les préciser tous.
. La clause ORDER BY est interdite dans la requête de création de la vue.
. La clause WITH CHECK OPTION (ou WITH LOCAL CHECK OPTION sous MySql) permet de garantir que les tuples insérés ou modifiés au travers de la vue sont compatibles avec la définition de la vue. Bien sûr, cette clause n'a de sens que si l'insertion ou la modification est possible.
. Toutes les requêtes de consultation sont autorisées sur la vue.
q Intérêt des vues
. Une vue permet d'assurer la confidentialité de certaines données : avec les vues, il est possible de mettre uniquement certaines colonnes et/ou certaines lignes à disposition de l'utilisateur.
. Une vue peut également être utilisée pour simplifier la formulation de requêtes pour les utilisateurs (le texte de la requête complexe est défini dans la vue).
Ex1 : la vue V_CLI_PA2 sur la table des clients ne délivre que certaines informations (numéro de client, nom du client et numéro de téléphone) relatives aux clients parisiens.
CREATE VIEW V_CLI_PA2
AS
. . .
TABLE CLIENT :
|
Num_cli |
Nom_cli |
Pre_cli |
Adr_cli |
Cdp_cli |
Vil_cli |
Tel_cli |
|
1 |
Dupont |
Alexandre |
69 rue de Turbigo |
75003 |
PARIS |
0143211234 |
|
2 |
Armand |
Thomas |
05 rue Royale |
75002 |
PARIS |
0144332211 |
|
3 |
Duval |
Cathy |
14 rue des Petits Champs |
75002 |
PARIS |
0142622222 |
|
|
|
|
|
|
|
|
|
5 |
Dupont |
Jean |
10 rue de Conte |
75003 |
PARIS |
0143211235 |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
VUE V_CLI_PA2 :
|
Num_cli |
Nom_cli |
Tel_cli |
|
1 |
Dupont |
0143211234 |
|
2 |
Armand |
0144332211 |
|
3 |
Duval |
0142622222 |
|
5 |
Dupont |
0143211235 |
ð on effectue ici une projection et une sélection.
ð la manipulation de la vue V_CLI_PA2 permet de masquer les lignes des clients 4, 6 et 7 et certaines colonnes pour les clients "visibles".
SELECT *
FROM V_CLI_PA2 ;
Résultat :
NUM_CLI NOM_CLI TEL_CLI
--------- -------------------- --------------------
1 Dupont 0143211234
2 Armand 0144332211
3 Duval 0142622222
5 Dupont 0143211235
Ex2 : la vue V_COMM permet de simplifier la formulation d’une requête complexe pour les utilisateurs.
Commande (num_com, dat_com, #num_cli)
Article (num_art, des_art)
Lg_comm (#num_com, #num_art, qte_lig)
CREATE VIEW V_COMM
AS
select C.num_com, C.dat_com, L.num_art, A.des_art, L.qte_lig
FROM Commande C, Lg_comm L, Article A
WHERE C.num_com = L.num_com
AND L.num_art = A.num_art ;
ð trois tables sont en jeu : LG_COMM, COMMANDE et ARTICLE,
ð on effectue une projection et des jointures
ð une fois la vue créée, le résultat est obtenu simplement à partir de la requête suivante :
SELECT *
FROM V_COMM ;
q Mise à jour (modification, insertion, suppression) à travers une vue
Il n'est pas possible d'effectuer des mises à jour à partir de n'importe quelle vue.
Si la définition de la vue comporte dans le SELECT de 1er niveau :
. plusieurs tables,
OU . une expression,
OU . une fonction,
OU . un GROUP BY,
OU . un UNION,
La mise à jour à travers la vue est alors interdite. On dit que la vue n'est pas modifiable.
Il sera également impossible de faire des insertions à travers la vue si un attribut déclaré NOT NULL dans la structure de la table, n'est pas repris dans la vue.
Peut-on faire des modifications à travers V_CLI_PAR ? . . .
Peut-on faire des modifications à travers V_CLI_VILLE ? . . .
Ex1 : modification à travers V_CLI_PAR :
UPDATE V_CLI_PAR
SET Tel_cli = 0142123456
WHERE Num_cli = 1;
Si la vue V_CLI_PAR avait été définie avec la clause WITH CHECK OPTION, la modification aurait également été acceptée.
Ex2 : insertion d'un nouveau client à partir de V_CLI_PAR :
INSERT INTO V_CLI_PAR VALUES
(8, ‘Portier’, 'Nicolas', ’3 rue de Lille’, 21000, ‘DIJON’, NULL) ;
=> 1 ligne créée.
L'insertion est effectuée. En revanche, la nouvelle ligne ne sera pas visible à partir de V_CLI_PAR.
Si la vue V_CLI_PAR avait été définie avec la clause WITH CHECK OPTION, l'insertion n'aurait pas été acceptée :
INSERT INTO V_CLI_PAR VALUES
(8, ‘Portier’, 'Nicolas', ’3 rue de Lille’, 21000, ‘DIJON’, NULL) ;
=> message d’erreur :
ORA-01402: vue WITH CHECK OPTION - violation de clause WHERE
q Suppression d'une vue
Une vue ne peut pas être modifiée : elle doit être supprimée puis recréée.
DROP VIEW NomVue ;