Rob Nagler <nagler@bivio.biz> writes:
> One of the reason postgres is faster on the q1-4 is that postgres
> supports OFFSET/LIMIT, and oracle doesn't. q7 and q8 are the queries
> that I've referred to recently (avg of group by).
Well the way to do offset/limit in Oracle is:
SELECT *
FROM (
SELECT ... , rownum AS n
WHERE rownum <= OFFSET+LIMIT
)
WHERE n > OFFSET
That's basically the same thing Postgres does anyways. It actually has to do
the complete query and fetch and discard the records up to the OFFSET and then
stop when it hits the LIMIT.
> q5 and q6 are too complex to discuss here, but the fundamental issue
> is the order in which postgres decides to do things.
That true for pretty 99% of all query optimization whether it's on Postgres or
Oracle. I'm rather curious to see the query and explain analyze output from q5
and q6.
--
greg