Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: query rewrite and ORDER BY: help please
Check the execution plans, especially the
cost, with and without the ORDER BY -
It is sometimes the case that Oracle detects
an option for using a different cheaper access
path driving through an index when a query
has an order by.
To solve the problem, try:
select * from
(
select /*+ no_merge */
{original query}
)
order by relevant columns
;
or use the REWRITE hint (though I can't
remember at the moment if it should be
rewrite of query_rewrite)
select /*+ rewrite */
{rest of original query}
order by
{whatever}
;
-- Jonathan Lewis Yet another Oracle-related web site: http://www.jlcomp.demon.co.uk pwenker_at_my-deja.com wrote in message <8ohh3l$ib5$1_at_nnrp1.deja.com>...Received on Wed Aug 30 2000 - 02:10:02 CDT
>Hi,
>
>I wonder if anyone can give me any information about the relationship
>between query rewrite and an ORDER BY clause in the SQL "intended" for a
>Materialized View.
>
>Here's the situation: I have query rewrite going successfully against a
>MV when there is no ORDER BY clause in the SQL, but when I add an ORDER
>BY clause (the ORDER BY clause contains only columns referenced in the
>SELECT clause that created the MV) to the SQL the query rewrite doesn't
>happen: EXPLAIN PLAN shows the query path as hitting the source tables
>instead of the MV.
>
>Anyone out there ever enconter a similiar situation?
>
>NOTE: I have a different scenario in which SQL with an ORDER BY clause
>*does* successfully re-write to hit a MV. The difference: this MV is an
>aggregate created with a GROUP BY clause. The one that's not working is
>not an aggregation, just a table join. I tried using a GROUP BY clause
>that doesn't aggregate (all columns in the SELECT clause also in the
>GROUP BY clause) but no dice.
>
>Any help is appreciated, Thanks! --Peter
>
>
>Sent via Deja.com http://www.deja.com/
>Before you buy.