| Materialized join view with an IOT [message #669036] |
Thu, 29 March 2018 13:00 |
John Watson
Messages: 8960
Registered: January 2010
Location: Global Village
|
Senior Member |
|
|
If a materialized join view is to be fast refreshable, it must include the rowids of the detail tables and the MV logs must be defined WITH ROWID. An IOT doesn't have a rowid, you can't specify WITH UROWID, and WITH PRIMARY KEY isn't good enough (though I've never understood why not - perhaps because Oracle lets you change primary keys?)
So is there any way to make a materialized join view that includes an IOT fast refreshable?
I tried running the advisor to see if it came up with anything, but it gives thisERROR at line 1:
ORA-13600: error encountered in Advisor
QSM-03113: Cannot tune the MATERIALIZED VIEW statement
QSM-02015: Index Organized Table (IOT) present in FROM list
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 86
ORA-06512: at "SYS.PRVT_ACCESS_ADVISOR", line 205
ORA-06512: at "SYS.PRVT_TUNE_MVIEW", line 1380
ORA-06512: at "SYS.DBMS_ADVISOR", line 828
ORA-06512: at line 1
Thank you for any insight.
[Updated on: Thu, 29 March 2018 13:01] Report message to a moderator |
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
