oracle 简单分区的学习
来源:互联网 发布:win10安装ubuntu双系统 编辑:程序博客网 时间:2024/05/16 09:42
Tables greater than 2 GB should always be considered as candidates for partitioning.
表的数据超过2GB的时候应该考虑使用分区表
Tables containing historical data, in which new data is added into the newest partition. A typical example is a historical table where only the current month's data is updatable and the other 11 months are read only.
表中有历史数据,但是新的数据只进入新的分区
When the contents of a table must be distributed across different types of storage devices.
PARTITION BY RANGE (column[, column ]...) [ INTERVAL ( expr ) [ STORE IN ( tablespace [, tablespace]...) ]] ( PARTITION [ partition ] range_values_clause table_partition_description [, PARTITION [ partition ] range_values_clause table_partition_description ]... )
需要我们指定的有:
l column:分区依赖列(如果是多个,以逗号分隔);
l partition:分区名称;
l values less than:后跟分区范围值(如果依赖列有多个,范围对应值也应是多个,中间以逗号分隔);
l tablespace_clause:分区的存储属性,例如所在表空间等属性(可为空),默认继承基表所在表空间的属性。
2 partition by range(id)(3 partition t_range_p1 values less than (100) tablespace founder,4 partition t_range_p2 values less than (200) tablespace founder01,5 partition t_range_p3 values less than (maxvalue) tablespace founder026 );
OWNER TABLE_NAME TABLESPACE_NAME PAR------------------------------- ----------------------------- ------------------------- -------FOUNDER TEST_PARTITION_RANGE YES
SQL> select partition_name,high_value,tablespace_name,table_name from dba_tab_partitions where table_name='TEST_PARTITION_RANGE' order by partition_position;TABLE_NAME PARTITI PARTITION_COUNT------------------------------ ------- ---------------TEST_PARTITION_RANGE RANGE 3
我们来看看oracle是如何给分区表分配空间的PARTITION_NAME HIGH_VALUE TABLESPACE_NAME TABLE_NAME--------------- ---------------- ---------------- ------------------------- -----------------------------------T_RANGE_P1 100 FOUNDER TEST_PARTITION_RANGET_RANGE_P2 200 FOUNDER01 TEST_PARTITION_RANGET_RANGE_P3 MAXVALUE FOUNDER02 TEST_PARTITION_RANGE
SQL> select segment_name,partition_name,segment_type,tablespace_name,header_file,header_block from dba_segments t where t.segment_name='TEST_PARTITION_RANGE';
PARTITION BY HASH (column [, column ] ...) { individual_hash_partitions |hash_partitions_by_quantity }
所以创建分区有两种方法:
SQL> create table dept2 (deptno number,deptname varchar2(32))
2 partition by hash(deptno) partitions 4;
方法二:指定分区的名字
SQL> create table dept3 (deptno number,deptname varchar2(32))
2 partition by hash(deptno)
3 (partition p1 tablespace founder
4 partition p2 tablespace founder02);
(PARTITION [ partition ]
list_values_clause table_partition_description
[, PARTITION [ partition ]
list_values_clause table_partition_description
]...
)
例子如下:
create table sales_list (salesman_id number(5), salesman_name varchar2(30), sales_state varchar2(20), sales_amount number(10), sales_date date) partition by list (sales_state) ( partition sales_west values ('California','Hawaii') tablespace x, partition sales_east values ('New York','Virginia') tablespace y, partition sales_central values ('Texas','Illinois') tablespace z, partition sales_other values(DEFAULT) tablespace o );
- oracle 简单分区的学习
- Oracle表分区学习
- 简单ORACLE分区表、分区索引
- 简单ORACLE分区表、分区索引
- 简单ORACLE分区表、分区索引
- 简单ORACLE分区表、分区索引
- 简单ORACLE分区表、分区索引
- 简单ORACLE分区表、分区索引
- 学习ORACLE分区(2)-创建range分区
- Oracle的分区管理
- Oracle的分区管理
- Oracle的分区管理
- Oracle 分区的原则
- oracle的表分区
- oracle 的二维分区
- LINUX 简单的分区
- Oracle表分区学习笔记
- Oracle表分区学习笔记
- 余弦相似性的文本计算思想
- Ubuntu 中Samba的安装
- VC中获取窗口句柄的各种方法 .
- HDU-1864 EXCEL排序
- 语言 unsigned 和 signed 类型相互转换深入理解
- oracle 简单分区的学习
- C# Math常用的方法
- 代理模式 The Proxy Pattern
- 如何编写更佳的bug report
- C# 4.0的并行任务
- ubuntu下安装rpm
- C++ - assert()函数
- HTTP长连接小议
- hdu 1232 畅通工程(最小生成树or并查集)