Most Performant way of performing slow sql query

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 Received on Thu Dec 23 2004 - 06:49:27 CST