Re: Most Performant way of performing sql query

Here's a demo of one possible strategy.
It gets the first number that is not currently there.

drop table t1;

create table t1
nologging
pctfree 0
as
with generator as (
 select --+ materialize
  rownum id
 from all_objects
 where rownum <= 1000
)
select
 /*+ ordered use_nl(v2) */
 rownum - 1 id
from
 generator v1,
 generator v2
where
 rownum <= 65536
;

delete from t1 where id = 65001;
delete from t1 where id = 65535;

alter table t1 add constraint t1_pk primary key (id);

begin
 dbms_stats.gather_table_stats(user, 't1', cascade => true); end;
.
/

select
 placed
from (
 select id, rank() over (order by id) placed  from t1
 )
where
 placed = id - 1
and rownum = 1
;

-- 
Regards

Jonathan Lewis

http://www.jlcomp.demon.co.uk/faq/ind_faq.html
The Co-operative Oracle Users' FAQ

http://www.jlcomp.demon.co.uk/seminar.html
Public Appearances - schedule updated Dec 23rd 2004






"C Foy" <foymail_at_uk2.net> wrote in message 
news:c7c03452.0412230448.580f0f1f_at_posting.google.com...
> 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