oracle基于函数的索引分析

顾名思义，基于函数的索引就把要做索引的字段的值先用function计算好，然后对计算好后的值作为索引的值再创建索引，广泛的用在很多场合，比如某个字段的值查询时大小写不敏感等可用此类型索引！当我们创建基于函数的索引时，oracle究竟做了哪些处理我们值得研究一下：

1.首先我们创建测试表：

create table test_fi as select * from all_objects;

 select obj# from obj$ where name='TEST_FI';

sys@ORCL> select name, col#, type#,intcol# from col$ where obj#=123368;

NAME                                 COL#      TYPE#    INTCOL#

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

GENERATED                              12          1         12

SECONDARY                              13          1         13

OWNER                                   1          1          1

OBJECT_NAME                             2          1          2

SUBOBJECT_NAME                          3          1          3

OBJECT_ID                               4          2          4

DATA_OBJECT_ID                          5          2          5

OBJECT_TYPE                             6          1          6

CREATED                                 7         12          7

LAST_DDL_TIME                           8         12          8

TIMESTAMP                               9          1          9

STATUS                                 10          1         10

TEMPORARY                              11          1         11

已选择13行。

sys@ORCL> SELECT cols, intcols, kernelcols FROM tab$

  2  WHERE obj# =123368;

      COLS    INTCOLS KERNELCOLS

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

        13         13         13

2：我们创建基于函数的索引，看基表发生哪些变化：

sys@ORCL> create index test_fi_idx on test_fi(upper(owner));

索引已创建。

sys@ORCL> select name, col#, type#,intcol# from col$ where obj#=123368;

NAME                                 COL#      TYPE#    INTCOL#

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

GENERATED                              12          1         12

SECONDARY                              13          1         13

SYS_NC00014$                            0          1         14

OWNER                                   1          1          1

OBJECT_NAME                             2          1          2

SUBOBJECT_NAME                          3          1          3

OBJECT_ID                               4          2          4

DATA_OBJECT_ID                          5          2          5

OBJECT_TYPE                             6          1          6

CREATED                                 7         12          7

LAST_DDL_TIME                           8         12          8

TIMESTAMP                               9          1          9

STATUS                                 10          1         10

TEMPORARY                              11          1         11

已选择14行

sys@ORCL> SELECT cols, intcols, kernelcols FROM tab$

  2  WHERE obj# =123368;

      COLS    INTCOLS KERNELCOLS

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

        13         14        13

这个时候我们发现oracle在col$基表中增加了一列，自动命名，对用户来讲伪列，不可见的，实际上对oracle来讲增加此列是为了统一 管理的需要。

sys@ORCL> select index_name, column_name, descend

  2  from user_ind_columns where table_name='TEST_FI';

INDEX_NAME                     COLUMN_NAME                    DESC

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

TEST_FI_IDX                    SYS_NC00014$                   ASC