oracle 建分区索引_Oracle分区表创建本地索引和局部索引的性能

1、演示环境：

SQL> select * from v$version;

BANNER

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

Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bi

PL/SQL Release 10.2.0.4.0 - Production

CORE10.2.0.4.0Production

TNS for 64-bit Windows: Version 10.2.0.4.0 - Production

NLSRTL Version 10.2.0.4.0 - Production

2、测试数据创建：

表创建SQL如下：

点击(此处)折叠或打开

create table T65_F_S_PGZ10023_M

(

STATISTIC_DT DATE,

INDICKEY VARCHAR2(20),

OBJKEY VARCHAR2(64),

INDICVAL1 NUMBER(30,2),

INDICVAL2 NUMBER(30,2),

INDICVAL3 NUMBER(30,2),

INDICVAL4 NUMBER(30,2),

INDICVAL5 NUMBER(30,2)

)

partition by range (STATISTIC_DT)

(

partition PT_20111231 values less than (TO_DATE(\'2012-01-01 00:00:00\', \'YYYY-MM-DD HH24:MI:SS\')) tablespace tsdat01,

partition PT_20120101 values less than (TO_DATE(\'2012-01-02 00:00:00\', \'YYYY-MM-DD HH24:MI:SS\')) tablespace tsdat01,

partition PT_20120102 values less than (TO_DATE(\'2012-01-03 00:00:00\', \'YYYY-MM-DD HH24:MI:SS\')) tablespace tsdat01,

partition PT_20120103 values less than (TO_DATE(\'2012-01-04 00:00:00\', \'YYYY-MM-DD HH24:MI:SS\')) tablespace tsdat01,

partition PT_20120104 values less than (TO_DATE(\'2012-01-05 00:00:00\', \'YYYY-MM-DD HH24:MI:SS\')) tablespace tsdat01,

partition PT_20120105 values less than (TO_DATE(\'2012-01-06 00:00:00\', \'YYYY-MM-DD HH24:MI:SS\')) tablespace tsdat01,

partition PT_20120106 values less than (TO_DATE(\'2012-01-07 00:00:00\', \'YYYY-MM-DD HH24:MI:SS\')) tablespace tsdat01,

partition PT_20120107 values less than (TO_DATE(\'2012-01-08 00:00:00\', \'YYYY-MM-DD HH24:MI:SS\')) tablespace tsdat01,

partition PT_20120108 values less than (TO_DATE(\'2012-01-09 00:00:00\', \'YYYY-MM-DD HH24:MI:SS\')) tablespace tsdat01,

partition PT_20120109 values less than (TO_DATE(\'2012-01-10 00:00:00\', \'YYYY-MM-DD HH24:MI:SS\')) tablespace tsdat01,

partition PT_20120100 values less than (TO_DATE(\'2012-01-11 00:00:00\', \'YYYY-MM-DD HH24:MI:SS\')) tablespace tsdat01,

partition PT_20120111 values less than (TO_DATE(\'2012-01-12 00:00:00\', \'YYYY-MM-DD HH24:MI:SS\')) tablespace tsdat01

);

CREATE INDEX PI_T65_F_S_PGZ10023_M ON T65_F_S_PGZ10023_M (OBJKEY) TABLESPACE TSDAT01 PARALLEL LOCAL;

测试数据生成SQL如下：

SQL>insert into T65_F_S_PGZ10023_M(INDICKEY,STATISTIC_DT,OBJKEY,INDICVAL1)

select 'PGZ10023' as INDICKEY, to_date('2012-01-08','yyyy-mm-dd') as STATISTIC_DT ,'81'||lpad(to_char(rownum),7,'0') as OBJKEY  ,trunc(dbms_random.value(1,rownum)) as INDICVAL1 from dual connect by  rownum <100000;

修改日期可以得到2012-01-01 到2012-01-08这8天的测试数据

测试数据按天汇总的记录数

SQL>  select statistic_dt,count(*) from t65_f_s_pgz10023_m group by statistic_dt;

STATISTIC_DT   COUNT(*)

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

2012-01-01        99999

2012-01-02        99999

2012-01-03        99999</