RE: materialized views and ORDER BY
- From: "Kerber, Andrew W." <Andrew.Kerber@xxxxxxx>
- To: spikey.mcmarbles@xxxxxxxxx, "ORACLE-L" <oracle-l@xxxxxxxxxxxxx>
- Date: Wed, 25 Apr 2007 11:33:45 -0500
Perhaps he could resolve the problem by creating a standard view of the
materialized view...
-----Original Message-----
From: oracle-l-bounce@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx] On Behalf Of Greg Norris
Sent: Wednesday, April 25, 2007 11:11 AM
To: ORACLE-L
Subject: materialized views and ORDER BY
I have a developer who's complaining that the ORDER BY clause is being
dropped from his materialized views, thus requiring him to recreate them
several times a day. I've told him I'm virtually certain this is by
design, so that the MV behavior doesn't change depending upon the
refresh method, but have so far been unable to find it explicitly
documented... can anyone point me to a definitive statement?
Here's a quick test case (some of the more trivial output has been
trimmed for brevity), to clarify what I'm babbling about...
SQL> create table test (
2 col1 number(2)
3 );
Table created.
SQL> insert into test values (1);
SQL> insert into test values (3);
SQL> insert into test values (9);
SQL> insert into test values (4);
SQL> insert into test values (7);
SQL> insert into test values (2);
SQL> insert into test values (5);
SQL> insert into test values (6);
SQL> insert into test values (8);
SQL> insert into test values (10);
SQL> commit;
Commit complete.
SQL> select * from test;
COL1
----------
1
3
9
4
7
2
5
6
8
10
10 rows selected.
SQL> create materialized view test_mv
2 refresh complete with rowid
3 as select * from test order by 1;
Materialized view created.
SQL> select * from test_mv;
COL1
----------
1
2
3
4
5
6
7
8
9
10
10 rows selected.
SQL> select query from user_mviews where mview_name = 'TEST_MV';
QUERY
----------------------------------------------
SELECT "TEST"."COL1" "COL1" FROM "TEST" "TEST"
SQL> exec dbms_mview.refresh('TEST_MV')
PL/SQL procedure successfully completed.
SQL> select * from test_mv;
COL1
----------
1
3
9
4
7
2
5
6
8
10
10 rows selected.
As you can see, Oracle appears to use the query AS-IS for the initial
creation (provided that you don't specify BUILD DEFERRED), so it does
get the requested ordering once... future refreshes will completely
ignore the ORDER BY, however, as it's been dropped from the query.
In real life, the query in question is a fairly involved beastie which
involves several join/union/grouping operations, and everything "always
worked correctly" in the past. The database was recently upgraded from
8.1.7.4 to 10.2.0.3, however, so I'm pretty sure this is really a case
of "what do you mean GROUP BY doesn't sort?!?" in disguise. I've
suggested a couple of workarounds already, so what I really need is to
convince them that this behavior is by design and thus we can't "fix"
the materialized view directly.
No need to even mention that developers shouldn't have the ability to
(re)create objects in the first place... completely out of my control in
this case.
Thanx!
--
"I'm too sexy for my code." -Awk Sed Fred
------------------------------------------------------------------------------
NOTICE: This electronic mail message and any attached files are confidential.
The information is exclusively for the use of the individual or entity intended
as the recipient. If you are not the intended recipient, any use, copying,
printing, reviewing, retention, disclosure, distribution or forwarding of the
message or any attached file is not authorized and is strictly prohibited. If
you have received this electronic mail message in error, please advise the
sender by reply electronic mail immediately and permanently delete the original
transmission, any attachments and any copies of this message from your computer
system. Thank you.
==============================================================================
Other related posts:
