Can we create index on views?
- 19 Answers
- 122509 Views
- CTS, Syntel, TCS, I also Faced
- E-Mail Answers
Answers were Sorted based on User's Feedback
Answer / madhoosapre
An index cannot be defined on a view. Because view is
virtual table, which consists of a subset of columns from
one more tables.
The Restrictions imposed on views are as follows:
1. A view can be created only in the current database.
2. A view can be created only if there is a SELECT
permission on its base table
3. A Trigger or an Index cannot be defined on a view.
4. A view cannot be derive its data from temporary tables.
5. The CREATE VIEW statement cannot be combined with other
SQL statements in a single batch.
| Is This Answer Correct ? | 144 Yes | 67 No |
Answer / ashok naik
Yes that is called indexed views using this we can improve
Query performance if your view contain single select
stament then we can easily establish unique clusterd index
| Is This Answer Correct ? | 63 Yes | 20 No |
Answer / md sajjad
No We cannot create index on view:
e.g
1* create view emp_view as select * from emp where
deptno=20
SQL> /
View created.
1* create index indx_emp on emp_view(empno)
SQL> /
create index indx_emp on emp_view(empno)
*
ERROR at line 1:
ORA-01702: a view is not appropriate here
| Is This Answer Correct ? | 39 Yes | 16 No |
Answer / janaka
Yes we can create Indexed view.
use
CREATE UNIQUE CLUSTERED INDEX i_IndexA
ON View_Name(field1,field2)
| Is This Answer Correct ? | 55 Yes | 35 No |
Answer / prakasam
yes. we can.
http://msdn.microsoft.com/en-us/library/aa258260
(v=sql.80).aspx - Refer the url
| Is This Answer Correct ? | 5 Yes | 2 No |
Answer / karthik
clear short article about Indexed View
http://mssqltipsandtricks.blogspot.com/2012/01/index-on-views.html
| Is This Answer Correct ? | 2 Yes | 0 No |
Answer / itishree chhotray
View should be Schema binding,before creating index on view.
We can create Unique Clustered index on view after that we
can create non clustered index but we should have at least
one unique clustered index on view.
| Is This Answer Correct ? | 0 Yes | 0 No |
What is a select query statement in oracle?
What happens to the indexes if a table is recovered?
What are the arithmetic operations?
Two triggers one is before insert and other is after insert are firing on a table.If 10 times you insert in a table.Then how many times before trigger and how many types after trigger will fire in pl/sql.
How to create a temporary table in oracle?
How to export data to a csv file?
Give the Types of modules in a form?
1. How actually index will work ? 2. Why do people prefer mostly bitmap index and btree index in datawarehouse ? 3. If I use the column in aggrigate functions like max,min,count and avg and if I have a index created on that column, will it increases the performance ?
What is meant by recursive hints in oracle?
normally database take to refresh time 2 hours. but client asked iwant to refresh with in 5 min that same database. do you have any option in BO and Oracle? explain me briefly...kavi
what is reindexing?
What is the relation of a user account and a schema?
Oracle General 
