Results 1 to 2 of 2
  1. #1
    Join Date
    Jan 2013
    Posts
    1

    Unanswered: Materialized view

    Hi All,

    I am trying to convert the below normal view to MATERIALIZED view to improve view performance.

    CREATE OR REPLACE FORCE VIEW V_IBM_DOCUMENTS
    (
    PATH,
    SORT_PATH,
    LANGUAGE_CODE,
    TITLE,
    ABSTRACT,
    SIZE_B,
    DOCTYPE_CODE,
    DOCHEADING_CODE,
    LAST_MOD_DATE,
    EXPIRATION_DATE,
    PUBLISH_DATE,
    SORT_WEIGHT,
    VERSION,
    UUID,
    REF_UUID,
    REF_PATH,
    OEM_CODE,
    PRODUCT_CODE,
    LONG_NAME,
    SHORT_NAME,
    WEB_NAME
    )
    AS
    SELECT DISTINCT m.PATH AS PATH,
    concatsorttitle (m.PATH, m.sort_weight, m.title) AS sort_path,
    LOWER (NVL (m.language_code, 'en')) AS language_code,
    m.title AS title,
    m.abstract AS abstract,
    m.size_b AS size_b,
    m.doctype_code AS doctype_code,
    m.docheading_code AS docheading_code,
    m.last_mod_date AS last_mod_date,
    m.expiration_dt AS expiration_date,
    m.publish_dt AS publish_date,
    m.sort_weight AS sort_weight,
    m.version AS version,
    m.uuid AS uuid,
    m.ref_uuid AS ref_uuid,
    muuid.PATH AS ref_path,
    SUBSTR (hop.oem_product_code, 1, INSTR (hop.oem_product_code, '::') - 1) AS oem_code,
    SUBSTR (hop.oem_product_code, INSTR (hop.oem_product_code, '::') + 2) AS product_code,
    po.long_name AS long_name,
    po.short_name AS short_name,
    po.web_name AS web_name
    FROM metadata m
    LEFT OUTER JOIN metadata_products hop
    ON m.PATH = hop.PATH
    LEFT OUTER JOIN product p
    ON p.code = SUBSTR (hop.oem_product_code, INSTR (hop.oem_product_code, '::') + 2)
    LEFT OUTER JOIN product_oem po
    ON po.PATH = p.PATH
    AND po.oem = SUBSTR (hop.oem_product_code, 1, INSTR (hop.oem_product_code, '::') - 1)
    LEFT OUTER JOIN metadata muuid
    ON m.ref_uuid = muuid.uuid
    WHERE (m.expiration_dt IS NULL
    OR m.expiration_dt > SYSDATE)
    AND (m.target_web_property_code IN ('EVERYWHERE', 'OTHER'))
    AND (m.doctype_code IN ('DOWNLOAD')
    AND m.docheading_code IN ('IBM')) ORDER BY sort_path, version DESC, PATH;

    Please suggest me which type of refresh/other parameters I can use for this requirement and provide your great input in tuning this query.

    Thanks,
    Ram

  2. #2
    Join Date
    Oct 2002
    Location
    Cape Town, South Africa
    Posts
    253
    REFRESH FAST ON COMMIT
    That will behave as closely to a view as possible. But remember, the results from the materialized view will only be up to date once your changes on the base tables are committed.
    You will also need a materialized view log on each of the base tables.
    You will probably need to change concatsorttitle

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •