When Materialized Views (and pre built tables for them) are created, we create the constraint and index together as follows.:
ALTER TABLE MV_TABLE
ADD CONSTRAINT tablename_PK
PRIMARY KEY (id_nbr)
USING INDEX TABLESPACE SP_64K_01_INDX
PCTFREE 5
INITRANS 2
MAXTRANS 255
STORAGE(FREELISTS 1
FREELIST GROUPS 1
BUFFER_POOL DEFAULT);
CREATE MATERIALIZED VIEW MV_TABLE
ON PREBUILT TABLE
USING INDEX PCTFREE 5 INITRANS 2 MAXTRANS 255
STORAGE(INITIAL 64K NEXT 64K MINEXTENTS 1 MAXEXTENTS UNLIMITED PCTINCREASE 0) TABLESPACE SP_64K_01_INDX
REFRESH FORCE
ON DEMAND
AS SELECT * FROM schema.table_name;
In Toad the Materialize View | Source tab shows the following:
CREATE UNIQUE INDEX tablename_PK ON MV_TABLE
(ID_NBR)
LOGGING
TABLESPACE SP_64K_01_INDX
PCTFREE 5
INITRANS 2
MAXTRANS 255
STORAGE (
INITIAL 64K
NEXT 64K
MINEXTENTS 1
MAXEXTENTS UNLIMITED
PCTINCREASE 0
FREELISTS 1
FREELIST GROUPS 1
BUFFER_POOL DEFAULT
)
NOPARALLEL;
DROP MATERIALIZED VIEW MV_TABLE;
CREATE MATERIALIZED VIEW MV_TABLE;
ON PREBUILT TABLE WITH REDUCED PRECISION
REFRESH COMPLETE ON DEMAND
WITH PRIMARY KEY
AS ...
Is there a ddl/script option to create the primary key as one (constraint and index at the same time so that NULLS are not allowed).
You need to be signed in and under a current maintenance contract to view premium knowledge articles.
© ALL RIGHTS RESERVED. Feedback Terms of Use Privacy Cookie Preference Center