[Oracle] Optimisation sql

Version imprimable

16/02/2009, 14h00
darkangel23fr

[Oracle] Optimisation sql
Rebonjour!

Bon, j'ai une requete qui prend des heures a s'executer et j'aimerais savoir s'il n'y a pas plus rapide:
Code:

1 2 3 4 SELECT distinct TO_CHAR(ctr.matricule) || ';' FROM contrats ctr, individus ind WHERE ctr.matricule NOT IN (SELECT matricule FROM individus);
Le but de cette requete est de lister tous les elements de la table contrats qui ne se trouvent pas dans la table individus.

Si vous pouvez m'aider, c'est genial!

Merci d'avance

Houla, joli produit cartésien.

Code:

1
2
3
4
SELECT TO_CHAR(ctr.matricule) || ';' as Matricule
FROM contrats ctr
WHERE NOT EXISTS (SELECT NULL FROM individus idv
                  WHERE ctr.matricule = idv.matricule)

16/02/2009, 14h25
orafrance

de toute façon faudra parcourir toute la table individus donc le NOT EXISTS j'suis pas sûr qu'il donne grand chose :?
16/02/2009, 14h25
darkangel23fr

Merci vraiment sincerement pour ta réponse, les délais n'ont effectivement rien a voir. Je suis desolé de poser ces questions un peu betes des fois mais au boulot, ils ont décidé de me donner un projet de requetes sql sous oracle a faire et je n'y connais rien (c'est pas faute de leur avoir dit)... en plus, ca doit etre terminé pour demain... Super.

J'aurais surement encore des questions a poser, désolé d'avance!

Merci encore
16/02/2009, 14h44
darkangel23fr

Pour OraFrance:

La requete que waldar m'a donné n'est donc pas bonne ou bien c'est juste en terme de délais que tu me dit que le Not Exist ne donne pas grand chose?

C'est correct, c'est vrai que l'écriture EXISTS est plus propice dans le sens vrai que faux.
Mais j'ai testé sur deux tables qui ont des volumétries significatives, le plan d'exécution est le même (et le temps de réponse est de deux secondes).
Je précise que ID_TABLE_1 est la PK de TABLE_1 (pas d'index sur ID_TABLE_2), le SGBD est Oracle 11.1.0.6.

NOT EXISTS

Code:

1
2
3
4
5
6
7
8
9
10
select count(*) from TABLE_1
where not exists (select null from TABLE_2 
                  where ID_TABLE_2 = ID_TABLE_1)
 
-- Explain plan :
SELECT STATEMENT  ALL_ROWSCost: 20,435  Bytes: 10  Cardinality: 1  			
	4 SORT AGGREGATE  Bytes: 10  Cardinality: 1  		
		3 HASH JOIN ANTI  Cost: 20,435  Bytes: 6,849,770  Cardinality: 684,977  	
			1 INDEX FAST FULL SCAN INDEX (UNIQUE) ID_TABLE_1 Cost: 315  Bytes: 4,002,485  Cardinality: 800,497  
			2 TABLE ACCESS FULL TABLE TABLE_2 Cost: 17,924  Bytes: 11,682,180  Cardinality: 2,336,436

NOT IN

Code:

1
2
3
4
5
6
7
8
9
select count(*) from TABLE_1
where ID_TABLE_1 not in (select ID_TABLE_2 from TABLE_2)
 
-- Explain plan :
SELECT STATEMENT  ALL_ROWSCost: 20,435  Bytes: 10  Cardinality: 1  			
	4 SORT AGGREGATE  Bytes: 10  Cardinality: 1  		
		3 HASH JOIN ANTI NA  Cost: 20,435  Bytes: 6,849,770  Cardinality: 684,977  	
			1 INDEX FAST FULL SCAN INDEX (UNIQUE) ID_TABLE_1 Cost: 315  Bytes: 4,002,485  Cardinality: 800,497  
			2 TABLE ACCESS FULL TABLE TABLE_2 Cost: 17,924  Bytes: 11,682,180  Cardinality: 2,336,436

La seule différence est sur le hash join anti, qui est NA dans un cas et pas NA dans un autre, mais je n'ai aucune idée de la différence.

Tant que j'y suis voici la méthode jointure externe.

OUTER JOIN

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
select count(*)
from TABLE_1 left outer join TABLE_2
      on ID_TABLE_2 = ID_TABLE_1
where PK_TABLE_2 is null
 
-- Explain plan :
Plan
SELECT STATEMENT  ALL_ROWSCost: 21,006  Bytes: 16  Cardinality: 1  				
	5 SORT AGGREGATE  Bytes: 16  Cardinality: 1  			
		4 FILTER  		
			3 HASH JOIN OUTER  Cost: 21,006  Bytes: 16  Cardinality: 1  	
				1 INDEX FAST FULL SCAN INDEX (UNIQUE) ID_TABLE_1 Cost: 315  Bytes: 4,002,485  Cardinality: 800,497  
				2 TABLE ACCESS FULL TABLE TABLE_2 Cost: 17,924  Bytes: 25,700,796  Cardinality: 2,336,436

16/02/2009, 15h21
orafrance

Citation:

Envoyé par darkangel23fr

Pour OraFrance:

La requete que waldar m'a donné n'est donc pas bonne ou bien c'est juste en terme de délais que tu me dit que le Not Exist ne donne pas grand chose?

Je ne pensais pas que sur un parcours complet de la table tu aurais un gain de perf significatif, mais tant mieux si c'est le cas ;)
16/02/2009, 15h24
Waldar

Orafrance, vous avez du rater son produit cartésien dans son premier from, c'est celà qui plombe ses performances !

Pour le NA, celà signifie Null Aware et c'est une méthode d'optimisation d'Oracle pour traiter les NOT IN justement.

Un article ici : http://structureddata.org/2008/05/22...are-anti-join/