Optimisation de requete

**zekmek** · 25/09/2006, 17h06

Bonjour,

J'ai la requete suivante qui met plus de 2 minutes a s'executer. Je m'y prend sans doutes tres mal... quelqu'un sait il comment je peux l'optimiser ?

SELECT
count(distinct(extractvalue (value(images),'//@numfich')))
FROM ae7qtme1 me1,
TABLE ( XMLSEQUENCE ( extract(me1.docxml, '//Image') ) ) images
WHERE me1.ID=methode.ID
and upper
(
extractvalue (value(images),'//@numfich')
)
NOT IN
(
SELECT
upper(mg1.NAME)
FROM ae7qtmg1 mg1,
ae7qtme1 me1,
ae7qtlkc lkc,
TABLE ( XMLSEQUENCE ( extract(me1.docxml, '//Image') ) ) images
where me1.id=lkc.FK_METHOD_ID
and lkc.FK_IMAGE_ID=mg1.ID
and me1.ID=methode.ID
and upper(extractvalue (value(images),'//@numfich')) = upper(mg1.NAME)
)

**sygale** · 25/09/2006, 17h37

Merci d'utiliser

Sinon il faut éviter les utilisaons de NOT IN

c'est vite fait mais essaye

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
SELECT
         count(distinct(extractvalue (value(images),'//@numfich')))
FROM     ae7qtme1 me1,
         TABLE ( XMLSEQUENCE ( extract(me1.docxml, '//Image') ) ) images
WHERE    me1.ID=methode.ID
and 
NOT EXISTS
(
SELECT
upper(mg1.NAME)
FROM ae7qtmg1 mg1,
ae7qtme1 me1,
ae7qtlkc lkc,
TABLE ( XMLSEQUENCE ( extract(me1.docxml, '//Image') ) ) images
where me1.id=lkc.FK_METHOD_ID
and lkc.FK_IMAGE_ID=mg1.ID
and me1.ID=methode.ID
and upper(extractvalue (value(images),'//@numfich')) = upper(mg1.NAME)
)

Peux tu me dire d'ou vient methode.ID qui n'est présent dans aucun FROM

**zekmek** · 25/09/2006, 17h51

Merci sygale :-) et désolé, la prochaine je mettrais la balise CODE ...

Envoyé par sygale

Peux tu me dire d'ou vient methode.ID qui n'est présent dans aucun FROM

En fait c'est une requete imbriquée dans une autre... c'est pourquoi j'ai methode.ID. Je ne voulais pas mettre l'intégralité de ma requete car elle est énorme.

En effet, je ne savais pas mais le NOT IN me pompe tout le temps d'execution. Par contre j'ai du mal a le remplacer ... sinon je peux faire 2 requetes mais j'aurais aimé eviter.

Pour info, j'arrive à éxécuter sans erreur la requete que tu m'as donné avec le NOT EXISTS mais je n'obtiens pas le bon résultat.

**zekmek** · 25/09/2006, 18h07

voila plus d'infos ...

Code :

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

1
2
3
4
-- Renvoi = 5
SELECT count(distinct(upper(extractvalue (value(images),'//@numfich'))))
FROM ae7qtme1 me1,TABLE ( XMLSEQUENCE ( extract(me1.docxml, '//Image') ) ) images
WHERE me1.ID=29746

Code :

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

1
2
3
4
-- Renvoi = 1323_FORMAT_D , C4AP1NCD , C4AP1NDD , C4AP1NED , C4AP1NFD
SELECT distinct(upper(extractvalue (value(images),'//@numfich')))
FROM ae7qtme1 me1,TABLE ( XMLSEQUENCE ( extract(me1.docxml, '//Image') ) ) images
WHERE me1.ID=29746

Code :

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

1
2
3
4
5
6
7
8
9
10
11
-- Renvoi = C4AP1NCD , C4AP1NDD , C4AP1NED , C4AP1NFD
SELECT
   distinct(upper(mg1.NAME))
   FROM ae7qtmg1 mg1,
   ae7qtme1 me1,
   ae7qtlkc lkc,
   TABLE ( XMLSEQUENCE ( extract(me1.docxml, '//Image') ) ) images
   where me1.id=lkc.FK_METHOD_ID
   and lkc.FK_IMAGE_ID=mg1.ID
   and me1.ID=29746
   and upper(extractvalue (value(images),'//@numfich')) = upper(mg1.NAME)

Maintenant quand j'imbrique toutes les requetes, j'aurais aimé trouvé 1 qui serait l'enregistrement : 1323_FORMAT_D. Or je trouve 0.

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
-- Renvoi = 0
SELECT count(distinct(upper(extractvalue (value(images),'//@numfich'))))
FROM ae7qtme1 me1,TABLE ( XMLSEQUENCE ( extract(me1.docxml, '//Image') ) ) images
WHERE me1.ID=29746
and NOT EXISTS
(
   SELECT
   distinct(upper(mg1.NAME))
   FROM ae7qtmg1 mg1,
   ae7qtme1 me1,
   ae7qtlkc lkc,
   TABLE ( XMLSEQUENCE ( extract(me1.docxml, '//Image') ) ) images
   where me1.id=lkc.FK_METHOD_ID
   and lkc.FK_IMAGE_ID=mg1.ID
   and me1.ID=29746
   and upper(extractvalue (value(images),'//@numfich')) = upper(mg1.NAME)
)

**remi4444** · 25/09/2006, 18h19

Est-ce que ta dernière requête est toujours longue ?

Ce qu'il faut faire avant tout, passer les stats sur ton schéma:

Code :

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

1
2
3
4
5
 
begin
dbms_stats.gather_schema_stats('TON_USER');
end;
/

Si la lenteur persiste, il faut te poser la question suivante (et y répondre...

): "Quelle est le (ou les) critère(s) qui fait que ma requête ne me renvoie que quelques lignes et non pas des millions ?".

**remi4444** · 25/09/2006, 18h52

Remarques subsidiaires:

- Utiliser le meme alias (me1) dans la requête et la sous-requête, c'est pas génial, ça veux dire qu'il n'y a pas de jointure entre les 2... (remplacée par la répétition de "me1.ID=29746" ). La jointure à faire serait plutot du style "sous_alias_me1.ID = me1.ID"

- ce n'est pas la peine d'utiliser un "distinct" dans la sous requête. Les clauses de filtrage exists/not exists sont justement optimisées pour s'arréter au 1ier enregistrement trouvé.

- si les tables ae7qtmg1 et ae7qtlkc sont plutot grosses, il faut s'assurer que leurs colonnes respectives mg1.ID et FK_METHOD_ID sont bien indexées.

Question:
Est-ce que les XML sont volumineux ?

**McM** · 26/09/2006, 00h16

Que donne ceci ?

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
SELECT COUNT(DISTINCT(UPPER(extractvalue (VALUE(images),'//@numfich'))))
FROM AE7QTME1 me1,
    TABLE ( XMLSEQUENCE ( EXTRACT(me1.docxml, '//Image') ) ) images
WHERE me1.ID=29746
AND NOT EXISTS
              (
                 SELECT 1
                 FROM AE7QTMG1 mg1,
                      AE7QTLKC lkc
                 WHERE lkc.fk_method_id = me1.ID
                 AND mg1.ID = lkc.fk_image_id
                 AND UPPER(extractvalue (VALUE(images),'//@numfich')) = UPPER(mg1.NAME)
              )

**zekmek** · 26/09/2006, 09h35

héhé, cette dernière requete marche super bien, elle renvoie le bon resultat mais par contre elle met autant de temps que la requete d'origine c'est a dire 73 000 ms ...

**McM** · 26/09/2006, 10h25

Faut des infos sur les tables, fichiers et l'explain plan.

**zekmek** · 26/09/2006, 10h35

Voici les infos sur les 3 tables.
J'ai un index sur la colonne NAME dans AE7QTMG1.

Par contre je n'arrive pas a creer l'index suivant:

Code :

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

CREATE INDEX AE7QTME6 ON AE7QTME1(extractValue(DOCXML,'//Image//@numfich'));

il me met:

longueur maximum de clé atteinte

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
 
CREATE TABLE AE7QTME1
(
    ID 					  NUMBER NOT NULL,
    PARENT_ID 			  NUMBER,
    VERSION               NUMBER DEFAULT '0' NOT NULL,
    METHOD_VERSION		  NUMBER DEFAULT '0' NOT NULL,
	WORKFLOW_ID			  NUMBER, 
    CODE				  VARCHAR2 (10),
    CODE_NATURE			  VARCHAR2 (1),
    LANGUE_ORIGINE 		  VARCHAR2(5) NOT NULL,
    CANCELED			  NUMBER,
    CREE				  NUMBER,
    ETAT				  VARCHAR2(10)		DEFAULT 'INIT' NOT NULL,
    THEO_BEGIN_DATE		  DATE,
    THEO_END_DATE		  DATE,
    REAL_BEGIN_DATE		  DATE,
    REAL_END_DATE		  DATE,    
    CHARGE    			  NUMBER 	    	NOT NULL,
    IMPORTANCE			  VARCHAR2(10)      DEFAULT '0' NOT NULL,
    ARCHIVAGE             NUMBER            DEFAULT '0' NOT NULL,
    MARK       			  VARCHAR2(100),
    LAST_BAT_DATE         TIMESTAMP(6),
    FIRST_DIFFUSION_DATE  TIMESTAMP(6),
    LAST_DIFFUSION_DATE	  TIMESTAMP(6),
    TYPE_REPRISE	      VARCHAR2 (20),
	TYPE_DIFFUSION        VARCHAR2 (20),
	ERROR_DIFFUSION       NUMBER,
    DOCXML 				  XMLTYPE ,
    FK_OPE_ID			  NUMBER NOT NULL
) TABLESPACE AE7QDDA3;
 
ALTER TABLE AE7QTME1
    ADD (CONSTRAINT AE7QCME1
PRIMARY KEY (ID) USING INDEX TABLESPACE AE7QDIX3);
 
ALTER TABLE AE7QTME1
   ADD CONSTRAINT AE7QIOS1 FOREIGN KEY (WORKFLOW_ID)
   REFERENCES AE7QTOS1 (ID)
;
 
ALTER TABLE AE7QTME1
    ADD CONSTRAINT AE7QIME1 FOREIGN KEY (FK_OPE_ID)
    REFERENCES AE7QTOP1 (ID)
;

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
 
CREATE TABLE AE7QTLKC
(
	FK_METHOD_ID NUMBER,
	FK_IMAGE_ID NUMBER
) TABLESPACE AE7QDDA1;
-- clé primaire
ALTER TABLE AE7QTLKC
ADD (CONSTRAINT AE7QCLKC
PRIMARY KEY (FK_METHOD_ID, FK_IMAGE_ID)  USING INDEX TABLESPACE AE7QDIX1);
-- Clé étrangère vers la table des méthodes
ALTER TABLE AE7QTLKC
ADD CONSTRAINT AE7QILKT FOREIGN KEY (FK_METHOD_ID)
REFERENCES AE7QTME1 (ID)
;
-- Clé étrangère vers la table des images
ALTER TABLE AE7QTLKC
ADD CONSTRAINT AE7QILKU FOREIGN KEY (FK_IMAGE_ID)
REFERENCES AE7QTMG1 (ID)
;

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
 
CREATE TABLE AE7QTMG1
(
    ID NUMBER NOT NULL,
    VERSION   NUMBER   DEFAULT '0' NOT NULL,
    CREATION_DATE TIMESTAMP(6),
    NAME VARCHAR2 (10)
) TABLESPACE AE7QDDA1;
 
ALTER TABLE AE7QTMG1
    ADD (CONSTRAINT AE7QCMG1
PRIMARY KEY (ID) USING INDEX TABLESPACE AE7QDIX1);
 
ALTER TABLE AE7QTMG1
	ADD (CONSTRAINT AE7QCMG2 UNIQUE(NAME) USING INDEX TABLESPACE AE7QDIX1);

**McM** · 26/09/2006, 11h22

Je pense que dans ton cas c'est le fichier XML qui est gênant (Mais j'en ai jamais utilisé). Quelle est sa taille ?

Pour éviter de refaire des extracts : tu prend tout et tu enlèves ce que tu veux pas :

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
SELECT COUNT (*)
FROM(
		SELECT UPPER(extractvalue (VALUE(images),'//@numfich'))
		FROM AE7QTME1 me1,
		    TABLE ( XMLSEQUENCE ( EXTRACT(me1.docxml, '//Image') ) ) images
		WHERE me1.ID=29746
	MINUS
		SELECT UPPER(mg1.NAME)
		FROM AE7QTMG1 mg1,
		     AE7QTLKC lkc
		WHERE lkc.fk_method_id = 29746
		AND mg1.ID = lkc.fk_image_id
	)

PS : Le MINUS fait un distinct

Optimisation de requete

Oracle

Discussions similaires

Partager

Partager