三十二、索引导致分区表变慢的解析

我会带着你远行 2022-04-06 15:56 272阅读 0赞

### 1、索引导致分区表变慢 ###

分区表上建索引，相当于每个分区建了一个小索引。

drop table part_tab purge;
    --创建分区表，id列分区
    create table part_tab (id int,col2 int,col3 int)
            partition by range (id)
            (
            partition p1 values less than (10000),
            partition p2 values less than (20000),
            partition p3 values less than (30000),
            partition p4 values less than (40000),
            partition p5 values less than (50000),
            partition p6 values less than (60000),
            partition p7 values less than (70000),
            partition p8 values less than (80000),
            partition p9 values less than (90000),
            partition p10 values less than (100000),
            partition p11 values less than (maxvalue)
            )
            ;
    --插入11万数据
    insert into part_tab select rownum,rownum+1,rownum+2 from dual connect by rownum <=110000;
    commit;
    --创建索引
    create  index idx_par_tab_col2 on part_tab(col2) local;
    create  index idx_par_tab_col3 on part_tab(col3) ;
    
    drop table norm_tab purge;
    create table norm_tab  (id int,col2 int,col3 int);
    insert into norm_tab select rownum,rownum+1,rownum+2 from dual connect by rownum <=110000;
    commit;
    create  index idx_nor_tab_col2 on norm_tab(col2) ;
    create  index idx_nor_tab_col3 on norm_tab(col3) ;
    
    set autotrace traceonly statistics
    set linesize 1000
    set timing on 
    
    --分区表如果有索引，查询条件中没有分区条件，索引将拖慢查询
    select * from part_tab where col2=8 ;
    select * from norm_tab where col2=8 ;
    
    --查询条件中带了分区条件，索引将提升查询速度
    select * from part_tab where col2=8 and id=2;
    select * from norm_tab where col2=8 and id=2;
    
    --查看索引高度等信息
    select index_name,
              blevel,
              leaf_blocks,
              num_rows,
              distinct_keys,
              clustering_factor
         from user_ind_statistics
        where table_name in( 'NORM_TAB');
        
    select index_name,
              blevel,
              leaf_blocks,
              num_rows,
              distinct_keys,
              clustering_factor FROM USER_IND_PARTITIONS where index_name like 'IDX_PAR_TAB%';
    
    
    select * from part_tab where col3=8 ;

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L25ld2JpZV85MDc0ODY4NTI_size_16_color_FFFFFF_t_70]

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L25ld2JpZV85MDc0ODY4NTI_size_16_color_FFFFFF_t_70 1]

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L25ld2JpZV85MDc0ODY4NTI_size_16_color_FFFFFF_t_70_pic_center][]

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L25ld2JpZV85MDc0ODY4NTI_size_16_color_FFFFFF_t_70]: /images/20220406/2ca7868906c14e6580d83f20d2c8b785.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L25ld2JpZV85MDc0ODY4NTI_size_16_color_FFFFFF_t_70 1]: /images/20220406/a57a407b27d7441da0cc6d5728d11bd8.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L25ld2JpZV85MDc0ODY4NTI_size_16_color_FFFFFF_t_70_pic_center]: /images/20220406/6015724637f840e2b9d6cdf681079a0a.png