Hi folks....
Ok, I've got this database that I'm tuning.
Anyway, currently the architecture of the views goes several layers deep:
1. Principle table
2. Mview doing some denormalization
3. View on the Mview doing some further flattening of the table and some
calculations.
4. Yet another view, doing yet more calculations.
5. The final view, that joins view 4 to other views.
I'm trying to remove some of these layers by rewriting view 4 so that I can
remove the MView (view 2) and also view 3.
The new code uses inline views something like this:
create view view_4 col1, col2, col3, etc..
from (select col1, col2, col3, etc.. from
(select col1, col2, col3 from table_1) )
where enter_join_conditions_here;
One last bit of info for you to consider is that in the last in-line view,
we do a table self join, aggregating some dollar amounts by date. This two
table join is going through 2 full table scans of the self joined table
which
I need to look into. That may well solve some of my performance problems.
The current architecture (with all views) returns my test queries in about 3
seconds, the new view I am working with returns results in about 23 seconds.
I'd obviously like to make it faster. I've tried NO_MERGE and ORDERED hints
in both the main view and the inline views, and I still need to look at some
of the indexing but I'd just like to solicit some additional ideas of things
to consider or try from you all.
Cheers (something I think I heard Connor say to me or someone else at one
time and now it just keeps coming out!),
Robert
Robert G. Freeman
Author of several books you can find on Amazon.com!
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Freeman Robert - IL
INET: [EMAIL PROTECTED]
Fat City Network Services -- 858-538-5051 http://www.fatcity.com
San Diego, California -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
