查看组成一个Index的column有哪些

下面是创建一个表，并在上面建立一些index的SQL.我们会新建一个用户，然后再那个schema下运行下面的SQL.

create table indtest (
f1_num number(10) not null,
f2_char varchar2(20) not null,
f3_numnull number(10) null,
f4_num number(10),
f5_char varchar2(20)
);
alter table indtest add constraint PK_T_indtest primary key (f1_num);
create index indtest_f2_char on indtest (f2_char ASC);
create index indtest_f23_char on indtest (f2_char, f3_numnull ASC);
create index indtest_f4_num on indtest (f4_num DESC);
create index func_indtest on indtest(upper(f2_char));
create index func_indtest_component on indtest(upper(f2_char), f3_numnull ASC);

1. 准备工作

SQL> select * from v$version;

BANNER
-----------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
PL/SQL Release 11.2.0.1.0 - Production
CORE 11.2.0.1.0 Production
TNS for 32-bit Windows: Version 11.2.0.1.0 - Production
NLSRTL Version 11.2.0.1.0 - Production

SQL> create user ind_test account unlock identified by test default tablespace users quota 500M on users;

用户已创建。
SQL> grant resource , connect to ind_test;

授权成功。

SQL> conn ind_test/test
已连接。

SQL> create table indtest (
2 f1_num number(10) not null,
3 f2_char varchar2(20) not null,
4 f3_numnull number(10) null,
5 f4_num number(10),
6 f5_char varchar2(20)
7 );

表已创建。

SQL> alter table indtest add constraint PK_T_indtest primary key (f1_num);

表已更改。

SQL> create index indtest_f2_char on indtest (f2_char ASC);

索引已创建。

SQL> create index indtest_f23_char on indtest (f2_char, f3_numnull ASC);

索引已创建。

SQL> create index indtest_f4_num on indtest (f4_num DESC);

索引已创建。

SQL> create index func_indtest on indtest(upper(f2_char));

索引已创建。

SQL> create index func_indtest_component on indtest(upper(f2_char), f3_numnull ASC);

索引已创建。

查看Index的组成column

　1. 使用DBMS_METADATA的下面两个函数.

　　dbms_metadata.get_ddl
　　dbms_metadata.get_depentent_ddl

SQL> select to_char(dbms_metadata.get_DDL('INDEX','FUNC_INDTEST_COMPONENT','IND_TEST')) "TEXT" from dual;

TEXT
------------------------------------------------------------------------------------------------------------------------
CREATE INDEX "IND_TEST"."FUNC_INDTEST_COMPONENT" ON "IND_TEST"."INDTEST" (UPPER("F2_CHAR"), "F3_NUMNULL")
PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS NOCOMPRESS LOGGING
TABLESPACE "USERS"

/*不输出与STORAGE有关的参数*/

SQL> EXECUTE DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM, 'STORAGE', false);

PL/SQL 过程已成功完成。

/*不输出与SEGMENT有关的参数*/

SQL> EXECUTE DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM, 'SEGMENT_ATTRIBUTES', false);

PL/SQL 过程已成功完成。

SQL> select to_char(dbms_metadata.get_DDL('INDEX','FUNC_INDTEST_COMPONENT','IND_TEST')) "TEXT" from dual;

TEXT
-----------------------------------------------------------------------------------------------------------------
CREATE INDEX "IND_TEST"."FUNC_INDTEST_COMPONENT" ON "IND_TEST"."INDTEST" (UPPER("F2_CHAR"), "F3_NUMNULL")

查看以下的V$试图.

dba/all/user_indexes
dba/all/user_ind_columns
dba/all/user_ind_expressions

在user_ind_columns里面，一个index由几个column组成就对应几行，只是有些index是function index，因此某些列的名字是系统生成的。

此时到user_ind_expressions里面可以看到对应的expression，它们是通过index_name 和column_position一起关联起来的.

SQL> col index_name format a30;
SQL> col table_name format a30;
SQL> col column_name format a30;
SQL> select * from user_ind_columns where index_name=upper('indtest_f2_char');

SQL> select * from user_ind_columns where index_name=upper('func_indtest_component');

INDEX_NAME                     TABLE_NAME                     COLUMN_NAME                    COLUMN_POSITION COLUMN_LENGTH CHAR_LENGTH DESCEND
------------------------------ ------------------------------ ------------------------------ --------------- ------------- ----------- --------
FUNC_INDTEST_COMPONENT         INDTEST                        SYS_NC00007$                              1               20          20 ASC
FUNC_INDTEST_COMPONENT         INDTEST                        F3_NUMNULL                                2               22           0 ASC

SQL> select * from user_ind_expressions where index_name=upper('func_indtest_component');

INDEX_NAME TABLE_NAME COLUMN_EXPRESSION COLUMN_POSITION
------------------------------ ------------------------------ -------------------------------------------------------------------------------- ---------------
FUNC_INDTEST_COMPONENT INDTEST UPPER("F2_CHAR") 1

SQL> select * from user_ind_expressions where index_name=upper('func_indtest');

SQL> select * from user_ind_columns where index_name=upper('func_indtest');

SQL> select INDEX_NAME,INDEX_TYPE,TABLE_OWNER,TABLE_NAME,TABLE_TYPE,UNIQUENESS,COMPRESSION from user_indexes order by INDEX_TYPE;

INDEX_NAME                     INDEX_TYPE                 TABLE_OWNER TABLE_NAME   TABLE_TYPE UNIQUENESS        COMPRESSION
---------------------------    -------------------------- ----------- ------------ ----------- ----------------- -----------
INDTEST_F4_NUM                 FUNCTION-BASED NORMAL       IND_TEST   INDTEST       TABLE      NONUNIQUE          DISABLED
FUNC_INDTEST_COMPONENT         FUNCTION-BASED NORMAL       IND_TEST   INDTEST       TABLE      NONUNIQUE          DISABLED
FUNC_INDTEST                   FUNCTION-BASED NORMAL       IND_TEST   INDTEST       TABLE      NONUNIQUE          DISABLED
PK_T_INDTEST                   NORMAL                      IND_TEST   INDTEST       TABLE      UNIQUE             DISABLED
INDTEST_F23_CHAR               NORMAL                      IND_TEST   INDTEST       TABLE      NONUNIQUE          DISABLED
INDTEST_F2_CHAR                NORMAL                      IND_TEST   INDTEST       TABLE      NONUNIQUE          DISABLED
已选择6行。

这里的结果显示 INDTEST_F4_NUM 也是一个function index, 但是我们创建的时候并没有指定函数，而是指定了DESC 顺序.

下面的查询结果页显示出INDTEST_F4_NUM 确实被当成是function index处理的,只是expression就是自己. 我推测这是Oracle对DESC index的特殊处理.

SQL> select * from user_ind_columns where index_name=upper('indtest_f4_num');

SQL> select * from user_ind_expressions where index_name=upper('indtest_f4_num');

把上面的三个V$视图一起联合起来，便有下面的SQL.当colum_name是系统生成的时候，表明column_expression就是一个function column.

SQL> select
2 ind_columns.index_name,
3 ind_expression.column_expression,
4 ind_columns.column_name
5 from user_indexes ind
6 left outer join user_ind_columns ind_columns on(ind.index_name=ind_columns.index_name)
7 left outer join user_ind_expressions ind_expression on
8 (ind_expression.index_name = ind_columns.index_name and ind_expression.column_position = ind_columns.column_position)
9 where ind.index_name=upper('FUNC_INDTEST_COMPONENT');

INDEX_NAME                     COLUMN_EXPRESSION                                                        COLUMN_NAME
------------------------------ -------------------------------------------------------------------------------- ------------------------------
FUNC_INDTEST_COMPONENT         UPPER("F2_CHAR")                                              SYS_NC00007$
FUNC_INDTEST_COMPONENT                                                                                             F3_NUMNULL

posted @ 2014-02-07 20:38 princessd8251 阅读(603) 评论(0) 收藏举报

刷新页面返回顶部