Bonjour,

Je travaille avec une base de données Oracle 10g :

Oracle Database 10g Release 10.1.0.5.0 - Production
J'ai deux tables :

Contrats :
CNT (CODSOC, ACHVTE, NUMCNT, ...)

Postes de contrats
CNP (CODSOC, ACHVTE, NUMCNT, TQOI, CODPRO, DATAPP, ...)

En souligné : INDEX UNIQUE faisant office de clé primaire.

Il n'y a pas de notion de clé primaire ni de clé étrangère définie dans la base (oui, je sais, c'est pourri mais c'est comme ça).

Le tuple CNP (CODSOC, ACHVTE, NUMCNT) permet de rattacher des postes à un contrat.

Je dois rechercher tous les contrats (sans doublon) pour lesquels il existe au moins un poste avec DATAPP vide (égale à ' ').

J'ai écrit trois requêtes (existe-t-il d'autres méthodes) ?

Code : Sélectionner tout - Visualiser dans une fenêtre à part
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
 
-- Celle qui vient naturellement
select cnt.numcnt
from cnt
where cnt.codsoc = 100
and cnt.achvte = 'V'
and exists (select null from cnp where cnp.codsoc = cnt.codsoc and cnp.achvte = cnt.achvte and cnp.numcnt = cnt.numcnt and cnp.datapp = ' ');
 
-- Celle qui semblerait-il était plus performance à l'époque de la 8i
select cnt.numcnt
from cnt
where cnt.codsoc = 100
and cnt.achvte = 'V'
and cnt.numcnt in (select cnp.numcnt from cnp where cnp.codsoc = cnt.codsoc and cnp.achvte = cnt.achvte and cnp.numcnt = cnt.numcnt and cnp.datapp = ' ');
 
-- Celle qui devrait être la plus naturelle pour le SGBD
select distinct cnt.numcnt
from cnt
inner join cnp on cnp.codsoc = cnt.codsoc and cnp.achvte = cnt.achvte and cnp.numcnt = cnt.numcnt and cnp.datapp = ' '
where cnt.codsoc = 100
and cnt.achvte = 'V';
Sur un volume de 107648 lignes dans CNT, 125634 dans CNP et 25808 lignes retournées, voici ce que ça donne :

- Les deux premières ont strictement le même plan d'exécution.
- Les trois ont un plan d'exécution qui a le même coût (554).

La troisième requête, pourtant plus naturelle pour le SGBD, est 2 fois plus lente (~0,2 seconde contre ~0,1 seconde pour les deux premières).

La première semble plus rapide que la seconde, alors qu'elle a le même plan d'exécution (en exécutant une dizaine de fois chacune des deux requêtes, la première est presque toujours < 0,1 alors que la seconde est presque toujours > 0,1).

Ce poste est motivé par la volonté de comprendre pourquoi telle syntaxe est plus rapide qu'une autre (alors qu'on fait exactement la même chose) et comment tirer une règle générale lors de l'écriture des requêtes.

Ce qui m'étonne, c'est que j'ai souvenir d'avoir préféré longtemps le EXIST plutôt que le DISTINCT/INNER JOIN et le IN, alors que le IN était plus rapide à une certaine époque (la 8i il me semble, ou la 9g), et que le DISTINCT/INNER JOIN est plus rapide avec SQL Server par exemple.

A part benchmarker chaque requête que j'écris, existe-t-il une règle générale ?