OraFAQ Forum: Replication

Home » Server Options » Replication » MV Issue

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

MV Issue [message #201669]

Mon, 06 November 2006 05:23

kkpareek
Messages: 5
Registered: November 2006

Junior Member

Hi

I have a MV and MV log on the master and doing fast refresh. I am using this concept to aggregate the data in MV from detailed data.

Now, there is requirement to purge the underlying detailed data without droping data from MV.

How this can be done? I tried dropping the MV log on master and then purging data. It worked but I could not fast refresh when I created the MV log again (ORA-12034).

Any ideas?

Kailash Pareek

Report message to a moderator

Re: MV Issue [message #201671 is a reply to message #201669]

Mon, 06 November 2006 05:36

Littlefoot
Messages: 21823
Registered: June 2005
Location: Croatia, Europe

Senior Member
Account Moderator

Oracle

ORA-12034: materialized view log on "string"."string" younger than last refresh

Cause: The materialized view log was younger than the last refresh.

Action: A complete refresh is required before the next fast refresh.

Report message to a moderator

Re: MV Issue [message #201674 is a reply to message #201671]

Mon, 06 November 2006 05:56

kkpareek
Messages: 5
Registered: November 2006

Junior Member

Hi Sr. Member

I think I did not make the problem/issue very clear. Let me try again.

Let us say I have table DAILY_SALES with daily sales data and have created MV log on it. I have created a MV (DAILY_SALES_MV) based on datewise sales with sum(sale_amt).

Now the requirement is to delete all last years data from DAILY_SALES while retaining the granular data in DAILY_SALES_MV for last year.

However, if I drop data from DAILY_SALES it will drop from MV too. I do not want it. How this can be done, if at all?

Kailash Pareek

Report message to a moderator

Re: MV Issue [message #201684 is a reply to message #201674]

Mon, 06 November 2006 06:33

Littlefoot
Messages: 21823
Registered: June 2005
Location: Croatia, Europe

Senior Member
Account Moderator

If I'm not wrong, materialized view will be OK (i.e. contain last year's data) as long as you don't refresh it.

Perhaps you should consider another method of storing history data - not a materialized view, but a table. Would some kind of data warehousing suit your needs? Read some more about it on OraFAQ and, of course, on OTN (Introduction to Data Warehousing).

Report message to a moderator

Re: MV Issue [message #201689 is a reply to message #201669]

Mon, 06 November 2006 06:48

kkpareek
Messages: 5
Registered: November 2006

Junior Member

Hi

Thanks for update. As long as I do not purge the detail data, my MV is safe. However, my client wants to purge the detail data every year.

I can use tables to store the summaries and can update them with PL/SQL programs but then I have to write tons of codes.

If I use MV, there is no coding. Looks like I have little option here.

I thought smart way of droping the MV logs, then purging and then creating MV logs again, hoping that after this, only the new DML will update the MV and old MV data will remain. However, oracle is not fooled and gave ORA-12034.

If there is any work around anybody knows, kindly let me know.

Kailash Pareek

Report message to a moderator

Re: MV Issue [message #201778 is a reply to message #201689]

Mon, 06 November 2006 12:47

Littlefoot
Messages: 21823
Registered: June 2005
Location: Croatia, Europe

Senior Member
Account Moderator

Would you really have to do a lot of coding to insert data into a table instead of create a materialized view? Couldn't you just use the code you already have - it is in the CREATE MATERIALIZED VIEW statement? It would then be something like this:

INSERT INTO histor_table (col1, col2, ...)
SELECT <your materialized view select statement here>
FROM production_table
WHERE date_column <condition here>;

Report message to a moderator

Re: MV Issue [message #201876 is a reply to message #201669]

Tue, 07 November 2006 02:59

kkpareek
Messages: 5
Registered: November 2006

Junior Member

Hi

Thanks for the code piece. It does become a bother when there are 100s of tables and then there are multiple levels of MV ( Nested MV) and it is not only insert but update too.

I have worked in good old ETL with CSV extract, ftp, then load in staging with SQL*Loader and the PL/SQL program to process them.

I feel MV is neat as long as you do not plan to delete data from base tables.

However, one workaround I have in mind is move the historical data to actual tables and drop and re-create the MV when purging on base tables is done.

For the reporting, then use the union all of historial table and current MV.

Kailahs

Report message to a moderator

Re: MV Issue [message #210287 is a reply to message #201669]

Wed, 20 December 2006 03:10

stirandas
Messages: 4
Registered: December 2006

Junior Member

Kailash,

If your requirement is to show only the latest/current year's data in the MV, then use the MV with filter, instead of disturbing oracle's internal mechanism (like MV logs).

Search for some doc on the net with the below title

snapshots with filter
materialized view with filter

Regards
Srikanth

Report message to a moderator

Re: MV Issue [message #232119 is a reply to message #201669]

Thu, 19 April 2007 09:24

bonzi
Messages: 1
Registered: April 2007
Location: Zagreb, Croatia

Junior Member

kkpareek wrote on Mon, 06 November 2006 05:23

If I understood correctly, the solution would be:

Build a table for MV manually
Create the MV with "on prebuilt table" clause
When you need to purge master table, drop the view, do the purge, re-create the view (still "on prebuilt table")

HTH (although I just noticed that the post I am relying to is from last year. Well, better late than never... Embarassed

)

Cheers!

Report message to a moderator

Previous Topic:	Re.Replication
Next Topic:	Materalized views and fast and complete refresh

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sat Dec 14 16:32:34 CST 2024