recherche explication sur comportement partition by

Version imprimable

Bonjour,

Je suis devant un cas sur lequel je cherche une explication. je vais vous mettre des exemples bruts.

numero_id est un champ de type varchar
Code:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 with myRequete as ( select rownum as id, m.numero_id, priorite from matricule m, identite i JOIN adresse a ON i.identifiant_fk = a.IDENTIFIANT_FK join type_adresse ta on ta.type_adresse_pk=a.type_adresse_fk where i.identifiant_fk = m.identifiant_fk AND m.numero_id IN ( '19640220751','19550608874','19700717693','19771120639','19790814641','19700911635') order by m.numero_id,ta.priorite) select numero_id priorite, rank() over(partition by numero_id order by priorite) rank from myRequete t1
Ce code executé par une machine windows XP cliente config en parametre regionaux anglais ne donne pas la meme reponse qu'une machine windows XP client config en param regionaux francais. En francais cette requette ne fonctionnne pas.
le calcul du rank ne donne pas le meme resultats en fonction des versions param geo des pc clients.

Par contre en rajoutant un to_char apres le partition by, toutes les machines clientes affichent les bons resultats.
Code:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 with myRequete as ( select rownum as id, m.numero_id, priorite from matricule m, identite i JOIN adresse a ON i.identifiant_fk = a.IDENTIFIANT_FK join type_adresse ta on ta.type_adresse_pk=a.type_adresse_fk where i.identifiant_fk = m.identifiant_fk AND m.numero_id IN ( '19640220751','19550608874','19700717693','19771120639','19790814641','19700911635') order by m.numero_id,ta.priorite) select numero_id priorite, rank() over(partition by to_char(numero_id) order by priorite) rank from myRequete t1
Nous avons regardé ds le cache d'oracle, ds le cas de la fonction sans to_char le code est le meme quel que soit les param geo du client, seul le resultat différe.

Nous avons trouver un paliatif mais nous n'avons pas trouvé l'explication. Avez vous une idée?

Je vais tenter de faire un jeu de données des demain qui montre ce resultat.

Ps:
Version d'oracle sur lesquel nous avons travaille: 10.2.0.1 et 10.2.0.4
Client Oracle ou driver jdbc appelant. 10.2.0.1,10.2.0.4 et 11.x.x.x

08/12/2009, 22h34
Waldar

Quels sont vos paramètres de langue, territoire et sort ?
Pouvez-vous fournir un petit jeu de données ?

Quels sont les types des colonnes numero_id et priorite ?
Que contiennent-ils réellement ?

J'ai un test:
Code:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 ALTER SESSION SET NLS_LANGUAGE='FRENCH'; with req as ( select * from ( select '19550613649' ancien_format_mat , 2 priorite from dual union all select '19630408117' ancien_format_mat , 2 priorite from dual union all select '19790201534' ancien_format_mat , 3 priorite from dual union all select '19760115676' ancien_format_mat , 2 priorite from dual union all select '19790201534' ancien_format_mat , 2 priorite from dual ) order by ancien_format_mat,priorite ) select ancien_format_mat, priorite, rank() over( PARTITION BY ancien_format_mat ORDER BY priorite ) rank from req t1
Cette requete executé via un appel jdbc(V10.2.0.4) a une base en 10.2.0.4 renvoi un resultat faux.

ANCIEN_FORMAT_MAT,"PRIORITE","RANK"
19550613649, 2, 1
19630408117, 2, 1
19760115676, 2, 1
19790201534, 2, 1
19790201534, 3, 5

Par contre si je met

Code:

ALTER SESSION SET NLS_LANGUAGE='AMERICAN';

La requete ci dessus s'effectue comme attendu.

ANCIEN_FORMAT_MAT,"PRIORITE","RANK"
19550613649, 2, 1
19630408117, 2, 1
19760115676, 2, 1
19790201534, 2, 1
19790201534, 3, 2

Si je supprime le "order by ancien_format_mat,priorite" ds les deux cas la requete donne le bon resultat.

Nous allons sans doute ouvrir un billet sur le support d'oracle

Ma config par defaut:
select * from v$nls_parameters;

NLS_LANGUAGE FRENCH
NLS_TERRITORY FRANCE
NLS_CURRENCY €
NLS_ISO_CURRENCY FRANCE
NLS_NUMERIC_CHARACTERS ,
NLS_CALENDAR GREGORIAN
NLS_DATE_FORMAT DD/MM/RR
NLS_DATE_LANGUAGE AMERICAN
NLS_CHARACTERSET WE8MSWIN1252
NLS_SORT BINARY
NLS_TIME_FORMAT HH24:MI:SSXFF
NLS_TIMESTAMP_FORMAT DD/MM/RR HH24:MI:SSXFF
NLS_TIME_TZ_FORMAT HH24:MI:SSXFF TZR
NLS_TIMESTAMP_TZ_FORMAT DD/MM/RR HH24:MI:SSXFF TZR
NLS_DUAL_CURRENCY €
NLS_NCHAR_CHARACTERSET AL16UTF16
NLS_COMP BINARY
NLS_LENGTH_SEMANTICS BYTE
NLS_NCHAR_CONV_EXCP FALSE

Edit: ajout des resultats.

Effectivement je retrouve le même problème sur une 11.1.0.7.0 !
Quand on regarde le plan d'exécution, sur les fonctions analytique l'explain plan indique WINDOW NOSORT (en FR et US), ce qui est plus qu'étrange alors qu'il est bien précisé.

C'est comme si l'ORDER BY imbriqué dans la vue tuait ceux qui suivent, fonctions de fenêtrages inclues.

Celà dit, les order by intermédiaires sont déconseillés tant que vous n'utilisez pas de pseudocolonne type rownum.

Notez qu'en écrivant la requête en deux WITH, le résultat est correct :

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
WITH req1 AS
(
SELECT '19550613649' as mat , 2 as prio FROM dual union ALL
SELECT '19630408117'        , 2         FROM dual union ALL
SELECT '19790201534'        , 3         FROM dual union ALL
SELECT '19760115676'        , 2         FROM dual union ALL
SELECT '19790201534'        , 2         FROM dual
)
  , req2 AS
(
  SELECT mat, prio
    FROM req1
ORDER BY mat asc, prio asc
)
  select mat, prio,
         rank()       over(PARTITION BY mat ORDER BY prio asc) as rk,
         row_number() over(PARTITION BY mat ORDER BY prio asc) as rn
    from req2
order by mat asc, prio asc;
 
MAT		PRIO	RK	RN
19550613649	2	1	1
19630408117	2	1	1
19760115676	2	1	1
19790201534	2	1	1
19790201534	3	2	2

Par contre si vous forcez le tri (en français, en us, en bin) avec NLSSORT, là ça fonctionne correctement, le WINDOW NOSORT devient un WINDOW SORT :

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
with req as
(
select mat, prio
from
    (
    select '19550613649' as mat , 2 as prio from dual union all
    select '19630408117'        , 2         from dual union all
    select '19790201534'        , 3         from dual union all
    select '19760115676'        , 2         from dual union all
    select '19790201534'        , 2         from dual
    )
order by mat asc, prio asc
)
  select mat, prio,
         rank() over(partition by mat order by nlssort(prio, 'nls_language=FRENCH'  ) asc) as rk_fr
    from req
order by mat asc, prio asc;

Salut !

Ca déchire votre truc !
Il y a clairement un bug... mais il est tout de même un peu bizarre de mettre un order by dans une sous-requête.

Si tu l'enlèves, ça marche. D'ailleurs, si tu ne mets pas la priorité, ça marche aussi (puisque du coup il re-sort) !

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
51
52
53
 
SQL> alter session set nls_language='FRENCH';
 
Session altered.
 
SQL> set autot on explain
SQL> WITH req AS (
  2     SELECT * FROM (
  3             SELECT '19550613649' ancien_format_mat , 2 priorite FROM dual union ALL
  4             SELECT '19630408117' ancien_format_mat , 2 priorite FROM dual union ALL
  5             SELECT '19790201534' ancien_format_mat , 3 priorite FROM dual union ALL
  6             SELECT '19760115676' ancien_format_mat , 2 priorite FROM dual union ALL
  7             SELECT '19790201534' ancien_format_mat , 2 priorite FROM dual
  8     )
  9     ORDER BY ancien_format_mat
 10  )
 11  SELECT
 12  ancien_format_mat,
 13  priorite,
 14  rank() over(
 15     PARTITION BY ancien_format_mat ORDER BY priorite
 16  ) rank
 17  FROM req t1
 18  /
 
ANCIEN_FORM   PRIORITE       RANK
----------- ---------- ----------
19550613649          2          1
19630408117          2          1
19760115676          2          1
19790201534          2          1
19790201534          3          2
 
 
Execution Plan
----------------------------------------------------------
Plan hash value: 2318045638
 
-------------------------------------------------------------------------
| Id  | Operation        | Name | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------
|   0 | SELECT STATEMENT |      |     5 |    80 |    12  (17)| 00:00:01 |
|   1 |  WINDOW SORT     |      |     5 |    80 |    12  (17)| 00:00:01 |
|   2 |   VIEW           |      |     5 |    80 |    11  (10)| 00:00:01 |
|   3 |    SORT ORDER BY |      |     5 |    80 |    11  (10)| 00:00:01 |
|   4 |     VIEW         |      |     5 |    80 |    10   (0)| 00:00:01 |
|   5 |      UNION-ALL   |      |       |       |            |          |
|   6 |       FAST DUAL  |      |     1 |       |     2   (0)| 00:00:01 |
|   7 |       FAST DUAL  |      |     1 |       |     2   (0)| 00:00:01 |
|   8 |       FAST DUAL  |      |     1 |       |     2   (0)| 00:00:01 |
|   9 |       FAST DUAL  |      |     1 |       |     2   (0)| 00:00:01 |
|  10 |       FAST DUAL  |      |     1 |       |     2   (0)| 00:00:01 |
-------------------------------------------------------------------------

EDIT :
Autant en 10.2.0.1 qu'en 9.2.0.8
Ah pardon Waldar, j'avais pas vu que tu parlais déjà de l'order by inutile...

09/12/2009, 18h07
boutade80

Oui ce problème nous a fait nous rendre compte qu'un order by etait de trop.
Merci pour vos retours.
Nous avions aussi constaté qu'en ecrivant:

PARTITION BY ancien_format_mat ORDER BY nlssort(priorite, 'nls_language=AMERICAN' )
ou
PARTITION BY NLSSORT(ancien_format_mat, 'nls_sort=BINARY') ORDER BY priorite

nous obtenions les resultats souhaités.
Nous etions par contre passé a coté du nosort de l'explain plan.