RE: Tuning Ville

Stephane,

Thanks for your thoughts..... :-)

>> I don't think that replacing a view with an in-line view brings, by itself, much
>> benefit.

I guess I see it differently. A MView by it's very nature is administratively more expensive than a regular view (I'm not talking about performance here). You have to worry if it's fast refreshable, to make it fast refreshable you sometimes have to go through some silly SQL programming stuff, you have to worry if the refresh is actually occurring, you have additional space considerations, etc. So, there are some good reasons not to use MViews in my mind's eye. Mind you I think there are good times and places to use them as well.

>> 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.

In this case I think the MView is additional overhead. It does some basic denormilization of the underlying table, but only on a row by row basis. So, in essence, if I have 17 rows in the underlying table, my MView has 17 rows in it as well. In my mind, this may not be the most efficient and architecturally
sound way of doing what we want.

Still, I agree that the potential benefits of indexing the MView may be helpful. As I said in my post, I still have some more analysis to do, but I'm fairly certain that the MView is less helpful in this case.

Thanks for your thoughts, I look forward to any more that you (or anyone else) might have.

RF

Robert G. Freeman
Technical Management Consultant
TUSC - The Oracle Experts www.tusc.com
904.708.5076 Cell (It's everywhere that I am!) Author of several books you can find on Amazon.com!

-----Original Message-----
Sent: Sunday, February 02, 2003 5:54 AM
To: Multiple recipients of list ORACLE-L

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).
-- 
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).