Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
![]() |
![]() |
Home -> Community -> Usenet -> c.d.o.server -> 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, > > ColinReceived on Thu Dec 23 2004 - 08:05:28 CST
![]() |
![]() |