Chapitre: INDEX, SYNONYME, VUE, SEQUENCE
1. Création d’index
- Un document est un objet de la base de données
- Il est utilisé par le serveur d’Oracle pour accéder plus rapidement aux lignes d’une table
en utilisant des pointeurs.
- Il est utilisé automatiquement par le serveur
- Les index sont créés soit
o Automatiquement : un index est créé automatiquement quand on définit une clé
primaire ou une colonne unique lors de la création et la définition d’une table
o Manuellement : on peut créer manuellement des index sur des colonnes pour
augmenter la vitesse d’accès aux lignes d’une table
Syntaxe :
CREATE INDEX nom_index ON nom_table(colonne1 [, col2] …);
Ex: create index emp_ename_tdx on EMP(ename);
Quand doit-on créer un index:
- Une colonne est utilisée fréquemment dans une clause WHERE ou dans une condition de
jointure
- Une colonne contient beaucoup de valeurs
- Une colonne contient un grand nombre de valeurs nulles
- Quand plus de deux colonnes sont fréquemment utilisées ensemble dans une clause
WHERE ou une condition de jointure
- Quand une table est très large (beaucoup de lignes) et les requêtes récupèrent moins de 2 à
4% des lignes
Quand doit-on ne pas créer un index :
- Quand la table est petite
- Quand les colonnes ne sont pas utilisées fréquemment comme étant une condition dans
une requête
- Quand les requêtes qui accèdent à cette table récupèrent plus de 4% des lignes
- Quand la table est mise à jour fréquemment
N.B. :
- Pour supprimer un index : DROP INDEX nom_index
- Les tables système (dictionnaire de données) qui contiennent les informations sur les
index sont :
o USER_IND_COLUMNS composée du nom de l’index, nom de la table
correspondante et du nom de la colonne d’index
o USER_INDEXES composé du nom de l’index et est-ce qu’il est unique
Ex:
Select IC.index_name, IC.column_name,
IC.colum_position “col_pos”, [Link]
From USER_INDEXES IX, USER_IND_COLUMNS IC Where
IC.index_name = IX.index_name
And IC.table_name = ‘EMP’;
Résultat:
Index_name Column_name Col_pos Uniqueness
EMP_ename_PK empno 1 Unique
EMP_ename_IDX Ename 1 Nonunique
2. Création des synonymes
CREATE [PUBLIC] SYNONYM [<USER.>]<nom_synonyme>
FOR [<USER.>] <objet>;/* Objet : est généralement une table */
PUBLIC : crée un synonyme accessible à tous les utilisateurs
Objet : identifie l’objet et pour lequel le synonyme est créé.
N.B.:
- Le nom d’un synonyme privé doit être distinct de tous les objets appartenant au
même utilisateur
- Un synonyme est utilisé pour la sécurité et la facilité de manipulation
- Son utilisation abusive augmente le temps d’exécution des requêtes
- Sert à référencer un objet sans indiquer son propriétaire
- Fournit un autre nom à un objet
- On peut également créer des synonymes pour des vues, des séquences, des procédures, …
et même pour des synonymes
- L’objet référencé ne peut être contenu dans un package
- Toutes les modifications effectuées sur les synonymes se répercutent sur la table
correspondante et vice versa
- Pour supprimer un synonyme : DROP SYNONYM nom_synonyme;
Ex1: créer un synonyme pour chacune des tables EMP et DEPT
CREATE SYSNONYM syn_emp FOR EMP;
CREATE SYNONYM syn_dept FOR DEPT;
Ex2: supprimer le synonyme syn_dept
DROP SYNONYM syn_dept;
3. Création des séquences
- génèrent automatiquement des nombres uniques
- objet partageable
- sont utilisées pour créer des valeurs pour les clés primaires
- remplace le code (insert into …)
- augmente la vitesse d’accès aux valeurs de séquence (cache)
- les valeurs sont mises en mémoire ce qui implique un accès plus rapide à ces valeurs
Syntaxe:
CREATE SEQUENCE nom_séquence
[INCREMENT BY n] [START WITH n]
[{MAXVALUE n | NOMAXVALUE}] [{MINVALUE n | NOMINVALUE}] [{CYCLE |
NOCYCLE}]
[{CACHE n | NOCACHE}];
Ou :
INCREMENT BY n: Pas = n, si n’est pas spécifié,
Pas=1(par défaut) START WITH n : c’est le 1er nombre de la séquence (valeur par
défaut=1)
MAXVALUE n : c’est la valeur max que la séquence peut générer
NOMAXVALUE : (par défaut) un max de 1027.
MINVALUE n : c’est la valeur min de la séquence
NOMINVALUE : (par défaut) un min de –(1027).
CYCLE : la séquence peut générer des valeurs après avoir atteint soit le max soit le min
NOCYCLE : (par défaut) ne peut générer des valeurs si > max et des valeurs < min.
CACHE n : spécifie le nombre (n) de valeurs que le serveur Oracle garde en mémoire
NOCACHE : (par défaut) le serveur Oracle garde 20 valeurs
Créer une séquence
Ne pas utiliser de cycle(ne pas générer des valeurs>MAXVAL et <MINVAL)
CREATE SEQUENCE dept_deptno
INCREMENT BY 1
START WITH 91
MAXVALUE 100
NOCACHE NOCYCLE;
Select dept_deptno.currval from dual;
Select dept_deptno.nextval from dual;
Cette commande crée une séquence ‘Dept_deptno’ qui sera utilisée pour la colonne
deptno de la table DEPT.
La séquence commence par 91
La valeur max = 100
Nocycle : pas de valeur > 100
Nocache : 20 valeurs en mémoire
Table système : USER_SEQUENCES
Ex:
select sequence_name, min_value, max_value, increment_by, last_number
From USER_SEQUENCES
Les pseudocolonnes : NEXTVAL, CURRVAL
Une fois que la séquence créée, on peut l’utiliser pour créer des nombres séquentiels pour
une table, et ce grâce a NEXTVAL et CURRVAL. NEXTVAL : est utilisée pour des
extractions successives de nombres de la séquence spécifiée ‘nom_seq.nextval’.
Une fois que ‘[Link]’ est référencée (exécutée) un nouveau nombre est généré et le
no de séquence courant est placé dans CURRVAL.
NEXTVAL : retourne la valeur suivante de la séquence
CURRVAL : obtient la valeur courante de la séquence
On peut utiliser CURRVAL, NEXTVAL : select, update (set), insert
On ne peut pas utiliser CURRVAL, NEXTVAL :
- select dans une vue
- un select avec distinct
- un select avec group by, having, ordre by
- une sous-requête dans une select, delete ou update
- valeur par défaut (DEFAULT) dans Create Table, Alter Table
Modifier une séquence : ALTER SEQUENCE nom_séquence; (avoir le privilège)
Supprimer une séquence : DROP SEQUENCE nom_séquence;
4. Commentaires sur les tables et les colonnes
COMMENT ON TABLE nom_table | COLUMN [Link]
IS chaîne de caractère;
N.B. : les commentaires sont insérés dans le dictionnaire de données. Leur consultation se
fait à travers les tables suivantes :
- USER_COL_COMMENTS (pour les colonnes)
- USER_TAB_COMMENTS (pour les tables)
Ex: Comment on column [Link] is ‘Numéro identifiant un employé’; Pour supprimer
un commentaire : COMMENT ON COLUMN [Link] IS ‘ ‘;
5. Création des vues
- Une vue est une table logique qui permet l’accès aux données d’une ou plusieurs tables de
façon transparente
- Une vue ne contient aucune lignes, les données sont stockées dans les tables
- Les vues sont utilisées pour
o Fournir un niveau supplémentaire de sécurité sur les tables, ainsi on peut
restreindre pour un utilisateur donné, l’accès à quelques lignes ou colonnes d’une
table
o Masquer la complexité : une table peut être la jointure de N tables
Syntaxe :
CEARTE [OR REPLACE] [FORCE | NOFORCE] VIEW nom_vue
[(alias_col1, alias_col2, …)] AS requête SQL
WITH CHECK OPTION [CONSTRAINT nom_contrainte] [WITH READ
ONLY]
Où :
OR REPLACE : permet de supprimer puis recréer la vue si elle existe déjà
FORCE : ignore les erreurs et crée la vue (même si la ou les table(s) n’existe(nt) pas
(rarement utilisée)
NO FORCE : par défaut (crée la vue uniquement si la ou les table(s) existe(nt))
ALIAS : spécifie les noms des colonnes ou expressions sélectionnées par le requête SQL
(alias)
WITH CHECK OPTION : spécifie que seulement les lignes accessibles par la vue
peuvent être modifiées ou insérées
nom_contrainte : c’est le nom affecté à la contrainte ‘With Check Option’
WITH READ ONLY : spécifie que les opérations DML ne peuvent être utilisées dans la
vue (en lecture seulement)
Remarques:
- La modification d’une table affecte la vue
- Le corps d’une table ne peut contenir la clause ORDER BY ou FOR UPDATE
- On ne peut pas effectuer des insertions, des mise à jour et des suppressions sur
une vue contenant une jointure, des opérations ensemblistes, des fonctions de
groupe, la clause GROUP BY et l’opération DISTINCT
- Pour supprimer une vue : DROP VIEW nom_vue;
- Les tables système : USER_VIEWS et USER_CATALOG (CAT)
Ex1: créer une vue sur la table EMP(empvu10) qui contient 3 colonnes : empno, ename,
job (sélectionner uniquement les employés du département no 10. Donner les alias
aux colonnes.
CREATE VIEW empvu10(num, nom, emploi)
AS select empno, ename, job from EMP where deptno = 10; Verification:
Select * from empvu10; /* on a 3 lignes (dept 10)*/
Si on insère des lignes dans la vue (INSERT INTO empvu10….) les lignes sont ajoutées
dans la table EMP.
Ex2 : créer une vue (dept_vue) sur la table EMP contenant le no dept, le salaire max, min
et moyen par département
CREATE OR REPLACE VIEW dept_vue(num_dept, sal_min, sal_max, sal_moy)
AS select deptno, min(sal), max(sal, avg(sal) from EMP group by deptno;
N.B.: on ne peut pas faire de mise à jour, suppression, insertion car existence de
fonctions de groupe et group by.
Ex3: créer une vue (empvue20) sur la table EMP qui sélectionne tous les employés qui travaillent dans le
dept mo 20. (ajouter la contrainte With Check Option)
CREATE OR REPLACE VIEW empvue20
AS select * from EMP where deptno = 20 with check option; Verification:
- si on veut insérer un employé dont le no dept = 30 alors impossible (with check option)
- si on veut mettre à jour le no dept = 10 pour un employé de la vue ceci est impossible
Ex4: créer une vue (en lecture seulement) empvue10 sur la table EMP (deptno=10) CREATE OR
REPLACE VIEW empnvue10 AS select * from EMP
where deptno = 10 with read only
Cette vue est en lecture seulement, donc on ne peut pas faire des insertions, des mises à jour ou des
suppressions
