Can we create index on views?

Answers were Sorted based on User's Feedback



Can we create index on views?..

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

Can we create index on views?..

Answer / mkjmkumar

We can Define a Synonym on View.

Is This Answer Correct ?    86 Yes 16 No

Can we create index on views?..

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

Can we create index on views?..

Answer / deepak

We can create indexes on materialised views

Is This Answer Correct ?    48 Yes 7 No

Can we create index on views?..

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

Can we create index on views?..

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

Can we create index on views?..

Answer / khushin dhulla

yes

Is This Answer Correct ?    14 Yes 8 No

Can we create index on views?..

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

Can we create index on views?..

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

Can we create index on views?..

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

Post New Answer

More Oracle General Interview Questions

Calculate difference between 2 date / times in oracle sql?

0 Answers  


What is raw datatype?

0 Answers  


How a database is related to tablespaces?

0 Answers  


What is a deadlock ? Explain .

1 Answers  


What privilege is needed for a user to create indexes in oracle?

0 Answers  






Write a syntax for update query in oracle?

0 Answers  


how will I find the first 5 highest salaried employees in each dept in oracle.

22 Answers   IBM,


How to view existing locks on the database?

0 Answers  


An automatic job running via DBMS_JOB has failedKnowing only that its failed, how do you approach troubleshooting this issue?

0 Answers  


various types of joins

9 Answers  


what is the syntax of update command?

3 Answers   Semantic Space,


how to do daily transactions with out sql* loader control file regesterd in apps?

0 Answers   Arrow Electronics,


Categories
  • Oracle General Interview Questions Oracle General (1789)
  • Oracle DBA (Database Administration) Interview Questions Oracle DBA (Database Administration) (261)
  • Oracle Call Interface (OCI) Interview Questions Oracle Call Interface (OCI) (10)
  • Oracle Architecture Interview Questions Oracle Architecture (90)
  • Oracle Security Interview Questions Oracle Security (38)
  • Oracle Forms Reports Interview Questions Oracle Forms Reports (510)
  • Oracle Data Integrator (ODI) Interview Questions Oracle Data Integrator (ODI) (120)
  • Oracle ETL Interview Questions Oracle ETL (15)
  • Oracle RAC Interview Questions Oracle RAC (93)
  • Oracle D2K Interview Questions Oracle D2K (72)
  • Oracle AllOther Interview Questions Oracle AllOther (241)