TP1: Optimisation de
BD
Ecole Marocaine des sciences de
l’ingénieur
Mohammed Bagour | Anas Timouch
1
2
Objectifs :
Partie 1 :
1.de créer une table destinée à contenir toutes les informations relatives
à un plan d'exécution.
2.d'exécuter une requête en demandant le stockage des explications
relatives à cette requête dans la table précédemment crée
3.d'interroger la table précédemment remplie pour connaître le plan
d'exécution. On utilise la commande EXPLAIN PLAN, et on choisit un
identifiant qui caractérisera le résultat de la commande et on indique la
requête qu’on souhaite analyser.
Partie 2 :
Créer deux tables.
La première table intitulée « emp » contient 14 entrées et la deuxième «
dept » en contient 4 lignes à partir du schéma scott.
Ajouter les deux clés primaires : PK_DEPT sur la table DEPT, et
PK_EMP sur la table EMP.
Créer deux indexes : Ind_DEPTNO sur la colonne DEPTNO de la
table EMP et Ind_JOB sur la colonne JOB de la table EMP.
Afficher et analysez le plan d’exécution physique des requêtes.
5IIR- EMSI Tanger
3
Réalisation :
1) Création des Tables
Figure 1 : creation TABLES
5IIR- EMSI Tanger
4
Figure 3 : création TABLES
Figure 2 : alimentation TABLES
5IIR- EMSI Tanger
5
Création des INDEX :
Figure 4 : création INDEX
Analyse du plan d’exécution physique des requêtes :
1) Requête 1 : SELECT * FROM emp;
Figure 5 : Plan d'execution REQ1
5IIR- EMSI Tanger
6
sql> ALTER SESSION SET STATISTICS_LEVEL = ALL;
sql> EXPLAIN PLAN SET STATEMENT_ID = ‘MyQuery1’ FOR SELECT * FROM emp;
sql> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY(‘PLAN_TABLE’,’MyQuery1’,’ALL’));
2) Requête 2 : SELECT * FROM emp WHERE ROWID = 'à saisir ';
Figure 6 : Plan d'éxecution REQ2
5IIR- EMSI Tanger
7
sql> ALTER SESSION SET STATISTICS_LEVEL = ALL;
sql> EXPLAIN PLAN SET STATEMENT_ID = ‘MyQuery2’ FOR SELECT * FROM emp
WHERE ROWID = 'à saisir ';
sql> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY(‘PLAN_TABLE’,’MyQuery2’,’ALL’));
3) Requete 3: SELECT * FROM emp WHERE empno = 7900;
Figure 7 : plan d'execution REQ3
5IIR- EMSI Tanger
8
sql> ALTER SESSION SET STATISTICS_LEVEL = ALL;
sql> EXPLAIN PLAN SET STATEMENT_ID = ‘MyQuery2’ FOR SELECT * FROM emp
WHERE empno = 7900;
sql> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY(‘PLAN_TABLE’,’MyQuery3’,’ALL’));
4) Requete 4: SELECT * FROM emp, dept WHERE emp.deptno = dept.deptno;
Figure 8 : plan d'execution REQ4
5IIR- EMSI Tanger
9
sql> ALTER SESSION SET STATISTICS_LEVEL = ALL;
sql> EXPLAIN PLAN SET STATEMENT_ID = ‘MyQuery2’ FOR SELECT * FROM emp, dept
WHERE emp.deptno = dept.deptno
sql> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY(‘PLAN_TABLE’,’MyQuery4’,’ALL’));
5) Requete 5 : SELECT * FROM emp, dept WHERE emp.deptno =
dept.deptno AND emp.empno = 7900;
Figure 9 : plan d'execution REQ 5
5IIR- EMSI Tanger
10
Figure 10 : Plan d'execution REQ 5
sql> ALTER SESSION SET STATISTICS_LEVEL = ALL;
sql> EXPLAIN PLAN SET STATEMENT_ID = ‘MyQuery2’ FOR SELECT * FROM emp,
dept WHERE emp.deptno = dept.deptno AND emp.empno = 7900;
sql> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY(‘PLAN_TABLE’,’MyQuery5’,’ALL’));
5IIR- EMSI Tanger
11
Requete 6: SELECT * FROM emp ORDER BY empno;
Figure 11 : Plan d'execution REQ 6
sql> ALTER SESSION SET STATISTICS_LEVEL = ALL;
sql> EXPLAIN PLAN SET STATEMENT_ID = ‘MyQuery2’ FOR SELECT * FROM emp
ORDER BY empno;
sql> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY(‘PLAN_TABLE’,’MyQuery6’,’ALL’));
5IIR- EMSI Tanger
12
Requete 7: SELECT * FROM emp WHERE job = 'CLERK' OR deptno = 10;
Figure 12 : plan d'execution REQ 7
sql> ALTER SESSION SET STATISTICS_LEVEL = ALL;
sql> EXPLAIN PLAN SET STATEMENT_ID = ‘MyQuery2’ SELECT * FROM emp WHERE
job = 'CLERK' OR deptno = 10;
sql> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY(‘PLAN_TABLE’,’MyQuery7’,’ALL’));
5IIR- EMSI Tanger
13
Requete 8 : SELECT * FROM emp WHERE job = 'CLERK' UNION ALL SELECT * FROM
emp WHERE deptno = 10 AND job
<> 'CLERK';
5IIR-
Figure 13 : EMSI Tanger
plan d'execution REQ8
14
sql> ALTER SESSION SET STATISTICS_LEVEL = ALL;
sql> EXPLAIN PLAN SET STATEMENT_ID = ‘MyQuery2’ FOR SELECT * FROM emp
WHERE job = 'CLERK' UNION ALL SELECT * FROM emp WHERE deptno = 10 AND job
<> 'CLERK';
sql> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY(‘PLAN_TABLE’,’MyQuery8’,’ALL’));
Requete 9: SELECT dname, deptno FROM dept WHERE NOT EXISTS (SELECT deptno
FROM emp WHERE dept.deptno = emp.deptno);
Figure 14 : Plan d'execution REQ9
5IIR- EMSI Tanger
15
sql> ALTER SESSION SET STATISTICS_LEVEL = ALL;
sql> EXPLAIN PLAN SET STATEMENT_ID = ‘MyQuery2’ FOR SELECT dname, deptno
FROM dept WHERE NOT EXISTS (SELECT deptno FROM emp WHERE dept.deptno =
emp.deptno);
sql> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY(‘PLAN_TABLE’,’MyQuery9’,’ALL’));
5IIR- EMSI Tanger
