Re: Tuning Ville

Freeman Robert - IL wrote:
>
> 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!
>

Robert,

  I don't think that replacing a view with an in-line view brings, by itself, much benefit. The benefit as I see it is removing references to stuff which, ultimately, one doesn't need, and factorizing - in the ideal world, every row containing data of interest for the query should be fetched only once ... If you can push at least part of your screening conditions into the in-line view, then you will get some benefit, otherwise ...
  Regarding the first draft significantly worse than the current, unsatisfactory version, I am glad to see it doesn't only happen to me :-).
  I fully agree with your 'delayering' but are you sure that you targeting the good layers? My own instinct would be to keep, of all views, the MView - because it is a real table on which you can create adhoc indexes.

-- 
Regards,

Stephane Faroult
Oriole Software
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Stephane Faroult
  INET: sfaroult_at_oriole.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).