通过案例学调优之--分区表基本管理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_t115: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_segments15:35:22 2 where segment_name='part_t1';segment_name segment_type tablespace_name bytes extents-------------------- ------------------ ------------------------------ ---------- ----------part_t1 table partition tbs4 131072 2part_t1 table partition tbs3 393216 6part_t1 table partition tbs2 393216 6part_t1 table partition tbs1 327680 515:31:38 sys@ test1 >select table_name,partitioning_type,partition_count,status from dba_part_tables15:32:21 2 where table_name='part_t1';table_name partition partition_count status------------------------------ --------- --------------- --------part_t1 range 4 valid