Query Performance.

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 Mon Jan 22 2001 - 13:53:40 CST