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

On Wed, 30 Aug 2006 18:19:47 +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: 44f54f1e.3241437_at_news.hetnet.nl...
>| 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.
>
>I don't understand what you meant with
>"I guess the solution cannot be contained within the SELECT itself..."
>Isn't what I've done in the last query (select * from table(gen_row(10));)?
>
>Regards
>Michel Cadot
>
>

Sorry about not being clearer: with 'contained within' I meant without having to create something external to the SELECT first, like the TYPE and the FUNCTION in your solution.

Regards,

Jaap. Received on Thu Aug 31 2006 - 13:19:29 CDT