oracle函数索引decode,decode函数索引的问题

SQL> create index sex_ind on test_sex (decode(sex,'m','man','w','women'));

索引已创建。

已用时间:  00: 00: 00.06

SQL> select /*+ index(test_sex sex_ind) */ decode(sex,'m','man','w','women') fro

m test_sex where decode(sex,'m','man','w','women') is not null;

DECOD

-----

man

man

women

women

women

已用时间:  00: 00: 00.01

执行计划

----------------------------------------------------------

Plan hash value: 3811407575

----------------------------------------------------------------------------

| Id  | Operation        | Name    | Rows  | Bytes | Cost (%CPU)| Time     |

----------------------------------------------------------------------------

|   0 | SELECT STATEMENT |         |     5 |    20 |     1   (0)| 00:00:01 |

|*  1 |  INDEX FULL SCAN | SEX_IND |     5 |    20 |     1   (0)| 00:00:01 |

----------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

1 - filter(DECODE("SEX",'m','man','w','women') IS NOT NULL)

Note

-----

- dynamic sampling used for this statement

统计信息

----------------------------------------------------------

62  recursive calls

0  db block gets

23  consistent gets

0  physical reads

0  redo size

491  bytes sent via SQL*Net to client

385  bytes received via SQL*Net from client

2  SQL*Net roundtrips to/from client

0  sorts (memory)

0  sorts (disk)

5  rows processed

SQL> select /*+ index(test_sex sex_ind) */ decode(sex,'m','man','w','women') fro

m test_sex where sex is not null;

DECOD

-----

man

women

man

women

women

已用时间:  00: 00: 00.00

执行计划

----------------------------------------------------------

Plan hash value: 1152696938

------------------------------------------------------------------------------

| Id  | Operation         | Name     | Rows  | Bytes | Cost (%CPU)| Time     |

------------------------------------------------------------------------------

|   0 | SELECT STATEMENT  |          |     5 |    20 |     3   (0)| 00:00:01 |

|   1 |  TABLE ACCESS FULL| TEST_SEX |     5 |    20 |     3   (0)| 00:00:01 |

------------------------------------------------------------------------------

Note

-----

- dynamic sampling used for this statement

统计信息

----------------------------------------------------------

19  recursive calls

0  db block gets

20  consistent gets

0  physical reads

0  redo size

501  bytes sent via SQL*Net to client

385  bytes received via SQL*Net from client

2  SQL*Net roundtrips to/from client

0  sorts (memory)

0  sorts (disk)

5  rows processed

SQL> desc test_sex

名称                                      是否为空? 类型

----------------------------------------- -------- ----------------------------

ID                                                 NUMBER

SEX                                       NOT NULL VARCHAR2(4)

NAME                                               VARCHAR2(10)

SQL>