用户可以一张表分成多个分区,在查询时,可以只访问特定的分区。
可将不同的分区存储在不同的磁盘,这样可以提高访问性能和安全性。
可以独立的备份和恢复每一个分区。
一、分区的类型
1、9i和10g的分区方法:
A、范围分区
以表中的一列或一组列的组合的值的范围进行分区
语法:
partition by range(列名)
(partition 分区1 values less than(值1),
partition 分区2 values less than(值2),
......
);
//创建一个学生表,按照学生年龄进行分区
SQL> create table student(sid number,sname varchar2(20),sage number)
2 partition by range(sage)
3 (partition children values less than(12),
4 partition teenagers values less than(20),
5 partition adults values less than(60),
6 partition seniors values less than(200));
Table created
//查看分区信息
SQL> select * from user_tab_partitions u where u.table_name='STUDENT';
TABLE_NAME COMPOSITE PARTITION_NAME SUBPARTITION_COUNT HIGH_VALUE HIGH_VALUE_LENGTH PARTITION_POSITION TABLESPACE_NAME PCT_FREE PCT_USED INI_TRANS MAX_TRANS INITIAL_EXTENT NEXT_EXTENT MIN_EXTENT MAX_EXTENT MAX_SIZE PCT_INCREASE FREELISTS FREELIST_GROUPS LOGGING COMPRESSION COMPRESS_FOR NUM_ROWS BLOCKS EMPTY_BLOCKS AVG_SPACE CHAIN_CNT AVG_ROW_LEN SAMPLE_SIZE LAST_ANALYZED BUFFER_POOL FLASH_CACHE CELL_FLASH_CACHE GLOBAL_STATS USER_STATS IS_NESTED PARENT_TABLE_PARTITION INTERVAL SEGMENT_CREATED
------------------------------ --------- ------------------------------ ------------------ -------------------------------------------------------------------------------- ----------------- ------------------ ------------------------------ ---------- ---------- ---------- ---------- -------------- ----------- ---------- ---------- ---------- ------------ ---------- --------------- ------- ----------- ------------ ---------- ---------- ------------ ---------- ---------- ----------- ----------- ------------- ----------- ----------- ---------------- ------------ ---------- --------- ------------------------------ -------- ---------------
STUDENT NO CHILDREN 0 12 2 1 SYSTEM 10 40 1 255 65536 1048576 1 2147483645 2147483645 1 1 YES DISABLED 0 0 0 0 0 0 2021/11/4 22: DEFAULT DEFAULT DEFAULT YES NO NO NO YES
STUDENT NO TEENAGERS 0 20 2 2 SYSTEM 10 40 1 255 65536 1048576 1 2147483645 2147483645 1 1 YES DISABLED