底层sql查询中的oracle索引

Question

的表

我有一个包含 2 列item_name varchar2 Brand varchar2

它们都有位图索引

假设我为特定品牌创建一个视图并重命名列 item_name ，类似于

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

我们无法在普通视图上创建索引，但是在发出该视图的基础查询时 Oracle 正在做什么？如果我们编写 select item from my_brand where item='item1' ，是否会使用 item_name 列的索引？

谢谢

Answer 1

答案将是“视情况而定”。索引访问路径当然是优化器开放的一个选项；但请记住，优化器会做出基于成本的决策。因此，本质上它将评估所有可用计划的成本并选择成本最低的计划。

这是一个例子：

    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

我们可以在普通视图上创建索引

不，你不能

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