row_number() issue - exponentially increases the record count
Hello all -
I'm trying to use the row_number to split the amount by the number of months between the dates and create one new row for the each month.
The use of row_number() is causing the data to be exponentially increase based -i.e currently there are 3 distinct records and each record should only return 2 rows but the this returns 12 rows and so on if the data increases which is causing a performance issue . I could put the entire query below again as a derived table and add a where condition as where rn < = tot_months but I just want to know if there is any way to limit the rows to the number of months reason being - for 48 distinct rows it is creating apprx 12000 rows and the performance has become a killer. You can try by adding more rows and see what I mean especially when the span between months is more -
Any help is greatly appreciated -
WITH testdata AS
(SELECT 1 id, 1001 acct_no, 2001 cnt_id, to_date('01/17/2007','mm/dd/yyyy') start_date, to_date('03/16/2007', 'mm/dd/yyyy') end_date, 8000 amount
FROM dual
UNION ALL
SELECT 2 id, 1002 acct_no, 2002 cnt_id, to_date('01/18/2007','mm/dd/yyyy') start_date, to_date('03/19/2007', 'mm/dd/yyyy') end_date, 90000 amount
FROM dual
UNION ALL
SELECT 3 id, 1003 acct_no, 2003 cnt_id ,to_date('02/08/2007','mm/dd/yyyy') start_date, to_date('04/11/2007', 'mm/dd/yyyy') end_date, 125000 amount
FROM dual)
SELECT DISTINCT id,
acct_no,
cnt_id,
start_date,
end_date,
amount,
row_number() over(PARTITION BY id , start_date ORDER BY cnt_id) rn,
ROUND(months_between(end_date, start_date)) tot_months
FROM
(SELECT id,
acct_no,
cnt_id,
start_date,
end_date,
amount
FROM testdata t
where ROUND(months_between(end_date, start_date)) > 1 )
t CONNECT BY LEVEL <= ROUND(months_between(end_date, start_date))
order by acct_no,cnt_id, start_date
Thanks.