Home » Server Options » Replication » MV Issue
MV Issue [message #201669] Mon, 06 November 2006 05:23 Go to next message
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
Re: MV Issue [message #201671 is a reply to message #201669] Mon, 06 November 2006 05:36 Go to previous messageGo to next message
Littlefoot
Messages: 21806
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.
Re: MV Issue [message #201674 is a reply to message #201671] Mon, 06 November 2006 05:56 Go to previous messageGo to next message
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


Re: MV Issue [message #201684 is a reply to message #201674] Mon, 06 November 2006 06:33 Go to previous messageGo to next message
Littlefoot
Messages: 21806
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).
Re: MV Issue [message #201689 is a reply to message #201669] Mon, 06 November 2006 06:48 Go to previous messageGo to next message
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
Re: MV Issue [message #201778 is a reply to message #201689] Mon, 06 November 2006 12:47 Go to previous messageGo to next message
Littlefoot
Messages: 21806
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>;
Re: MV Issue [message #201876 is a reply to message #201669] Tue, 07 November 2006 02:59 Go to previous messageGo to next message
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
Re: MV Issue [message #210287 is a reply to message #201669] Wed, 20 December 2006 03:10 Go to previous messageGo to next message
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
Re: MV Issue [message #232119 is a reply to message #201669] Thu, 19 April 2007 09:24 Go to previous message
bonzi
Messages: 1
Registered: April 2007
Location: Zagreb, Croatia
Junior Member
kkpareek wrote on Mon, 06 November 2006 05:23
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




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!

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


Current Time: Thu Mar 28 14:52:13 CDT 2024