Oracle-L: Tuning Ville

From: Freeman Robert - IL <FREEMANR_at_tusc.com>
Date: Sun, 02 Feb 2003 00:43:36 -0800
Message-ID: <F001.0054178D.20030202004336@fatcity.com>

Hi folks....

Ok, I've got this database that I'm tuning.

Anyway, currently the architecture of the views goes several layers deep:

Principle table
Mview doing some denormalization
View on the Mview doing some further flattening of the table and some calculations.
Yet another view, doing yet more calculations.
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: FREEMANR_at_tusc.com

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: ListGuru_at_fatcity.com (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). Received on Sun Feb 02 2003 - 02:43:36 CST