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).
WORKAROUND
none
STATUS
Enhancement request ST61831 has been submitted to Development for consideration in a future release of Toad for Oracle.
