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
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.