1,表的结构

　　create table t_more_col(col1 int,col2 int,col3 int,col4 int,col5 int,col6 int,col7 int,col8 int,col9 int, col10 int);

2，插表

declare

type typ_tab_col1 is table of pls_integer index by pls_integer;

typ_tab_col1_1 typ_tab_col1;

begin

for i in 1..1000000 loop

typ_tab_col1_1(i):=i;

end loop;

forall i in 1..1000000

insert into t_more_col values(typ_tab_col1_1(i),null,null,null,null,null,null,null,null,null);

commit;

end;

3,对非空列col1建索引

create index idx_t_more_col on t_more_col(col1);

对空null列col2建索引

create index idx_t_more_col_col2 on t_more_col(col2);

对空null列与非空列建索引

create index idx_t_more_col_col1_col2 on t_more_col(col1,col2);

4,查索引

select　index_name,blevel,leaf_blocks,distinct_keys,

avg_leaf_blocks_per_key,avg_data_blocks_per_key,

clustering_factor,num_rows,sample_size,to_char(last_analyzed,'yyyymmdd hh24:mi:ss')

from user_indexes u where u.INDEX_NAME='IDX_T_MORE_COL';

5,小结:

1,非空列，索引记录数同于表的记录数

2,空列,索上不占用空间，不进行存储

3,空列与非空列联合,索引占空间

ITPub博客

表为多列为null的表之索引示例