索引优化系列十四--啥时分区索引性能反而低

drop table part_tab purge;

创新互联长期为上千客户提供的网站建设服务，团队从业经验10年，关注不同地域、不同群体，并针对不同对象提供差异化的产品和服务；打造开放共赢平台，与合作伙伴共同营造健康的互联网生态环境。为吉林企业提供专业的网站建设、做网站，吉林网站改版等技术服务。拥有10年丰富建站经验和众多成功案例,为您定制开发。

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)

        )

        ;

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

set linesize 1000

set timing on 

select * from part_tab where col2=8 ;

执行计划

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

| Id  | Operation                          | Name             | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |

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

|   0 | SELECT STATEMENT                   |                  |     1 |    39 |    13   (0)| 00:00:01 |       |       |

|   1 |  PARTITION RANGE ALL               |                  |     1 |    39 |    13   (0)| 00:00:01 |     1 |    11 |

|   2 |   TABLE ACCESS BY LOCAL INDEX ROWID| PART_TAB         |     1 |    39 |    13   (0)| 00:00:01 |     1 |    11 |

|*  3 |    INDEX RANGE SCAN                | IDX_PAR_TAB_COL2 |     1 |       |    12   (0)| 00:00:01 |     1 |    11 |

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

统计信息

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

          0  recursive calls

          0  db block gets

         24  consistent gets

          0  physical reads

          0  redo size

        539  bytes sent via SQL*Net to client

        416  bytes received via SQL*Net from client

          2  SQL*Net roundtrips to/from client

          0  sorts (memory)

          0  sorts (disk)

          1  rows processed

select * from norm_tab where col2=8 ;

执行计划

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

| Id  | Operation                   | Name             | Rows  | Bytes | Cost (%CPU)| Time     |

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

|   0 | SELECT STATEMENT            |                  |     1 |    39 |     2   (0)| 00:00:01 |

|   1 |  TABLE ACCESS BY INDEX ROWID| NORM_TAB         |     1 |    39 |     2   (0)| 00:00:01 |

|*  2 |   INDEX RANGE SCAN          | IDX_NOR_TAB_COL2 |     1 |       |     1   (0)| 00:00:01 |

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

统计信息

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

          0  recursive calls

          0  db block gets

          4  consistent gets

          0  physical reads

          0  redo size

        543  bytes sent via SQL*Net to client

        416  bytes received via SQL*Net from client

          2  SQL*Net roundtrips to/from client

          0  sorts (memory)

          0  sorts (disk)

          1  rows processed

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%';