分区表的globalindex 和 local index 跨分区查询性能比较

首先初始化数据

create table tect8_tab2  partition by hash(col1) partitions 4nologging

as

select level as id ,

      dbms_random.string('p',10) as col1 ,

      dbms_random.string('p',10) as col2 ,

       dbms_random.string('p',10) as col3

 from dual

 connectby level <= 500000 ;

create table tect8_tab3 partition by hash(col1)partitions 4 nologging

as

select * from tect8_tab2 ;

创建索引

idx_local_tect8_tab2_col1为本地索引。（不包含partition key）

idx_local_tect8_tab3_col1为全局非分区索引。（不包含partition key）

create index idx_local_tect8_tab2_col2 ontect8_tab2 (col2) local ;

create index idx_local_tect8_tab3_col2 ontect8_tab3 (col2) ;

_dexter@FAKE> select index_name ,partition_name , partition_position ,object_type , blevel , leaf_blocks

  2    from user_ind_statistics

  3   where INDEX_NAME in('IDX_LOCAL_TECT8_TAB2_COL2', 'IDX_LOCAL_TECT8_TAB3_COL2')

  4   order by 1,3 ;

INDEX_NAME                     PARTITION_NAME                 PARTITION_POSITIONOBJECT_TYPE               BLEVEL LEAF_BLOCK

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

IDX_LOCAL_TECT8_TAB2_COL2      SYS_P141                                        1PARTITION                 1        385

IDX_LOCAL_TECT8_TAB2_COL2      SYS_P142                                        2PARTITION                 1         381

IDX_LOCAL_TECT8_TAB2_COL2      SYS_P143                                        3PARTITION   &n