具有主键的Oracle物化视图 [英] Oracle Materialized Views with primary key

问题描述

我在下面创建了Oracle物化视图:

I created the Oracle Materialized View below:

CREATE MATERIALIZED VIEW MyMV
REFRESH COMPLETE ON DEMAND
AS

SELECT t1.*
  FROM table1 t1, table2 t2 where t1.id=t2.id;

table1有一个主键，并且成功创建了MV，但未在物化视图表中创建主键.

The table1 has a primary key and the MV was created succesfully but the primary key was not created in the materialized view table.

还有其他方法可以用主键创建MV吗?

Is there any other way to create MVs with primary keys?

推荐答案

这是因为您的物化视图基于两个表，如果您基于具有主键的单个表创建视图，则将在您的物化视图. 如果需要，您仍然可以在以后创建索引:

it's because your materialized view is based on two tables, if you create your view based on a single table with a primary key, then the primary key is created on you Materialized view. You can still create the index afterwards if you need one:

SQL> create table t1(id number);

Table created.

SQL> create table t2(id number);

Table created.

SQL> alter table t1 add primary key (id);

Table altered.

SQL> alter table t2 add primary key (id);

Table altered.

SQL> CREATE MATERIALIZED VIEW MyMV
REFRESH COMPLETE ON DEMAND
AS
SELECT t1.*
  FROM t1, t2 where t1.id=t2.id;  2    3    4    5

Materialized view created.

SQL> create unique index myindex on MyMV(id);

Index created.

编辑

创建一个主键而不是唯一索引:

create a primary key instead of the unique index:

SQL> alter materialized view MyMV add constraint PK_ID primary key (id);

Materialized view altered.

SQL> alter table t3 add constraint FK_TABLE3_MyMV foreign key (id) references MyMV (id);

Table altered.

这篇关于具有主键的Oracle物化视图的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！