Re: Query Performance.

I've seen similar results. My guess is that the optimizer can do a better job the
more information it has. So performance is better optimizing the combined query
than it is by optimizing the view + optimizing the query using the view. In my case,
the view and query had overlap. So some tables were being hit twice unnecessarily.
I saw a 20x-40x gain. I'd recommend Guy Harrison's book Oracle SQL High-Performance
Tuning (although Ed. 2 is coming soon, I understand - Hope the tools work for 8i) for insights.

• Gordon

"Brian Tkatch" <SPAMBLOCK.Maxwell_Smart_at_ThePentagon.com.SPAMBLOCK> wrote in message news:3a6c8c68.1130582516_at_news.alt.net...
> I do not the enough about query optimization, but hopefully you can
> help me understand this.
>
> I have a query that is using six tables, including one twice (in the
> count of six), and a view. The view is four tables outer joined with
> NVL() providing a default value. There is one common table between the
> view and the query.
>
> I am timing with 'SET TIMING ON' and then encapsulating each query in
> a 'SELECT COUNT(*) FROM (<query>);'.
>
> The query took approximatly 13 seconds to run. I then took the view
> and mixed it into the query and it took approximately 5 seconds to
> run. I then realized my mistake of leaving the view and its needed
> where clause in the query (unneeded now because I mixed the view in
> directly) and the query took over 9 seconds to run.
>
> I am curious to know why adding extra things to the query (I.E. The
> view) shaved off 3 to 4 seconds.
>
> Note, that when I added the view directly into the table, I obviously
> changed the name of the returned column, to reflect the newly included
> table and not the view.
>
> Brian
Received on Tue Jan 30 2001 - 08:01:37 CST