Re: Most Performant way of performing sql query

From: Holger Baer <holger.baer_at_science-computing.de>
Date: Thu, 23 Dec 2004 14:49:02 +0100
Message-ID: <cqeice$5pc$1@news.BelWue.DE>

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

As a starting point:

baer_at_DEMO10G>create table demo (id number);

Table created.

baer_at_DEMO10G>insert into demo select rownum from all_objects;

36480 rows created.

baer_at_DEMO10G> with number_tab as (select rownum rn from (select 1 from dual group by cube (1,2,3,4,5,6,7,8,9,10)),

   2 (select 1 from dual group by cube (1,2,3,4,5,6,7,8,9,10)) where rownum <=66000)    3 select min (rn) from (
   4 select id,rn from demo, number_tab    5 where id(+)=rn
   6 and id is null)
   7 /

    MIN(RN)

---

      36481

Elapsed: 00:00:00.15
baer_at_DEMO10G> with number_tab as (select rownum rn from (select 1 from dual group by cube (1,2,3,4,5,6,7,8,9,10)),
  (select 1 from dual group by cube (1,2,3,4,5,6,7,8,9,10)) where rownum <=66000)   select rn from (
  select id,rn from demo, number_tab
  where id(+)=rn
  and id is null)
  order by 1
/

.
.
.

<output truncated>

29520 rows selected.

Elapsed: 00:00:01.50
baer_at_DEMO10G>

HTH Holger Received on Thu Dec 23 2004 - 07:49:02 CST