通过案例学调优之--分区表基本管理



通过案例学调优之--分区表基本管理1、建立tablespace并将数据文件存储到不同表空间（分散I/O)15:15:14SYS@test1selectfile_id,file_name,tablespace_namefromdba

通过案例学调优之--分区表基本管理

1、建立tablespace并将数据文件存储到不同表空间（分散I/O)

15:15:14 SYS@ test1 >select file_id,file_name,tablespace_name from dba_data_files order by 3;

FILE_ID FILE_NAME TABLESPACE_NAME ---------- -------------------------------------------------- ------------------------------ 5 /dsk1/oradata/test1/tbs1.dbf TBS1 6 /dsk2/oradata/test1/tbs2.dbf TBS2 8 /dsk3/oradata/test1/tbs3.dbf TBS3 9 /dsk4/oradata/test1/tbs4.dbf TBS4

2、创建分区

创建range 分区：

15:26:04 SYS@ test1 >create table part_t1 15:27:35 2 PARTITION BY RANGE (object_id) 15:27:35 3 (partition p1 values less than (4000) tablespace tbs1, 15:27:35 4 partition p2 values less than (8000) tablespace tbs2, 15:27:35 5 partition p3 values less than (12000) tablespace tbs3, 15:27:35 6 partition p4 values less than (maxvalue) tablespace tbs4) 15:27:35 7 as 15:27:35 8 select owner,object_name,object_id,object_type,TIMESTAMP,status from dba_objects; Table created.

查看分区信息：

15:27:38 SYS@ test1 >select count(*) from part_t1 partition(p1); COUNT(*) ---------- 3931

每个分区都是一个都是的segment：

15:34:42 SYS@ test1 >select segment_name,segment_type,tablespace_name,bytes,extents from dba_segments 15:35:22 2 WHERE segment_name='PART_T1'; SEGMENT_NAME SEGMENT_TYPE TABLESPACE_NAME BYTES EXTENTS -------------------- ------------------ ------------------------------ ---------- ---------- PART_T1 TABLE PARTITION TBS4 131072 2 PART_T1 TABLE PARTITION TBS3 393216 6 PART_T1 TABLE PARTITION TBS2 393216 6 PART_T1 TABLE PARTITION TBS1 327680 515:31:38 SYS@ test1 >select table_name,PARTITIONING_TYPE,PARTITION_COUNT,STATUS from dba_part_tables 15:32:21 2 where table_name='PART_T1'; TABLE_NAME PARTITION PARTITION_COUNT STATUS ------------------------------ --------- --------------- -------- PART_T1 RANGE 4 VALID