BUT2/BD/Lekpa/TP_Optimisation2/Reponse.sql

--Exercice 1

CREATE INDEX ix_cl ON CLIENT(id);
/*
impossible d'inserer cet index parce que id est la clef primaire.
et les clef primaire ont deja un index
*/



--Exercice 2

SET AUTOTRACE ON
SELECT * FROM LIGNE_COMMANDE WHERE COMMANDE_ID=46;
--observation

CREATE INDEX ix_commande_id ON LIGNE_COMMANDE (COMMANDE_ID,
PRODUIT_ID) ;

SET AUTOTRACE ON
SELECT * FROM LIGNE_COMMANDE WHERE COMMANDE_ID=46;
--observation: l'index est sur la colonne COMMANDE_ID donc c'est beaucoup plus efficace

SELECT PRODUIT_ID FROM LIGNE_COMMANDE WHERE
COMMANDE_ID = 46;
--observation: l'index est sur la colonne COMMANDE_ID et PRODUIT_ID donc c'est beaucoup plus efficace

DROP INDEX ix_commande_id;



--Exercice 3

SELECT cl.nom, SUM(lc.prix_total)
FROM CLIENT cl
INNER JOIN COMMANDE c ON c.client_id = cl.id
INNER JOIN LIGNE_COMMANDE lc ON lc.commande_id = c.id
GROUP BY cl.nom;

SELECT cl.nom, SUM(lc.prix_total) FROM CLIENT cl INNER JOIN COMMANDE c ON c.client_id = cl.id INNER JOIN LIGNE_COMMANDE lc ON lc.commande_id = c.id GROUP BY cl.nom;

--on utilise ici des hash join pour les jointures.
--les tables sont lu integralement

CREATE index test1 on COMMANDE(client_id);
CREATE index test2 on LIGNE_COMMANDE(commande_id);

DROP INDEX test1;
DROP INDEX test2;



--Exercice 4

SELECT * FROM LIGNE_COMMANDE WHERE PRODUIT_ID IN (SELECT ID FROM PRODUIT WHERE PRIX_UNITAIRE>100);
SELECT L.* FROM LIGNE_COMMANDE L JOIN PRODUIT P ON (L.PRODUIT_ID=P.ID) WHERE P.PRIX_UNITAIRE>100;
--parreille 13 de cout et 46 de consistents get
SELECT * FROM LIGNE_COMMANDE lc WHERE EXISTS (SELECT * FROM PRODUIT p WHERE p.id=lc.produit_id AND p.prix_unitaire > 100);
-- 93 consistent gets

--il doit y avoir une erreur quelque part. Mais voici du plus efficace au moins efficace:
-- 1) EXISTS / NOT EXISTS
-- 2) JOIN ON
-- 3) WHERE IN



--Exercice 5

CREATE TABLE BIGBITMAP(
    id NUMBER PRIMARY KEY,
    nbitmap NUMBER,
    date_insertion DATE
);

BEGIN
for id in 0..300000 LOOP
INSERT INTO BIGBITMAP VALUES
( id,
MOD(id,5) ,
TO_DATE(sysdate, 'DD/MM/YYYY')
);
Commit;
END LOOP;
dbms_output.put_line ('100000 lignes inserees');
END;

CREATE INDEX ix_nbitmap_tree ON BIGBITMAP (nbitmap);
DROP INDEX ix_nbitmap_tree;

CREATE BITMAP INDEX ix_nbitmap_bitmap ON BIGBITMAP (nbitmap);
DROP INDEX ix_nbitmap_bitmap;

/*
    218  recursive calls
	0    db block gets
    3741 consistent gets
    1568 physical reads
	  0  redo size
	335  bytes sent via SQL*Net to client
	 41  bytes received via SQL*Net from client
	  2  SQL*Net roundtrips to/from client
	  5  sorts (memory)
	  0  sorts (disk)
	  1  rows processed
*/

/*
	  0  recursive calls
	  0  db block gets
       2558  consistent gets
	  0  physical reads
	  0  redo size
	335  bytes sent via SQL*Net to client
	 41  bytes received via SQL*Net from client
	  2  SQL*Net roundtrips to/from client
	  0  sorts (memory)
	  0  sorts (disk)
	  1  rows processed
*/
/*
52  recursive calls
	  0  db block gets
	127  consistent gets
	 35  physical reads
	  0  redo size
	335  bytes sent via SQL*Net to client
	 41  bytes received via SQL*Net from client
	  2  SQL*Net roundtrips to/from client
	  0  sorts (memory)
	  0  sorts (disk)
	  1  rows processed
*/