173 lines
4.9 KiB
Plaintext
173 lines
4.9 KiB
Plaintext
|
|
Aller sur pipit/~tancrez/
|
|
pour avoir le sujet.
|
|
|
|
TD02 :
|
|
|
|
Création des tables :
|
|
|
|
|
|
create table film (
|
|
id_film number(4) PRIMARY KEY,
|
|
titre varchar(255),
|
|
annee number(6),
|
|
id_realisateur varchar(255)
|
|
)
|
|
TABLESPACE USERS;
|
|
|
|
create table seance (
|
|
id_seance number(4) PRIMARY KEY,
|
|
heuredebut number(4),
|
|
heurefin number(4),
|
|
id_salle number(4),
|
|
id_film number(4)
|
|
|
|
create table cinema (
|
|
id_cinema number(4) PRIMARY KEY,
|
|
nom varchar(255),
|
|
adresse varchar(255)
|
|
)
|
|
TABLESPACE USERS;
|
|
|
|
create table salle (
|
|
id_salle number(4) PRIMARY KEY,
|
|
nom varchar(255),
|
|
capacite number(3),
|
|
id_cinema number(4)
|
|
)
|
|
TABLESPACE USERS;
|
|
|
|
ALTER TABLE salle
|
|
ADD CONSTRAINT f_id_cinema FOREIGN KEY (id_cinema) REFERENCES cinema(id_cinema);
|
|
|
|
)
|
|
|
|
ALTER TABLE seance
|
|
ADD CONSTRAINT f_id_salle FOREIGN KEY (id_salle) REFERENCES salle(id_salle);
|
|
|
|
ALTER TABLE seance
|
|
ADD CONSTRAINT f_id_film FOREIGN KEY (id_film) REFERENCES film(id_film);
|
|
|
|
|
|
--------------------------------------------------------------------------
|
|
AIDE AIDE AIDE AIDE AIDE
|
|
|
|
http://www.toutenligne.com/index.php?contenu=sql_explain&menu=sql
|
|
|
|
|
|
Aller aussi sur : http://pipit/~3fheitz
|
|
|
|
---------------------------------------------------------------------------
|
|
|
|
|
|
create table PLAN_TABLE (
|
|
statement_id varchar2(30),
|
|
timestamp date,
|
|
remarks varchar2(80),
|
|
operation varchar2(30),
|
|
options varchar2(30),
|
|
object_node varchar2(128),
|
|
object_owner varchar2(30),
|
|
object_name varchar2(30),
|
|
object_instance numeric,
|
|
object_type varchar2(30),
|
|
optimizer varchar2(255),
|
|
search_columns number,
|
|
id numeric,
|
|
parent_id numeric,
|
|
position numeric,
|
|
cost numeric,
|
|
cardinality numeric,
|
|
bytes numeric,
|
|
other_tag varchar2(255),
|
|
partition_start varchar2(255),
|
|
partition_stop varchar2(255),
|
|
partition_id numeric,
|
|
other long,
|
|
distribution varchar2(30))
|
|
tablespace USERS
|
|
;
|
|
|
|
----------------------------------------------------------------------------
|
|
|
|
set linesize 255
|
|
set wrap on
|
|
col acces for a20
|
|
col object_name for a10
|
|
select id, lpad(' ',parent_id+1)||operation acces,options,object_name,parent_id,position
|
|
from plan_table where statement_id ='MonPremierEssai';
|
|
|
|
----------------------------------------------------------------------------
|
|
ensuite taper la commande suivante :
|
|
|
|
explain plan
|
|
set statement_id ='MonPremierEssai'
|
|
for
|
|
select * from cinema where nom='Le rex'
|
|
;
|
|
|
|
Pour finalement vérifier le contenu ainsi générer :
|
|
|
|
select operation,options,object_name,id,parent_id,position
|
|
from plan_table
|
|
where statement_id='MonPremierEssai' order by id
|
|
;
|
|
|
|
CE QUI DONNE :
|
|
|
|
OPERATION OPTIONS OBJECT_NAME ID PARENT_ID POSITION
|
|
------------------------------ ------------------------------ ------------------------------ ---------- ---------- ----------
|
|
SELECT STATEMENT 0
|
|
TABLE ACCESS FULL CINEMA 1 0 1
|
|
|
|
|
|
¤ Pour question 2 on a :
|
|
|
|
explain plan
|
|
set statement_id ='MonSecondEssai'
|
|
for
|
|
SELECT * FROM cinema WHERE id_cinema=1908;
|
|
|
|
set linesize 255
|
|
set wrap on
|
|
col acces for a20
|
|
col object_name for a10
|
|
select id, lpad(' ',parent_id+1)||operation acces,options,object_name,parent_id,position
|
|
from plan_table where statement_id ='MonSecondEssai';
|
|
|
|
¤ Ce qui donne :
|
|
|
|
ID ACCES OPTIONS OBJECT_NAM PARENT_ID POSITION
|
|
---------- -------------------- ------------------------------ ---------- ---------- ----------
|
|
0 SELECT STATEMENT
|
|
1 TABLE ACCESS BY INDEX ROWID CINEMA 0 1
|
|
2 INDEX UNIQUE SCAN SYS_C00243 1 1
|
|
9
|
|
|
|
¤ Pour les jointures on a :
|
|
|
|
explain plan
|
|
set statement_id ='MonTroisiemeEssai'
|
|
for
|
|
SELECT cinema.nom, capacite
|
|
FROM cinema, salle WHERE cinema.id_cinema=salle.id_cinema;
|
|
|
|
set linesize 255
|
|
set wrap on
|
|
col acces for a20
|
|
col object_name for a10
|
|
select id, lpad(' ',parent_id+1)||operation acces,options,object_name,parent_id,position
|
|
from plan_table where statement_id ='MonTroisiemeEssai';
|
|
|
|
¤ Ce qui donne :
|
|
|
|
ID ACCES OPTIONS OBJECT_NAM PARENT_ID POSITION
|
|
---------- -------------------- ------------------------------ ---------- ---------- ----------
|
|
0 SELECT STATEMENT
|
|
1 NESTED LOOPS 0 1
|
|
2 TABLE ACCESS FULL SALLE 1 1
|
|
3 TABLE ACCESS BY INDEX ROWID CINEMA 1 2
|
|
4 INDEX UNIQUE SCAN SYS_C00243 3 1
|
|
9
|
|
|