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,空列與非空列聯合,索引佔空間

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

相關文章