为什么索引用不上

tom_fu

在OE.orders表的order_total列上创建索引收集统计，B树索引应该是有rowid信息的呀，但在查询rowid时用不上这个索引，不得其解

SQL> create index order_total_idx on orders(order_total desc);
Index created.
SQL> execute dbms_stats.gather_table_stats(ownname=>'OE', tabname=>'ORDERS',cascade=>true);
PL/SQL procedure successfully completed.
SQL> select rowid from orders where order_total=123;
no rows selected

Execution Plan
----------------------------------------------------------
Plan hash value: 1610984585
------------------------------------------------------------------------------------
| Id  | Operation        | Name            | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT |                 |     1 |    18 |     1   (0)| 00:00:01 |
|*  1 |  INDEX RANGE SCAN| ORDER_TOTAL_IDX |     1 |    18 |     1   (0)| 00:00:01 |
------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - access(SYS_OP_DESCEND("ORDER_TOTAL")=HEXTORAW('3DFDE7FF') )
       filter(SYS_OP_UNDESCEND(SYS_OP_DESCEND("ORDER_TOTAL"))=123)

Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          1  consistent gets
          0  physical reads
          0  redo size
        240  bytes sent via SQL*Net to client
        377  bytes received via SQL*Net from client
          1  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          0  rows processed
SQL> select order_total from orders;
105 rows selected.

Execution Plan
----------------------------------------------------------
Plan hash value: 1275100350
----------------------------------------------------------------------------
| Id  | Operation         | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |        |   105 |   630 |     3   (0)| 00:00:01 |
|   1 |  TABLE ACCESS FULL| ORDERS |   105 |   630 |     3   (0)| 00:00:01 |
----------------------------------------------------------------------------

Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
         14  consistent gets
          0  physical reads
          0  redo size
       1587  bytes sent via SQL*Net to client
        454  bytes received via SQL*Net from client
          8  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
        105  rows processed
SQL> select rowid from orders order by order_total desc;
105 rows selected.

Execution Plan
----------------------------------------------------------
Plan hash value: 36248429
-----------------------------------------------------------------------------
| Id  | Operation          | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |        |   105 |  1890 |     4  (25)| 00:00:01 |
|   1 |  SORT ORDER BY     |        |   105 |  1890 |     4  (25)| 00:00:01 |
|   2 |   TABLE ACCESS FULL| ORDERS |   105 |  1890 |     3   (0)| 00:00:01 |
-----------------------------------------------------------------------------

Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          7  consistent gets
          0  physical reads
          0  redo size
       2509  bytes sent via SQL*Net to client
        454  bytes received via SQL*Net from client
          8  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
        105  rows processed
SQL> select /*+ rule */ rowid from orders order by order_total desc;
105 rows selected.

Execution Plan
----------------------------------------------------------
Plan hash value: 36248429
-------------------------------------
| Id  | Operation          | Name   |
-------------------------------------
|   0 | SELECT STATEMENT   |        |
|   1 |  SORT ORDER BY     |        |
|   2 |   TABLE ACCESS FULL| ORDERS |
-------------------------------------
Note
-----
   - rule based optimizer used (consider using cbo)

Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
          7  consistent gets
          0  physical reads
          0  redo size
       2509  bytes sent via SQL*Net to client
        454  bytes received via SQL*Net from client
          8  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
        105  rows processed

SQL> select /*+ index ( orders order_total_idx) */ rowid from orders order by order_total desc;
105 rows selected.

Execution Plan
----------------------------------------------------------
Plan hash value: 36248429
-----------------------------------------------------------------------------
| Id  | Operation          | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |        |   105 |  1890 |     4  (25)| 00:00:01 |
|   1 |  SORT ORDER BY     |        |   105 |  1890 |     4  (25)| 00:00:01 |
|   2 |   TABLE ACCESS FULL| ORDERS |   105 |  1890 |     3   (0)| 00:00:01 |
-----------------------------------------------------------------------------

Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
          7  consistent gets
          0  physical reads
          0  redo size
       2509  bytes sent via SQL*Net to client
        454  bytes received via SQL*Net from client
          8  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
        105  rows processed

anbob

order_total 有null 值否？

tom_fu

没有，示例数据库里的OE

anbob

when you create index desc keywords is specified , will as  function-bash index,call internal function sys_op_descend, If your query sql and  where clause is not used in that column,i think CBO never use this index.

[ 本帖最后由 anbob 于 2012-6-15 17:04 编辑 ]