Re: How to retrive Deleted Sequence Number????

From: Holger Baer <holger.baer_at_science-computing.de>
Date: Wed, 03 Nov 2004 10:12:29 +0100
Message-ID: <cma7du$62c$1@news.BelWue.DE>

Michel Cadot wrote:
> "Arijit Chatterjee" <arijitchatterjee123_at_yahoo.co.in> a écrit dans le message de
> news:ea01504d.0411020038.32ddad72_at_posting.google.com...
>

>>Respected faculties,
>>Is it possible through a query
>>without selecting any user table
>>showing this type of output
>>===========================
>>Col1
>>----
>>1
>>2
>>3
>>4
>>....
>>....
>>900000
>>===========================
>>Through this query I can solve this problem.
>>Regards
>>Arijit Chatterjee

>
>
> The best way i know is:
>
> select rownum
> from (select 1 from dual group by cube(1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20))
> where rownum<=900000
> /
>
> or you can use a PL/SQL function to generate the numbers.
> But if you want to use SQL, you can't avoid selecting.
>

At least on my test machine (10g) this is totally not viable. I started the query about 14hours ago, and it's still running. Would you care to elaborate a bit more how to use this query?

Creating a new table, filling it in a for loop, selecting from it and dispose the table (about the worst way I could think of) would outperform this one.

As to the OP: You don't want to select from a user table, but how about a system table:

select rownum from (select 1 from all_objects, all_objects, all_objects) where rownum <=900000;

But this one didn't fly, either. (45000*45000 is quite a lot to choose from) For some reason that I'll have to research further, this was really fast:

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 <= 900000
/

HTH
Holger Received on Wed Nov 03 2004 - 03:12:29 CST