Re: Query help
- From: Riyaj Shamsudeen <riyaj.shamsudeen@xxxxxxxxx>
- To: angani@xxxxxxxxx
- Date: Sat, 1 Mar 2014 09:30:10 -0800
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@xxxxxxxxx> 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
>
Other related posts:
