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

"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 Received on Wed Aug 30 2006 - 11:19:47 CDT