refresh fast + logs

[mila82]

ppl

i created a post earlier in regards to refreshing a materialized view. i saw that i am able to do a refresh fast on my materialized view but you have to have a materialized view log ...
and does anyone know if a refresh fast copies all of the data from the master tables or does it only copy the new entries to the materialized view ??

my question is: i have to do an update on 27 tables ...
so i have to create 27 logs on each of the master tables ...

does this somehow affect performance ... ??

is there a better way to do it ??

is it possible to create a materialized view without the refresh option - never - and create a trigger which would update the materialized view ... ( if i was to create a trigger, you would apply the trigger on all 27 tables, RIGHT ??? )

help would be appreciated ...

merry xmas and a good slide into the new year ...

cheers
mila

[slimdave]

A refresh fast is based only on the MV log entries, or direct path log entries. You have to create the MV's after the MV log or you'll get a 12034 error: http://oraclesponge.blogspot.com/200...-view-log.html

MV logs do affect performance.

If you want a better way to do this, then you'd better descrivbe exactly what problem you're trying to solve.

[mila82]

hey ppl

this is what im trying to do .
i have a materialized view which gets its data from 27 live tables .. now, the materialized view needs to be refreshed every day with only the latest data to be picked up ..
what it means that i do not want to go through all the data of the 27 tables ( would affect the peformance as there are millions of transactions ) .. i just want to pick up the latest stuff ...

so i thought of doing a fast refresh but i guess as you mentioned it would affect the performance .
so what are my other options ?? i thought of creating a trigger but im not too sure if that is possible .. can you set the refresh to never and then create a trigger which would fire when the base tables change ??
another suggestion was to create a materialized view for each of the 27 tables but im not too sure if this is the best way.

your input to this would be greatly appreciated mate. hope you can help me as i am very stuck on this and cannot move forward with my project.

cheers
milos

[slimdave]

Is this a single MV formed by joining these 27 tables, or a UNION ALL of them?

[mila82]

Is this a single MV formed by joining these 27 tables, or a UNION ALL of them?

its a union of all 27 tables

eg:

SELECT
  SOURCE_PARTICIPANT_ID,
  SOURCE_PARTICIPANT_ROLE,
  TXN_DATE_TIME,
  UD_TYPE,
  UD_SUBTYPE,
  BUSINESS_DATE,
  NULL AS TRANSACTION_VALUE,
  CARD_SERIAL_NUMBER,
  DEVICE_ID,
  EXCEPTION_LIST,
  STREAMING_SESSION_ID
 FROM CUT_AP_ACTIONLIST
 WHERE ISS_EXCEPTION = 'Y' OR CCH_EXCEPTION = 'Y'
UNION 
SELECT
  SOURCE_PARTICIPANT_ID,
  SOURCE_PARTICIPANT_ROLE,
  TXN_DATE_TIME,
  UD_TYPE,
  UD_SUBTYPE,
  BUSINESS_DATE,
  NULL AS TRANSACTION_VALUE,
  CARD_SERIAL_NUMBER,
  DEVICE_ID,
  EXCEPTION_LIST,
  STREAMING_SESSION_ID  
 FROM CUT_AP_BLOCK
 WHERE ISS_EXCEPTION = 'Y' OR CCH_EXCEPTION = 'Y'
UNION
 SELECT
  SOURCE_PARTICIPANT_ID,
  SOURCE_PARTICIPANT_ROLE,
  TXN_DATE_TIME,
  UD_TYPE,
  UD_SUBTYPE,
  BUSINESS_DATE,
  NULL AS TRANSACTION_VALUE,
  CARD_SERIAL_NUMBER,
  DEVICE_ID,
  EXCEPTION_LIST,
  STREAMING_SESSION_ID  
 FROM CUT_AP_DEPOSIT
 WHERE ISS_EXCEPTION = 'Y' OR CCH_EXCEPTION = 'Y'
etc
etc

[slimdave]

could it not be a union all?