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: 44f7269e.346046_at_news.hetnet.nl...
| 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.

Oh! Yes this is what you have to pay to avoid memory consumption during query execution but think you have to create it once on a "public" schema (one that contains all useful stuff for anybody) and then use anywhere.

Regards
Michel Cadot Received on Thu Aug 31 2006 - 13:35:58 CDT