Thanks, Arup. Your advice is always good.
David
From: Arup Nanda [EMAIL PROTECTED]
Reply-To: [EMAIL PROTECTED]
To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED]
Subject: Re: Refresh option for Materialized view , want to use it during
refresh - for
Date: Tue, 28 Oct 2003 12:09:25
-To: [EMAIL PROTECTED]
To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED]
Subject: Re: Refresh option for Materialized view , want to use it during
refresh - for
Date: Fri, 24 Oct 2003 18:04:33 -0800
David,
Answers to your questions:
(1) Without knowing your exact needs, I wil offer a few
-L [EMAIL PROTECTED]
Subject: Re: Refresh option for Materialized view , want to use it during refresh -
for
Arup,
I really appreciate your answer in great details. I got on prebuilt table
work. Thanks a lot for your help. Here is another question:
Do you see any advantage to use
table t to table t2
5. rename table t1 to t
6. rename table t2 to t1
7. truncate table t1 for next day loading
David
From: Arup Nanda [EMAIL PROTECTED]
Reply-To: [EMAIL PROTECTED]
To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED]
Subject: Re: Refresh option for Materialized view
Hi Arup,
This is a very good method. I would like to use it to modify some of my
data loading procedures. Here are my questions:
1. Do I need to create the table on the step 1 every time when I refresh the
data If I refresh data once per day?
2. Is ON PREBUILT TABLE available on Oracle 8i?
did i read that correctly that create table as is superior to a materialized
view for nightly loads? We drop all the tables in some of our schemas and
rebuild them with create table as statements. I was going to try out
materialized views to see if they were faster.
guess they are not?
-
David,
Answers to your questions:
(1) Without knowing your exact needs, I wil offer a few different scenarios.
I am assuming that you are doing a complete refresh every time. The
following pertain to that.
Say, your name of the MV is MV1. Here are the steps the first time.
1. Create table MV1
No, you didn't read it completely. Create table and create MV do the same
thing - produce a copy of the data on a different location (or a different
segment) that can be queried independently. However, I proposed a different
way of doing the MV creating and refreshing, not using the
no we dont need query rewrite. we load data every night across a database
link. we drop and recreate all the tables from scratch. I thought about
using materialized views. i thought they might load faster because of fast
refresh. not sure if we can do that across a db link.
- Original Message
i thought they might load faster because of fast refresh.
If you can do fast refresh, then MVs may be better. However, here are a few
considerations before you do so:
1. Fast refresh requires creation of mv logs on the master tables, which are
populated by triggers. This will affect performance
view , want to use it during refresh
Siddharth,
I will offer a slightly out-of-the-box solution. Please read it through till
the end to determine its applicability in your case.
It seems yours refresh interval is once a day and you don't mind stale data for
a max of 24 hours. You also refresh
Hi
Gurus,
I
have a materialized view, which is based on Oracle Apps tables and on remote
database. The view refresh takes around ½ hour, during this time period I
cannot see any records in the materialized view and therefore my application
faces errors.
The
following is the view
ame from my
website www.proligence.com/downlaods.html,
titled "Painless Master Table Alter" from the Presentations
Section.HTH.Arup Nanda- Original
Message - From: Siddharth Haldankar To: Multiple recipients of list
ORACLE-L Sent: Tuesday, October 21, 2003 3:59 AMSubject: Refresh
13 matches
Mail list logo
