Optimisation de requête (niveau hard)

Invité · 11/07/2013, 12h14

Bonjour à tous

Je viens vers vous car j'ai réalisé une requête qui devait récupérer deux informations :
- le nom de la colonne qui contient est la cible d'une requête de clé étrangère
- le nom de la table où se trouve la clé appelée

J'ai, après de longues heures de travail, réussi à faire la requête suivante qui me renvoie les bons résultats mais en 4 secondes !

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
22
23
24
25
26
27
 
select
    col2.column_Name,col.table_name
from 
    dba_cons_columns col, dba_cons_columns col2, dba_constraints cons, dba_constraints cons2
where 
    cons.r_owner = col.owner 
and 
    cons.r_constraint_name = col.constraint_name
and 
    col.owner= 'MASTER_LYON'
and 
    col2.owner= 'MASTER_LYON'
and
    cons.owner ='MASTER_LYON'
and
    cons2.owner ='MASTER_LYON'
and 
    cons.table_name = 'F_ACCEPTATION_DYN'
and
    cons2.table_name = 'F_ACCEPTATION_DYN'
and 
    cons.constraint_name = cons2.constraint_name
and 
    cons2.constraint_name = col2.constraint_name
order by 
    cons.table_name DESC;

Si vous avez des idées sur comment obtenir la même chose en moins de 4 secondes je prends

**mnitu** · 11/07/2013, 14h52

Ajoutez le hint SQL RULE

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
 
Select /*+ RULE*/
...

Invité · 11/07/2013, 15h06

Je ne comprend pas désolé :/

**mnitu** · 11/07/2013, 15h08

Ce n'est pas grave.

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
22
23
24
25
26
27
 
SELECT /*+ RULE */
    col2.column_Name,col.table_name
FROM 
    dba_cons_columns col, dba_cons_columns col2, dba_constraints cons, dba_constraints cons2
WHERE 
    cons.r_owner = col.owner 
AND 
    cons.r_constraint_name = col.constraint_name
AND 
    col.owner= 'MASTER_LYON'
AND 
    col2.owner= 'MASTER_LYON'
AND
    cons.owner ='MASTER_LYON'
AND
    cons2.owner ='MASTER_LYON'
AND 
    cons.table_name = 'F_ACCEPTATION_DYN'
AND
    cons2.table_name = 'F_ACCEPTATION_DYN'
AND 
    cons.constraint_name = cons2.constraint_name
AND 
    cons2.constraint_name = col2.constraint_name
ORDER BY 
    cons.table_name DESC;

Invité · 11/07/2013, 15h40

Je l'ai ajouté, cela ne change rien :/

Invité · 11/07/2013, 16h16

Après de longues heures de réflexion, je pense avoir trouvé une solution beaucoup plus rapide.
Je souhaite faire une jointure entre deux requêtes, voici mes requêtes :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
select 
    *
from
   dba_cons_columns      col, 
   dba_constraints       cons
where 
   cons.r_owner = col.owner
and 
   cons.r_constraint_name = col.constraint_name
and 
   cons.table_name = 'F_ACCEPTATION_DYN'
and 
    col.owner= 'MASTER_LYON';

et

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
 
select *  from dba_cons_columns where table_name= 'F_ACCEPTATION_DYN' and owner= 'MASTER_LYON';

La colonne de la jointure se nommerait constraint_name

**mnitu** · 11/07/2013, 16h17

Chez moi oui (comparez le temps des premières deux exécutions aux suivantes)

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
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
 
Connected to Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 
Connected as mni
 
SQL> set timi on
SQL> 
SQL> SELECT
  2      col2.column_Name,col.table_name
  3  FROM
  4      dba_cons_columns col, dba_cons_columns col2, dba_constraints cons, dba_constraints cons2
  5  WHERE
  6      cons.r_owner = col.owner
  7  AND
  8      cons.r_constraint_name = col.constraint_name
  9  AND
 10      col.owner= 'HR'
 11  AND
 12      col2.owner= 'HR'
 13  AND
 14      cons.owner ='HR'
 15  AND
 16      cons2.owner ='HR'
 17  AND
 18      cons.table_name = 'DEPARTMENTS'
 19  AND
 20      cons2.table_name = 'DEPARTMENTS'
 21  AND
 22      cons.constraint_name = cons2.constraint_name
 23  AND
 24      cons2.constraint_name = col2.constraint_name
 25  ORDER BY
 26      cons.table_name DESC;
 
 
COLUMN_NAME                                                                      TABLE_NAME
-------------------------------------------------------------------------------- ------------------------------
MANAGER_ID                                                                       EMPLOYEES
LOCATION_ID                                                                      LOCATIONS
 
Executed in 2,652 seconds
 
SQL> r
 
COLUMN_NAME                                                                      TABLE_NAME
-------------------------------------------------------------------------------- ------------------------------
MANAGER_ID                                                                       EMPLOYEES
LOCATION_ID                                                                      LOCATIONS
 
Executed in 2,761 seconds
 
SQL> 
SQL> SELECT /*+ RULE */
  2      col2.column_Name,col.table_name
  3  FROM
  4      dba_cons_columns col, dba_cons_columns col2, dba_constraints cons, dba_constraints cons2
  5  WHERE
  6      cons.r_owner = col.owner
  7  AND
  8      cons.r_constraint_name = col.constraint_name
  9  AND
 10      col.owner= 'HR'
 11  AND
 12      col2.owner= 'HR'
 13  AND
 14      cons.owner ='HR'
 15  AND
 16      cons2.owner ='HR'
 17  AND
 18      cons.table_name = 'DEPARTMENTS'
 19  AND
 20      cons2.table_name = 'DEPARTMENTS'
 21  AND
 22      cons.constraint_name = cons2.constraint_name
 23  AND
 24      cons2.constraint_name = col2.constraint_name
 25  ORDER BY
 26      cons.table_name DESC;
 
COLUMN_NAME                                                                      TABLE_NAME
-------------------------------------------------------------------------------- ------------------------------
LOCATION_ID                                                                      LOCATIONS
MANAGER_ID                                                                       EMPLOYEES
 
Executed in 0,312 seconds
 
SQL> r
 
COLUMN_NAME                                                                      TABLE_NAME
-------------------------------------------------------------------------------- ------------------------------
LOCATION_ID                                                                      LOCATIONS
MANAGER_ID                                                                       EMPLOYEES
 
Executed in 0,125 seconds
 
SQL>

Invité · 11/07/2013, 16h25

Effectivement, merci

Cependant je vise un temps maxi de 0.5 s par requête.

Que penses-tu de ma seconde proposition ?

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
 
SELECT 
    *
FROM
   dba_cons_columns      col, 
   dba_constraints       cons
WHERE 
   cons.r_owner = col.owner
AND 
   cons.r_constraint_name = col.constraint_name
AND 
   cons.table_name = 'F_ACCEPTATION_DYN'
AND 
    col.owner= 'MASTER_LYON';

Faire une jointure sur constraint_name

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
 
SELECT *  FROM dba_cons_columns WHERE table_name= 'F_ACCEPTATION_DYN' AND owner= 'MASTER_LYON';

**pacmann** · 11/07/2013, 16h52

Salut,

Pourquoi dans la première requête (de départ) tu fais deux fois la jointure sur les colonnes ? Vu que tu ne définis pas le lien entre elle, cela fait un produit cartésien...

Est-ce que tu peux ré-expliquer ce que tu cherches à faire ?
Un exemple concret des données attendues en sortie, ça serait pas mal

Invité · 11/07/2013, 17h07

Je ne suis pas très fort, j'ai demandé de l'aide à un informaticien pour la première requête (la partie ou on fait appelle à 2 fois les mêmes tables).

Je souhaite obtenir deux des informations présentes que l'on trouve dans le referential d'une table.
http://www.casimages.com/img.php?i=1...0701155731.jpg
Il s'agit de celle du R Table et du Columns.

Pour cela, je dois combiner les tables dba_cons_columns et dba_constraints mais je n'arrive pas à le faire de la sorte que j'en extrais les informations suivantes :
http://www.casimages.com/img.php?i=1...1040933979.jpg

Je pensais utiliser le résultat des deux requêtes cités dans les post précédent afin de faire les liens entre le nom de la colonne et la table de référence.

**pacmann** · 11/07/2013, 17h25

Le plus important, ce n'est pas la méthode qu'on va utiliser. Il faut d'abord arriver à décrire exactement ce qu'on veut.

Je supopse qu'il faut ajouter dans la jointure le critère sur la position de la colonne de la contrainte :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
 
SELECT cons.table_name, col.column_name, cons2.table_name, col2.column_name
FROM dba_constraints cons
  join dba_constraints cons2 on cons.r_constraint_name = cons2.constraint_name
  join dba_cons_columns col on col.constraint_name = cons.constraint_name
  join dba_cons_columns col2 on col2.constraint_name = cons2.constraint_name and col2.position = col.position
WHERE 
    col.owner= 'MASTER_LYON'
AND col2.owner= 'MASTER_LYON'
AND cons.owner ='MASTER_LYON'
AND cons2.owner ='MASTER_LYON'
AND cons.table_name = 'F_ACCEPTATION_DYN'
ORDER BY cons.table_name DESC;

Invité · 12/07/2013, 09h27

Bonjour Pacmann,

Merci pour ton aide, cependant le temps de réalisation de la requête reste toujours le même, aux alentours de 4 secondes :/

Je pense que ma requête est trop lourde par définition, je dois la réaliser plus de 600 fois, cela ne va pas passer

Si tu as d'autres idées, je prend

**pacmann** · 12/07/2013, 09h51

En gros, tu te tapes le boulot à la main pour chacune des 600 tables à traiter ?
Quelle est la liste des tables que tu dois traiter ?

En modifiant un peut la requête, tu peux ajouter un table_name IN (ta_liste).

Invité · 12/07/2013, 10h04

Exactement, en faite je dois construire un plug-in similaire à hibernate mais avec juste les fonctionnalités nécessaires.

J'obtiens ma liste de table à partir de cette requête

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
 
select TABLE_NAME,TABLE_TYPE from cat where table_type='TABLE' and table_name like 'F_%' or table_name like 'T_%' ORDER BY TABLE_NAME ASC ;

**pacmann** · 12/07/2013, 10h06

Et donc, comme ça ?

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
 
SELECT cons.table_name, col.column_name, cons2.table_name, col2.column_name
FROM dba_constraints cons
  JOIN dba_constraints cons2 ON cons.r_constraint_name = cons2.constraint_name
  JOIN dba_cons_columns col ON col.constraint_name = cons.constraint_name
  JOIN dba_cons_columns col2 ON col2.constraint_name = cons2.constraint_name AND col2.position = col.position
WHERE 
    col.owner= 'MASTER_LYON'
AND col2.owner= 'MASTER_LYON'
AND cons.owner ='MASTER_LYON'
AND cons2.owner ='MASTER_LYON'
AND cons.table_name IN (
SELECT 
TABLE_NAME
FROM 
cat 
WHERE 
table_type='TABLE' 
AND table_name LIKE '%F_' OR table_name LIKE '%T_' 
)
ORDER BY cons.table_name DESC;

Invité · 12/07/2013, 10h19

Merci pour la requête, on passe à 20 secondes de temps d'executions

J'utilise celle-ci qui fait exactement la même chose mais en 6 secondes ^^

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
22
23
24
25
26
27
 
select 
    col2.column_Name,col.table_name, cons.constraint_name
from 
    dba_cons_columns col, dba_cons_columns col2, dba_constraints cons, dba_constraints cons2
where 
    cons.r_owner = col.owner 
and 
    cons.r_constraint_name = col.constraint_name
and 
    col.owner= 'MASTER_LYON'
and 
    col2.owner= col.owner
and
    cons.owner =col.owner
and
    cons2.owner =col.owner
--and 
--    cons.table_name = 'F_ACCEPTATION_DYN'    
--and
--    cons2.table_name = 'F_ACCEPTATION_DYN'
and 
    cons.constraint_name = cons2.constraint_name
and 
    cons2.constraint_name = col2.constraint_name
order by 
    cons.table_name ASC;

**pacmann** · 12/07/2013, 10h24

1) il y a dedans toutes les tables, pas seulement celles ciblées dans ta table cat.

2) tu ne fais pas de lien entre les deux listes de colonnes, je suis pas sûr que le résultat te convienne.

3) je n'ai pas vraiment l'impression que tu essaies de comprendre ce que j'essaie de t'expliquer, j'abandonne

Bon courage...

Invité · 12/07/2013, 10h33

1) il y a dedans toutes les tables, pas seulement celles ciblées dans ta table cat.

2) tu ne fais pas de lien entre les deux listes de colonnes, je suis pas sûr que le résultat te convienne.

Entiérement d'accord, seulement le résultat est pertinent. Après réflexion, je pense utiliser ta requête même si plus lourde car elle me prémunira de problèmes futures concernant les tables.

3) je n'ai pas vraiment l'impression que tu essaies de comprendre ce que j'essaie de t'expliquer, j'abandonne

Pas compris pourquoi tu dis cela mais soit, merci pour ton aide en tout cas

**pacmann** · 12/07/2013, 13h41

Le résultat de ta requête n'est bon que si toutes tes FK ne portent que sur une seule colonne.

Trouve une FK qui porte sur plusieurs colonnes, et regarde ce que ta requête renvoie pour cette table, je pense que ça devrait te convaincre.

Optimisation de requête (niveau hard)

SQL Oracle

Discussions similaires

Partager

Partager