Home » SQL & PL/SQL » SQL & PL/SQL » Query optimization
Query optimization [message #206495] Thu, 30 November 2006 05:19 Go to next message
ramesh_samane
Messages: 63
Registered: May 2006
Member
Can anybody please tell me which one of the query below qill be faster? Assume there are no indexes on any of the columns.

# 1

select col1,
col2,
'' col3,
col4,
A.col5,
A.col6,
count(*) cnt
from A,
B,
C,
OUTER D
where A.id = B.id
and B.id1 = c.id1
and A.id2 = D.id2
and A.i = 'Y'
and D.i = 'Y'
group by 1,2,3,4,5,6;

# 2

select col1,
col2,
'' col3,
col4,
A.col5,
A.col6,
count(*) cnt
from A,
B,
C,
OUTER (select id2
from A,
D
where A.id2 = D.id2
and A.i = 'Y'
and D.i = 'Y') D
where A.id = B.id
and B.id1 = c.id1
and A.id2 = D.id2
group by 1,2,3,4,5,6;

Thanks,
Ramesh.






Re: Query optimization [message #206503 is a reply to message #206495] Thu, 30 November 2006 05:57 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
both will fail just as fast.

Re: Query optimization [message #206508 is a reply to message #206495] Thu, 30 November 2006 06:39 Go to previous messageGo to next message
ramesh_samane
Messages: 63
Registered: May 2006
Member
Hey I know it will fail syntactically...what I meant was I need to take OUTER join on the resultset I get from joining A & D....my question is whether my query will work fast by limiting it to only the resultset in INLINE VIEW?

Please don't check the syntax of the query but only the logic behind it. Actually this is an Informix query. So the syntax may seem strange to you.
Re: Query optimization [message #206509 is a reply to message #206508] Thu, 30 November 2006 06:43 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Ah, so you're asking us to apply all our knowledge of how the Oracle Cost Based Optimizer would structure the access paths for this query, and then you're going to cross your fingers and hope that the same applies to Informix.

Excuse me while I put a more skepical hat on.

I don't think it's possible to say which will run faster without knowing a lot more about the data distribution.
Re: Query optimization [message #206512 is a reply to message #206495] Thu, 30 November 2006 06:51 Go to previous messageGo to next message
ramesh_samane
Messages: 63
Registered: May 2006
Member
Guys, I just want to know logically which one will be faster independent of the database and the data. I know it will be interpreted in different ways in Oracle and in Informix.

Thanks for taking time to reply...
Re: Query optimization [message #206516 is a reply to message #206512] Thu, 30 November 2006 07:08 Go to previous message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
IMO, there is no answer to your question - it has no meaning.

A queries speed is the length of time it takes to execute, on a database, with data.

If you can come up with a definition of 'speed', in the context of a query not being run on a database, and not having a data set to operate on, then possibly I can answer the question.

Previous Topic: How To Select Other user's Table
Next Topic: display total number of rows with function and criteria
Goto Forum:
  


Current Time: Tue Aug 22 17:46:58 CDT 2017

Total time taken to generate the page: 0.02245 seconds