Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: query rewrite and ORDER BY: help please

Re: query rewrite and ORDER BY: help please

From: Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk>
Date: Wed, 30 Aug 2000 08:10:02 +0100
Message-ID: <967619812.25123.1.nnrp-09.9e984b29@news.demon.co.uk>

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>...

>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.
Received on Wed Aug 30 2000 - 02:10:02 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US