Re: Most Performant way of performing slow sql query

"C Foy" <foymail_at_uk2.net> wrote in message news:c7c03452.0412230449.f8911b5_at_posting.google.com...
> Hi All,
>
> I need some help to find an optimal way to perfom the following :
>
> Given a table sydsession with column sessionid which contains numeric
> data between 0 and 65535
>
> problem: I need to find the most performant way of performing the
> following query -
> want to select a sessionid between 0 and 65535 that is not present in
> the table sydsession (sorted from 0 to 65535)
>
> The following query works but is extremely slow:
>
> SELECT sydsessionid FROM (SELECT sydsessionid FROM (SELECT
> column_value AS sydsessionid FROM TABLE(CAST(numbers(0,65535) AS
> number_table))) WHERE sydsessionid NOT IN (SELECT DISTINCT
> sydsessionid FROM sydsession WHERE sydsessionid BETWEEN 0 AND 65535)
> ORDER BY sydsessionid) WHERE rownum <= 1;
>
> (taking about 6 seconds to execute)
>
> In addition, this query also works but is still slow (although faster
> than the previous):
>
> SELECT MIN(sydsessionid) FROM (SELECT sydsessionid from counters MINUS
> SELECT DISTINCT sydsessionid FROM sydsession WHERE sydsessionid
> BETWEEN 0 AND 65535);
>
> This table uses a temporary table called counters which contains
> numeric values 0 to 65535.
>
> Thanks in advance,
>
> Colin

no need for the temp table.

select sessionid from sydsessionid s where sessionid not in

   (select (rownum-1) from user_objects uo where rownum<65536) Received on Thu Dec 23 2004 - 07:17:53 CST