Home » SQL & PL/SQL » SQL & PL/SQL » Can we create index on view?
Can we create index on view? [message #197331] Wed, 11 October 2006 00:12 Go to next message
Messages: 78
Registered: March 2006
Location: Dubai
Can we create index on view? And is really view make improves the query? If yes then how? One more thing when we create a view is data stored in a view is order by or indexed already if i cannot mention order by clause at the time of view creation (here we suppose that the base table have 2-3 indexes already). Actually today i create one view but still not find any much difference in performance improved?
Re: Can we create index on view? [message #197340 is a reply to message #197331] Wed, 11 October 2006 00:33 Go to previous message
Messages: 7062
Registered: December 2001
Senior Member
Can you create an index on a view?
A view is a stored select statement, not stored data. So, if you want an index you should make it on the base table(s). Note that you can create an index on a materialized view (snapshot).

Is a view sorted by default?
If you don't provide an order by in a view's select statement, the data is not sorted. If Oracle performs an index scan, it might be sorted but there is no guarantee.

Does a view improve performance?
As a view is a stored select statement, it does not guarantee to increase performance. If the SQL is poorly written, the view will not perform very well.

Previous Topic: Tuning SQL query (merged 2 cross-posts)
Next Topic: want to get number of sundays in a month
Goto Forum:

Current Time: Tue Aug 22 00:32:49 CDT 2017

Total time taken to generate the page: 0.04674 seconds