The 'ORDER BY' Clause is Missing from the QUERY Column in DBA_MVIEWS. (Doc ID 1273985.1)

Last updated on FEBRUARY 02, 2017

Applies to:

Oracle Server - Enterprise Edition - Version: 11.2.0.1 and later   [Release: 11.2 and later ]
Information in this document applies to any platform.

Symptoms

When creating a Materialized View it is noticed that the ORDER BY clause of the MV definition has disappeared, and this was without any error/warning message at MV creation time.

This can be seen by conducting a simple test, as follows:

SQL> create materialized view test123_dummy_mv as select * from user_objects order by object_id;

Materialized view created.

SQL> select query from user_mviews where mview_name = 'TEST123_DUMMY_MV';

QUERY

select * from user_objects;

As can  be seen, the "order by object_id" has been excluded. This occurs also on 10.2.0.3.0 and 9.2.0.8.0 database versions.

Even when running the DBMS_METADATA routines we can see the phrase 'ORDER BY' is missing:

set long 100000
set pagesize 80
col DDL format a140
select DBMS_METADATA.GET_DDL('MATERIALIZED_VIEW', 'DUMMY123_MV','DWH') DDL from dual;

CREATE MATERIALIZED VIEW "DWH"."DUMMY123_MV" ("OBJECT_NAME", "SUBOBJECT_NAME",
"OBJECT_ID", "DATA_OBJECT_ID", "OBJECT_TYPE", "CREATED", "LAST_DDL_TIME", "TIME
STAMP", "STATUS", "TEMPORARY", "GENERATED", "SECONDARY", "NAMESPACE", "EDITION_N
AME")
ORGANIZATION HEAP PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOG
GING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DE
FAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "DM2"
BUILD IMMEDIATE
USING INDEX
REFRESH FORCE ON DEMAND
USING DEFAULT LOCAL ROLLBACK SEGMENT
USING ENFORCED CONSTRAINTS DISABLE QUERY REWRITE
AS select * from user_objects

Changes

None, as this is expected behaviour as can be seen below.

Cause

Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms