Row generator that doesn't use memory( was: Advice on calendar function)

On Mon, 28 Aug 2006 19:42:41 +0200, "Michel Cadot" <micadot{at}altern{dot}org> wrote:

>
><artmt_at_hotmail.com> a écrit dans le message de news: 1156784801.685662.314210_at_i42g2000cwa.googlegroups.com...
>|I am considering writing a table function to return a row for each
>| calendar date within supplied data range.
>|
>| Here is the SQL:
>|
>|
>| SELECT to_date(day_year_seq||'-'||year,'ddd-yyyy'),day_abs_seq
>| FROM(SELECT day_abs_seq,
>| year,
>| row_number() over(partition by year order by day_abs_seq)
>| day_year_seq
>| FROM (SELECT rownum day_abs_seq
>| FROM dba_objects
>| WHERE rownum <= :dt_end-trunc(:dt_start,'year')+1) a,
>| (SELECT year,
>| to_date('01-jan-'||year)-trunc(:dt_start,'year')+1
>| day_start_abs_seq,
>| to_date('31-dec-'||year)-trunc(:dt_start,'year')+1
>| day_end_abs_seq
>| FROM(SELECT rownum+to_number(to_char(:dt_start,'yyyy'))-1
>| year
>| FROM dba_objects
>| WHERE rownum <=
>| to_number(to_char(:dt_end,'yyyy'))-to_number(to_char(:dt_start,'yyyy'))+1))
>| b
>| WHERE a.day_abs_seq between b.day_start_abs_seq and
>| b.day_end_abs_seq);
>|
>|
>| Any comments of the approach?
>| In particular are there advantages to using the dictionary vs
>| non-dictionary row source?
>| Is dba_objects a good choice?
>| Any other toughts?
>|
>| Thanks
>| Art
>|
>
>SQL> with dates as (select sysdate-7 dt_start, sysdate+7 dt_end from dual)
> 2 select dt_start+rownum-1 "Date"
> 3 from dates
> 4 connect by level <= dt_end-dt_start
> 5 /
>Date
>----------
>08/21/2006
>08/22/2006
>08/23/2006
>08/24/2006
>08/25/2006
>08/26/2006
>08/27/2006
>08/28/2006
>08/29/2006
>08/30/2006
>08/31/2006
>09/01/2006
>09/02/2006
>09/03/2006
>
>14 rows selected.
>
>Regards
>Michel Cadot
>
>

Hi Michael,

I like the way you use the CONNECT BY clause to create a row generator, and a quick one at that. I used

SELECT NULL FROM dual GROUP BY CUBE (1,1,1...)

to generate rows, but this gets slow very quickly. Your solution is much faster.

Your solution still uses memory though, so a question to all: is there a way make a subquery that generates rows without the memory usage getting higher the more rows you generate? And let's forbid the use of existing tables and views, except for DUAL.

Jaap. Received on Mon Aug 28 2006 - 14:02:37 CDT