ORA-01702
Tried to create an index, but it failed with ORA-01702.
SQL> create index emp_salary on happy_employees (salary);
create index emp_salary on happy_employees (salary)
*
ERROR at line 1:
ORA-01702: a view is not appropriate here
ORA-01702 means that the object you want to create an index on is essentially a view which contains no concrete data, so it's not allowable to create any index on it.
OK, is it really a view? Let's check its object type.
SQL> select object_type from all_objects where owner = 'HR' and object_name = 'HAPPY_EMPLOYEES';
OBJECT_TYPE
-----------------------
VIEW
Solution
To solve the problem, we have 2 options.
Base Table
That is to say, we should create the index on the base table, not the view. Now the question is, what is the base table of the view?
Check Base Table of a View
We query TEXT_VC of ALL_VIEWS for sure.
SQL> select text_vc from all_views where owner = 'HR' and view_name = 'HAPPY_EMPLOYEES';
TEXT_VC
--------------------------------------------------------------------------------
select "EMPLOYEE_ID","FIRST_NAME","LAST_NAME","EMAIL","PHONE_NUMBER","HIRE_DATE"
,"JOB_ID","SALARY","COMMISSION_PCT","MANAGER_ID","DEPARTMENT_ID" from employees
where salary > 10000
Then we create the index on the base table.
SQL> create index emp_salary on employees (salary);
Index created.
Don't worry about the performance of querying on the view, view merging is able to use the index you have just created on the base table whenever it's necessary.
Materialized View
Yes, a normal view is not appropriate here, but a solid materialized view would be suitable and fitting this case.
Materialized view is a special type of view which contains concrete data and is allowable to create index on it. We may consider to use a materialized view instead of a normal view.
To replace a normal view with a materialized view, we take the following steps.
Drop the Normal View
SQL> drop view happy_employees;
View dropped.
Create a Materialized View
SQL> create materialized view happy_employees as select * from employees where salary > 10000;
Materialized view created.
Let's check its object type again.
SQL> select object_type from all_objects where owner = 'HR' and object_name = 'HAPPY_EMPLOYEES';
OBJECT_TYPE
-----------------------
TABLE
MATERIALIZED VIEW
It's a materialized view now.
Create an Index on the Materialized View
SQL> create index emp_salary on happy_employees (salary);
Index created.
We fixed ORA-01702.