Primary key of a materialized view

[ddrillich]

Good Day,

We have the following materialized view.

The table bv_product has OID as primary key while ext_bhi_product_attr has PROD_ID as the primary key.

We would like to know what the primary key of the materialized view is.

Any ideas?


CREATE MATERIALIZED VIEW V_PRODUCT
TABLESPACE DEV01_PERM
PCTUSED 0
PCTFREE 10
INITRANS 2
MAXTRANS 255
STORAGE (
INITIAL 64K
MINEXTENTS 1
MAXEXTENTS UNLIMITED
PCTINCREASE 0
BUFFER_POOL DEFAULT
)
NOCACHE
NOLOGGING
NOCOMPRESS
NOPARALLEL
BUILD IMMEDIATE
REFRESH FORCE ON DEMAND
WITH PRIMARY KEY
AS
/* Formatted on 2008/08/19 09:24 (Formatter Plus v4.8.8) */
SELECT ---
a.OID OID, a.prod_id, 'BV_PRODUCT' table_name, a.store_id store_id,
a.deleted deleted, a.status status, a.last_mod_time last_mod_time,
( a.NAME
|| ' '
|| a.dept
|| ' '
|| a.sdesc2
|| ' '
|| a.appdef1
|| ' '
|| a.appdef2
|| ' '
|| a.appdef3
|| ' '
|| a.audio_file
|| ' '
|| a.video_file
|| ' '
|| a.inst_type
|| ' '
|| a.objective
|| ' '
|| a.hcc_part_assembly_num
|| ' '
|| a.hcc_division
|| ' '
|| a.hcc_family
|| ' '
|| a.hcc_product_line
|| ' '
|| a.hcc_prod_type
|| ' '
|| a.hcc_nozzle_type
|| ' '
|| a.hcc_prod_id
) concat1,
a.bot_family_code bot_family_code,
DECODE (a.store_id, 105, 'Y', 'N') bot_product, a.material_status,
p.ww_total, a.longdesc dretitle ---
FROM bv_product a, ext_bhi_product_attr p ---
WHERE a.store_id IN (103, 105, 109) AND a.prod_id = p.prod_id(+) ---
UNION ALL ---
SELECT ---
a.OID OID, a.prod_id, 'MR_PROD_ATTRVAL' table_name,
a.store_id store_id, a.deleted deleted, a.status status,
a.last_mod_time last_mod_time,
(b.mr_attr_key || ' ' || b.mr_attr_val) concat1,
a.bot_family_code bot_family_code,
DECODE (a.store_id, 105, 'Y', 'N') bot_product, a.material_status,
p.ww_total, NULL ---
FROM bv_product a, mr_prod_attrval b, ext_bhi_product_attr p ---
WHERE a.OID = b.OID AND a.store_id IN (103, 105, 109) AND a.prod_id = p.prod_id(+)
---
UNION ALL ---
SELECT ---
a.OID OID, a.prod_id, 'MR_REL_CONTENT' table_name, a.store_id store_id,
a.deleted deleted, a.status status, a.last_mod_time last_mod_time,
(c.rc_content_key || ' ' || c.rc_relation_type) concat1,
a.bot_family_code bot_family_code,
DECODE (a.store_id, 105, 'Y', 'N') bot_product, a.material_status,
p.ww_total, NULL ---
FROM bv_product a, mr_rel_content c, ext_bhi_product_attr p ---
WHERE a.OID = c.OID AND a.store_id IN (103, 105, 109) AND a.prod_id = p.prod_id(+)
---
UNION ALL ---
SELECT ---
a.OID OID, a.prod_id, 'BV_EP_PDAT' table_name, a.store_id store_id,
a.deleted deleted, a.status status, a.last_mod_time last_mod_time,
( d.attachment_oid
|| ' '
|| d1.attachment_name
|| ' '
|| d1.store_id
|| ' '
|| d1.creation_time
|| ' '
|| d1.status
|| ' '
|| d1.deleted
|| ' '
|| d1.last_mod_time
|| ' '
|| d1.description
|| ' '
|| d1.file_path
|| ' '
|| d1.file_size
|| ' '
|| d1.user_id
) concat1,
a.bot_family_code bot_family_code,
DECODE (a.store_id, 105, 'Y', 'N') bot_product, a.material_status,
p.ww_total, NULL ---
FROM bv_product a, bv_ep_pdat d, bv_ep_att d1, ext_bhi_product_attr p ---
WHERE a.OID = d.OID
AND d.attachment_oid = d1.OID
AND a.store_id IN (103, 105, 109)
AND a.prod_id = p.prod_id(+) ---
UNION ALL ---
SELECT ---
a.OID OID, a.prod_id, 'MR_REL_CONTENT' table_name, a.store_id store_id,
a.deleted deleted, a.status status, a.last_mod_time last_mod_time,
( LANGUAGE
|| ' '
|| e.name_translate
|| ' '
|| e.sdesc_translate
|| ' '
|| e.longdesc_translate
|| ' '
|| e.cp_spec_translate
) concat1,
a.bot_family_code bot_family_code,
DECODE (a.store_id, 105, 'Y', 'N') bot_product, a.material_status,
p.ww_total, NULL ---
FROM bv_product a, bv_prod_translate e, ext_bhi_product_attr p ---
WHERE a.OID = e.OID AND a.store_id IN (103, 105, 109) AND a.prod_id = p.prod_id(+)
---
UNION ALL ---
SELECT ---
a.OID OID, a.prod_id, 'LT_BOT_PROD_FEATURES' table_name,
a.store_id store_id, a.deleted deleted, a.status status,
a.last_mod_time last_mod_time,
( f.bot_feature_code
|| ' '
|| f.bot_feature_desc
|| ' '
|| f.bot_feature_value
|| ' '
|| f.bot_prod_feature_family_code
) concat1,
a.bot_family_code bot_family_code,
DECODE (a.store_id, 105, 'Y', 'N') bot_product, a.material_status,
p.ww_total, NULL ---
FROM bv_product a, lt_bot_prod_features f, ext_bhi_product_attr p ---
WHERE a.OID = f.OID AND a.store_id IN (103, 105, 109) AND a.prod_id = p.prod_id(+)
---
UNION ALL ---
SELECT ---
a.OID OID, a.prod_id, 'LT_BOT_PROD_BOM' table_name,
a.store_id store_id, a.deleted deleted, a.status status,
a.last_mod_time last_mod_time,
(g.component || ' ' || g.component_desc) concat1,
a.bot_family_code bot_family_code,
DECODE (a.store_id, 105, 'Y', 'N') bot_product, a.material_status,
p.ww_total, NULL ---
FROM bv_product a, lt_bot_prod_bom g, ext_bhi_product_attr p ---
WHERE a.OID = g.OID AND a.store_id IN (103, 105, 109) AND a.prod_id = p.prod_id(+)
---
UNION ALL ---
SELECT ---
a.OID OID, a.prod_id, 'LT_CLIFT_PROD_ATTR' table_name,
a.store_id store_id, a.deleted deleted, a.status status,
a.last_mod_time last_mod_time,
(h.attr_name || ' ' || h.attr_value) concat1,
a.bot_family_code bot_family_code,
DECODE (a.store_id, 105, 'Y', 'N') bot_product, a.material_status,
p.ww_total, NULL ---
FROM bv_product a, lt_clift_prod_attr h, ext_bhi_product_attr p ---
WHERE a.OID = h.OID AND a.store_id IN (103, 105, 109) ---
AND a.prod_id = p.prod_id(+);

COMMENT ON MATERIALIZED VIEW V_PRODUCT IS 'snapshot table for snapshot DEV01.V_PRODUCT';

CREATE INDEX I_MV_PRODUCT_MODTIME_STORE ON V_PRODUCT
(LAST_MOD_TIME, STORE_ID)
NOLOGGING
TABLESPACE DEV01_PERM
PCTFREE 10
INITRANS 2
MAXTRANS 255
STORAGE (
INITIAL 64K
MINEXTENTS 1
MAXEXTENTS UNLIMITED
PCTINCREASE 0
BUFFER_POOL DEFAULT
)
NOPARALLEL;

CREATE INDEX I_MV_PRODUCT_OID ON V_PRODUCT
(OID)
NOLOGGING
TABLESPACE DEV01_PERM
PCTFREE 10
INITRANS 2
MAXTRANS 255
STORAGE (
INITIAL 64K
MINEXTENTS 1
MAXEXTENTS UNLIMITED
PCTINCREASE 0
BUFFER_POOL DEFAULT
)
NOPARALLEL;



Regards,
Dan

 

[Dagon]

It would probably be a.OID and table_name, which is used to distinguish between the records being returned by the UNION ALL. If, as you say, ext_bhi_product_attr has PROD_ID as its primary key, each row in bv_product which joins to it will only return a single row. Therefore, ext_bhi_product_attr cannot increase the cardinality of the query. There are, however, lots of other tables in your queries (e.g. lt_clift_prod_attr) and you haven't said what the primary keys of these are, so they may increase the cardinality if they are not uniquely joined to bv_product.

 

[ddrillich]

Hi Dagon,

Thank you for your answer.

As it stands now, our Autonomy Connector seems to work fine when we define OID to be the primary key.

Regards,
Dan