Home » SQL & PL/SQL » SQL & PL/SQL » suggest me to which one is best way to write query and why? (merged 4)
suggest me to which one is best way to write query and why? (merged 4) [message #404616] Fri, 22 May 2009 08:22 Go to next message
akki.Kumar
Messages: 7
Registered: May 2009
Location: baroda
Junior Member

hi All Members,
suggest me to which one is best way to write query and why?
 select * from(
 with
 asp_pk as
 (select Entity_own_fk from login_mst where username='sunil291')
 ,
 ac_fk as
 (select ac_pk from ac a,asp_pk asp where a.asp_fk=asp.Entity_own_fk)
 select author_pk from author au,ac_fk ac where au.ac_fk =ac.ac_pk);

or

select author_pk from author where ac_fk in
(select ac_pk from ac where asp_fk in
(select Entity_own_fk from login_mst where username='sunil287'))


which one is best way to write query and why? [message #404617 is a reply to message #404616] Fri, 22 May 2009 08:25 Go to previous messageGo to next message
akki.Kumar
Messages: 7
Registered: May 2009
Location: baroda
Junior Member

hi All Members,

here i write same query in two way,
suggest me to hi All Members,

here i write same query in two way,
suggest me to which one is best way to write query and why?
 select * from(
 with
 asp_pk as
 (select Entity_own_fk from login_mst where username='sunil291')
 ,
 ac_fk as
 (select ac_pk from ac a,asp_pk asp where a.asp_fk=asp.Entity_own_fk)
 select author_pk from author au,ac_fk ac where au.ac_fk =ac.ac_pk);

or

select author_pk from author where ac_fk in
(select ac_pk from ac where asp_fk in
(select Entity_own_fk from login_mst where username='sunil287'))


and why?
 select * from(
 with
 asp_pk as
 (select Entity_own_fk from login_mst where username='sunil291')
 ,
 ac_fk as
 (select ac_pk from ac a,asp_pk asp where a.asp_fk=asp.Entity_own_fk)
 select author_pk from author au,ac_fk ac where au.ac_fk =ac.ac_pk);

or

select author_pk from author where ac_fk in
(select ac_pk from ac where asp_fk in
(select Entity_own_fk from login_mst where username='sunil287'))


Re: which one is best way to write query and why? [message #404622 is a reply to message #404617] Fri, 22 May 2009 08:36 Go to previous message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
As with many things in life, Occam's Razor applies.

The simplest solution is usually the best. Simple SQL gives the Oracle Optimizer the best chance to come up with a good solution.

Also, a simple solution is easier to read and cheaper to maintain. For this same reason, formatting and indentation is a good idea.

For instance, take a look at this:

SELECT author_pk 
FROM   author 
WHERE  ac_fk IN (
    SELECT ac_pk 
    FROM   ac 
    WHERE  asp_fk IN (
        SELECT entity_own_fk 
        FROM   login_mst 
        WHERE  username='sunil287'
    )
)

Don't you think that looks simpler than your solution? Even though it is syntactially identical?

Ross Leishman
Previous Topic: Procedure -Performance Issue..
Next Topic: Table relationships
Goto Forum:
  


Current Time: Thu Dec 08 06:20:56 CST 2016

Total time taken to generate the page: 0.07159 seconds