Bonjour,

dans un datawarehouse, j'ai classiquement une table facture et lignes de factures. La table facture fait environ 1Go, contre 10Go pour les lignes de factures. Je n'ai pas l'option de partitionning.

Lors de la construction de gros agrégats, j'ai besoin de :
- récupérer x axes temps (year to date, month to date, derniere semaine, les historiques équivalents)
- des informations référentielles (la classification des clients et la classification des articles, ...)
- sommer le CA, compter le nombre de clients, le nombre de lignes de livraison
- faire des sommes équivalentes, mais en ne prenant que les clients qui ont fait plus de 200€ au cours de la dernière période (glissantes).

La première approche conduit à une requête du type :

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
select id_lap -- période de temps
	, id_sfc -- sous famille client
	, id_sfa -- sous famille article
	, avg(nb_lg_par_livraison) -- nombre de lignes par livraison
	, sum(ca) -- CA
	, count(distinct id_cln) -- nombre de clients
	, sum(case when id_cln_200 then ca) -- CA des clients ayant fait plus de 200€ sur le mois
	, count(distinct id_cln_200)
from (
		select facture.id_cln, facture.id_sfc, article.id_sfa, ligne_de_facture.ca
			-- id_per est une période temps (4 ou 5 semaines suivant les mois) 
			, case when sum(ca) over (partition by facture.id_cln, calendrier.id_per) > 200 then id_cln end id_cln_200
		from facture, ligne_de_facture, calendrier_periode, article, client
		where facture.id_fac = ligne_de_facture.id_fac
		and facture.dat_fac = calendrier_periode.id_jour
		and article.id_art = ligne_de_facture.id_art
		and client.id_cln = facture.id_cln
     )
     , calendrier
where fact.dat_fact between calendrier.id_sem and calendrier.id_sem + 6
Modéle de données
calendrier_periode : id_jour (05/05/2006) et la période correspondante (01/05/2006)
facture : id_cln (id du client), id_fac (id de la facture), dat_fac (date)...
ligne_de_facture : id_fac, id_art (id de l'article), CA (prix de la ligne de facture)
article : id_art, id_sfa (sous famille article)
client : id_cln, id_sfc (sous famille client)
calendrier : id_lap (code de la période. IE YTD 2006, MTD 09/2006), id_sem (la liste de tous les lundis qui sont dans la période)
Coté optim, j'ai joué sur le paramétre non documentation _smm_max_size et _pga_max_size pour avoir jusque 500Mo de mémoire pour les tris (dans la PGA)
Je suis en train de faire un bench en stockant toutes les jointures dans une table intermédiaire qui est stocké sous la forme d'un IOT (clé = id_cln + dat_fac + id_art + id_fac)
Je construit bien sur une table intermédiaire pour filtrer les dates qui ne m'interressent pas (pas d'option partitionning), en utilisant la compression d'extent (4Go au final)

Est-ce que quelqu'un a déjà été confronté à ce genre de requête ? Quelles sont les méthodes d'optimisation qui ont été retenue / envisagée ?

L'agrégation que je dois construire est très large en terme d'historique, mais conduit à une table trés petite (30Mo) : j'ai en effet besoin d'avoir les périodes suivantes :
période en cours (1 mois), dernière période échue (1 mois), derniere semaine échue,
year to date (max 1 an), 12 mois glissants
les périodes de temps qui me permettent de comparer avec la valeur année précédente

par avance merci