oracle index in the underlying sql query-CodePudding

I have a table with 2 columns

item_name varchar2 brand varchar2

both of them have bitmap index

let's say I create a view for a specific brand and rename the column item_name ,something like that

create view my_brand as select item_name as item from table x where brand='x'

We cannot create an index on a normal view but what is Oracle doing when issuing the underlying query of that view? Is the index of the item_name column being used if we write select item from my_brand where item='item1'?

thanks

CodePudding user response：

We can create an index on a normal view

No, you can't

SQL> create table as_idx_view (item_name varchar2(10), brand varchar2(10));

Table created.

SQL> create view as_view as select item_name item from as_idx_view where brand = 'X';

View created.

SQL> create index as_idx_view_idx1 on as_view (item);
create index as_idx_view_idx1 on as_view (item)
                                 *
ERROR at line 1:
ORA-01702: a view is not appropriate here

CodePudding user response：

The answer will be “it depends”. The index access path is certainly an option open to the optimizer; but remember that the optimizer makes a cost based decision. So essentially it will evaluate the cost of all the available plans and choose the one with the lowest cost.

It’s easy enough for you to check for yourself. Just run an explain plan:

Explain plan for
select item from my_brand where item='item1';

Select * from table( dbms_xplan.display );