Discussion :

[ldiaz]

Bonjour a tous,

(oracle 10G -- Linux 5)
petit casse tête de vendredi, j'ai cette requête:

Code :

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

1
2
3
4
5
6
7
8
9
10
 SELECT 
        P.ID,
        P.ES,
        FN.GR,
        P.PVP
 FROM PROD P
 INNER JOIN FAM FN ON FN.GEN=P.GEN
 WHERE P.ID NOT LIKE '00%' 
 AND decode(P.ID,'06570074','770', decode(P.ID,'73268025','229', decode(P.ID,'63210660','208', decode(P.ID,'61154352','150', decode(P.ID,'06553572','753', decode(P.ID,'06563389','763', DECODE(substr(P.ID,3,1), '9', P.FAM||SUBSTR(P.ID,4,2), '8', P.FAM||SUBSTR(P.ID,4,2), '7', P.FAM||SUBSTR(P.ID,4,2), SUBSTR(P.ID,3,3) ) ) ) ) ) ) ) = FN.ID  
 AND P.ID NOT IN('53006262','53328461','53328462');

Telle que vous la voyez, cette requête génère un full scan sur la table PROD, bien que cette table possède une PK sur le champs ID.
Si vous changez le NOT IN de la fin par un IN, la PK est prise en compte et de 60 000 GETS ça passe a 8... Le résultat n'est plus le même bien sur, et je voudrais savoir s'il existe un moyen d'utiliser la PK avec le NOT IN.

quelqu'un a une idée?
Moi je sèche, j'ai pensé a un index INVERSE, mais comme la tabla est utilisée pour d'autres traitements si j'inverse l'index, j'ai peur que cela dégrade d'autres requêtes.

D'avance merci pour toute suggestion.

[Arkhena]

Bonjour,

Si l'optimiseur Oracle n'utilise pas la PK, c'est qu'il considère que c'est plus coûteux que de faire un fullscan... Et on le comprend!

Vous utilisez un PK, donc des enregistrements tous différents -> très grande disparité de valeur

Dans le premier cas, vous lui demandez de récupérer toutes les données sauf 3 : il est plus intéressant de faire un full scan!

Dans le deuxième cas, vous lui demandez de ne récupérer que 3 valeurs parmi toutes vos données : il est plus intéressant de passer par l'index...

Un full scan n'est pas forcément un problème de performances et passer par un indes peut même être moins bon qu'un full scan en terme de performances...

Cordialement,

Arkhena

[ldiaz]

Bonjour,
oui je suis d'accord. C'est donc la logique même de la requête qu'il faut changer.
Une sortie serait de recuperer aussi ces 3 valeurs, ce qui permetrais d'utiliser la PK, et ensuite de faire un delete dans le package... Un delete de 3 registres etant beaucoup moins couteux que le fullscan. Dans ce cas, le full scan est un probleme car la table est immense, et les gets sont elevés.

Merci pour tout, et si vous voyez une autre forme de faire, ça m'interesse

[Arkhena]

Bonjour,

Je crains de manquer d'information pour pouvoir vous donner quelque chose de plus précis...

Cependant, je pense que vous avez des gros soucis avec votre modèle de données.

- J'ai l'impression que vous stockez l'id (qui il me semble est la clé primaire de la table prod) sous forme de chaîne de caractères (alors que vous stockez des nombres dedans).
Il serait plus efficace d'utiliser une clé primaire numérique.(cf http://sqlpro.developpez.com/cours/clefs/)

- Pourquoi être obligé de faire des décodes dans la jointure entre la table FAM et la table PROD ? La condition de jointure

Code :

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

FN.GEN=P.GEN

ne suffit pas ?

Cordialement,

Arkhena

[ldiaz]

Bonjour
oui je suis d'accord aussi, il s'agit de la relation entre le dba et les developpeurs... J'ai remarquer ce detail du varchar sur le champ ID et ça m'a etonné aussi. C'est dans la liste des trucs a voir avec eux.

Pour le decode, c'est pareil je dois voir avec les dev. Cependant, j'ai essayé la requête en commentant le decode et le full scan reste.. C'est bien le NOT IN qui me joue un mauvais plan.

On gagnera beaucoup en changant l'ID de varchar a number? je vais lire le lien que tu as envoyé.
Merci encore !

[ldiaz]

Re bonjour,
dans le cas ou je dois changer l'ID de varchar2 a number... Existe t'il une note metalink pour ça? je demande car j'ai pas encore l'acces.

D'avance merci

[pachot]

Bonjour,

je voudrais savoir s'il existe un moyen d'utiliser la PK avec le NOT IN.

Une sortie serait de recuperer aussi ces 3 valeurs, ce qui permetrais d'utiliser la PK

la table est immense

Si la requête récupère toutes les lignes d'une table immense sauf 3, le full scan est probablement la bonne solution. Lorsque vous utilisez IN au lieu de NOT IN, vous allez chercher seulement 3 lignes: l'accès par index est parfait.
Mais si vous voulez presque toutes les lignes de PROD passer par un index serait catastrophique.

Sinon, à propos de:

Il serait plus efficace d'utiliser une clé primaire numérique.(cf http://sqlpro.developpez.com/cours/clefs/)

Non. en tout cas pas sous Oracle. Le type de donnée est déterminé par l'analyse. Ici il semble que les zéros devant sont significatifs: NOT LIKE '00%'

Code :

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

1
2
3
4
5
SQL> select dump('53006262'),dump(53006262) from dual;
 
DUMP('53006262')                      DUMP(53006262)
------------------------------------- ---------------------------
Typ=96 Len=8: 53,51,48,48,54,50,54,50 Typ=2 Len=5: 196,54,1,63,63

Pensez-vous que comparer 5 octets au lieu de 8 fera une grosse différence ?

Cordialement,
Franck.

[ldiaz]

Bonjour a tous,

merci pour tous vos commentaires. Donc ici le full scan est inevitable,, il s'agit d'un select pour faire un insert. Donc pour optimiser le procedure qui en definitive est un insert into, je vais essayer de:

- Desactiver la PK sur la table ou je fais l'insert
- passer la table a NOLOGGING
- ajouter un hint append
- A la fin de l'insert, reactiver la PK et le LOGGING
- Calculer stats

Qu'en pensez vous?