Skip to Main Content
  • Questions
  • dbms_metadata.get_ddl gives wrong order for sequence columns

Breadcrumb

Question and Answer

Chris Saxon

Thanks for the question, Raul.

Asked: June 30, 2021 - 8:23 am UTC

Last updated: July 02, 2021 - 4:09 pm UTC

Version: 19.11

Viewed 1000+ times

You Asked

Hi

I will create a table, and then create a materialized view log on top of it, where I will use sequence keyword to order certain columns specifically.

Notice, that I create mviewlog with "sequence (col3, col2, col1)"

SQL> create table raulkaubi.mvlog_test1(id number PRIMARY KEY, col1 date, col2 number, col3 number);
create materialized view log on raulkaubi.mvlog_test1 with rowid, sequence (col3, col2, col1) including new values;


Now when I check the DDL of this object, and I get this:

set pagesize 0
set lines 200
set long 90000
set feedback off
set echo off
select dbms_metadata.get_ddl('MATERIALIZED_VIEW_LOG','MLOG$_MVLOG_TEST1','RAULKAUBI') from dual;

  CREATE MATERIALIZED VIEW LOG ON "RAULKAUBI"."MVLOG_TEST1"
 PCTFREE 10 PCTUSED 30 INITRANS 1 MAXTRANS 255 LOGGING
  TABLESPACE "USERS"
  WITH ROWID, SEQUENCE ( "COL1", "COL2", "COL3" ) INCLUDING NEW VALUES


Notice, the ordering is wrong (seems like ordering is always alphabetically):

SEQUENCE ( "COL1", "COL2", "COL3" )



In addition to that, shouldn't there be some ordering information as well (for example column_id etc..) in these views:
DBA_MVIEW_LOG_FILTER_COLS
ALL_MVIEW_LOG_FILTER_COLS


Regards
Raul Kaubi

and Chris said...

What is it you're trying to do here?

The sequence clause is unrelated to the column order. From the docs:

SEQUENCE

Specify SEQUENCE to indicate that a sequence value providing additional ordering information should be recorded in the materialized view log. Sequence numbers are necessary to support fast refresh after some update scenarios.


The column list states the values you want to store in the materialized view log. You can use each of these independently. Using SEQUENCE has no effect on the filter columns:

create table t (
  id int primary key, c1 int, c2 int
);

create materialized view log on t with
  rowid, sequence;
  
desc mlog$_t;

Name              Null?   Type            
M_ROW$$                     VARCHAR2(255)    
SEQUENCE$$                  NUMBER           
SNAPTIME$$                  DATE             
DMLTYPE$$                   VARCHAR2(1)      
OLD_NEW$$                   VARCHAR2(1)      
CHANGE_VECTOR$$             RAW(255 BYTE)    
XID$$                       NUMBER  

drop materialized view log on t;
  
create materialized view log on t with
  rowid, ( c2, c1 );
  
desc mlog$_t;

Name              Null?   Type            
ID                          NUMBER           
C2                          NUMBER           
C1                          NUMBER           
M_ROW$$                     VARCHAR2(255)    
SNAPTIME$$                  DATE             
DMLTYPE$$                   VARCHAR2(1)      
OLD_NEW$$                   VARCHAR2(1)      
CHANGE_VECTOR$$             RAW(255 BYTE)    
XID$$                       NUMBER   


Note that the first time the MV log includes SEQUENCE$$; the second time it omits this and includes the columns C2, C1 instead.

That said, I can see that get_ddl returns these columns in alphabetical order:

select dbms_metadata.get_ddl ( 'MATERIALIZED_VIEW_LOG', 'MLOG$_T', user ) 
from   dual;

  CREATE MATERIALIZED VIEW LOG ON "CHRIS"."T"
 PCTFREE 10 PCTUSED 30 INITRANS 1 MAXTRANS 255 LOGGING
  TABLESPACE "USERS" 
  WITH PRIMARY KEY, ROWID ( "C1", "C2" ) EXCLUDING NEW VALUES  


If this is something you need, speak with support to raise a bug. Similarly if you want this information in the dictionary views raise an enhancement request.

Rating

  (2 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

A reader, July 02, 2021 - 7:46 am UTC

I mean, isn't the column order "(col3, col2, col1)" important at all then..?

Since GET_DDL gives me totally different order (alphabetically).

Raul
Chris Saxon
July 02, 2021 - 10:54 am UTC

The column order affects which order the columns appear in the MV log table (MLOG$_...)

I'm not aware of any differences this makes to the refresh process.

A reader, July 02, 2021 - 11:15 am UTC

So basically, it is the same thing when I create a table tab1 with columns (c3, c2, c1)

You're saying that it is OK for GET_DDL to give me a table script with random column order..?

(I doubt it)

Why would the MLOG$ table be any different.

Raul




Chris Saxon
July 02, 2021 - 4:09 pm UTC

It's rare that you'll write code to access the MLOG$ table directly - I'm curious as to what practical difference this makes to you. Precisely what are you doing for this to have an impact on your application?

But like I said - raise a bug for this.

More to Explore

SQL

The Oracle documentation contains a complete SQL reference.