query rewrite and ORDER BY: help please

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 Tue Aug 29 2000 - 18:31:01 CDT