Re: SQL grouping continous records

From: Rene Nyffenegger <rene.nyffenegger_at_gmx.ch>
Date: 28 Feb 2003 16:57:08 GMT
Message-ID: <b3o4d4$1p1i68$1@ID-82536.news.dfncis.de>

> Rene Nyffenegger <rene.nyffenegger_at_gmx.ch> wrote in message news:<b2bqaq$1b3trk$1_at_ID-82536.news.dfncis.de>...

>> Which Oracle Version???
>>

> Oracle Version is 8i.
> 
> Dear Rene,
> for this small example is your resolution perfect. 
> But i have thousands of records and the performance is down.
> I think the reason is the Inline-View for calculating row_last_different.

> I created the Inline-View as View.

That won't make much of a difference.

> Problem: If I select some Records of the view, before the Records > are returned the View was completely for the whole Tabele executetd.

What did you expect? If you don't confine the access to the underlying table, a full table scan will be performed, which takes time according to its size. If you want to speed up things, you need to only query a portion of the table and have an index on it.

hth

Rene Nyffenegger

> 
> Can you help me?
> 
> Thx
> Peter
> 

>> Starting with 8i (I believe) you have the mighty analytical functions
>> at your disposal:
>>
>>
>> set feedback off
>>
>> create table sum_ (col1 number, col2 char(1));
>>
>> insert into sum_ values ( 1,'A');
>> insert into sum_ values ( 2,'A');
>> insert into sum_ values ( 3,'B');
>> insert into sum_ values ( 4,'A');
>> insert into sum_ values ( 5,'C');
>> insert into sum_ values ( 6,'C');
>> insert into sum_ values ( 7,'A');
>> insert into sum_ values ( 8,'A');
>> insert into sum_ values ( 9,'A');
>> insert into sum_ values (10,'A');
>> insert into sum_ values (11,'C');
>> insert into sum_ values (12,'C');
>>
>>
>> select
>> row_last_different - lag(row_last_different,1,0) over (order by col1) cnt,
>> col2
>> from (
>> select
>> col1,
>> row_last_different,
>> col2 from (
>> select
>> col1,
>> decode(lead (col2,1) over(order by col1),col2,null,rank() over(order by col1)) row_last_different,
>> col2
>> from
>> sum_
>> )
>> where row_last_different is not null
>> );
>>
>> drop table sum_;
>>
>> results in:
>>
>>
>> CNT C
>> ---------- -
>> 2 A
>> 1 B
>> 1 A
>> 2 C
>> 4 A
>> 2 C
>>
>>
>> Hth
>>
>> Rene Nyffenegger

-- 
  no sig today

Received on Fri Feb 28 2003 - 10:57:08 CST