foreign key non indexé [Résolu]

[olivanto]

bonjour,

je suis sur une 9iR2, sous windows.

Je cherche à trouver dans ma base les foreign key qui ne sont pas indexées (il y a une heure, je ne savais même pas que c'était possible...)

Quelqu'un aurait'il une méhode ou une requête pour m'éviter de chercher table à table ?

Intérêt : il semblerait qu'Oracle vérouille toute la table lors de mises à jour d'un champ en foreign key non indéxé !!

Pour les verrous, combine de temps Oracle garde t'il un verrou avant d'annuler la transaction (cas d'un utilsiateur en modification qui sort brutalement !) ; peut'on régler ce "timer" ??

[kalyparker]

Bonjour,

peut être une piste :
la table ALL_CONSTRAINTS possède une colonne constraint_type qui losqu'elle vaut P concerne les Primary key, et losqu'elle vaut R correspond aux foreign key.

[McM]

Pour la liste des Foreign key non indexés, Asktom est ton ami.

http://asktom.oracle.com/tkyte/unindex/index.html

J'aime bien sa façon de faire en 1 select.

Code :

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
COLUMN COLUMNS format a20 word_wrapped
COLUMN table_name format a30 word_wrapped
 
SELECT decode( b.table_name, NULL, '****', 'ok' ) STATUS, 
	   a.table_name, a.COLUMNS, b.COLUMNS
FROM 
( SELECT substr(a.table_name,1,30) table_name, 
		 substr(a.constraint_name,1,30) constraint_name, 
	     max(decode(position, 1,     substr(column_name,1,30),NULL)) || 
	     max(decode(position, 2,', '||substr(column_name,1,30),NULL)) || 
	     max(decode(position, 3,', '||substr(column_name,1,30),NULL)) || 
	     max(decode(position, 4,', '||substr(column_name,1,30),NULL)) || 
	     max(decode(position, 5,', '||substr(column_name,1,30),NULL)) || 
	     max(decode(position, 6,', '||substr(column_name,1,30),NULL)) || 
	     max(decode(position, 7,', '||substr(column_name,1,30),NULL)) || 
	     max(decode(position, 8,', '||substr(column_name,1,30),NULL)) || 
	     max(decode(position, 9,', '||substr(column_name,1,30),NULL)) || 
	     max(decode(position,10,', '||substr(column_name,1,30),NULL)) || 
	     max(decode(position,11,', '||substr(column_name,1,30),NULL)) || 
	     max(decode(position,12,', '||substr(column_name,1,30),NULL)) || 
	     max(decode(position,13,', '||substr(column_name,1,30),NULL)) || 
	     max(decode(position,14,', '||substr(column_name,1,30),NULL)) || 
	     max(decode(position,15,', '||substr(column_name,1,30),NULL)) || 
	     max(decode(position,16,', '||substr(column_name,1,30),NULL)) COLUMNS
    FROM user_cons_columns a, user_constraints b
   WHERE a.constraint_name = b.constraint_name
     AND b.constraint_type = 'R'
   GROUP BY substr(a.table_name,1,30), substr(a.constraint_name,1,30) ) a, 
( SELECT substr(table_name,1,30) table_name, substr(index_name,1,30) index_name, 
	     max(decode(column_position, 1,     substr(column_name,1,30),NULL)) || 
	     max(decode(column_position, 2,', '||substr(column_name,1,30),NULL)) || 
	     max(decode(column_position, 3,', '||substr(column_name,1,30),NULL)) || 
	     max(decode(column_position, 4,', '||substr(column_name,1,30),NULL)) || 
	     max(decode(column_position, 5,', '||substr(column_name,1,30),NULL)) || 
	     max(decode(column_position, 6,', '||substr(column_name,1,30),NULL)) || 
	     max(decode(column_position, 7,', '||substr(column_name,1,30),NULL)) || 
	     max(decode(column_position, 8,', '||substr(column_name,1,30),NULL)) || 
	     max(decode(column_position, 9,', '||substr(column_name,1,30),NULL)) || 
	     max(decode(column_position,10,', '||substr(column_name,1,30),NULL)) || 
	     max(decode(column_position,11,', '||substr(column_name,1,30),NULL)) || 
	     max(decode(column_position,12,', '||substr(column_name,1,30),NULL)) || 
	     max(decode(column_position,13,', '||substr(column_name,1,30),NULL)) || 
	     max(decode(column_position,14,', '||substr(column_name,1,30),NULL)) || 
	     max(decode(column_position,15,', '||substr(column_name,1,30),NULL)) || 
	     max(decode(column_position,16,', '||substr(column_name,1,30),NULL)) COLUMNS
    FROM user_ind_columns 
   GROUP BY substr(table_name,1,30), substr(index_name,1,30) ) b
WHERE a.table_name = b.table_name (+)
  AND b.COLUMNS (+) LIKE a.COLUMNS || '%'
/

[olivanto]

merci pour l'éfficacité !

Je m'aperçois que j'ai un petit paquet de FK non indéxées ; vous êtes dans le même cas que moi où je suis le seul boulet à avoir une base pourrie ?

[McM]

En fait ça dépend pas mal de ta base de donnée.
Un gars d'oracle (suite à des pb de perfs) nous a dit : Tous ces indexes sur les FK qui ne servent jamais : A supprimer. Trop d'indexes pourrissent aussi la base.

[olivanto]

il a dit çà ??? je croyais qu'au contraire, c'était le fait de ne pas avoir d'index sur les FK qui générait des soucis (full scan, locks )

[McM]

Oui les locks... mais bon, si tu as 20 indexes sur ta table, chaque insertion/delete dans ta table va mettre à jour les indexes, les trier, etc...
Faut voir si les références maitres sont souvent modifiées/supprimées

[Wurlitzer]

Moi je suis partisan d'indexer toute les FK. (t inquiete pas t'es pas le seul a avoir une base pourrie). Les lock de niveau table sont tres pénalisant, je ne parle meme pas des éventuelles FK en delete cascade.

Partir dans une analyse, référence souvent modifié ou non me parait complètement irréaliste ! !

En plus c'est quoi la conclusion. Si la référence est souvent modifié c'est a dire que l'on a souvent besoin de l'index puisque qu'il y a souvent des problème avec les lock mais c'est aussi dans ce cas que l'on met souvent a jour les index et donc que c'est "couteux" en terme de perf.

C'est mon avis et je le partage....

[olivanto]

bon, donc la meilleure solution semble bien d'indexer les FK ; mais pourquoi Oracle n'indexe pas par défaut dans ce cas ??

[Wurlitzer]

Grande question metaphysique à la quelle je n'ai toujousr pas trouvé de réponse

[olivanto]

bon bon ... merci pour tout !

Moi et ma base pourrie, on a du pain sur la planche ...