How to add primary key to materialized view in oracle-CodePudding

please help me with an example on how to add primary key to materialized view in oracle 19c or 21c. Also, real life application where we use primary key to materialized view. I posting this after complete research on materialized view. I have a strong opinion that primary key is not required on materialized view. Please correct me if I am wrong.

CodePudding user response：

How to add a primary key? Using the ALTER TABLE statement.

This is a sample table; I'll create a materialized view on it.

SQL> create table test as
  2    select deptno, empno, ename, job
  3    from emp;

Table created.

SQL> create materialized view mv_test as
  2    select deptno, empno, ename, job
  3    from test;

Materialized view created.

Primary key:

SQL> alter table mv_test add constraint pk_mvt primary key (empno);

Table altered.

SQL>

Real life: use it whenever appropriate. Primary key implicitly creates index on primary key column(s). Index improves performance (if optimizer chooses to use it).