oracle index in the underlying sql query

Question

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

Answer 1

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.

Here is an example:

    create table tab1 ( item_name varchar2(15), brand varchar2(15) );
    
    insert into tab1
    select 'Name '||to_char( rownum), 'Brand '||to_char(mod(rownum,10))
    from dual
    connect by rownum < 1000000
    
    commit;
    
    exec dbms_stats.gather_table_stats( user, 'TAB1' );
    
    create bitmap index bm1 on tab1 ( item_name );
    create bitmap index bm2 on tab1 ( brand );
    
    create or replace view my_brand 
    as select item_name as item from tab1 where brand = 'Brand 1';
    
    explain plan for
    select item from my_brand where item = 'Name 1001'
    
    select * from table( dbms_xplan.display )


    --------------------------------------------------------------------------------------------
    | Id  | Operation                           | Name | Rows  | Bytes | Cost (%CPU)| Time     |
    --------------------------------------------------------------------------------------------
    |   0 | SELECT STATEMENT                    |      |     1 |    20 |     3   (0)| 00:00:01 |
    |*  1 |  TABLE ACCESS BY INDEX ROWID BATCHED| TAB1 |     1 |    20 |     3   (0)| 00:00:01 |
    |   2 |   BITMAP CONVERSION TO ROWIDS       |      |       |       |            |          |
    |*  3 |    BITMAP INDEX SINGLE VALUE        | BM1  |       |       |            |          |
    --------------------------------------------------------------------------------------------
     
    Predicate Information (identified by operation id):
    ---------------------------------------------------
     
       1 - filter("BRAND"='Brand 1')
       3 - access("ITEM_NAME"='Name 1001')

Answer 2

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