Discussion :

[Hugues_78]

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

[orafrance]

bonjour, j'ai reformaté le message pour plus de clarté

Est-ce que tu as regardé ce que peut proposer CUBE, ROLLUP et autres joyeusetés du genre ?

Je te déconseille fortement de toucher aux paramètres cachés, surtout pour un seul besoin très spécifique

[Hugues_78]

Bonjour,

merci pour le reformatage du code et pour la réponse...

les fonctions cube et rollup auraient un intérêt pour limiter le nombre de période temps (id_lap) mais mes périodes d'analyse sont trop complexe pour que ce soit utilisable.

Pour ce qui est du paramétre non documenté, c'est un classique en datawarehouse :
Oracle a introduit le pga_aggregate_target sur la 9i, mais également une limitation sur l'allocation : une session peut prendre au maximum 5% du PGA totale, avec un plafond à 64Mo. Ces seuils passent (de mémoire) à 30% et 100Mo quand on a des process paralléle. (ils ont oubliés la BI...)
==> je modifie donc pour utiliser des choses qui sont plus en phase avec la volumétrie de mes traitements et le nombre d'utilisateur simultanné.

(j'ai 1,5Go de PGA et rarement plus de 3 users actifs simultanné, pour une cinquantaine de connections)
==> repasser en sort/hash_area_size n'est donc pas envisageable et les 64 Mo bien trop petit (temps de traitement divisés par 2 et tris en multipass devenu trés rare dans v$sql_workarea ).

Cdlt

Hugues

[orafrance]

si tu as une base dédiée DWH c'est nickel en effet

[remi4444]

Elle est où ta jointure entre ta table calendrier et ton sous-select ?????

La ça sent le produit cartésien....

[remi4444]

2ieme....

T'es sur que tu t'es pas planté de requête ??
Comment peux-tu mettre une des fonctions de regroupement et des colonnes simples sans meme mettre de "GROUP BY" ?