Re: How to Improve Materialized View Refresh & Query Performance ?

On Feb 28, 8:35 pm, "shakespeare" <what..._at_xs4all.nl> wrote:
> <krisl..._at_gmail.com> schreef in berichtnews:84c1efed-57c3-4285-85d8-5df7fe0dbd40_at_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 TRTYPE
> > FROM 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 TRTYPE
> > FROM 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 :
>
> > 1) 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
>
> All rowids are in the SAME column.....
>
> Shakespeare- Hide quoted text -
>
> - Show quoted text -

Hi,
What does it mean : "All rowids are in the SAME column....." ??

Is it like this : CREATE INDEX MV_TRANS_STOCK_IDX1 ON MV_TRANS_STOCK (HRID, DRID, CRID); Thank you,
xtanto Received on Thu Feb 28 2008 - 21:30:00 CST