Database DataWarehousing (MOSC)

MOSC Banner

Materialized view refresh performs poorly

edited Feb 17, 2014 8:45AM in Database DataWarehousing (MOSC) 1 commentAnswered ✓

Hello Everyone,

I have a materialized view log created on a 10gR2 database server, and materialized view on another 10gR2 database server.

CREATE MATERIALIZED VIEW "X66"."X6600VM01" ("CODNUM_001", "CLAVEAF_001", "NSS_001",

..................

ON PREBUILT TABLE WITHOUT REDUCED PRECISION

USING INDEX

REFRESH COMPLETE ON DEMAND

USING DEFAULT LOCAL ROLLBACK SEGMENT

DISABLE QUERY REWRITE

AS SELECT

CODNUM_001, CLAVEAF_001,NSS_001

..........

FROM

Q97B.Q97B01V53@Q97B_DBL0

;

The materialized view gets complete refresh on demand.

The materialized view has 5 indexes.

The problem is the long time when performing a complete refresh, on demand: it takes about 45 mins.

I know it is related to the indexes, but, how could i improve the refreshing time?

Howdy, Stranger!

Log In

To view full details, sign in to My Oracle Support Community.

Register

Don't have a My Oracle Support Community account? Click here to get started.

Category Leaderboard

Top contributors this month

New to My Oracle Support Community? Visit our Welcome Center

MOSC Help Center