Discussion :

[JB4096]

Bonsoir,

je souhaiterais proposer diverses langues à mes visiteurs. Pour se faire j'impose une langue de référence (anglais - en) et propose diverses autres dont la traduction est souhaitable (français - fr, par exemple).

Ainsi souhaiterais-je via une requête recueillir l'ensemble des catégories présentes dans la base de donnée pour une langue donnée (pour l'exemple, français). Toutefois, s'il venait à manquer une traduction, je souhaiterais que la requête aille piocher dans la langue de référence, l'anglais (qui se doit d'être complète).

Pourriez-vous m'aider à optimiser ce gouffre à performances ?

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 
  JB.JB__LC.I,
  JB.JB__LC.N
FROM
  JB.JB__LC
WHERE
  JB.JB__LC.C = 'fr'
 
UNION
 
SELECT 
  JB.JB__LC.I,
  JB.JB__LC.N
FROM
  JB.JB__LC
WHERE
  JB.JB__LC.C = 'en' AND
  JB.JB__LC.I
 
  NOT IN
 
  (SELECT
  JB.JB__LC.I
  FROM
  JB.JB__LC
  WHERE
  JB.JB__LC.C = 'fr')

Voici le schéma des deux tables :

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
Oracle Express Edition - indexé et contraint comme il se doit.
 
JB__SC (comme JB__Structure of categories, simple hièrarchie id/parent_id) :
 
"I";"P" - Id, parent_id.
"1";"0"
"2";"1"
"3";"1"
"4";"3"
 
JB__LC (comme JB__Localized Categories, ensemble des traductions) :
 
"I";"N";"D";"C" - Id, nom, description, code langue.
"1";"Produits";"null";"fr"
"2";"Fleurs";"null";"fr"
"3";"Graines";"null";"fr"
"1";"Products";"null";"en"
"2";"Flowers";"null";"en"
"3";"Seeds";"null";"en"
"4";"Herb seeds";"null";"en" <= veuillez noter que 4 n'a pas de traduction francophone.

Code :

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

1
2
3
4
5
Résultat espéré :
"1";"Produits"
"2";"Fleurs"
"3";"Graines"
"4";"Herb seeds"

Question subsidiaire : habitué de Firebird, j'utilisais jusqu'alors la représentation intervallaire pour classer un arbre (bornes gauche/droite), toutefois Oracle semble proposer une fonction native pour parcourir ceux-ci (Start with / connect by), est-ce plus performant ? Ou peut-être existe-t-il une méthode encore plus performante via Oracle ?

N'hésitez pas à me conseiller des tutoriels/livres/.sql sur ces deux "problèmes" (plurilinguisme et hièrarchie) sous Oracle, Google me boude.

Merci beaucoup pour votre aide.

[pifor]

Oracle supporte le multilinguisme (cela s'appelle globalization) dans les bases de données pour les jeux de caractères, les messages d'erreur, les formats de date, heure, monnaie, etc. Cependant cela ne comprend pas la traduction des données applicatives en différentes langues.

Pour optimiser une requête qui utilise IN, voir la section 11.5.3.4 Use of EXISTS versus IN for Subqueries du Performance Tuning Guide.

Si cela ne suffit pas, il faudrait donner le maximum d'informations possibles:

• le DDL de création de la table et des index
• le code SQL complet de la requête concernée
• le résultat de EXPLAIN PLAN pour la requête
• le résultat de TKPROF sur l'exécution de la requête (après SQL_TRACE=TRUE)
• la façon dont vous calculez les statistiques pour les objets concernés

[Garuda]

Pour la traduction, l'idée est de faire une jointure externe de la table JB__LC sur elle même.
Si le champ "N" (nom) est null pour le francais (dans T2), alors on prend la valeur pour l'anglais (dans T1)

Code :

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

1
2
3
4
 
SELECT t1.i, NVL (t2.n, t1.n) "N"
  FROM jb__lc t1, jb__lc t2
 WHERE t2.i(+) = t1.i AND t2.c(+) = 'fr' AND t1.c = 'en'

NB : Cette solution ne marche que pour les valeurs de "I" ayant un nom en anglais.
Hope it helps

[laurentschneider]

je verrais assez

Code :

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

1
2
3
4
5
6
7
8
9
10
 
select i,
  max(n) keep (
    dense_rank first order by 
      case c 
        when 'fr' then 1 
        when 'en' then 2
      end) n
from jb__lc
group by i;

Code :

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

1
2
3
4
5
6
7
 
         I N         
---------- ----------
         1 Produits  
         2 Fleurs    
         3 Graines   
         4 Herb seeds

[laurentschneider]

Question subsidiaire : habitué de Firebird, j'utilisais jusqu'alors la représentation intervallaire pour classer un arbre (bornes gauche/droite), toutefois Oracle semble proposer une fonction native pour parcourir ceux-ci (Start with / connect by), est-ce plus performant ? Ou peut-être existe-t-il une méthode encore plus performante via Oracle ?

N'hésitez pas à me conseiller des tutoriels/livres/.sql sur ces deux "problèmes" (plurilinguisme et hièrarchie) sous Oracle, Google me boude.

pour les comparaisons entre arbres et pour une approche mathématique du problème, j'ai récemment lu sql design pattern de http://vadimtropashko.wordpress.com
il faut s'accrocher, c'est assez scientifique comme bouquin, mais je peux imaginer que ça te plaise.

quand à la question du connect by et de sa performance, je peux te dire que c'est largement plus performant qu'une fonction "maison" et en plus, il y a beaucoup de nouveautés dans les versions récentes (nocycle, root, isleaf)

[JB4096]

Bonsoir,

et merci Pifor, je vais me renseigner sur Globalization. Je pensais faire de même en ajoutant des champs à "JB__SL", la table contenant les données relatives aux langues.


Merci Garuda & Laurent pour vos solutions ! Je suis actuellement entrain de les essayer avec diverses volumétries mais aussi bien pour l'une que pour l'autre TOAD m'alerte que :

Problematic SQL Statement
Full table scan with table size larger than the
Problematic SQL Full Table Scan Threshold (8
Kbytes).(JB.JB__LC 64)

Après quelques recherches sur Google (maigres), il semblerait que cette alerte survienne en raison d'une mauvaise structure dans une clause WHERE. Évidemment, la solution proposée (via Google : inverser recherche et résultat espéré soit : 1 = X au lieu de X = 1) ne solutionne pas le problème, auriez-vous une idée ?


Laurent, je m'étais intéressé aux écrits de Vadim Tropashko sur DBAzine (notamment Trees in SQL: Nested Sets and Materialized Path), pensez-vous que ce modèle soit le plus viable ?

Votre blog semble proposer de nombreux petits "trucs" bien utiles, aussi attends-je votre livre avec enthousiasme, celui-ci pourrait sans doute m'aider à retrouver mes marques dans les procédures stockées.


Encore merci à tous pour vos conseils.
Oracle semble proposer tellement de solutions que je m'y perds très vite.

[laurentschneider]

tout d'abord, mon statement n'a rien de "problématique", si tu sélectionne toute la table, alors tu fais un full-table-scan, logique non?

Si tu sélectionnes seulement quelques lignes (where i between 1 and 4), alors tu devrais pouvoir employer l'index. J'employe justement cette méthode dans un projet actuel et je t'assure que c'est une bombe

Hélas mon livre ne traite pas des procédures (plsql) mais des requêtes select (sql).

Quant au livre de Vadim, il est essentiel que le lecteur teste par lui-même toutes les solutions proposées, Vadim compare des méthodes, au niveau de la logique, pas au niveau de la mise en production

[JB4096]

Merci pour vos réponses.
En effet, ajouter une clause Where pour restreindre la requête anihile l'alerte.

Je me suis repenché sur la solution de Vadim Tropashko hier soir et vais faire de même ce soir en développant quelques procédures ou fonctions.

[JB4096]

Bonsoir, je me permets de revenir vers vous pour une question sans doute très bête.

Est-il possible de réemployer un champ nouvellement crée (x) comme argument de sys_connect_by_path ?

Code :

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

1
2
3
4
5
6
7
8
9
select i,
  max(n) keep (
    dense_rank first order by 
      case c 
        when 'fr' then 1 
        when 'en' then 2
      end) n
from jb__lc
group by i;

Code :

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

1
2
3
4
5
6
7
8
9
10
select i,
  max(n) keep (
    dense_rank first order by 
      case c 
        when 'fr' then 1 
        when 'en' then 2
      end) x, sys_connect_by_path (x, '/')
from jb__lc
group by i;

Merci,
JB.

[laurentschneider]

1) tu ne peux pas employer sys_connect_by_path sans connect by prior
2) tu ne peux pas employer d'alias dans une fonction, il te faudrait alors une sous-requête

[JB4096]

Il y a bien sûr un connect by mais j'ai bêtement recopié le [code] précédent.

Merci beaucoup, c'est la réponse que je craignais.

[JB4096]

Voici donc la requête :

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     S.I,
           (LPAD(' ', (LEVEL-1)*4) || (MAX (L.N)KEEP (DENSE_RANK FIRST ORDER BY CASE L.L
                WHEN 'FR'
                   THEN 1
                WHEN 'EN'
                   THEN 2
             END)
           )) N,
           MAX (SYS_CONNECT_BY_PATH (L.N, '/')) P
      FROM JB__SC S INNER JOIN JB__LC L ON S.I = L.I
CONNECT BY PRIOR S.I = S.P
START WITH 1 = S.I
  GROUP BY S.I, LEVEL
  ORDER BY MAX(ROWNUM)

Ainsi que le résultat (selon les données proposées par SQLPro) et le problème :

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
I	N			P
---------------------------------------------------------------------
1	Transport		/Transport
2	    Terrestre		/Transport/Terrestre
5	        Voiture		/Transport/Terrestre/Voiture
6	        Camion		/Transport/Terrestre/Camion
7	        Moto		/Transport/Terrestre/Moto
18	            Trail	/Transport/Terrestre/Moto/Trail
19	            Side-car	/Transport/Terrestre/Moto/Side-car
8	        Vélo		/Transport/Terrestre/Vélo
3	    Marin		/Transport/Marin
15	        Voilier		/Transport/Marin/Voilier
16	        Paquebot	/Transport/Marin/Paquebot
17	        Planche à voile	/Transport/Marin/Windsurf <- Il sélectionne la valeur anglaise.
4	    Aérien		/Transport/Aérien
9	        Hélico		/Transport/Aérien/Hélico
10	        Avion		/Transport/Aérien/Avion
20	            Civil	/Transport/Aérien/Avion/Civil
21	            Tourisme	/Transport/Aérien/Avion/Tourisme
22	            Militaire	/Transport/Aérien/Avion/Militaire
11	        ULM		/Transport/Aérien/ULM
12	        Fusée		/Transport/Aérien/Fusée
13	        Parachute	/Transport/Aérien/Parachute
14	        Planeur		/Transport/Aérien/Planeur

Dans l'immédiat voici ma solution mais il existe certainement mieux :

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
SELECT I, (LPAD(' ', (V-1)*4) || N) N, REPLACE (P, T, N) P FROM (
	SELECT     S.I,
	           (MAX (L.N)KEEP (DENSE_RANK FIRST ORDER BY CASE L.L
	                WHEN 'FR'
 	                  THEN 1
	                WHEN 'EN'
 	                  THEN 2
	             END)
	           ) N,
		MAX(L.N) T,
		LEVEL V,
		MAX (SYS_CONNECT_BY_PATH (L.N, '/')) P
	FROM JB__SC S INNER JOIN JB__LC L ON S.I = L.I
	CONNECT BY PRIOR S.I = S.P
	START WITH 1 = S.I
	GROUP BY S.I, LEVEL
	ORDER BY MAX(ROWNUM))

Et le résultat :

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
I	N			P
---------------------------------------------------------------------
1	Transport		/Transport
2	    Terrestre		/Transport/Terrestre
5	        Voiture		/Transport/Terrestre/Voiture
6	        Camion		/Transport/Terrestre/Camion
7	        Moto		/Transport/Terrestre/Moto
18	            Trail	/Transport/Terrestre/Moto/Trail
19	            Side-car	/Transport/Terrestre/Moto/Side-car
8	        Vélo		/Transport/Terrestre/Vélo
3	    Marin		/Transport/Marin
15	        Voilier		/Transport/Marin/Voilier
16	        Paquebot	/Transport/Marin/Paquebot
17	        Planche à voile	/Transport/Marin/Planche à voile
4	    Aérien		/Transport/Aérien
9	        Hélico		/Transport/Aérien/Hélico
10	        Avion		/Transport/Aérien/Avion
20	            Civil	/Transport/Aérien/Avion/Civil
21	            Tourisme	/Transport/Aérien/Avion/Tourisme
22	            Militaire	/Transport/Aérien/Avion/Militaire
11	        ULM		/Transport/Aérien/ULM
12	        Fusée		/Transport/Aérien/Fusée
13	        Parachute	/Transport/Aérien/Parachute
14	        Planeur		/Transport/Aérien/Planeur