Optimisation de requete

[crocodingo]

Bonjour,

j'ai un petit souci avec une requete sql. Je travail avec oracle 8i.

Mon probleme est au niveau de la taille de mes tables, je fais une rquete de ce genre :

Code :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
 
SELECT att1, att2, att3,................
FROM
(   SELECT ............
    FROM table1, table2
    WHERE .......
    AND .....
) a, 
(   SELECT attx, max(date) AS madate
    FROM table3, table4
    WHERE.......
    AND .....
    GROUP BY attx) b
WHERE a.cle = b.cle
ORDER BY ..........

table1 : 27 000 000 de lignes
table2 : 180 000 000 de lignes
table3 : 685 000 de lignes
table4 : 4 600 000 lignes

est ce que quelqu'un sait s'il y a un moyen d'effectuer la requete plus rapidement?

Merci bcp

[McM]

peut être en modifiant la requete, mais il faudrait la vraie requete, complète.

[orafrance]

qu'est ce qui motive l'écriture avec 2 sous requêtes ? Tu ne peux pas tout mettre dans le FROM ?

[crocodingo]

Si je peut tout mettre, c'etait pour s'implifier.

Voila la requete :

Code :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
 
SELECT record_date,trans_id,x.buyer_id,dispatch_code1,tax_code,origin,name,string_value,date_value,MONT,BILL_TO
	FROM (	
	SELECT t.record_date,t.trans_id,buyer_id,dispatch_code1,tax_code,t.origin,name,string_value,date_value,amount AS MONT
	FROM transac t,transac_detail d
	WHERE t.TRANS_ID = d.TRANS_ID
	AND buyer_id LIKE 'CTR%'
	AND isolated_invoice_id IS NULL
	AND amount != 0
	AND ((name='parameter.COMPTACOD') OR (name='default.recurring_start') OR (name='default.recurring_stop'))) x, 
	(SELECT buyer_id,max(date_value) AS BILL_TO
	FROM account_operation a, operation_detail d 	
	WHERE a.OPERATION_ID = d.OPERATION_ID 
	AND name = 'bnr.bill_to' 
	GROUP BY buyer_id) y
	WHERE x.buyer_id = y.buyer_id(+)
	ORDER BY buyer_id,trans_id,string_value,date_value;

la 2éme requete me sert juste a recupérer la plus grand date de chaque client et a la positionner dans mon select tout la haut.

le probleme, c'est la structure des table operation_detail et transac_detail qui sont des tables géneriques.

elles possedent 4 colones : id,name,string_value,date_value.
cela permet d'ajouter n'importe quel attribut (le nom de l'attibut dans name, la valeur dans date_value ou string value et le lien vers la reference du champ de la table principale).

je sais pas si je suis clair. Enfin cela donne un nombre de lignes tres important mais j'ai fais cette reque pour tour recuperer d'un coup et traité les données apres.

J'avais dans un premier temps decomposer en plusieur requete que je parcourai avec des curseurs, mais la c'est encore pire....

[orafrance]

et le plan d'exécution ?

[crocodingo]

tu entend quoi par plan d'execution?

[McM]

Pour simplifier :

Code :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
SELECT t.record_date, t.trans_id, buyer_id, dispatch_code1, tax_code, t.origin, NAME, 
		string_value, date_value, amount AS MONT,
		(SELECT MAX(date_value) 
				FROM ACCOUNT_OPERATION a, OPERATION_DETAIL d 	
				WHERE a.operation_id = d.operation_id 
				AND NAME = 'bnr.bill_to' 
				WHERE buyer_id = d.buyer_id -- ou t.buyer_id... Je ne sais pas dans quelle table
				) AS BILL_TO
FROM TRANSAC t, TRANSAC_DETAIL d
WHERE t.TRANS_ID = d.TRANS_ID
AND buyer_id LIKE 'CTR%'
AND isolated_invoice_id IS NULL
AND amount != 0
AND ((NAME='parameter.COMPTACOD') OR (NAME='default.recurring_start') OR (NAME='default.recurring_stop'))
ORDER BY buyer_id,trans_id,string_value,date_value;

[orafrance]

Envoyé par crocodingo

tu entend quoi par plan d'execution?

l'explain plan recherche

[crocodingo]

je m'explique:

je dois recuperer toutes les transaction (de la table transac) dont la date d'enregistrement est superieur à la bill_to (date que je recupere avec ma deuxième requete, la bill_to.)

mais pour aller plus vite, ici je recupere toutes les transac et je trie apres en pl/sql dans mon curseur. Sinon , apres plusieurs heures je n'ai tjs pas de resultat sur la requete...

[orafrance]

tu iras probablement plus vite en SQL, si c'est lent c'est que la requête n'est pas optimisée. Je te conseille de chercher MAX avec OVER et DENSE_RANK

[crocodingo]

ok je vais cherché de ce coté la car la requete que je vous ai montrer marche tres bien sur des tables reduite, mais avec la volumetrie que je vous ai donné tt a l'heure, au bout de 4h30 sa tourne encors et j'ai pas de resultat^^

[McM]

si c'est > et qu'il faut que bill_to existe

Code :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
 
SELECT t.record_date, t.trans_id, buyer_id, dispatch_code1, tax_code, t.origin, NAME, 
	string_value, date_value, amount AS MONT
FROM TRANSAC t, TRANSAC_DETAIL d
WHERE t.TRANS_ID = d.TRANS_ID
AND buyer_id LIKE 'CTR%'
AND isolated_invoice_id IS NULL
AND amount != 0
AND ((NAME='parameter.COMPTACOD') OR (NAME='default.recurring_start') OR (NAME='default.recurring_stop'))
AND date_value > (SELECT MAX(date_value) 
		FROM ACCOUNT_OPERATION a, OPERATION_DETAIL d 	
		WHERE a.operation_id = d.operation_id 
		AND NAME = 'bnr.bill_to' 
		WHERE buyer_id = d.buyer_id -- ou t.buyer_id... Je ne sais pas dans quelle table
		)
ORDER BY buyer_id,trans_id,string_value,date_value;

Si c'est > mais aussi si bill_to is null

Code :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
 
SELECT t.record_date, t.trans_id, buyer_id, dispatch_code1, tax_code, t.origin, NAME, 
	string_value, date_value, amount AS MONT
FROM TRANSAC t, TRANSAC_DETAIL d
WHERE t.TRANS_ID = d.TRANS_ID
AND buyer_id LIKE 'CTR%'
AND isolated_invoice_id IS NULL
AND amount != 0
AND ((NAME='parameter.COMPTACOD') OR (NAME='default.recurring_start') OR (NAME='default.recurring_stop'))
AND NOT EXISTS (SELECT 1 
		FROM ACCOUNT_OPERATION a, OPERATION_DETAIL d 	
		WHERE a.operation_id = d.operation_id 
		AND NAME = 'bnr.bill_to' 
		WHERE buyer_id = d.buyer_id -- ou t.buyer_id... Je ne sais pas dans quelle table
		AND date_value >= d.date_value -- ou t.date_value.
		)
ORDER BY buyer_id,trans_id,string_value,date_value;

[McM]

Vu la volumétrie, j'espère qu'il y a de bons indexes (sur NAME)

[crocodingo]

ba le pb c'es qu'il y a pas d'index sur name....

par contre la table operation_detail et transac_detail sont partitionnées sur la record_date.

[crocodingo]

Sinon une jointure entre une table de 20 000 000 de lignes et une table de 200 000 000 de ligne, sa donne quoi au niveau du tps d'execution?

[Mehdilis]

Bonsoir cher ami, normalement pour étudier ta requête tu devrais nous fournir un plan d’exécution, mais quand même j’essaierais en premier temps de te donner quelques remarques qui puissent l’optimiser .

1 . Pense a créer un index sur la colonne operation_id de chaqu’une des tables ACCOUNT_OPERATION a, OPERATION_DETAIL d, s’il ne l’ont pas.
2.Pense aussi a créer un index sur la colonne TRANS_ID des tables transac et transac_detail
s’il ne l’ont pas.
3.Si amount !=0 , si cette colonne est indexée, oracle le négligera aussi, ==è solution si l’index n’existe pas crée le et transforme cette clause en (amount> 0) and (amount < 0).

Un autre conseil : en général, une jointure externe est beaucoup plus rapide qu’un existe ou not existe.

Teste( le meilleur maître est monsieur TEST) et tiens moi au courant, n’oublie pas le plan d’exécution !!!.

[orafrance]

Envoyé par crocodingo

ba le pb c'es qu'il y a pas d'index sur name....

par contre la table operation_detail et transac_detail sont partitionnées sur la record_date.

oui mais le record_date n'est pas utilisé si je ne m'abuse alors si t'as pas d'index GLOBAL alors les perfs ne seront pas super

Envoyé par crocodingo

Sinon une jointure entre une table de 20 000 000 de lignes et une table de 200 000 000 de ligne, sa donne quoi au niveau du tps d'execution?

ça dépend de l'indexation