Re: Most Performant way of performing slow sql query

From: Justin <Nomail_at_home.com>
Date: 25 Dec 2004 08:50:19 -0600
Message-ID: <b2tqs0l91iecj7d05lpfa7ojkp26jclhj6@news-west.newscene.com>

On 23 Dec 2004 04:49:27 -0800, foymail_at_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 Received on Sat Dec 25 2004 - 08:50:19 CST