Re: Most Performant way of performing slow sql query

Hi,

Am 23 Dec 2004 04:49:27 -0800 schrieb C Foy <foymail_at_uk2.net>:

> 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)
vious):
>
> SELECT MIN(sydsessionid) FROM (SELECT sydsessionid from counters MINUS
> SELECT DISTINCT sydsessionid FROM sydsession WHERE sydsessionid
> BETWEEN 0 AND 65535);
You should redesign your data model for accessing the "smallest free sessionid" via index path.

• Take a table with all numbers 0 ... 65535 and a column "occupied" with value range {0,1}. Index the column occupied via bitmap index.
• select min(sessionid) from counters where occupied=0

Or other (and better) strategy. Write a function get_sessionid which uses an oracle sequence. This function has to reset the sequence if .nextval=65535.

-- 
Frank Piron,
defrankatkonaddot
(leftrotate two)