The 'ORDER BY' Clause is Missing from the QUERY Column in DBA_MVIEWS.
Last updated on FEBRUARY 02, 2017
Applies to:Oracle Server - Enterprise Edition - Version: 18.104.22.168
Information in this document applies to any platform.
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';
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 22.214.171.124.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
ORGANIZATION HEAP PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOG
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)
REFRESH FORCE ON DEMAND
USING DEFAULT LOCAL ROLLBACK SEGMENT
USING ENFORCED CONSTRAINTS DISABLE QUERY REWRITE
AS select * from user_objects
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