Re: View in V6 vs. V7

Richard A. Wark writes:

>My organization has a need to explain why a report (relying solely
>on a view) takes 1 min to run on ORACLE7 yet pokes along for 4 mins
>on the older V6 database. Is there really that much that has
>changed in Oracle's handling of views between the two versions?
 

>I seem to recall an article in an old "Oracle" Magazine which
>discussed many issues in migrating from V6 to V7, having looked
>through the stack, I can't seem to find the issue.
 

>Our difference in performance in table only queries is not nearly as
>pronounced as where views are involved. All data is consistent
>between the two databases (row counts and all..)
 

>Thanks for your help,
 

>Stories of similar experiences or technical data would be
>very helpful. Please e-mail (or post if you wish) any e-mails
>will be summary posted at a later date.
 

>Richard A. Wark
>Computer Sciences Corp. warkr_at_vanadium.brooks.af.mil
>Brooks AFB, San Antonio TEXAS

Oracle 6 elaborates a view as a temporary table and then applies where clauses.
Later release of 6 beginning with 6.0.33 (I think) incorporate the where clauses into the text of the view before it is elaborated. This can have a very large performance impact. Note that the new optimization method can be surpressed with an init.ora parameter. David Roth

Carty Mailloux Consulting                                      All
opinions expressed are my own.
UNIX Client/Server System Integrators                     Please that them
for what they are 
(508) 392-1300x20                                                 worth.