I have a sql statement which retreives rows of around 13000+ records in 3 secs.
I created a view out of the same sql statement and when i access the view it retreives the same no of records in 4 min.
Can any one put come light on as to what would be the problem.
A view is just a stored sql statement, it will run att he same speed as the sql statement it is based on.
Post a cut and paste of the SQL query alone showing it running in three seconds, the create view statement, then a query against the view showing 4 minutes. My bet would be that you are not comparing apples to apples. The only fair comparision is along the lines of:
Code:
SELECT * FROM t;
CREATE VIEW v AS SELECT * FROM t;
SELECT * FROM v;
If you are doing anything different, then the problem lies in how you are accessing the view.
Interesting. Try removing the ALL ROWS hint from NOT EXISTS query. It is superflous anyway, and I seem to recall that there are occasionally issues with hints in views.
If that doesn't fix things, post the explain plans and statistics for the plain statement and for the view.
I'd start by looking at the timings in SQL*Plus. (TOAD? will have to wrap its own code around yours for display purposes - this might be causing a problem.)
"The power of instruction is seldom of much efficacy except in those happy dispositions where it is almost superfluous" - Gibbon, quoted by R.P.Feynman
Execution of sql script from either TOAD or SQL*Plus didnt made a huge difference.
I debugged the view and i found if i remove the ROWNUM clause then the query fetches records exactly as desired. Now, this is major problem for me now .
Can anyone put some light on this.
I am adding the sql queries along with this.
Last edited by surajitmitra78; 07-21-2005 at 09:08 AM.
Performance Issue with View and Query
Reply With Quote
.