How to Create an Index-Organized Materialized View using Prebuilt Table (Doc ID 1591444.1)

Last updated on MARCH 27, 2014

Applies to:

Oracle Database - Enterprise Edition - Version 10.2.0.1 and later
Information in this document applies to any platform.

Goal

To show an example to create Index-Organized (IOT) Materialized View on prebuilt table. This could be useful in cases e.g. to circumvent ORA-12082 that would be received otherwise CREATE MATERIALIZED VIEW ... ORGANIZATION INDEX statement where primary key cannot be specified.

As it is mentioned in <Note 114272.1> for more complex queries the CREATE MATERIALIZED VIEW ... ORGANIZATION INDEX statement may return error

ORA-12082: "%"."%" cannot be index organized

as no primary key is passed to the IOT MV.

The following shows an example for such situation:

 

Solution

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