recherche explication sur comportement partition by

**boutade80** · 08/12/2009, 19h51

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 :

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
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 :

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
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

**Waldar** · 08/12/2009, 22h34

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 ?

**boutade80** · 09/12/2009, 13h43

J'ai un test:

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
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 :

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

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.

**Waldar** · 09/12/2009, 14h45

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 :

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
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 :

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
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;

**pacmann** · 09/12/2009, 15h40

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 :

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
 
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...

**boutade80** · 09/12/2009, 18h07

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.

recherche explication sur comportement partition by

SQL Oracle

Discussions similaires

Partager

Partager