本地索引--测试 - 墨天轮文档

本地索引--测试

布衣

519

4页

0次

2020-05-13

5墨值下载

本地索引-全表扫描

-- 测试：

create table test (id number,

t_data date，

tname varchar(20),

t1_date date)

partition by RANGE (t_data)

(

partition PART2015_10 values less than (TIMESTAMP' 2015-11-01 00:00:00') tablespace pay_dat,

partition PART2015_11 values less than (TIMESTAMP' 2015-12-01 00:00:00') tablespace pay_dat,

partition PART_max values less than (maxvalue) tablespace pay_dat

);

insert into test values(1,to_date('2015-10-01','yyyy-mm-dd'),'a',to_date('2015-10-01','yyyy-mm-dd'));

insert into test values(2,to_date('2015-10-02','yyyy-mm-dd'),'a',to_date('2015-10-02','yyyy-mm-dd'));

insert into test values(3,to_date('2015-10-03','yyyy-mm-dd'),'a',to_date('2015-10-03','yyyy-mm-dd'));

insert into test values(4,to_date('2015-10-04','yyyy-mm-dd'),'a',to_date('2015-10-04','yyyy-mm-dd'));

insert into test values(5,to_date('2015-11-01','yyyy-mm-dd'),'b',to_date('2015-11-01','yyyy-mm-dd'));

insert into test values(6,to_date('2015-11-02','yyyy-mm-dd'),'b',to_date('2015-11-02','yyyy-mm-dd'));

insert into test values(7,to_date('2015-11-03','yyyy-mm-dd'),'b',to_date('2015-11-03','yyyy-mm-dd'));

insert into test values(8,to_date('2015-11-04','yyyy-mm-dd'),'b',to_date('2015-11-04','yyyy-mm-dd'));

insert into test values(9,to_date('2015-12-01','yyyy-mm-dd'),'c',to_date('2015-12-01','yyyy-mm-dd'));

insert into test values(10,to_date('2015-12-02','yyyy-mm-dd'),'c',to_date('2015-12-02','yyyy-mm-dd'));

insert into test values(11,to_date('2015-12-03','yyyy-mm-dd'),'c',to_date('2015-12-03','yyyy-mm-dd'));

insert into test values(12,to_date('2015-12-04','yyyy-mm-dd'),'c',to_date('2015-12-04','yyyy-mm-dd'));

insert into test values(13,to_date('2015-12-01','yyyy-mm-dd'),'c',to_date('2015-12-01','yyyy-mm-dd'));

insert into test values(14,to_date('2015-12-05','yyyy-mm-dd'),'c',to_date('2015-12-05','yyyy-mm-dd'));

SQL> select count(*) from test partition(PART2015_10);

COUNT(*)

----------

SQL> select count(*) from test partition(PART2015_11);

COUNT(*)

----------

SQL> select count(*) from test partition(PART_max);

COUNT(*)

----------

-- 因为t_data是分区键，所以这样就创建了一个有前缀的本地索引。

create index inx_t_date on test(t_data) local tablespace pay_ind_dat;

-- 因为id不是分区键，所以这样就创建了一个无前缀的本地索引。

create index inx_id on test(id) local tablespace pay_ind_dat;

-- 从user_part_indexes视图也可以证明刚才创建的索引，一个是有前缀的，一个是无前缀的

select u_x.TABLE_NAME, u_i.index_name,u_x.locality,u_x.ALIGNMENT, u_i.status, u_i.partition_name

from user_ind_partitions u_i

left join user_part_indexes u_x

on u_i.index_name = u_x.INDEX_NAME

where u_x.TABLE_NAME = 'TEST';

TABLE_NAME INDEX_NAME LOCALI ALIGNMENT STATUS PARTITION_NAME

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

TEST INX_T_DATE LOCAL PREFIXED <--前缀 USABLE PART2015_10

TEST INX_T_DATE LOCAL PREFIXED <--前缀 USABLE PART2015_11

TEST INX_T_DATE LOCAL PREFIXED <--前缀 USABLE PART_MAX

TEST INX_ID LOCAL NON_PREFIXED <--无前缀 USABLE PART2015_10

TEST INX_ID LOCAL NON_PREFIXED <--无前缀 USABLE PART2015_11

TEST INX_ID LOCAL NON_PREFIXED <--无前缀 USABLE PART_MAX

-- 执行计划：

-- 查询非前缀

select * From test where id=1 and tname='a';

SQL> -- 查询前缀：

SQL> select * from test where t_data=to_date('2015-10-01','yyyy-mm-dd') and tname='a';

-- 查询非分区字段：

select * from test where t1_date=to_date('2015-10-01','yyyy-mm-dd') and tname='a';

-- 本地索引失效：

alter table test move partition PART_max;