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

From: Michel Cadot <micadot{at}altern{dot}org>
Date: Mon, 28 Aug 2006 21:53:51 +0200
Message-ID: <44f349cf$0$975$626a54ce@news.free.fr>

"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 Received on Mon Aug 28 2006 - 14:53:51 CDT