Performance Issue with View and Query

[surajitmitra78]

Hi,

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.

[John Spencer]

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:

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.

John

[surajitmitra78]

Hi John,

look at the attachment and comment pls.

[John Spencer]

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.

John

[DaPi]

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.)

[surajitmitra78]

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 :confused: .
Can anyone put some light on this.
I am adding the sql queries along with this.

[surajitmitra78]

Can anyone comment on this and come with some hints or probable solution

[surajitmitra78]

Hey! DaPI do you have anything to solve this problem.

[tamilselvan]

Trace the sql and post the tkprof output for both queries.

Tamil