Bonjour,

Je penche actuellement sur un package Oracle qui contient entre autre la procédure suivante:

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
PROCEDURE calculate_ytd_data
IS
BEGIN
INSERT
     INTO A.aa_idp_facts_fin
    (
      SCENARIO_ID, YEAR_ID, MONTH_ID, BU_ID, CUSTOMER_ID, SECTOR_ID, PRODUCT_ID, SERVICEAREA_ID, OU_SHORT_DESC,
      THIRD_PARTY_REVENUE, INTERCOMPANY_REVENUE, TOTAL_COST_OF_SITE, TOTAL_ALLOCATED_OVERHEADS, REVENUE_OVERHEADS, COST_OVERHEADS, SLA_OVERHEADS,
      COUNTRY_OVERHEADS, NON_OPERATING_OVERHEADS, REGIONAL_DIVISIONAL_OVERHEADS, TOTAL_NET_REVENUE, GROSS_PROFIT, TOTAL_COUNTRY_OVERHEADS,
      RESULTS_BEFORE_EBIT, EBIT, RECEIVABLES_UPTO_30, RECEIVABLES_31_TO_60, RECEIVABLES_61_TO_90, RECEIVABLES_91_TO_120, RECEIVABLES_121_TO_150,
      RECEIVABLES_151_TO_180, RECEIVABLES_181_TO_210, RECEIVABLES_211_TO_270, RECEIVABLES_271_TO_360, RECEIVABLES_OVER_360, UNALLOCATED_CASH, DSO, CREDIT_TERMS,
      AMOUNT_OVERDUE, BAD_DEBT, NUMBER_OF_INVOICES, TAX_RATE, THIRD_PARTY_RECEIVABLES, CURRENCY_ID, SOURCE_ID, UNCOLLECTABLE_CASH, TIMESERIES_ID, LOCAL_CUSTOMER_ID
    )
   SELECT /*+ ALL_ROWS */ SCENARIO_ID, YEAR_ID, period_ytd_id MONTH_ID, BU_ID, CUSTOMER_ID, SECTOR_ID, PRODUCT_ID, SERVICEAREA_ID, OU_SHORT_DESC,
    sum(THIRD_PARTY_REVENUE) THIRD_PARTY_REVENUE, sum(INTERCOMPANY_REVENUE) INTERCOMPANY_REVENUE, sum(TOTAL_COST_OF_SITE) TOTAL_COST_OF_SITE, sum(TOTAL_ALLOCATED_OVERHEADS) TOTAL_ALLOCATED_OVERHEADS, sum(REVENUE_OVERHEADS) REVENUE_OVERHEADS, sum(COST_OVERHEADS) COST_OVERHEADS, sum(SLA_OVERHEADS) SLA_OVERHEADS,
    sum(COUNTRY_OVERHEADS) COUNTRY_OVERHEADS, sum(NON_OPERATING_OVERHEADS) NON_OPERATING_OVERHEADS, sum(REGIONAL_DIVISIONAL_OVERHEADS) REGIONAL_DIVISIONAL_OVERHEADS, sum(TOTAL_NET_REVENUE) TOTAL_NET_REVENUE, sum(GROSS_PROFIT) GROSS_PROFIT, sum(TOTAL_COUNTRY_OVERHEADS) TOTAL_COUNTRY_OVERHEADS,
    sum(RESULTS_BEFORE_EBIT) RESULTS_BEFORE_EBIT, sum(EBIT) EBIT, null RECEIVABLES_UPTO_30, null RECEIVABLES_31_TO_60, null RECEIVABLES_61_TO_90, null RECEIVABLES_91_TO_120, null RECEIVABLES_121_TO_150,
    null RECEIVABLES_151_TO_180, null RECEIVABLES_181_TO_210, null RECEIVABLES_211_TO_270, null RECEIVABLES_271_TO_360, null RECEIVABLES_OVER_360, null UNALLOCATED_CASH, null DSO, null CREDIT_TERMS,
    null AMOUNT_OVERDUE, null BAD_DEBT, null NUMBER_OF_INVOICES, null TAX_RATE, null THIRD_PARTY_RECEIVABLES, CURRENCY_ID, SOURCE_ID, null UNCOLLECTABLE_CASH, 'YTD' TIMESERIES_ID, LOCAL_CUSTOMER_ID
 
     FROM A.aa_facts_fin
  JOIN A.m_years
       ON ( years_id = year_id )
  JOIN
    (SELECT *
       FROM B.s_period2ytd
      WHERE ytd_type = 'CURR_YTD'
    )
       ON ( period_id   = month_id )
    WHERE timeseries_id = 'MTD'
 GROUP BY SCENARIO_ID, YEAR_ID, period_ytd_id, BU_ID, CUSTOMER_ID, SECTOR_ID, PRODUCT_ID, SERVICEAREA_ID, OU_SHORT_DESC, CURRENCY_ID, SOURCE_ID, TIMESERIES_ID, LOCAL_CUSTOMER_ID    
    ;
COMMIT;
END calculate_ytd_data;
Cette procédure vient à la fin de 10 autres (une pour vider la table, 6 pour la remplir a partir d'autres tables et 3 pour calculer certaines colonnes) et elle prend à elle seule autant de temps que toutes les autres réunies. La table aa_facts_fin contient au début de la procédure aux alentours de 2 millions de lignes. Je pensais notamment rajouter l'hint /+APPEND NOLOGGING/ mais malheureusement mon expérience en SQL est limitée et je pense qu'on peut beaucoup gagner à retravailler la requête. Je suis donc ouvert à toute suggestion.

Merci d'avance et n'hésitez pas à demander s'il vous faut plus d'informations