Re: Query Performance.

In article <3a6c8c68.1130582516_at_news.alt.net>,   SPAMBLOCK.Maxwell_Smart_at_ThePentagon.com.SPAMBLOCK (Brian Tkatch) wrote:
> 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
>

Without the text of the queries (how you joined the view and other tables), and information about the tables (keys, indices, etc.) it is really difficult to say. Adding a new table may provide the optimizer a different and more efficient search path thru the data. Or the condition used to join the table reduces the result set. Or even you are on a slow machine and the timings alone don't mean anything.

Since Sybrand brought his name up in another thread, I'll mention:

Guy Harrison has a book on Oracle Performance Tuning.

It's very helpful and informative. I don't just recommend it, I use it.

HTH,
ed

--
Ed Prochak
Magic Interface, Ltd.
ORACLE Development, conversions, training and support
440-498-3700 magic_at_magicinterface.com <<<WE MOVED!


Sent via Deja.com
http://www.deja.com/