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

"C Foy" <foymail@uk2.net> wrote in message
news:c7c03452.0412230449.f8911b5@posting.google.co m...
> 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)

C Foy wrote:

> 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

Go to http://www.psoug.org
click on Morgan's Library
click on explain plan
You will fine a six queries, labelled 1 through 6. Take a look at
the various examples and adapt them for your purpose: Especially
number 6 and modify to use NOT EXISTS.
--
Daniel A. Morgan
University of Washington
damorgan@x.washington.edu
(replace 'x' with 'u' to respond)


-----------== Posted via Newsfeed.Com - Uncensored Usenet News ==----------
http://www.newsfeed.com The #1 Newsgroup Service in the World!
-----= Over 100,000 Newsgroups - Unlimited Fast Downloads - 19 Servers =-----

Hi,

Am 23 Dec 2004 04:49:27 -0800 schrieb C Foy <foymail@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)

On 23 Dec 2004 04:49:27 -0800, foymail@uk2.net (C Foy) wrote:

>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

here are three approaches I can think of:

Analytic:

SELECT MIN (sydsessionid + 1)
FROM (SELECT sydsessionid,
LEAD (sydsessionid) OVER (ORDER BY sydsessionid) next_id
FROM sydsession
WHERE sydsessionid <= 65535)
WHERE sydsessionid != next_id - 1

it will give you the first free id in the first gap found.

Not sure how it's performance is on your system compared to the "not
exists" variant:

Not exists:

SELECT MIN (sydsessionid + 1) free
FROM sydsessionid
WHERE NOT EXISTS (SELECT sydsessionid
FROM sydsessionid t
WHERE t.sydsessionid =
sydsessionid.sydsessionid+1)
AND sydsessionid BETWEEN 0 AND 65535



IOT:

CREATE TABLE t_iot
(ID
,CONSTRAINT pk_t_iot PRIMARY KEY (ID )
)
ORGANIZATION INDEX NOCOMPRESS
AS ( SELECT ROWNUM FROM all_objects WHERE ROWNUM<65536)


SELECT MIN (ID) free
FROM t_iot
WHERE NOT EXISTS (SELECT NULL
FROM sydsession t
WHERE t.sydsessionid = t_iot.ID)
AND sydsessionid <= 65535


On my 4 Mio test table the second took three times as long.
an the third one was the fastest.



Regards

Justin