Not enough memory for CONNECT BY operation when using interval '3' minute-CodePudding

If I use the following code for my report it works fine:

select 
to_date(substr(:P1_START,1,15), 'YYYY-MM-DD"T"HH24MI')   (rownum/480)  s
from dual
connect by 
to_date(substr(:P1_START,1,15), 'YYYY-MM-DD"T"HH24MI')   (rownum/480)  <= to_date(substr(:P1_END,1,15), 'YYYY-MM-DD"T"HH24MI')

But if I change (rownum/480) to interval '3' minute I get a ORA-30009: Not enough memory for CONNECT BY operation error on the report

select 
to_date(substr(:P1_START,1,15), 'YYYY-MM-DD"T"HH24MI')   interval '3' minute  s
from dual
connect by 
to_date(substr(:P1_START,1,15), 'YYYY-MM-DD"T"HH24MI')   interval '3' minute  <= to_date(substr(:P1_END,1,15), 'YYYY-MM-DD"T"HH24MI')

Is they a way I can keep using interval '3' minute?

CodePudding user response：

Well... the problem is that the statement is generating so much rows that all the computing memory you have available is used up. Do you need that many rows ? Have you checked how many rows oracle is trying to generate for you when it is throwing that error ? If you double the memory and then you run the same query where the interval between start and end date is double as large it will also error out.

Do you really need to generate this table on the fly with a CONNECT BY statement ? Maybe you could also just create an actual table with 3 minute intervals and join to that table in your query. It would give you the same functionality and be a lot more efficient from a db perspective: that table can be indexed and it would be shared across user sesssions.