473,404 Members | 2,114 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,404 software developers and data experts.

Materialized view creation taking too long

CREATE MATERIALIZED VIEW cust_sales_mv
tablespace claims_5m
BUILD IMMEDIATE
REFRESH COMPLETE on demand with rowid
disable QUERY REWRITE
AS

SELECT c.claim_number,
c.status,
c.date_received,
aag.state,
p.pay_amount
FROM claims.ardis_active_groups aag,
claims.claim c,
claims.payment p
WHERE aag.GROUP_ID = c.GROUP_ID
AND c.claim_seq = p.claim_seq
AND c.status IN ('O', 'C')

UNION


SELECT c.claim_number,
c.status,
c.date_received,
sp.state,
p.pay_amount
FROM claims.claim c, claims.payment p, claims.service_provider sp
WHERE c.claim_seq = p.claim_seq
AND c.service_provider_seq = sp.service_provider_seq
AND c.status IN ('O', 'C')

UNION


SELECT c.claim_number,
c.status,
c.date_received,
mea.state,
p.pay_amount
FROM claims.claim c,
claims.payment p,
claims.claim_profile cp,
gman.mem_evd_address@ardis mea
WHERE c.claim_seq = p.claim_seq
AND c.member_claim_profile_seq = cp.claim_profile_seq
AND cp.ardis_member_seq = mea.mem_evd_seq
AND c.status IN ('O', 'C');


Create table as select statement takes very less time. Please let me know how do i optimize materialized view to make sure it gets created in a lesser time.
Aug 1 '11 #1
0 1678

Sign in to post your reply or Sign up for a free account.

Similar topics

2
by: anna | last post by:
Hi all, I've been digging this problem almost 3 days now. I created some 'normal' views and then join them all together to create a materialized view but I got this error message: "Subquery...
5
by: Bernard Segonnes | last post by:
Hi I'm a bit lost with snapshot creation, so can I have an easy (but with all details : *.ora, user grants, *.sql, db links, ...) example of snapshot between : Server1 : instance : I1...
1
by: Matthieu Exbrayat | last post by:
Hie all, I am currently using materialized views within Oracle 9i. For instance I create mv1 on DB bddist2, as a snapshot of sur1 on bddist1 : SQL> create materialized view mv1 2 refresh...
3
by: M. Mehta | last post by:
It seems that you can not create a materialized view if you are using outer joins...can someone please verify this? Thanks M. Mehta Please follow my example below: created 2 tables:
1
by: Hans-Jürgen Schönig | last post by:
hello ... i am running into a couple of questions regarding materialized view logs. my goal is to take data from an oracle database and replicate it to some remote data store (maybe some other...
2
by: holdingbe | last post by:
Hi, I created a materialized view. when i refresh that views, it shows error like this materialized view not present... I created materialized logs also..... Regards...
1
by: rumasinha | last post by:
Hi, I first created a materialized view that was based on the org striped views like po_headers hence the automatic refresh was not happening. After automatic complete refresh, the materialized...
2
by: Preeti Bajaj | last post by:
I created a materialized view log on table DUMMY_TAB as shown below: create materialized view log on DUMMY_TAB with primary key including new values; Primary key for the table DUMMY_TAB is...
3
by: kewldotnet | last post by:
I have a query thats taking long time to execute. So i have created a Materialized view to refresh it every hour. But when the Materialized view is being refreshed, there is no data in the...
1
by: zeena | last post by:
I am using a materialized view to get order details from a different schema. I expect my view to get refreshed every BOD. I created my Materialized view with the following code, CREATE MATERIALIZED...
0
by: Charles Arthur | last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
1
by: nemocccc | last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
1
by: Sonnysonu | last post by:
This is the data of csv file 1 2 3 1 2 3 1 2 3 1 2 3 2 3 2 3 3 the lengths should be different i have to store the data by column-wise with in the specific length. suppose the i have to...
0
by: Hystou | last post by:
There are some requirements for setting up RAID: 1. The motherboard and BIOS support RAID configuration. 2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...
0
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However,...
0
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers,...
0
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows...
0
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.