索引性能 实验分析

一, 分析对象 ：  表不分区，索引不分区        &           表分区，索引local分区

1, 建表：

temp1 表 ： 表不分区， 索引不分区

create table temp1 nologging  tablespace P17ETLTBS as 
select /*+ parallel(t,8) */  * from t_cc_l1_cpic_policy t where 1=1 and policy_id < 2500000 ;  

alter table temp1 parallel 1 ;

create index i_temp1_policyno on temp1(ext_policy_no) parallel 4 tablespace P17IDXTBS02 ; 

alter index i_temp1_policyno parallel 1 ;

temp2 表 ： 表按 保单号 进行 hash 分区， 索引 local 分区

create table TEMP2
(
  POLICY_ID                  NUMBER(10) not null,
  EXT_POLICY_NO              VARCHAR2(45),
  EXT_SUBCOMP_NO             VARCHAR2(30),
  SUB_COMPANY_ID             NUMBER(10),
  SUBCOMP_NAME               VARCHAR2(180),
  BEGIN_OPERATION_DATE       DATE,
  END_OPERATION_DATE         DATE,
  POLICY_ISSUE_DATE          DATE,
  POLICY_STATUS_CD           NUMBER(10),
  POLICY_STATUS              VARCHAR2(180),
  EXT_POLICY_APP_NO          VARCHAR2(45),
  EXT_SUB_CENTRAL_COMPANY_NO VARCHAR2(30),
  SUB_CENTRAL_COMPANY_ID     NUMBER(10),
  SUB_CENTRAL_COMPANY        VARCHAR2(180),
  POL_CD                     VARCHAR2(20),
  POL_PRODUCT_NAME           VARCHAR2(180),
  EXT_AGENT_CD               VARCHAR2(60),
  AGENT_NAME                 VARCHAR2(180),
  SOURCE_SYSTEM              VARCHAR2(10),
  CRT_USER_ID                NUMBER(10),
  CRT_DTTM                   DATE,
  LASTUPT_USER_ID            NUMBER(10),
  LASTUPT_DTTM               DATE,
  DATA_HIERARCHY_TYPE        VARCHAR2(10),
  ENABLE_FLG                 VARCHAR2(1),
  PRODUCT_ASS_CODE           VARCHAR2(8),
  POLICY_ASS_CODE            VARCHAR2(15),
  SALE_CHANNEL               VARCHAR2(2)
)
partition by hash (EXT_POLICY_NO)
(
  partition SYS_P1
    tablespace P17ETLTBS,
  partition SYS_P2
    tablespace P17DATATBS02,
  partition SYS_P3
    tablespace P17ETLTBS,
  partition SYS_P
    tablespace P17DATATBS02,
  partition SYS_P4
    tablespace P17ETLTBS,
  partition SYS_P5
    tablespace P17DATATBS02,
  partition SYS_P6
    tablespace P17ETLTBS,
  partition SYS_P7
    tablespace P17DATATBS02,
  partition SYS_P8
    tablespace P17ETLTBS
);

insert into temp2
select /*+ parallel(t,8) */  * from t_cc_l1_cpic_policy t where 1=1 and policy_id < 2500000 ; 
commit; 

create index i_temp2_policyno on temp2(EXT_POLICY_NO) local parallel 4 ;
alter index i_temp2_policyno parallel  1 ;

创建 统计表

create table TEMP_STAT
(
  TYPE       CHAR(1),
  SID        NUMBER,
  STATISTIC# NUMBER,
  VALUE      NUMBER
)

至此 ,temp1 , temp2 表，都有 200百万左右的数据。

2，执行分析SQL ;

declare
  v_policyno varchar2(45);
  v_polcd     varchar2(20);
  v_POL_PRODUCT_NAME VARCHAR2(100);
  v_sysdate  timestamp;
  v_random   number;
  type ty_policyno is table of t_cc_l1_cpic_policy.ext_policy_no%type;
  type ty_pol_cd is table of t_cc_l1_cpic_policy.pol_cd%type;
  v_list_policyno ty_policyno;
  v_list_pol_cd ty_pol_cd;
  v_table t_cc_l1_cpic_policy%rowtype;
begin

  v_random := trunc(2500000 * dbms_random.value);

  v_sysdate := sysdate;

  execute immediate 'truncate table temp_stat ';

  select t.ext_policy_no,t.pol_cd bulk collect
    into v_list_policyno,v_list_pol_cd
    from t_cc_l1_cpic_policy t
   where 1 = 1
     and policy_id < 2500000
     and policy_id >= v_random
     and policy_id <= v_random + 10000;

  dbms_output.put_line(sysdate - v_sysdate);

  for i in 1 .. v_list_policyno.count loop
    v_policyno := v_list_policyno(i);
    v_polcd  := v_list_pol_cd(i); 
  end loop;

  insert into temp_stat
    select '1', t.* from v$mystat t;
  commit;

  v_sysdate := sysdate;

  for i in 1 .. v_list_policyno.count loop
    select t.*
      into v_table
      from temp1 t
     where 1 = 1
       and t.ext_policy_no = v_list_policyno(i)
       and t.pol_cd = v_list_pol_cd(i)
       and t.enable_flg = '1';
    if i = v_list_policyno.count then
      dbms_output.put_line(i);
    end if;
  end loop;

  dbms_output.put_line(sysdate - v_sysdate);

  insert into temp_stat
    select '2', t.* from v$mystat t;
  commit;

  v_sysdate := sysdate;

  for i in 1 .. v_list_policyno.count loop
    select t.*
      into v_table
      from temp2 t
     where 1 = 1
       and t.ext_policy_no = v_list_policyno(i)
       and t.pol_cd = v_list_pol_cd(i)
       and t.enable_flg = '1';
    if i = v_list_policyno.count then
      dbms_output.put_line(i);
    end if;
  end loop;

  dbms_output.put_line(sysdate - v_sysdate);

  insert into temp_stat
    select '3', t.* from v$mystat t;
  commit;

end;

3, 结果分析:

a, 运行时间分析：

    以下SQL语句，返回如下信息：

+000000000 00:00:49.000000000
7934
+000000000 00:00:01.000000000
7934
+000000000 00:00:01.000000000

b, v$mystat 分析：

select a.statistic# ,c.NAME , a.dif "first", b.dif "sec" , b.dif - a.dif from ( 
select t2.value - t1.value dif,t1.statistic# from temp_stat t1 , temp_stat t2
where 1=1 and t1.statistic# = t2.statistic# 
and t1.type = '1'
and t2.type = '2'
and  t2.value <> t1.value ) a,  (
select t2.value - t1.value dif,t1.statistic# from temp_stat t1 , temp_stat t2
where 1=1 and t1.statistic# = t2.statistic# 
and t1.type = '2'
and t2.type = '3'
and  t2.value <> t1.value 
) b , v$statname c where a. statistic# = b.statistic# 
and a.statistic# = c.STATISTIC# 
order by  (  b.dif - a.dif  ) desc 

结果如下：

	STATISTIC#	NAME	DIF	DIF	B.DIF-A.DIF
1	58	physical read bytes	12910592	18481152	5570560
2	39	physical read total bytes	12910592	18481152	5570560
3	243	IMU Redo allocation size	6296	7352	1056
4	242	IMU undo allocation size	72	1000	928
5	97	free buffer inspected	1536	2304	768
6	37	physical read total IO requests	1576	2256	680
7	57	physical read IO requests	1576	2256	680
8	55	physical reads cache	1576	2256	680
9	54	physical reads	1576	2256	680
10	93	free buffer requested	1582	2256	674
11	176	undo change vector size	952	1016	64

从上得知： 表不分区读IO  12.9 MB , 分区且使用本地索引 18.4 MB .