Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Materialized Views for Query Rewrite

Posted on 2003-10-30
7
Medium Priority
?
1,294 Views
Last Modified: 2008-03-04
Oracle 9i Rel 2 Ver. 9.2.0.4 Windows 2000 Advanced Server

I am administering a datawarehouse and have been asked to improve the performance of reports being created and run by Cognos.  I want to create materialized views and use query rewrite.  Can somebody explain the steps to plan and implement this?  

I have a couple of sample reports.  I'm trying to decide whether or not to do this top down or bottom up from the report.  I'm thinking it would be better to implement a consistent vision across the whole star schema rather than try to do this one view and one report at a time.

If I don't do top down, the number of tables in the first report is 12.  I'm thinking I have to do this in small chunks joining three or four tables in a view.  If I do that will all of them have to be full refresh?

Please answer the following questions:
Is it best to look at the data model and implement mviews based on the key relationships between fact and dim? or, look at the (35) reports and try and create mviews that support various reports?

A few tables in the view, or many?

We don't have many space limitations.  The db is only about 15 g in size and I have 300 g available.  The db is only really used from 8-6 Monday through Friday so I can refresh etc outside those hours.

If I'm not asking the right questions or approaching this correctly please suggest another direction.  
0
Comment
Question by:DonFreeman
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
  • 2
  • +1
7 Comments
 
LVL 23

Assisted Solution

by:seazodiac
seazodiac earned 375 total points
ID: 9653651
Please answer the following questions:
Is it best to look at the data model and implement mviews based on the key relationships between fact and dim? or, look at the (35) reports and try and create mviews that support various reports?

---->Of course, take a look at the reports first. What tables need to be joined to present the information to complete the report. I am sure that 35 reports must have focused on a common set of tables. From there, you can take a look at the table schema from PK-FK point of view(or FACT -DIMENSION point of view), they are sometimes equivalent in DATA warehouse env.

A few tables in the view, or many?

From Oracle9i, you can have as many table in the mw as you want.
But since the refresh of Materialized view is really a tricky issue, you should balance the number of tables in the MW, refresh frequency, refresh data load, and time, etc. You can get a best estimate by studying your reports and DW schema carefully.
0
 
LVL 5

Expert Comment

by:DrJekyll
ID: 9653669
It may not be your database at all...How do things perform outside of Cognos.  What are you using to build your cubes?  Can you explain your environment?  How many fact tables, dimensions?
0
 
LVL 1

Author Comment

by:DonFreeman
ID: 9657549
The cubes are created and ETL is being done using Informatica power mart.  It's a communicable disease reporting database with three fact tables, investigation_fact and disease_rpt_fact are the two main ones, and followup_fact.  There are 21 dimension tables with about another 10 or so helper tables. Examples of dimension tables are jurisdiction, location, person, disease, time, etc.

This is all kind of anticipatory, our user base is going to go from 50 to 400 in the next couple of months as we go into full production. The database was created on Oracle Standard and we just got an Enterprise license last week so we don't have any of the Enterprise Edition data warehouse features enabled.  We felt the most valuable things to implement and the order we would implement would be mviews, bitmapped indexes, partitioning.... We have about 10 more weeks, essentially to redesign the application, before we reach peak load. We're thinking this isn't going to hold up when we get 400 users on board.  
0
Fill in the form and get your FREE NFR key NOW!

Veeam® is happy to provide a FREE NFR server license to certified engineers, trainers, and bloggers.  It allows for the non‑production use of Veeam Agent for Microsoft Windows. This license is valid for five workstations and two servers.

 
LVL 23

Expert Comment

by:seazodiac
ID: 9658631
Yes, by upgrading your oracle database to EE version , alone is a big help.

just thinking about the capability added, Materialized views, Bitmapped Indexes, partitioning ..they all are essential ingredients for the success of a databwarehouse database.

You go right ahead.
0
 
LVL 2

Expert Comment

by:Kong
ID: 9665654
15GB datawarehouse?! :-)

I agree, upgrade to EE ASAP, start integrating partitioning, bitmap (join) indexes - you may need to turn them off for ETL as they'll cause all sorts of locking issues if you have them on during your load. Is parallel query available in standard edition?

We tested Cognos during the POC, there's a query rewrite feature which you should use. From memory, Cognos sucks all your data into cubes so the likelyhood of queries hitting the actual database is very small - that's why they're hardware heavy...
0
 
LVL 1

Author Comment

by:DonFreeman
ID: 9670605
Cognos has a drill-through capability.  We build six cubes from the database which currently satisfies most users.  When we are fully up and running I think we will have a lot more drill through activity hitting the database.  I can't see a use for bitmap join indexes and materialized views at the same time on the same tables. It seems kind of redundant.  If I create a materialized view using columns from four tables that are normally joined what's the point of creating a bitmap join-index on the same tables?  Am I missing something here?
0
 
LVL 2

Accepted Solution

by:
Kong earned 375 total points
ID: 9675308
Bitmap join indexes (BJI) take considerably less space than MVs.

With BJIs you don't have to worry about query_rewrite_integrity & state of MVs, and/or MV logs.

BJIs improves complete rebuilds on multijoin MVs :-)

Using one and/or the other is dependent on requirements, don't treat these two features as an exlusive OR.

Hope that helps
K
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

How to Create User-Defined Aggregates in Oracle Before we begin creating these things, what are user-defined aggregates?  They are a feature introduced in Oracle 9i that allows a developer to create his or her own functions like "SUM", "AVG", and…
Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
This video shows setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines
This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.

704 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question