Re: Performance and Views

From original note >>
The query ran in one sixth the time, or about one hour instead of six+. The DBA never looked at any output from "Explain Plan", but claims the performance improvement came from creating the view because Oracle had "already created the view". I am of the opinion that because the subquery was moved to the view, and the view used in the "from" clause, that Oracle only did the work once and that previously it could have been executing the subquery repeatedly. But if I'm wrong I would like to be corrected. <<

To my knowledge, in 7.2 a view is the same as in 7.1, nothing more than a stored query. When you execute a query against a view Oracle first tries to merge the view into your query and execute the resulting SQL. If the optimizer can not do this then it will try to merge the query into the view ( I am not sure of the difference, but it is different ) and use the resulting SQL. Finally if necessary Oracle will execute the view first and then execute the query against the results of the view.

I think you may find it worth your effort to run the explain plans on the query and see how Oracle changed its access path. Also find out if your DBA updated the statistics on the underlying table anywhere during this process.

Mark Powell -- The only advise that counts is the advise that you follow so follow your own advise Received on Sat Feb 22 1997 - 00:00:00 CET