[XMLTYPE] - Index - TABLE ACCESS FULL

Version imprimable

bonjour,

voila plus jour que je galère sur l'impossibilité de me débarrasserd'un "TABLE ACCESS FULL" !!

J'ai une table comme suit les documents XML font ~30ko.

Code:

1
2
3
4
5
CREATE TABLE XYZREGISTRE of XMLType
    XMLTYPE store AS OBJECT RELATIONAL
    XMLSCHEMA "http://X.Y.Z/projet/ABC/schema/xyzmessage.xsd"
	ELEMENT "xyzmessage"
/

Plein d'index de ce genre.

Code:

1
2
CREATE INDEX idx_xyzreg_1 ON XYZREGISTRE
	(extractvalue(object_value,'/xyzmessage/META_DATA/md_hopcreator')) TABLESPACE INDX;

si je fait un requete que ne recupère que 1 champs (indexé) par de pb.

par contre comment peut on optimiser cette requete :

Code:

SELECT extract(object_value,'/xyzmessage').getClobVal() FROM XYZREGISTRE WHERE existsNode(object_value,'/xyzmessage[NOID="x007tzg4263') = 1;

Code:

1
2
3
4
5
6
7
8
9
10
11
 
-----------------------------------------------------------------------------------------------------------------
| Id  | Operation            | Name        | Rows  | Bytes | Cost (%CPU)| Time     |    TQ  |IN-OUT| PQ Distrib |
-----------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |             |     1 |  2548 |     2   (0)| 00:00:01 |        |      |            |
|   1 |  PX COORDINATOR      |             |       |       |            |          |        |      |            |
|   2 |   PX SEND QC (RANDOM)| :TQ10000    |     1 |  2548 |     2   (0)| 00:00:01 |  Q1,00 | P->S | QC (RAND)  |
|   3 |    PX BLOCK ITERATOR |             |     1 |  2548 |     2   (0)| 00:00:01 |  Q1,00 | PCWC |            |
|*  4 |     TABLE ACCESS FULL| XYZREGISTRE |     1 |  2548 |     2   (0)| 00:00:01 |  Q1,00 | PCWP |            |
 
 4 - filter("XYZREGISTRE"."SYS_NC00017$"='x007tzg4263')

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
 
Statistiques
----------------------------------------------------------
         37  recursive calls
          3  db block gets
       5206  consistent gets
          0  physical reads
        628  redo size
       1065  bytes sent via SQL*Net to client
        674  bytes received via SQL*Net from client
          5  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
          1  rows processed

pour eviter un access full.
note :: le .getClobVal() est OBLIGATOIRE.

merci de votre aide !!

16/01/2008, 09h10
mnitu

Et pour quoi veut tu te débarrasser du FULL ? Les résultats n’ont pas l’air d’indiquer un souci de performance ?
16/01/2008, 09h29
orafrance

j'y connais pas grand chose (en fait rien :aie:) mais d'après l'exemple de la doc j'ai pas l'impression que l'index porte sur la même chose que la requête.

Je me serais attendu à un WHERE du genre :

Code:

object_value,'/xyzmessage[META_DATA="x007tzg4263'

ou trouver NOID dans l'index :koi:

Sinon, sache qu'il n'est pas forcément intéressant de passer par un index, si Oracle ramène beaucoup de ligne (par rapport au nombre de référence dans l'index) un FTS est plus intéressant :)
16/01/2008, 09h31
orafrance

cet exemple à l'air intéressant aussi : http://download.oracle.com/docs/cd/B...tm#sthref10107

tu peux essayer un index comme ça ?

Code:

CREATE INDEX idx_xyzreg_1 ON XYZREGISTRE (XMLDATA."NOID")

en fait j'ai tourné le problème un peu dans tous les sens.

en effet il n'y a pas problème de perf - pour le moment - c'est une base de dev.
J'ai fait des test d'accès à 100, 1000, 10000 lignes ça fonctionne avec des temps d'accès <3sec... mais pour plus tard...

Les temps données par explain plan sont un peu faussé lorsque la requête a déjà été exécuté...

en ce qui concerne le fait d'utiliser "WHERE extractValu"e au lieu de "/xpath[param=value] "; j'ai remarqué que c'était moins couteux en E/S pour oracle - Maintenant savoir pourquoi ?-

la ou cela me pose problème. c'est que Oracle n'utilise pas les index que j'ai mis en place.

Autre exemple :

quand je fait un
Code:

1 2 SELECT * FROM TOTO WHERE ROWID=( SELECT ROWID FROM TOTO WHERE A=1) ;
exemple avec XMLTYPE
Code:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 select extract(object_value,'/abcmessage').getClobVal() from XYZREGISTRE where rowid=( SELECT rowid FROM XYZREGISTRE WHERE existsNode(object_value,'/abcmessage[NODC="fj65743Hde"]') = 1); ----------------------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | TQ |IN-OUT| PQ Distrib | ----------------------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 2548 | 3 (0)| 00:00:01 | | | | |* 1 | FILTER | | | | | | | | | | 2 | FAST DUAL | | 1 | | 2 (0)| 00:00:01 | | | | | 3 | TABLE ACCESS BY USER ROWID| XYZREGISTRE | 1 | 2548 | 1 (0)| 00:00:01 | | | | | 4 | PX COORDINATOR | | | | | | | | | | 5 | PX SEND QC (RANDOM) | :TQ10000 | 1 | 2548 | 2 (0)| 00:00:01 | Q1,00 | P->S | QC (RAND) | | 6 | PX BLOCK ITERATOR | | 1 | 2548 | 2 (0)| 00:00:01 | Q1,00 | PCWC | | |* 7 | TABLE ACCESS FULL | XYZREGISTRE | 1 | 2548 | 2 (0)| 00:00:01 | Q1,00 | PCWP | | -----------------------------------------------------------------------------------------------------------------------
L'index est bien utilisé pour le rowID, par contre le fait de mettre * ou une liste de champs que je souhaite récupérer l'explain fini par un ACCESS FULL !!

De plus je n'arrive pas a forcer l'usage des index même avec /*index()*/ dans la requete !!

une idée...

16/01/2008, 16h34
mnitu
Si t’a pas un problème de perf ne t’invente pas un problème d’optimisation !
Si tu penses que les gros volumes vont poser des problèmes fais un benchmark et vérifiez.
Si tu veut obtenir les informations concernant les choix de l’optimiseur fait une trace de l’événement 10053, mais comprendre et interpréter les résultats n’est pas simple. Il y a « N » raisons pour expliquer pourquoi Oracle n’utilise un index.

Citation:

Les temps données par explain plan sont un peu faussé lorsque la requête a déjà été exécuté...

C'est pas vrai. Mais tu peut faire un
Code:

1 2 alter system flush shared_pool
si tu pense que ça aide.
16/01/2008, 17h52
orafrance

si je vois bien tu utilises le parallélisme ce qui peut expliquer l'usage des FTS :?
16/01/2008, 18h05
Michel SALAIS

Citation:

SELECT * FROM TOTO WHERE ROWID=(
SELECT ROWID FROM TOTO WHERE A=1) ;

C'est très curieux comme approche!
Pourquoi ne pas faire

Code:

SELECT * FROM TOTO WHERE A=1
16/01/2008, 20h52
jacquesh
Citation:

Envoyé par Michel SALAIS

C'est très curieux comme approche!
Pourquoi ne pas faire

Code:

SELECT * FROM TOTO WHERE A=1

parce que suivant la configuration de l'XML TABLE même avec un index j'ai
Code:

1 2 select F1 from toto where A=1
la tout va bien

par contre

Code:

select * from toto where A=1

TABLE ACCESS FULL !!

je me suis dit que si je trouve rapidement le rowid, ramener toutes les informations de ce rowid serai une facon d'aider oracle à trouver l'information sans tout scanner....
mais malheureusement TABLE ACCESS FULL !! too.

bizard... vous avez dis bizard

pour répondre à "orafrance" : j'ignorai que le parallélisme imposait le FTS... je vais fouiller dans cette direction.

merci pour l'info
16/01/2008, 21h06
jacquesh

Bingo !!

c'était bien le parallélisme qui provoquai le non-usage des index !!
si quelqu'un a de ma littérature sur le sujet;je suis preneur.

Merci encore.

SOLVED
16/01/2008, 22h51
orafrance

qui dit parallélisme dit FTS moins couteux pour l'optimiseur donc le CBO rechigne moins à y recourir ;)

c'est pas automatique mais ça risque d'arriver plus souvent... encore une démonstration que le parallélisme est loin d'être toujours miraculeux :mrgreen: