java分区索引_表分区与索引分区

最新推荐文章于 2023-10-10 21:31:05 发布

weixin_39984098

最新推荐文章于 2023-10-10 21:31:05 发布

阅读量209

点赞数

文章标签： java分区索引

本文链接：https://blog.csdn.net/weixin_39984098/article/details/114902486

版权

一：前言

二：分区技术概述

三：创建表分区

1：范围分区

1 ----创建一个商品零售表，然后为该表按照销售日期所在季度创建4个分区

3 SYSTEM@orcl> ho clear

6 SYSTEM@orcl> select tablespace_name from dba_data_files;

8 TABLESPACE_NAME

9 ------------------------------

10 USERS

11 UNDOTBS1

12 SYSAUX

13 SYSTEM

14 EXAMPLE

15 TBS_TEST1

16 TBS_TEST_3

17 TBS_TEST4

18 TBS_TEST5

19 TBS_BIG_1

21 10 rows selected.

23 SYSTEM@orcl> conn scott/scott;

24 Connected.

25 SCOTT@orcl> create table ware_retail_part --创建一个描述商品零售的数据表

26 2 (

27 3 id integer primary key,--销售编号

28 4 retail_date date,--销售日期

29 5 ware_name varchar2(50)--商品名称

30 6 )

31 7 partition by range(retail_date)

32 8 (

33 9 --2011年第一个季度为part_01分区

34 10 partition par_01 values less than(to_date('2011-04-01','yyyy-mm-dd')) tablespace TBS_TEST1,

35 11 --2011年第二个季度为part_02分区

36 12 partition par_02 values less than(to_date('2011-07-01','yyyy-mm-dd')) tablespace TBS_TEST_3,

37 13 --2011年第三个季度为part_03分区

38 14 partition par_03 values less than(to_date('2011-10-01','yyyy-mm-dd')) tablespace TBS_TEST4,

39 15 --2011年第四个季度为part_04分区

40 16 partition par_04 values less than(to_date('2012-01-01','yyyy-mm-dd')) tablespace TBS_TEST1

41 17 );

44 Table created.

46 SCOTT@orcl> SCOTT@orcl>

1 create table ware_retail_part2 --创建一个描述商品零售的数据表

2 (

3 id integer primary key,--销售编号

4 retail_date date,--销售日期

5 ware_name varchar2(50)--商品名称

6 )

7 partition by range(id,retail_date)--按照销售序号和销售日期分区

8 (

9 --第一个分区part_01

10 partition par_01 values less than(10000,to_date('2011-12-01','yyyy-mm-dd')) tablespace TBSP_1,

11 --第一个分区part_02

12 partition par_02 values less than(20000,to_date('2012-12-01','yyyy-mm-dd')) tablespace TBSP_1,

13 --第一个分区part_03

14 partition par_03 values less than(maxvalue,maxvalue) tablespace TBSP_2

15 );

2: 散列分区

1 SCOTT@orcl>

2 SCOTT@orcl> conn sys/oracle as sysdba;

3 Connected.

4 SYS@orcl> select tablespace_name from dba_data_files;

6 TABLESPACE_NAME

7 ------------------------------

8 USERS

9 UNDOTBS1

10 SYSAUX

11 SYSTEM

12 EXAMPLE

13 TBS_TEST1

14 TBS_TEST_3

15 TBS_TEST4

16 TBS_TEST5

17 TBS_BIG_1

19 10 rows selected.

21 SYS@orcl> conn scott/scott;

22 Connected.

23 SCOTT@orcl> create table ware_retail_part3 --创建一个描述商品零售的数据表

24 2 (

25 3 id integer primary key,--销售编号

26 4 retail_date date,--销售日期

27 5 ware_name varchar2(50)--商品名称

28 6 )

29 7 partition by hash(id)

30 8 (

31 9 partition par_01 tablespace TBS_TEST1,

32 10 partition par_02 tablespace TBS_TEST4

33 11 );

35 Table created.

37 SCOTT@orcl>

2 SCOTT@orcl> insert into ware_retail_part3 values(99,to_date('2018-03-22','yyyy-mm-dd'),'adfadsfas');

4 1 row created.

6 SCOTT@orcl> select * from ware_retail_part3 partition(par_02);

8 ID RETAIL_DA WARE_NAME

9 ---------- --------- --------------------------------------------------

10 99 22-MAR-18 adfadsfas

12 SCOTT@orcl> select * from ware_retail_part3 partition(par_01);

14 no rows selected

16 SCOTT@orcl>

1 SCOTT@orcl> conn sys/oracle as sysdba;

2 Connected.

3 SYS@orcl> select tablespace_name from dba_data_files;

5 TABLESPACE_NAME

6 ------------------------------

7 USERS

8 UNDOTBS1

9 SYSAUX

10 SYSTEM

11 EXAMPLE

12 TBS_TEST1

13 TBS_TEST_3

14 TBS_TEST4

15 TBS_TEST5

16 TBS_BIG_1

18 10 rows selected.

20 SYS@orcl> conn scott/scott;

21 Connected.

22 SCOTT@orcl> create table person(

23 2 id number primary key,

24 3 name varchar2(20),

25 4 sex varchar2(2)

26 5 )partition by hash(id)

27 6 partitions 2

28 7 store in(TBS_TEST1,TBS_TEST_3);

31 Table created.

33 SCOTT@orcl> SCOTT@orcl>

1 SCOTT@orcl> conn sys/oracle as sysdba;

2 Connected.

3 SYS@orcl> select tablespace_name from dba_data_files;

5 TABLESPACE_NAME

6 ------------------------------

7 USERS

8 UNDOTBS1

9 SYSAUX

10 SYSTEM

11 EXAMPLE

12 TBS_TEST1

13 TBS_TEST_3

14 TBS_TEST4

15 TBS_TEST5

16 TBS_BIG_1

18 10 rows selected.

20 SYS@orcl> conn scott/scott;

21 Connected.

22 SCOTT@orcl> create table goods_1 ( --//定义包含商品信息表

23 2 id number, --编号

24 3 goodname varchar2(50) --名称

25 4 )storage(initial 2048k) ---定义表分区的初始化空间大小为 2048 kb

26 5 partition by hash(id) --创建id列作为分区键的hash表分区

27 6 ( partition par1 tablespace TBS_TEST1,

28 7 partition par2 tablespace TBS_TEST_3

29 8 );

31 Table created.

33 SCOTT@orcl>

3：列表分区

1 create table clients

2 (

3 id integer primary key,

4 name varchar2(50),

5 province varchar2(20)

6 )

7 partition by list(province)

8 (

9 partition shandong values('山东省'),

10 partition guangdong values('广东省'),

11 partition yunnan values('云南省')

12 );

4：组合分区

2 SCOTT@orcl> conn sys/oracle as sysdba;

3 Connected.

4 SYS@orcl> select tablespace_name from dba_data_files;

6 TABLESPACE_NAME

7 ------------------------------

8 USERS

9 UNDOTBS1

10 SYSAUX

11 SYSTEM

12 EXAMPLE

13 TBS_TEST1

14 TBS_TEST_3

15 TBS_TEST4

16 TBS_TEST5

17 TBS_BIG_1

19 10 rows selected.

21 SYS@orcl> conn scott/scott;

22 Connected.

24 SCOTT@orcl> create table person2 --创建以一个描述个人信息的表

25 2 (

26 3 pid number primary key, --个人的编号

27 4 name varchar2(20), --姓名

28 5 sex varchar2(2) --性别

29 6 )

30 7 partition by range(pid )--以id作为分区键创建范围分区

31 8 subpartition by hash(name)--以name列作为分区键创建hash子分区

32 9 subpartitions 2 store in(TBS_TEST_3,TBS_TEST4)--hash子分区公有两个，分别存储在两个不同的命名空间中

33 10 (

34 11 partition par1 values less than(5000),--范围分区，id小于5000

35 12 partition par2 values less than(10000),--范围分区，id小于10000

36 13 partition par3 values less than(maxvalue)--范围分区，id不小于10000

37 14 );

39 Table created.

41 SCOTT@orcl>

5：interval 分区

2 SCOTT@orcl> conn sys/oracle as sysdba;

3 Connected.

4 SYS@orcl> select tablespace_name from dba_data_files;

6 TABLESPACE_NAME

7 ------------------------------

8 USERS

9 UNDOTBS1

10 SYSAUX

11 SYSTEM

12 EXAMPLE

13 TBS_TEST1

14 TBS_TEST_3

15 TBS_TEST4

16 TBS_TEST5

17 TBS_BIG_1

19 10 rows selected.

21 SYS@orcl> conn scott/scott;

22 Connected.

23 SCOTT@orcl> create table saleRecord(

24 2 id number primary key, --编号

25 3 goodsname varchar2(50),--商品名称

26 4 saledate date,--销售日期

27 5 quantity number--销售量

28 6 )

29 7 partition by range(saledate)

30 8 interval (numtoyminterval(1,'year'))

31 9 (

32 10 --设置分区键值日期小于2012-01-01

33 11 partition par_fist values less than (to_date('2012-01-01','yyyy-mm-dd'))

34 12 );

36 Table created.

38 SCOTT@orcl>

四：表分区策略

五：管理表分区

1：添加表分区

1 alter table clients add partition hebei values('河北省') storage(inital 10k next 20kl) tablespace tbsp_1 nologging;

2: 合并分区

、

1:合并散列分区

1 SCOTT@orcl> alter table person coalesce partition;

3 Table altered.

5 SCOTT@orcl>

2: 合并复合分区

2 SCOTT@orcl> alter table person2 modify partition par3 coalesce subpartition;

4 Table altered.

6 SCOTT@orcl>

3：删除分区

1：删除一个表分区

1 SCOTT@orcl> alter table WARE_RETAIL_PART drop partition par_04;

3 Table altered.

5 SCOTT@orcl>

2：删除有数据和全局索引的表分区

1 SCOTT@orcl> alter table WARE_RETAIL_PART drop partition par_04;

3 Table altered.

5 SCOTT@orcl> select t.*,i.index_type from user_ind_columns t,user_indexes i where t.index_name = i.index_name and t.table_name = i.table_name and t.table_name ='WARE_RETAIL_PART';

7 INDEX_NAME TABLE_NAME

8 ------------------------------ ------------------------------

9 COLUMN_NAME

10 --------------------------------------------------------------------------------

11 COLUMN_POSITION COLUMN_LENGTH CHAR_LENGTH DESC INDEX_TYPE

12 --------------- ------------- ----------- ---- ---------------------------

13 SYS_C0012488 WARE_RETAIL_PART

14 ID

15 1 22 0 ASC NORMAL

18 SCOTT@orcl> select t.*,i.index_type from user_ind_columns t,user_indexes i where t.index_name = i.index_name and t.table_name = i.table_name and t.index_name ='WARE_index';

20 no rows selected

22 SCOTT@orcl> alter index SYS_C0012488 rebuild;

24 Index altered.

26 SCOTT@orcl>

3：使用 delete 和　alter table… drop partition 语句

1 SCOTT@orcl> delete from WARE_RETAIL_PART where retail_date >=to_date('201110-01','yyyy-mm-dd') ;

3 0 rows deleted.

5 SCOTT@orcl> alater table ware_retail_part drop partition par_04;

4：删除具有完整性约束的分区

4: 并入分区

1 SCOTT@orcl> conn sys/oracle as sysdba;

2 Connected.

3 SYS@orcl> select tablespace_name from dba_data_files;

5 TABLESPACE_NAME

6 ------------------------------

7 USERS

8 UNDOTBS1

9 SYSAUX

10 SYSTEM

11 EXAMPLE

12 TBS_TEST1

13 TBS_TEST_3

14 TBS_TEST4

15 TBS_TEST5

16 TBS_BIG_1

18 10 rows selected.

20 SYS@orcl> conn scott/scott;

21 Connected.

22 SCOTT@orcl> select table_name from user_tables;

24 TABLE_NAME

25 ------------------------------

26 DEPT

27 EMP

28 BONUS

29 SALGRADE

30 DEPT_LOG

31 GOODS

32 DDL_OPER_LOG

33 TB_TEST

34 STUDENTS

35 STUDENTS_DEMO

36 STUDENTS_DEMO1

38 TABLE_NAME

39 ------------------------------

40 STUDENTS_3

41 STUDENTS_4

42 BOOKS

43 BOOKS_1

44 MEMBERS

45 WARE_RETAIL_PART

46 WARE_RETAIL_PART3

47 PERSON

48 GOODS_1

49 PERSON2

50 SALERECORD

52 TABLE_NAME

53 ------------------------------

54 STUDENTS_7

55 STUDENTS_5

57 24 rows selected.

59 SCOTT@orcl> --创建表和分区

60 SCOTT@orcl> create table sales--创建一个销售记录表

61 2 (

62 3 id number primary key,--记录编号

63 4 goodsname varchar2(10),--商品名

64 5 saledate date--销售日期

65 6 )

66 7 partition by range(saledate)--按照日期分区

67 8 (

68 9 --第一季度数据

69 10 partition part_sea1 values less than(to_date('2011-04-01','yyyy-mm-dd')) tablespace TBS_TEST1 ,

70 11 --第二季度数据

71 12 partition part_sea2 values less than(to_date('2011-07-01','yyyy-mm-dd')) tablespace TBS_TEST_3 ,

72 13 --第三季度数据

73 14 partition part_sea3 values less than(to_date('2011-10-01','yyyy-mm-dd')) tablespace TBS_TEST1 ,

74 15 --第四季度数据

75 16 partition part_sea4 values less than(to_date('2012-01-01','yyyy-mm-dd')) tablespace TBS_TEST_3

76 17 );

79 Table created.

81 SCOTT@orcl> SCOTT@orcl>

2 SCOTT@orcl> SCOTT@orcl> create index index_3_4 on sales(saledate)

3 2 local(

4 3 partition part_seal tablespace TBS_TEST1,

5 4 partition part_sea2 tablespace TBS_TEST_3,

6 5 partition part_sea3 tablespace TBS_TEST1,

7 6 partition part_sea4 tablespace TBS_TEST_3

8 7 );

10 Index created.

12 SCOTT@orcl>

2 SCOTT@orcl> --并入分区

3 SCOTT@orcl> alter table sales merge partitions part_sea3,part_sea4 into partition part_sea4;

6 Table altered.

8 SCOTT@orcl> SCOTT@orcl>

1 SCOTT@orcl> SCOTT@orcl> --重建局部索引

2 SCOTT@orcl> alter table sales modify partition part_sea4 rebuild unusable local indexes;

4 Table altered.

6 SCOTT@orcl>

六：创建索引分区

1：索引分区概述

2：本地索引分区

1:准备好需要的表空间

1 SYS@orcl> create tablespace ts_1 datafile '/u01/app/oracle/oradata/orcl/ts1.dbf' size 10m extent management local autoallocate ;

3 Tablespace created.

5 SYS@orcl> create tablespace ts_2 datafile '/u01/app/oracle/oradata/orcl/ts2.dbf' size 10m extent management local autoallocate ;

7 Tablespace created.

9 SYS@orcl> create tablespace ts_3 datafile '/u01/app/oracle/oradata/orcl/ts3.dbf' size 10m extent management local autoallocate ;

11 Tablespace created.

13 SYS@orcl>

2：创建一个储存学生成绩的分区表studentgrade,该表共有3个分区，分别位于表空间ts_1/ts_2/ts_3；

2 SYS@orcl> conn scott/scott

3 Connected.

4 SCOTT@orcl> create table studentgrade(

5 2 id number primary key,

6 3 name varchar2(10),

7 4 subject varchar2(10),

8 5 grade number

9 6 )

10 7 partition by range(grade)

11 8 (

12 9 partition par_nopass values less than (60) tablespace ts_1,

13 10 partition par_pass values less than(70) tablespace ts_2,

14 11 partition par_good values less than(maxvalue) tablespace ts_3

15 12 );

17 Table created.

19 SCOTT@orcl>

3：根据表分区创建本地索引分区，与表分区一样。索引分区也是3个分区(p1,p2,p3)

1 SCOTT@orcl> create index grade_index on studentgrade(grade) local

2 2 (

3 3 partition p1 tablespace ts_1,

4 4 partition p2 tablespace ts_2,

5 5 partition p3 tablespace ts_3

6 6 );

8 Index created.

4: 通过 dba_ind_partitions 视图来进行查看索引分区信息

1 SCOTT@orcl> conn sys/oracle as sysdba;

2 Connected.

4 SYS@orcl> desc dba_ind_partitions;

5 Name Null? Type

6 ----------------------------------------- -------- ----------------------------

7 INDEX_OWNER VARCHAR2(30)

8 INDEX_NAME VARCHAR2(30)

9 COMPOSITE VARCHAR2(3)

10 PARTITION_NAME VARCHAR2(30)

11 SUBPARTITION_COUNT NUMBER

12 HIGH_VALUE LONG

13 HIGH_VALUE_LENGTH NUMBER

14 PARTITION_POSITION NUMBER

15 STATUS VARCHAR2(8)

16 TABLESPACE_NAME VARCHAR2(30)

17 PCT_FREE NUMBER

18 INI_TRANS NUMBER

19 MAX_TRANS NUMBER

20 INITIAL_EXTENT NUMBER

21 NEXT_EXTENT NUMBER

22 MIN_EXTENT NUMBER

23 MAX_EXTENT NUMBER

24 MAX_SIZE NUMBER

25 PCT_INCREASE NUMBER

26 FREELISTS NUMBER

27 FREELIST_GROUPS NUMBER

28 LOGGING VARCHAR2(7)

29 COMPRESSION VARCHAR2(8)

30 BLEVEL NUMBER

31 LEAF_BLOCKS NUMBER

32 DISTINCT_KEYS NUMBER

33 AVG_LEAF_BLOCKS_PER_KEY NUMBER

34 AVG_DATA_BLOCKS_PER_KEY NUMBER

35 CLUSTERING_FACTOR NUMBER

36 NUM_ROWS NUMBER

37 SAMPLE_SIZE NUMBER

38 LAST_ANALYZED DATE

39 BUFFER_POOL VARCHAR2(7)

40 FLASH_CACHE VARCHAR2(7)

41 CELL_FLASH_CACHE VARCHAR2(7)

42 USER_STATS VARCHAR2(3)

43 PCT_DIRECT_ACCESS NUMBER

44 GLOBAL_STATS VARCHAR2(3)

45 DOMIDX_OPSTATUS VARCHAR2(6)

46 PARAMETERS VARCHAR2(1000)

47 INTERVAL VARCHAR2(3)

48 SEGMENT_CREATED VARCHAR2(3)

2 SYS@orcl> select index_owner,index_name ,partition_name,tablespace_name from dba_ind_partitions where INDEX_NAME='GRADE_INDEX';

4 INDEX_OWNER INDEX_NAME

5 ------------------------------ ------------------------------

6 PARTITION_NAME TABLESPACE_NAME

7 ------------------------------ ------------------------------

8 SCOTT GRADE_INDEX

9 P1 TS_1

11 SCOTT GRADE_INDEX

12 P2 TS_2

14 SCOTT GRADE_INDEX

15 P3 TS_3

18 SYS@orcl> INDEX_NAME

3：全局索引分区

2 SCOTT@orcl> create index index_saleprice on BOOKS(saleprice) global partition by range(saleprice) ( partition p1 values less than(30),partition p2 values less than(50),partition p3 values less than(maxvalue) );

4 Index created.

1 SCOTT@orcl> create index index_ISBN on books(ISBN) global partition by hash(ISBN);

3 Index created.

5 SCOTT@orcl>

七：管理索引分区

1：索引分区管理的操作列表

2：索引分区管理的实际操作

1：删除索引分区

2 SCOTT@orcl> alter index index_saleprice drop partition p2;

4 Index altered.

1 SCOTT@orcl> alter index index_saleprice drop partition p1;

3 Index altered.

4 SCOTT@orcl> alter index index_saleprice drop partition p1;

6 Index altered.

2：重命名索引分区

2 SCOTT@orcl> alter index index_saleprice rename partition p3 to p_new;

4 Index altered.

6 SCOTT@orcl>

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