Toad World® Forums

MVIEW Does not keep WITH ROWID in DDL

Below is a create materialized view statement that I specify
with rowid. However, after the mview gets created the ddl shows with primary
key. Why doesn’t it keep the with rowid definition and why doesn’t it create
the I_SNAP$ index.

CREATE MATERIALIZED VIEW SIEBEL.MV_GST

NOCACHE

LOGGING

NOCOMPRESS

NOPARALLEL

BUILD IMMEDIATE

USING INDEX

REFRESH FAST ON DEMAND

WITH ROWID

AS

SELECT OI_F.ACCNT_BILL_GEO_WID,

SEG.ROW_WID AS
SEGMENT_WID,

PROD.PART_NUM AS
PROD_PART_NUM,

PROD.PROD_NAME,

PROD.PRODUCT_TYPE_CODE
AS PROD_TYPE,

PROD.SUB_TYPE AS
PROD_SUB_TYPE,

SRC.CAMP_NAME,

SRC.CAMP_SRC_NUM,

SEG.SGMT_NAME AS
SEGMENT_NAME,

SEG.SGMT_SRC_ID AS
SEGMENT_SRC_ID,

LPAD (PER.EMP_NUM, 11,
‘0’) AS X_EMPLID,

LPAD (PER.EMP_NUM, 9,
‘0’) AS X_PEOPLE_ID,

PROD.X_13_MONTH_FLG,

OI_F.ACCNT_ID,

SRC.ROWID AS SRC_ROWID,

OI_F.ROWID AS
ORITEM_ROWID,

SEG.ROWID AS
SEGMENT_ROWID,

ORD.ROWID AS
ORDER_ROWID,

PROD.ROWID AS
PROD_ROWID,

ORG.ROWID AS ORG_ROWID,

PER.ROWID AS PER_ROWID,

BILL_TO_ACCNT.ROWID AS
BILL_ROWID,

SHIP_TO_ACCNT.ROWID AS
SHIP_ROWID

FROM SIEBEL.W_ORDERITEM_F OI_F,

SIEBEL.W_SOURCE_D SRC,

SIEBEL.W_SEGMENT_D SEG,

SIEBEL.W_ORDER_D ORD,

SIEBEL.W_PRODUCT_D
PROD,

SIEBEL.W_ORG_D
BILL_TO_ACCNT,

SIEBEL.W_ORG_D
SHIP_TO_ACCNT,

SIEBEL.W_ORG_D ORG,

SIEBEL.W_PERSON_D PER

WHERE OI_F.SOURCE_WID = SRC.ROW_WID(+) AND
OI_F.SEGMENT_WID = SEG.ROW_WID(+) AND OI_F.ORDER_WID = ORD.ROW_WID(+) AND
OI_F.PROD_WID = PROD.ROW_WID(+) AND OI_F.CONTACT_WID = PER.ROW_WID(+) AND
OI_F.BILL_ACCNT_WID = BILL_TO_ACCNT.ROW_WID(+) AND OI_F.SHIP_ACCNT_WID =
SHIP_TO_ACCNT.ROW_WID(+) AND OI_F.ACCNT_WID = ORG.ROW_WID(+);

Message was edited by: Garivaldo

Message from: susan1

However, after the mview gets created the shows with primary key. Why
doesn’t it keep the with rowid definition and why doesn’t it create the


Historical Messages

Author: James Salerno
Date: Wed Aug 31 04:08:24 PDT 2011
However, after the mview gets created the shows with primary key. Why
doesn’t it keep the with rowid definition and why doesn’t it create the
__

Author: Garivaldo
Date: Fri Aug 05 09:03:29 PDT 2011
Below is a create materialized view statement that I specify with rowid.
However, after the mview gets created the ddl shows with primary key. Why
doesn’t it keep the with rowid definition and why doesn’t it create the I_SNAP$
index.

CREATE MATERIALIZED VIEW SIEBEL.MV_GST

NOCACHE

LOGGING

NOCOMPRESS

NOPARALLEL

BUILD IMMEDIATE

USING INDEX

REFRESH FAST ON DEMAND

WITH ROWID

AS

SELECT OI_F.ACCNT_BILL_GEO_WID,

SEG.ROW_WID AS SEGMENT_WID,

PROD.PART_NUM AS PROD_PART_NUM,

PROD.PROD_NAME,

PROD.PRODUCT_TYPE_CODE AS PROD_TYPE,

PROD.SUB_TYPE AS PROD_SUB_TYPE,

SRC.CAMP_NAME,

SRC.CAMP_SRC_NUM,

SEG.SGMT_NAME AS SEGMENT_NAME,

SEG.SGMT_SRC_ID AS SEGMENT_SRC_ID,

LPAD (PER.EMP_NUM, 11, ‘0’) AS X_EMPLID,

LPAD (PER.EMP_NUM, 9, ‘0’) AS X_PEOPLE_ID,

PROD.X_13_MONTH_FLG,

OI_F.ACCNT_ID,

SRC.ROWID AS SRC_ROWID,

OI_F.ROWID AS ORITEM_ROWID,

SEG.ROWID AS SEGMENT_ROWID,

ORD.ROWID AS ORDER_ROWID,

PROD.ROWID AS PROD_ROWID,

ORG.ROWID AS ORG_ROWID,

PER.ROWID AS PER_ROWID,

BILL_TO_ACCNT.ROWID AS BILL_ROWID,

SHIP_TO_ACCNT.ROWID AS SHIP_ROWID

FROM SIEBEL.W_ORDERITEM_F OI_F,

SIEBEL.W_SOURCE_D SRC,

SIEBEL.W_SEGMENT_D SEG,

SIEBEL.W_ORDER_D ORD,

SIEBEL.W_PRODUCT_D PROD,

SIEBEL.W_ORG_D BILL_TO_ACCNT,

SIEBEL.W_ORG_D SHIP_TO_ACCNT,

SIEBEL.W_ORG_D ORG,

SIEBEL.W_PERSON_D PER

WHERE OI_F.SOURCE_WID = SRC.ROW_WID(+) AND OI_F.SEGMENT_WID = SEG.ROW_WID(+) AND
OI_F.ORDER_WID = ORD.ROW_WID(+) AND OI_F.PROD_WID = PROD.ROW_WID(+) AND
OI_F.CONTACT_WID = PER.ROW_WID(+) AND OI_F.BILL_ACCNT_WID =
BILL_TO_ACCNT.ROW_WID(+) AND OI_F.SHIP_ACCNT_WID = SHIP_TO_ACCNT.ROW_WID(+) AND
OI_F.ACCNT_WID = ORG.ROW_WID(+);

Message was edited by: Garivaldo
__


However, after the mview gets created the shows with primary key. Why doesn’t it keep the with rowid definition and why doesn’t it create the