Re: Table/view to generate integers from 1 to 500

Kirtan Acharya wrote:
> Hi,
> I'd like to add that this query will retrieve numbers only till the 9th
> power of 2 i.e. 512. So if u need higher numbers, keep on adding digits
> to the end of the list
> group by cube(1,2,3,4,5,6,7,8,9,10,11,12....)
>
> hope this helps,
> kirtan
>

This is true, but performance is rapidly declining. For larger numbers, you're better off by creating cartesian products from two or more cubes.

At least for my test machine:

   1 select count(*) from (select 1 from dual group by cube(1,2,3,4,5,6,7,8,9,10

))

   2* , (select 1 from dual group by cube (1,2,3,4,5,6,7,8,9,10)) baer_at_DEMO10G>/

   COUNT(*)

    1048576

Elapsed: 00:00:00.21

baer_at_DEMO10G>select count(*) from (select 1 from dual group by cube(1,2,3,4,5,6,

7,8,9,10,11,12,13,14,15));

   COUNT(*)

      32768

Elapsed: 00:01:05.23
baer_at_DEMO10G>/

   COUNT(*)

      32768

Elapsed: 00:00:00.22
baer_at_DEMO10G>select count(*) from (select 1 from dual group by cube(1,2,3,4,5,6,7,8,9,10,11,12,13,14));

   COUNT(*)

      16384

Elapsed: 00:00:00.09
baer_at_DEMO10G>

So subsequent calls of the larger cube are fast, but the initial call can be really slow. Your treshold might be different from mine, but I'd be very cautios to go beyond 10 before using the cartesian product.

Cheers,

Holger Received on Mon Jan 17 2005 - 09:06:29 CST