Friday, April 06, 2012

ORA-01702

This was tested on an Oracle 11 database. A view is like a stored select statement. It contains no data and its contents need to be recalculated each time you query it. You therefore cannot add an index to a view:
 
SQL> create table table_list
  2  as select * from dba_tables
  3  /
 
Table created.
 
SQL> create view view1 as
  2  select owner, count(*) number_found
  3  from table_list
  4  group by owner
  5  /
 
View created.
 
SQL> create index view_index
  2  on view1(owner)
  3  /
on view1(owner)
   *
ERROR at line 2:
ORA-01702: a view is not appropriate here
 
SQL>

No comments: