Re: Query help

From: Riyaj Shamsudeen <riyaj.shamsudeen_at_gmail.com>
Date: Sat, 1 Mar 2014 09:30:10 -0800
Message-ID: <CAA2DszxUY2MFoD5VrsZuDLMEJSu=d8VrgqFqjtj=ywJX3WpnvQ_at_mail.gmail.com>

Learn analytic functions. You can write concise, efficient SQL statements. See that the following rewrites accesses the table just once. (I didn't test the rewrite though, and the performance depends upon the table layout and data pattern.)

<shamelsss ad>
In our book,
http://www.amazon.com/Pro-Oracle-SQL-Karen-Morton/dp/1430262206/ref=sr_1_1?ie=UTF8&qid=1393694717&sr=8-1&keywords=pro+oracle+SQL, I have written a chapter on analytic functions. It might be useful for you. </shameless ad>

Variation 1:

select * from (
  select distinct
    t1.c3,
   max(t1.c5) over (partition by c3 rows unbounded preceding and unbounded following) max_c5,

   max(t1.c4) over (partition by c3 rows unbounded preceding and unbounded following) max_c4,

   count(1) over (partition by c3 rows unbounded preceding and unbounded following) cnt_c3
  from T1
  WHERE T1.C1 = 576
  AND t1.C6 IN ('configuration', 'shotList') )
where cnt_c3 >1
/

Variation 2:

  select distinct
    t1.c3,
   max(t1.c5) over (partition by c3 rows unbounded preceding and unbounded following) max_c5,

   max(t1.c4) over (partition by c3 rows unbounded preceding and unbounded following) max_c4
  from T1
  WHERE T1.C1 = 576
  AND t1.C6 IN ('configuration', 'shotList') GROUP BY t1.C3
HAVING COUNT(t1.C1) > 1
/

Cheers

Riyaj Shamsudeen
Principal DBA,
Ora!nternals - http://www.orainternals.com - Specialists in Performance, RAC and EBS
Blog: http://orainternals.wordpress.com/ Oracle ACE Director and OakTable member <http://www.oaktable.com/>

Co-author of the books: Expert Oracle
Practices<http://tinyurl.com/book-expert-oracle-practices/> , Pro Oracle SQL, <http://tinyurl.com/ahpvms8> <http://tinyurl.com/ahpvms8>Expert
RAC Practices 12c. <http://tinyurl.com/expert-rac-12c> Expert PL/SQL practices <http://tinyurl.com/book-expert-plsql-practices>

<http://tinyurl.com/book-expert-plsql-practices>

On Fri, Feb 28, 2014 at 4:45 PM, Raju Angani <angani_at_gmail.com> wrote:

> Hi SQL Gurus,
>
> Is there a alternative(better) way to write the below query?
>
>
> drop table t1;
>
> create table T1(C1 INT, C2 INT, C3 VARCHAR2(1000), C4 INT, C5 INT, C6
> VARCHAR2(512),
> primary key (c1,c2));
>
> create index idx_t1_c3 on t1(c3,c4);
>
> INSERT INTO T1
> VALUES(576,1,'C://VOL/516AFE-345D1-TEEW-130.190_0.LOG',1975,1975,'configuration');
> INSERT INTO T1
> VALUES(576,0,'C://VOL/516AFE-345D1-TEEW-130.190_0.LOG',0,0,'shotList');
> INSERT INTO T1
> VALUES(577,1,'C://VOL/516AFE-345D1-TEEW-130.180_0.LOG',2345,2345,'configuration');
> INSERT INTO T1
> VALUES(577,0,'C://VOL/516AFE-345D1-TEEW-130.180_0.LOG',0,0,'shotList');
> INSERT INTO T1
> VALUES(578,1,'C://VOL/516AFE-345D1-TEEW-130.170_0.LOG',2346,2345,'configuration');
> INSERT INTO T1
> VALUES(578,0,'C://VOL/516AFE-345D1-TEEW-130.170_0.LOG',0,0,'shotList');
> INSERT INTO T1
> VALUES(579,1,'C://VOL/516AFE-345D1-TEEW-130.160_0.LOG',2345,2345,'configuration');
> INSERT INTO T1
> VALUES(579,0,'C://VOL/516AFE-345D1-TEEW-130.190_0.LOG',0,0,'shotList');
>
> select * from T1;
>
> SELECT t1.C3, MAX(t1.C5), MAX(t1.C4)
> FROM T1 t1, T1 tmp
> WHERE tmp.C1 = 576
> AND tmp.C6 IN ('configuration', 'shotList')
> AND tmp.C3 = t1.C3
> GROUP BY t1.C3
> HAVING COUNT(t1.C1) > 1;
>
> Thank you
> RA
>

--
http://www.freelists.org/webpage/oracle-l

Received on Sat Mar 01 2014 - 18:30:10 CET