SQL & PL/SQL

Hi all.

I am using a recursive with query to split accounting data, according to competence.

My base data, item_amount has the following structure (:

• item
• start_date
• end_date
• amount

and I should end up with the structure monthy_item_amount:

• item
• month
• monthly_amount

The math behind the process is quite simple

where month m range from month of start_date to month of end date.

The recursive with implementation is straightforward :

with montly_item_amount(item, month, start_date, end_date, amount, monthly_amount) 
as
(
select item, trunc(start_date, 'm') as month, start_date, end_date, amount, 
	   amount * (least(end_date, last_day(start_date)) - start_date) / 
	            (end_date - start_date) as monthly_amount
from item_amount
union all 
select item, add_months(month, 1) as month, start_date, end_date, amount, 
	   amount * (least(end_date, last_day(add_months(month, 1))) - add_months(month, 1)) / 
	            (end_date - start_date)
from montly_item_amount
where add_months(month, 1) <= end_date
)
select item, month, sum(monthly_amount) as monthly_amount 
from monthly_item_amount
group by  item, month

I am applying this query on a large table, 200 M records, where only 3% of record enters the UNION ALL part, and for an average of 3 cycle of recursion.

The time required to process the query is, on our system, Oracle 19C on UNIX multiprocessor, roughly 7 time the time required to process only the upper part of the query (50 mins vs 7).

Is there any explanation for this increase in processing time, given the simple structure of the query?

Many thanks for your opinion.

Maurizio