[9i] FULL TABLE SCAN sur colonne entrant dans la PK

Version imprimable

29/04/2008, 17h31
Vld44

[9i] FULL TABLE SCAN sur colonne entrant dans la PK
Salut,

Soit la table suivante :
Code:

1 2 CREATE TABLE ARF_TEMP_myIds(INSTANCEID VARCHAR2(44)); ALTER TABLE ARF_TEMP_myIds ADD CONSTRAINT PK_MYIDS PRIMARY KEY (INSTANCEID);
mes jointures avec cette table me font systématiquement un full scan et je ne comprends pas pourquoi. Est-ce que cela veut aussi dire que l'index ne sert à rien ?

Merci

edit : je précise qu'elles sont "conventionnelles" c'est à dire de type where A = B
29/04/2008, 17h39
McM

Non, ça veut dire qu'Oracle a décidé de ne pas aller lire l'index suivant les paramètres de l'optimiseur.

1/ Les stats sont elles à jour ?
2/ La table est elle grosse (parce que sinon c'est plus simple de monter toute la table en mémoire)
3...

Il faut ajouter quelques détails.

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
 
Explain plan for 
Select * 
from ARF_TEMP_myIds a join ARF_TEMP_myIds b using (INSTANCEID)
/
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
 
-------------------------------------------------------------------------
| Id  | Operation            |  Name       | Rows  | Bytes | Cost (%CPU)|
-------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |             |    82 |  3936 |     4   (0)|
|   1 |  NESTED LOOPS        |             |    82 |  3936 |     4   (0)|
|   2 |   INDEX FULL SCAN    | PK_MYIDS    |    82 |  1968 |    26   (0)|
|*  3 |   INDEX UNIQUE SCAN  | PK_MYIDS    |     1 |    24 |            |
-------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
 
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
---------------------------------------------------
 
   3 - access("A"."INSTANCEID"="B"."INSTANCEID")

29/04/2008, 17h41
Vld44

ah bien vu, je créé à la volée la table et l'index donc aucune stat n'est à jour.

Cependant je sais à tout moment le compte de la table, puis-je forcer le compilateur à une cardinalité donnée ? il me semble avoir vu passer une syntaxe avec "(cardinalité)" ...
29/04/2008, 17h44
McM

Je n'ai pas compris ta notion de cardinalité.
29/04/2008, 17h47
Vld44

et bien puisque l'optimiseur n'a pas de statistiques au moment des requêtes, ne pourrais-je pas les lui spécifier ? (contenu de la table à l'instant t)

/*+ CARDINALITY (100) */

par exemple ... Il faut que je bosse mon pl/sql ><

edit : je suis sous 9i !!!
29/04/2008, 21h39
mnitu

Je n'ai pas calculé les statistique dans mon exemple et l'index est utilisé (la tables est vide).
Peut tu mieux expliquer quel est ton problème ?
Sinon il y a un hint SQL concernant la cardinalité mais est-ce que t'a vraiment besoin ?
30/04/2008, 10h38
orafrance

un exemple de requête ça pourrait être pas mal déjà... est-ce qu'il y une clause WHERE sur la colonne de la PK au moins ?

ce qu'il y a c'est que j'aurais bien une requête complète à vous fournir mais elle est un peu nébuleuse sortie de son contexte quoi ...

La voilà, avec la jointure en gras :

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
MERGE INTO TABLE0 op
USING
    (
     SELECT ids.instanceid, rv.id
     FROM ARF_TEMP_myIds ids, TABLE1 oi, TABLE2 rv
     WHERE ids.instanceid = oi.id
     AND   rv.name = (oi.name || '-' || '1278')
     AND   oi.conceptid = 'CODMETHODBIN'
    ) raws
ON (op.instanceid = raws.instanceid AND op.propertyid = '1278')
WHEN MATCHED THEN
    UPDATE SET op.rawvalue = raws.id
WHEN NOT MATCHED THEN
    INSERT (op.instanceid, op.propertyid,op.propvalue,op.rawvalue)
    VALUES (raws.instanceid,'1278',NULL,raws.id)

Voici l'explain plan associé avec en gras la ligne qui parle du full scan :

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
MERGE STATEMENT, GOAL = CHOOSE   Cost=9    Cardinality=1    Bytes=121
 MERGE    Object name=TABLE0 
  VIEW                    
   NESTED LOOPS OUTER  Cost=9    Cardinality=1    Bytes=306
    NESTED LOOPS            Cost=6    Cardinality=1    Bytes=257
     MERGE JOIN CARTESIAN   Cost=4    Cardinality=1    Bytes=177
 TABLE ACCESS FULL Object name=TABLE2 Cost=2 Cardinality=1 Bytes=153
      BUFFER SORT            Cost=2    Cardinality=82    Bytes=1968
       TABLE ACCESS FULL    Object name=ARF_TEMP_MYIDS    Cost=2    Cardinality=82    Bytes=1968
     TABLE ACCESS BY INDEX ROWID  Object name=TABLE1 Cost=2    Cardinality=1    Bytes=80
      INDEX UNIQUE SCAN  Object name=PK_TABLE1 Cost=1 Cardinality=1    
    TABLE ACCESS BY INDEX ROWID  Object name=TABLE0 Cost=3    Cardinality=1    Bytes=49
     INDEX UNIQUE SCAN  Object name=PK_TABLE0 Cost=2 Cardinality=1

Voilà ...

30/04/2008, 13h45
orafrance

bah voila, il y a tellement peut de ligne que ça sert à rien de faire un accès par l'index.

Faut pas croire que FTS = perf catastrophique, c'est un peu plus fin que ça ;) En effet, dans le cas présent Oracle ne lit que le fichier de données au lieu de lire index + données ce qui est trop couteux quand la table est petite :)
30/04/2008, 14h11
Vld44

la table est remplie dans une transaction et peut contenir plusieurs millions d'enregistrements.

tu veux dire que dans ce dernier cas, l'index serait utilisé ?
30/04/2008, 14h27
mnitu

J'aimerai bien avoir un desc de la table Table1 pour analyser le type de données de la colonne id.
30/04/2008, 14h34
orafrance

Citation:

Envoyé par Vld44

tu veux dire que dans ce dernier cas, l'index serait utilisé ?

si la requête ne ramène pas un trop gros échantillon et que les stats sont à jour, oui :)
30/04/2008, 14h54
Vld44

Citation:

Envoyé par mnitu

J'aimerai bien avoir un desc de la table Table1 pour analyser le type de données de la colonne id.

Code:

WHERE ids.instanceid = oi.id

oi.id : VARCHAR2(44)
ids.instanceid : VARCHAR2(44)

est-ce que au passage vous voyez des abérations dans mon code ?
Je suis pas hyper doué en pl/sql et ça m'étonnerait pas que j'ai fait une énormité :D
30/04/2008, 15h34
mnitu

Citation:

Envoyé par Vld44

[code]...
est-ce que au passage vous voyez des abérations dans mon code ?
Je suis pas hyper doué en pl/sql et ça m'étonnerait pas que j'ai fait une énormité :D

J’avais pensé que si les colonnes n’ont pas le même type ça pouvait expliquer le full table scan par la conversion implicite fait dans ce cas par Oracle mais ce n’est pas le cas.
Sinon calcule les statistiques, examine le plan, charge les tables avec un test réel, recalcule les statistiques et examine le plan. Pour l’instant ce qui se passe est normal.