oracle分区+分区索引+函数索引

<pre name="code" class="sql">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;