Home » RDBMS Server » Server Administration » Materialized join view with an IOT (DB 12.2)
Materialized join view with an IOT [message #669036] Thu, 29 March 2018 13:00
John Watson
Messages: 8738
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 this
ERROR 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
This isn't any sort of production problem, I'm just thinking through a few possibilities.

Thank you for any insight.

[Updated on: Thu, 29 March 2018 13:01]

Report message to a moderator

Previous Topic: Encryption options for Standard editions
Next Topic: ORA-27369: job of type EXECUTABLE failed with exit code: Permission denied
Goto Forum:

Current Time: Thu Aug 18 01:49:47 CDT 2022