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

Last updated on NOVEMBER 06, 2023

Applies to:

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

My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.