Laufzeit von Stored Procedure verkürzen

[jobo]

Ich habe den ersten Teil Deiner Unionabfrage umgebaut, so dass schätzungsweise ein Table Scan reicht.
Ist in Oracle gemacht, dürfte aber keine große Rolle spielen. Die Feldtypen habe ich natürlich "optimiert".

Statt der Split Funktion habe ich aus Bequemlichkeit eine normale Tabelle verwendet, geht ja nur um ein Beispiel.
Die Tabelle hat keine Indizes oder Constraints, knapp 1 Mio Datensätze.
Als Test habe ich noch eine Datumsspalte im 2. Select verwendet. Wirst Du wahrscheinlich auch haben / brauchen.

Code:

10:01:02 SQL> select * from fnsplit;
 
BRAND      PRODUCTLINE
---------- -----------
Mercedes   Carrera
Mercedes   B-Klasse
Porsche    Cayenne
Porsche    Boxter
Porsche    Carrera
 
Executed in 0,125 seconds
 
10:01:19 SQL> 
10:01:46 SQL>        Select 'Warehouse' as Storage, 'SKUs' as Typ,
           2              sum(PLCMIN25), sum(PLC_25), sum( PLC_30), sum( PLC_40 ), sum(PLC_50_55),
           3              sum(PLC_60), sum(PLC_70), sum(PLC_TOTAL) from (
           4              select
           5                 case when ALL_LowestPLC_SKU IN ('#', '00', '10', '20')
           6                      then 1 else 0 end as PLCmin25,
           7                 case when ALL_LowestPLC_SKU = ('25')
           8                      then 1 else 0 end as PLC_25,
           9                 case when ALL_LowestPLC_SKU = ('30')
          10                      then 1 else 0 end as PLC_30,
          11                 case when ALL_LowestPLC_SKU = '40'
          12                      then 1 else 0 end as PLC_40,
          13                 case when ALL_LowestPLC_SKU IN ( '50', '55')
          14                      then 1 else 0 end as PLC_50_55,
          15                 case when ALL_LowestPLC_SKU = '60'
          16                      then 1 else 0 end as PLC_60,
          17                 case when ALL_LowestPLC_SKU = '70'
          18                      then 1 else 0 end as PLC_70,
          19                 1 as PLC_total,
          20                 a.all_date
          21             From inv_all a
          22            Where ALL_Brand In (SELECT Brand FROM fnSplit)
          23              And ALL_ProductLine In (SELECT Productline FROM fnSplit)
          24              AND (ALL_AvailableStock > 0 OR
          25                   ALL_Pir > 0 OR
          26                   ALL_Pir1 > 0 OR
          27                   ALL_Pir2 > 0 OR
          28                   ALL_Pir3 > 0))x
10:01:46 SQL> /
 
STORAGE   TYP  SUM(PLCMIN25) SUM(PLC_25) SUM(PLC_30) SUM(PLC_40) SUM(PLC_50_55) SUM(PLC_60) SUM(PLC_70) SUM(PLC_TOTAL)
--------- ---- ------------- ----------- ----------- ----------- -------------- ----------- ----------- --------------
Warehouse SKUs           231      282634      183282       98890          98846      183942         110         999889
 
Executed in 1,155 seconds
 
10:01:50 SQL> delete from fnsplit s where s.brand ='Mercedes';
 
2 rows deleted
 
Executed in 0,047 seconds
 
10:03:05 SQL> 
10:03:22 SQL>        Select 'Warehouse' as Storage, 'SKUs' as Typ,
           2              sum(PLCMIN25), sum(PLC_25), sum( PLC_30), sum( PLC_40 ), sum(PLC_50_55),
           3              sum(PLC_60), sum(PLC_70), sum(PLC_TOTAL) from (
           4              select
           5                 case when ALL_LowestPLC_SKU IN ('#', '00', '10', '20')
           6                      then 1 else 0 end as PLCmin25,
           7                 case when ALL_LowestPLC_SKU = ('25')
           8                      then 1 else 0 end as PLC_25,
           9                 case when ALL_LowestPLC_SKU = ('30')
          10                      then 1 else 0 end as PLC_30,
          11                 case when ALL_LowestPLC_SKU = '40'
          12                      then 1 else 0 end as PLC_40,
          13                 case when ALL_LowestPLC_SKU IN ( '50', '55')
          14                      then 1 else 0 end as PLC_50_55,
          15                 case when ALL_LowestPLC_SKU = '60'
          16                      then 1 else 0 end as PLC_60,
          17                 case when ALL_LowestPLC_SKU = '70'
          18                      then 1 else 0 end as PLC_70,
          19                 1 as PLC_total,
          20                 a.all_date
          21             From inv_all a
          22            Where ALL_Brand In (SELECT Brand FROM fnSplit)
          23              And ALL_ProductLine In (SELECT Productline FROM fnSplit)
          24              AND (ALL_AvailableStock > 0 OR
          25                   ALL_Pir > 0 OR
          26                   ALL_Pir1 > 0 OR
          27                   ALL_Pir2 > 0 OR
          28                   ALL_Pir3 > 0))x
10:03:22 SQL> /
 
STORAGE   TYP  SUM(PLCMIN25) SUM(PLC_25) SUM(PLC_30) SUM(PLC_40) SUM(PLC_50_55) SUM(PLC_60) SUM(PLC_70) SUM(PLC_TOTAL)
--------- ---- ------------- ----------- ----------- ----------- -------------- ----------- ----------- --------------
Warehouse SKUs           231      199650      127974       71214          71170      128590         110         709379
 
Executed in 1,466 seconds
 
10:03:26 SQL> 
10:04:05 SQL>        Select 'Warehouse' as Storage, 'SKUs' as Typ, to_char(all_date, 'YYMM'),
           2              sum(PLCMIN25), sum(PLC_25), sum( PLC_30), sum( PLC_40 ), sum(PLC_50_55),
           3              sum(PLC_60), sum(PLC_70), sum(PLC_TOTAL) from (
           4              select
           5                 case when ALL_LowestPLC_SKU IN ('#', '00', '10', '20')
           6                      then 1 else 0 end as PLCmin25,
           7                 case when ALL_LowestPLC_SKU = ('25')
           8                      then 1 else 0 end as PLC_25,
           9                 case when ALL_LowestPLC_SKU = ('30')
          10                      then 1 else 0 end as PLC_30,
          11                 case when ALL_LowestPLC_SKU = '40'
          12                      then 1 else 0 end as PLC_40,
          13                 case when ALL_LowestPLC_SKU IN ( '50', '55')
          14                      then 1 else 0 end as PLC_50_55,
          15                 case when ALL_LowestPLC_SKU = '60'
          16                      then 1 else 0 end as PLC_60,
          17                 case when ALL_LowestPLC_SKU = '70'
          18                      then 1 else 0 end as PLC_70,
          19                 1 as PLC_total,
          20                 a.all_date
          21             From inv_all a
          22            Where ALL_Brand In (SELECT Brand FROM fnSplit)
          23              And ALL_ProductLine In (SELECT Productline FROM fnSplit)
          24              AND (ALL_AvailableStock > 0 OR
          25                   ALL_Pir > 0 OR
          26                   ALL_Pir1 > 0 OR
          27                   ALL_Pir2> 0 OR
          28                   ALL_Pir3> 0)
          29                   )x
          30            group by to_char(all_date, 'YYMM')
          31  /
 
STORAGE   TYP  TO_CHAR(ALL_DATE,'YYMM') SUM(PLCMIN25) SUM(PLC_25) SUM(PLC_30) SUM(PLC_40) SUM(PLC_50_55) SUM(PLC_60) SUM(PLC_70) SUM(PLC_TOTAL)
--------- ---- ------------------------ ------------- ----------- ----------- ----------- -------------- ----------- ----------- --------------
Warehouse SKUs 1103                                63       49242       31428       17682          17672       31590          30         174355
Warehouse SKUs 1102                                63       44234       28096       16022          16010       28270          30         157647
Warehouse SKUs 1101                                63       54450       34902       19422          19410       35070          30         193467
Warehouse SKUs 1104                                42       51724       33548       18088          18078       33660          20         183910
 
Executed in 0,889 seconds
 
10:04:08 SQL>