Re: Most Performant way of performing sql query

Jonathan Lewis wrote:
> 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
> ;
>
>
>

Just for fun I compared your solution with mine performancewise and as expected I lost ;-)

However, if you have a gapless dataset that just happens not to go up to the 65535 boundary, your solution doesn't find a row, so there might be a word of caution to the OP in order.

Have a very nice x-mas

Holger Received on Thu Dec 23 2004 - 08:53:32 CST