Advice on calendar function
This is a discussion on Advice on calendar function within the Oracle Database forums, part of the Database Server Software category; --> I am considering writing a table function to return a row for each calendar date within supplied data range. ...
| |||||||
| FAQ | Members List | Calendar | Search | Today's Posts | Mark Forums Read |
02-25-2008, 07:39 AM
| ||||
| ||||
 Advice on calendar function 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      |
|
02-25-2008, 07:39 AM
| |||
| |||
| Re: Advice on calendar function <artmt@hotmail.com> a écrit dans le message de news: 1156784801.685662.314210@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 |
|
02-25-2008, 07:39 AM
| |||
| |||
| Row generator that doesn't use memory( was: Advice on calendar function) On Mon, 28 Aug 2006 19:42:41 +0200, "Michel Cadot" <micadot{at}altern{dot}org> wrote: > ><artmt@hotmail.com> a écrit dans le message de news: 1156784801.685662.314210@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. |
|
02-25-2008, 07:39 AM
| |||
| |||
| Re: Row generator that doesn't use memory( was: Advice on calendar function) "Jaap W. van Dijk" <j.w.vandijk.removethis@hetnet.nl> a écrit dans le message de news: 44f33b2a.2760203@news.hetnet.nl... | On Mon, 28 Aug 2006 19:42:41 +0200, "Michel Cadot" | <micadot{at}altern{dot}org> wrote: | | > | ><artmt@hotmail.com> a écrit dans le message de news: 1156784801.685662.314210@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 |
|
02-25-2008, 07:42 AM
| |||
| |||
| 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@hetnet.nl> a écrit dans le message de news: 44f33b2a.2760203@news.hetnet.nl... >| On Mon, 28 Aug 2006 19:42:41 +0200, "Michel Cadot" >| <micadot{at}altern{dot}org> wrote: >| >| > >| ><artmt@hotmail.com> a écrit dans le message de news: 1156784801.685662.314210@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. |
|
02-25-2008, 07:42 AM
| |||
| |||
| Re: Row generator that doesn't use memory( was: Advice on calendar function) "Jaap W. van Dijk" <j.w.vandijk.removethis@hetnet.nl> a écrit dans le message de news: 44f54f1e.3241437@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@hetnet.nl> a écrit dans le message de news: 44f33b2a.2760203@news.hetnet.nl... | >| On Mon, 28 Aug 2006 19:42:41 +0200, "Michel Cadot" | >| <micadot{at}altern{dot}org> wrote: | >| | >| > | >| ><artmt@hotmail.com> a écrit dans le message de news: 1156784801.685662.314210@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 |
|
02-25-2008, 07:45 AM
| |||
| |||
| 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@hetnet.nl> a écrit dans le message de news: 44f54f1e.3241437@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@hetnet.nl> a écrit dans le message de news: 44f33b2a.2760203@news.hetnet.nl... >| >| On Mon, 28 Aug 2006 19:42:41 +0200, "Michel Cadot" >| >| <micadot{at}altern{dot}org> wrote: >| >| >| >| > >| >| ><artmt@hotmail.com> a écrit dans le message de news: 1156784801.685662.314210@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. |
|
02-25-2008, 07:45 AM
| ||||
| ||||
| Re: Row generator that doesn't use memory( was: Advice on calendar function) "Jaap W. van Dijk" <j.w.vandijk.removethis@hetnet.nl> a écrit dans le message de news: 44f7269e.346046@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@hetnet.nl> a écrit dans le message de news: 44f54f1e.3241437@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@hetnet.nl> a écrit dans le message de news: 44f33b2a.2760203@news.hetnet.nl... | >| >| On Mon, 28 Aug 2006 19:42:41 +0200, "Michel Cadot" | >| >| <micadot{at}altern{dot}org> wrote: | >| >| | >| >| > | >| >| ><artmt@hotmail.com> a écrit dans le message de news: 1156784801.685662.314210@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 |
Linear Mode
