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

On Mon, 28 Aug 2006 21:53:51 +0200, "Michel Cadot" <micadot{at}altern{dot}org> wrote:

>
>"Jaap W. van Dijk" <j.w.vandijk.removethis_at_hetnet.nl> a écrit dans le message de news: 44f33b2a.2760203_at_news.hetnet.nl...
>| 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.
>
>To avoid performances issue with CUBE you can use a cartesian
>product of multiple CUBE:
>
>For &1 <= 1024:
>SELECT ROWNUM
>FROM ( SELECT 1 FROM DUAL GROUP BY CUBE (1,2,3,4,5,6,7,8,9,10) )
>WHERE ROWNUM <= &1
>/
>
>For &1 <= 1024*1024
>SELECT ROWNUM
>FROM ( SELECT 1 FROM DUAL GROUP BY CUBE (1,2,3,4,5,6,7,8,9,10) ),
> ( SELECT 1 FROM DUAL GROUP BY CUBE (1,2,3,4,5,6,7,8,9,10) )
>WHERE ROWNUM <= &1
>/
>
>For &1 <= 1024*1024*1024:
>SELECT ROWNUM
>FROM ( SELECT 1 FROM DUAL GROUP BY CUBE (1,2,3,4,5,6,7,8,9,10) ),
> ( SELECT 1 FROM DUAL GROUP BY CUBE (1,2,3,4,5,6,7,8,9,10) ),
> ( SELECT 1 FROM DUAL GROUP BY CUBE (1,2,3,4,5,6,7,8,9,10) )
>WHERE ROWNUM <= &1
>/
>
>To avoid memory consumption you can use PL/SQL:
>
>SQL> create type row_table as table of number;
> 2 /
>
>Type created.
>
>SQL> Create or replace function gen_row (num_rows in number) return row_table
> 2 parallel_enable pipelined is
> 3 begin
> 4 for x in 1..num_rows loop
> 5 pipe row (x);
> 6 end loop;
> 7 return;
> 8 end;
> 9 /
>
>Function created.
>
>SQL> select * from table(gen_row(10));
>COLUMN_VALUE
>------------
> 1
> 2
> 3
> 4
> 5
> 6
> 7
> 8
> 9
> 10
>
>10 rows selected.
>
>Regards
>Michel Cadot
>
>
>

Nice way to use a table function (always on the lookout for outside-the-box uses, aren't you?).

I guess the solution cannot be contained within the SELECT itself, without having to create something first. The only way to do that I guess is to use DUAL and that will mean memory consumption.

Jaap. Received on Wed Aug 30 2006 - 03:50:56 CDT