Primary Key Constraint and its Index's Names Are Not Same For Master Table and MVIEW. (Doc ID 2049847.1)

Last updated on SEPTEMBER 18, 2015

Applies to:

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

Goal

The name of the Primary Key Constraint and its Index is not same between Master Table and MVIEW.

Consider a master table TEST created as:
create table test ( c1 varchar2(100), CONSTRAINT TEST_PK primary key(c1) USING INDEX );

Create a Materialized view log as:
CREATE MATERIALIZED VIEW LOG ON TEST WITH PRIMARY KEY;

The following are the names of the PK constraint and its index of the Master Table:
Constraint Name: TEST_PK
Index Name: TEST_PK

Now, Create the MVIEW as:

CREATE MATERIALIZED VIEW TEST_MV (C1)
REFRESH FAST ON DEMAND
WITH PRIMARY KEY
AS SELECT C1 FROM TEST;

The following are the names of the PK constraint and its index of the MVIEW:
Constraint Name: SYS_C_SNAP$_501TEST_PK
Index Name: SYS_C_SNAP$_501TEST_PK

The question is:
Why the constraint name & index name of the MVIEW differs from the Master Table?

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