How to Improve Materialized View Refresh & Query Performance ?
From: <krislioe_at_gmail.com>
Date: Thu, 28 Feb 2008 02:55:38 -0800 (PST)
Message-ID: <84c1efed-57c3-4285-85d8-5df7fe0dbd40@u69g2000hse.googlegroups.com>
CON.PRODUCT = SRD.PRODUCT
UNION ALL
SELECT
INVH.INV_NO DOC_NO, INVH.INV_DATE DOC_DATE, INVH.REGS_CODE AS DOC_TYPE,
CON.PRODUCT = INVD.PRODUCT AND
INVH.STATUS <> 'C'
UNION ALL
... AND so ON... --[I have about 15 other SELECTs ]
Date: Thu, 28 Feb 2008 02:55:38 -0800 (PST)
Message-ID: <84c1efed-57c3-4285-85d8-5df7fe0dbd40@u69g2000hse.googlegroups.com>
Hi all,
I have a materialized view as follow :
CREATE MATERIALIZED VIEW MV_TRANS_STOCK
PARALLEL
BUILD IMMEDIATE
REFRESH FAST ON COMMIT
AS
SELECT SRH.SR_NO DOC_NO, SRH.SR_DATE DOC_DATE, SRH.REGS_CODE
DOC_TYPE,
SRD.PRODUCT, SRH.WH_CODE, SRD.RACK, SRD.BATCH, SRD.EXP_DATE, SRH.LOC_CODE, SRD.QTY, SRH.ROWID HRID, SRD.ROWID DRID, CON.ROWID CRID, 1 AS TRTYPEFROM SALESRETURN_D SRD , SALESRETURN_H SRH, MV_CONVERT_UOM CON WHERE SRH.SR_ID = SRD.SR_ID_HDR AND
CON.PRODUCT = SRD.PRODUCT
UNION ALL
SELECT
INVH.INV_NO DOC_NO, INVH.INV_DATE DOC_DATE, INVH.REGS_CODE AS DOC_TYPE,
INVD.PRODUCT, INVD.WH_CODE, INVD.RACK, INVD.BATCH, INVD.EXP_DATE, INVH.LOC_CODE, INVD.QTY, INVH.ROWID HRID, INVD.ROWID DRID, CON.ROWID CRID, 3 AS TRTYPEFROM INVOICE_BATCH INVD, INVOICE_H INVH, MV_CONVERT_UOM CON WHERE INVH.INV_ID = INVD.INVH_ID AND
CON.PRODUCT = INVD.PRODUCT AND
INVH.STATUS <> 'C'
UNION ALL
... AND so ON... --[I have about 15 other SELECTs ]
I have two question :
- What index are required to improve the refresh : is it like below ??
CREATE INDEX MV_TRANS_STOCK_IDX1 ON MV_TRANS_STOCK (HRID, DRID, CRID);
Will it help ??? Or I have to create 3 indexes, one for each ROWID
COLUMN ?
2) what index required to improve query performance ?
FYI the key columns are : PRODUCT, WH_CODE, RACK, BATCH, EXP_DATE,
LOC_CODE
Is it ONE BITMAP index containing all the key colums ?
OR 6 bitmap indexes for each key columns ?
Thank you for your support,
Kristanto
Received on Thu Feb 28 2008 - 04:55:38 CST