SYSTEM@oracle10g>create table test_view as select * from dba_objects;
表已创建。
SYSTEM@oracle10g>ed
已写入 file afiedt.buf
  1  create view v_test_view as select substr(object_name,0,3) object_name,
  2* object_id,object_type from test_view
SYSTEM@oracle10g>/
视图已创建。
SYSTEM@oracle10g>create index i_test_view  on test_view(object_name);
索引已创建。
SYSTEM@oracle10g>set autot trace statistics
SYSTEM@oracle10g>select * from v_test_view where object_name='AAA';
未选定行

统计信息
----------------------------------------------------------
          0  recursive calls
          0  db block gets
        693  consistent gets
        135  physical reads
          0  redo size
        401  bytes sent via SQL*Net to client
        389  bytes received via SQL*Net from client
          1  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          0  rows processed

通过视图走全表,逻辑读也比较大

SYSTEM@oracle10g>select substr(object_name,0,3) object_name,object_id,object_type from test_view
  2   where object_name='AAA';
未选定行

统计信息
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          2  consistent gets
          0  physical reads
          0  redo size
        401  bytes sent via SQL*Net to client
        389  bytes received via SQL*Net from client
          1  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          0  rows processed

不走视图走索引,逻辑读很小

到这里不知道大家看出问题没有,前面我特意不给出谓词信息

SYSTEM@oracle10g>select substr(object_name,0,3) object_name,object_id,object_type from test_view
  2  where object_name='AAA';
执行计划
----------------------------------------------------------
Plan hash value: 2475448246
--------------------------------------------------------------------------------
-----------
| Id  | Operation                   | Name        | Rows  | Bytes | Cost (%CPU)|
Time     |
--------------------------------------------------------------------------------
-----------
|   0 | SELECT STATEMENT            |             |     1 |    90 |     1   (0)|
00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| TEST_VIEW   |     1 |    90 |     1   (0)|
00:00:01 |
|*  2 |   INDEX RANGE SCAN          | I_TEST_VIEW |     1 |       |     1   (0)|
00:00:01 |
--------------------------------------------------------------------------------
-----------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("OBJECT_NAME"='AAA')
Note
-----
   - dynamic sampling used for this statement

SYSTEM@oracle10g>select * from v_test_view where object_name='AAA';
执行计划
----------------------------------------------------------
Plan hash value: 3554956191
-------------------------------------------------------------------------------
| Id  | Operation         | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |           |     8 |   720 |   156   (3)| 00:00:02 |
|*  1 |  TABLE ACCESS FULL| TEST_VIEW |     8 |   720 |   156   (3)| 00:00:02 |
-------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter(SUBSTR("OBJECT_NAME",0,3)='AAA')
Note
-----
   - dynamic sampling used for this statement

这地方冒出个substr(object_name,0,3),看看前面的视图定义可以看出,视图里取的就是
substr(object_name,0,3)但是别名为object_name,因此使用视图参数的时候,oracle使用
了视图里的substr(object_name,0,3)作为查询,而索引是建立在object_name上的,因此
用不了索引,更严重的后果是,这样导致语句的含义和原本想要的结果不一致了,因此建议
在写视图的时候,如果这个字段上包含函数或者是别的计算表达式,不要用表的字段名做为
别名,修改下视图的定义

SYSTEM@oracle10g>create or replace view v_test_view as select substr(object_name,0,3) object_name1,
  2  object_name, object_id,object_type from test_view;
视图已创建。
SYSTEM@oracle10g>select * from v_test_view where object_name='AAA';
执行计划
----------------------------------------------------------
Plan hash value: 2475448246
--------------------------------------------------------------------------------
-----------
| Id  | Operation                   | Name        | Rows  | Bytes | Cost (%CPU)|
Time     |
--------------------------------------------------------------------------------
-----------
|   0 | SELECT STATEMENT            |             |     1 |    90 |     1   (0)|
00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| TEST_VIEW   |     1 |    90 |     1   (0)|
00:00:01 |
|*  2 |   INDEX RANGE SCAN          | I_TEST_VIEW |     1 |       |     1   (0)|
00:00:01 |
--------------------------------------------------------------------------------
-----------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("OBJECT_NAME"='AAA')
Note
-----
   - dynamic sampling used for this statement