oracle 分区条件加函数,oracle分区+分区索引+函数索引

create table test_zhi4

(

id number,

name varchar2(4000),

time date,

timestr varchar2(2)

)

partition by range(timestr)

(

partition t_range_1 values less than (01),

partition t_range_2 values less than (02),

partition t_range_3 values less than (03),

partition t_range_4 values less than (maxvalue)

);

--global分区索引

create index idx_parti_range_timestr on test_zhi4(timestr)

global partition by range(timestr)

(

partition t_range_1 values less than (01),

partition t_range_2 values less than (02),

partition t_range_3 values less than (03),

partition t_range_4 values less than (maxvalue)

);

--local分区索引

create index idx_parti_range_timestr on test_zhi4(timestr) local;

--日期函数索引

create index idx_test_zhi4_fun_time on test_zhi4(to_char(time ,'yyyy-mm-dd')); --字符串索引

create index idx_test_zhi4_func_time on test_zhi4(trunc(time)); --trunc索引

--插入数据

insert into test_zhi4 (id, name, time, timestr) values (1, 'q1', to_date('01-04-2015', 'dd-mm-yyyy'), '01');

--查询 观察是否走了索引

select * from test_zhi4 partition (t_range_1) where timestr = '01'; --走了索引分区

select * from test_zhi4 partition (t_range_1) where trunc(time) = date'2015-04-01'; --走了trunc索引

select * from test_zhi4 partition (t_range_1) where to_char(time ,'yyyy-mm-dd') = '2015-04-01'; --走了字符串索引

--并行无日志建索引

create index aml2010.idx_amlm_ars_coult_inf_his on aml2010.amlm_ars_coult_inf_his(cust_seq) parallel 4 nologging;

--打开日志

alter index aml2010.idx_amlm_ars_coult_inf_his noparallel logging;