[Fonctions analytiques] : inhibe les indexs ?

**PpPool** · 20/04/2006, 16h21

Bonjour à tous

Oracle 9i Release 9.2.0.1.0

Je voudrai avoir votre avis sur cette requete. j'obtiens un access full alors que :

- le champ FACT_DATE est indexé
- les champs TYPF_ID et CAMP_ID sont des FK également indexés

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
 
SQL> EDIT
écrit fichier afiedt.buf
  1  SELECT FACT_DATE AS DATE_REF,
  2                SUM(CASE WHEN TYPF_ID='A' THEN -FACT_MT_HT ELSE FACT_MT_HT END ) OVER(ORDER BY FACT_DATE) AS
  3         FROM FACTURATION
  4         WHERE CAMP_ID=TO_NUMBER( '2006' )
  5         AND TYPF_ID<>'REMB'
  6*        AND FACT_DATE<=TO_DATE( '20/04/2006' ,'DD/MM/YYYY')
SQL> /
 
Plan d'exécution
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=4 Card=27 Bytes=567)
   1    0   WINDOW (SORT) (Cost=4 Card=27 Bytes=567)
   2    1     TABLE ACCESS (FULL) OF 'FACTURATION' (Cost=2 Card=27 Byt
          es=567)

La fonction analytique n'inhibe t-elle pas ici les indexs ?

Merci de votre aide

@

**orafrance** · 20/04/2006, 16h25

utilisation de fonction -> pas d'index utilisé

Essaye :

CAMP_ID=2006

**orafrance** · 20/04/2006, 16h26

Envoyé par PpPool

La fonction analytique n'inhibe t-elle pas ici les indexs ?

Non mais TO_NUMBER et TO_DATE oui... les fonctions appliquées sur les colonnes indexées interdisent l'usage de l'index... ou alors il faut créer un index de fonction ce que je ne conseille pas

**nuke_y** · 20/04/2006, 16h29

Ah bon ? Pourquoi tu ne conseilles pas les index de fonction ? A part le fait qu'ici ça ne semble pas nécessaire, bien sûr.

**orafrance** · 20/04/2006, 16h38

Envoyé par nuke_y

Ah bon ? Pourquoi tu ne conseilles pas les index de fonction ? A part le fait qu'ici ça ne semble pas nécessaire, bien sûr.

Si tu changes l'applicatif et le modéle pour éviter la fonction (une jointure number -> varchar qui devient number -> number), tu perds l'index. C'est beaucoup plus judicieux d'adapter l'application ou le modéle que créer un index de fonction qui me parait être de l'ordre de la rustine.

Néanmoins, cela peut s'avérer très pratique pour les fonctions plus évoluées

Mais ça ne reste qu'une "phylosophie" personnelle

**nuke_y** · 20/04/2006, 18h22

Envoyé par Fred_D

Si tu changes l'applicatif et le modéle pour éviter la fonction (une jointure number -> varchar qui devient number -> number), tu perds l'index. C'est beaucoup plus judicieux d'adapter l'application ou le modéle que créer un index de fonction qui me parait être de l'ordre de la rustine.

Ah oui, d'un point de vue "propreté" on est bien d'accord. Comme je le fait régulièrement, j'ai eu peur de ne pas connaître un problème technique lié à cette solution.

**PpPool** · 20/04/2006, 16h31

Envoyé par Fred_D

Non mais TO_NUMBER et TO_DATE oui... les fonctions appliquées sur les colonnes indexées interdisent l'usage de l'index... ou alors il faut créer un index de fonction ce que je ne conseille pas

salut Fred,

oui mais c'est :
TO_CHAR(CAMP_ID)='2006' qui inhibera mon index et non CAMP_ID=TO_NUMBER('2006')

Je viens de toute façon d'essayer et cela ne change rien

**orafrance** · 20/04/2006, 16h36

tu as complétement raison

les stats sont-elles calculées ? Que donne un hint pour forcer l'usage de l'index ?

**PpPool** · 20/04/2006, 16h40

Envoyé par Fred_D

tu as complétement raison

çà arrive même aux meilleurs ! la preuve -)

Envoyé par Fred_D

les stats sont-elles calculées ? Que donne un hint pour forcer l'usage de l'index ?

les stats sont à jour

je vais essayer avec un hint, peux tu me redonner un exemple d'utilisation. merci

**orafrance** · 20/04/2006, 16h42

Code :

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

SELECT /*+ index(FACTURATION, FACTURATION_IDX_1) */FACT_DATE AS DATE_REF

Vérifie que dans ce cas le coût est inférieur... ce qui n'est pas évident

**sygale** · 20/04/2006, 16h41

Envoyé par PpPool

oui mais c'est :
TO_CHAR(CAMP_ID)='2006' qui inhibera mon index et non CAMP_ID=TO_NUMBER('2006')

Oui c'est l'utilisation de fonction sur les champs qui rends les indexs inutilisable !!

On pourrait avoir les index de la table FACTURATION, c'est peut ê la somme sur les montant, as tu un index bitmap sur TYPF_ID, car je suppose que c'est le type de facture, Facture, Avoir, REMBoursement .. dc faible cardinalité.

Et la clause

Code :

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

1
2
3
WHERE CAMP_ID=TO_NUMBER( '2006' )
         AND TYPF_ID<>'REMB'
         AND FACT_DATE<=TO_DATE( '20/04/2006' ,'DD/MM/YYYY')

Ramene t elle + de 80% de la table facturation ? si oui oracle a plutôt intéret a faire un FULL

**PpPool** · 20/04/2006, 16h53

Salut sygale,

Envoyé par sygale

as tu un index bitmap sur TYPF_ID, car je suppose que c'est le type de facture, Facture, Avoir, REMBoursement .. dc faible cardinalité.

je suis en standard edition pas de possibilités de faire des indexs bitmaps

Envoyé par sygale

Et la clause

Code :

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

1
2
3
WHERE CAMP_ID=TO_NUMBER( '2006' )
         AND TYPF_ID<>'REMB'
         AND FACT_DATE<=TO_DATE( '20/04/2006' ,'DD/MM/YYYY')

Ramene t elle + de 80% de la table facturation ? si oui oracle a plutôt intéret a faire un FULL

non cette clause ne raméne que 10% de la table.

Sinon, je reviens sur l'origine de mon post : je n'ai pas de pbs de perfs avec cette requete, mais je voulai savoir si vous aviez déjà constaté des pbs de ce genre avec des fonctions analytiques.

merci

**orafrance** · 20/04/2006, 16h55

l'intérêt de la fonction analytique c'est qu'elle traite les lignes fetchées... donc APRES récupération des données (me semble-t-il

) donc aucune perturbation du plan d'exécution n'est à craindre.

Essaye le hint RULE aussi

**aline** · 20/04/2006, 16h54

Envoyé par PpPool

Bonjour à tous

Oracle 9i Release 9.2.0.1.0

Je voudrai avoir votre avis sur cette requete. j'obtiens un access full alors que :

- le champ FACT_DATE est indexé
- les champs TYPF_ID et CAMP_ID sont des FK également indexés

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
 
SQL> EDIT
écrit fichier afiedt.buf
  1  SELECT FACT_DATE AS DATE_REF,
  2                SUM(CASE WHEN TYPF_ID='A' THEN -FACT_MT_HT ELSE FACT_MT_HT END ) OVER(ORDER BY FACT_DATE) AS
  3         FROM FACTURATION
  4         WHERE CAMP_ID=TO_NUMBER( '2006' )
  5         AND TYPF_ID<>'REMB'
  6*        AND FACT_DATE<=TO_DATE( '20/04/2006' ,'DD/MM/YYYY')
SQL> /
 
Plan d'exécution
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=4 Card=27 Bytes=567)
   1    0   WINDOW (SORT) (Cost=4 Card=27 Bytes=567)
   2    1     TABLE ACCESS (FULL) OF 'FACTURATION' (Cost=2 Card=27 Byt
          es=567)

La fonction analytique n'inhibe t-elle pas ici les indexs ?

Merci de votre aide

@

On peux répondre très simplement à ta question.
Il suffit de ne pas utiliser ta fonction analytique et de regarder le résultat obtenu
que donne?

Code :

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

1
2
3
4
5
6
 
 1  SELECT FACT_DATE AS DATE_REF
  3         FROM FACTURATION
  4         WHERE CAMP_ID=TO_NUMBER( '2006' )
  5         AND TYPF_ID<>'REMB'
  6*        AND FACT_DATE<=TO_DATE( '20/04/2006' ,'DD/MM/YYYY')

a priori le même résultat, mais c'est très facile de vérifier.

Je pense que tes indexes ne sont pas assez seletif et que c'est pour cela qu'Oralce ne les utilise pas.

Est-ce que cette requete donne le même résultat que la tienne?

Code :

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

1
2
3
4
5
6
7
 
 1  SELECT FACT_DATE AS DATE_REF
  3         FROM FACTURATION
  4         WHERE CAMP_ID=TO_NUMBER( '2006' )
  5         AND TYPF_ID<>'REMB'
  6        AND FACT_DATE<=TO_DATE( '20/04/2006' ,'DD/MM/YYYY')
7           AND FACT_DATE>=TO_DATE( '01/01/2006' ,'DD/MM/YYYY')

**PpPool** · 20/04/2006, 17h01

Envoyé par aline

On peux répondre très simplement à ta question.
Il suffit de ne pas utiliser ta fonction analytique et de regarder le résultat obtenu

Salut Aline

j'ai bien besoin de ma fonction analytique !
elle me permet de faire une somme cumulée -)

elle marche très bien d'ailleurs

@

**aline** · 20/04/2006, 18h09

Envoyé par PpPool

Salut Aline

j'ai bien besoin de ma fonction analytique !
elle me permet de faire une somme cumulée -)

elle marche très bien d'ailleurs

@

Evidement que tu as besoin de ta fonction.
Je te conseillai de ne pas l'utiliser uniquement pour verifier que le plan d'execution était le même avec ou sans.
Une fois ceci verifié, on sait que le problème ne vient pas de ta fonction analytique.
Il vient comme je le disai a priori du fait que tes index ne sont pas assez selectifs.
c'est pour cela que je t'avais rajouté une clause where en espérant qu'elle ne change en rien le résultat de ta requete mais qu'elle puisse en amélirer l'éxécution.

En général, c'est le genre de chose qui nous parraissent telement évidente qu'on ne le dis jamais dans notre code. Il ne faux quand même pas oublier que l'on parle à une machine!

Code :

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

1
2
 
AND FACT_DATE>=TO_DATE( '01/01/2006' ,'DD/MM/YYYY')

**pifor** · 20/04/2006, 17h01

Est-ce que les stats sont vraiment à jour ?

Code :

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

1
2
3
4
5
   SELECT STATEMENT Optimizer=CHOOSE (Cost=4 Card=27 Bytes=567)
   1    0   WINDOW (SORT) (Cost=4 Card=27 Bytes=567)
   2    1     TABLE ACCESS (FULL) OF 'FACTURATION' (Cost=2 Card=27 Byt
          es=567)

S'il n' y a que 27 lignes dans la table, aucun intérêt d'utiliser les index ...

[Fonctions analytiques] : inhibe les indexs ?

Oracle

Vue hybride

Discussions similaires

Partager

Partager