查看组成一个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');
INDEX_NAME TABLE_NAME COLUMN_NAME COLUMN_POSITION COLUMN_LENGTH CHAR_LENGTH DESCEND
------------------------------ ------------------------------ ------------------------------ --------------- ------------- ----------- --------
INDTEST_F2_CHAR INDTEST F2_CHAR 1 20 20 ASC
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');
INDEX_NAME TABLE_NAME COLUMN_EXPRESSION COLUMN_POSITION
------------------------------ ------------------------------ -------------------------------------------------------------------------------- ---------------
FUNC_INDTEST INDTEST UPPER("F2_CHAR") 1
SQL> select * from user_ind_columns where index_name=upper('func_indtest');
INDEX_NAME TABLE_NAME COLUMN_NAME COLUMN_POSITION COLUMN_LENGTH CHAR_LENGTH DESCEND
------------------------------ ------------------------------ ------------------------------ --------------- ------------- ----------- --------
FUNC_INDTEST INDTEST SYS_NC00007$ 1 20 20 ASC
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');
INDEX_NAME TABLE_NAME COLUMN_NAME COLUMN_POSITION COLUMN_LENGTH CHAR_LENGTH DESCEND
------------------------------ ------------------------------ ------------------------------ --------------- ------------- ----------- --------
INDTEST_F4_NUM INDTEST SYS_NC00006$ 1 34 0 DESC
SQL> select * from user_ind_expressions where index_name=upper('indtest_f4_num');
INDEX_NAME TABLE_NAME COLUMN_EXPRESSION COLUMN_POSITION
------------------------------ ------------------------------ -------------------------------------------------------------------------------- ---------------
INDTEST_F4_NUM INDTEST "F4_NUM" 1
把上面的三个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
