表概述
表是 Oracle 数据库中最基本的数据存储结构。数据在表中以行(row)和列(column)的形式存储。用户在定义表时,需要设定表名(table name)(例如employees 表),还要设定表内各列的列名(column name)(例如 employee_id,last_name,及job_id 列),数据类型(datatype)(例如VARCHAR2,DATE,或NUMBER),及宽度(width)。有些数据类型的宽度是固定的,例如DATE 类型。而对于 NUMBER 类型的列来说,则需要定义精度(precision)及数值范围(scale)。
oracle数据库组织表的四种基本方式
1、堆组织表(Heap Organize Table) 普通的oracle表,数据存储没有顺序。
2、索引组织表(index-organized table) 存储B-树索引结构中排序的数据
3、集群表(cluster table) 共享相同数据库块的一组表
4、分区表(partition-table) 将大量数据的表根据不同的划分条件分解成分区的子表
以及一些特殊表:临时表(temporary table)、外部表(EXTERNAL TABLE)
删除表中数据
delete:DML语句,产生undo,删除数据量量大的时候比较慢,能用rollback撤销。
truncate:DDL语句,不会产生undo,不能撤销
并行创建表、索引
create table test_f PARALLEl(DEGREE 4) NOLOGGING as select * from dba_objects;
create index test_c_idx_1 on test_c(id_no) parallel (degree 4) nologging;
指定多个进程并行执行加载数据,且用nologging指定oracle不写undo。
如果像移动表,也可以使用move
alter table table_name move tablespace new_tablespace;
移动表之后,行的rowid发生了变化,因而该表上的索引会不可用。必须重新建立(re-create) 或者重建(rebuild)索引。
rebuild:
alter index index_name rebuild online nologging;
也可以通过rebuild index 来改变索引的表空间;
alter index index_name rebuild tablespace new_tablespace;
exp:
SQL> alter index test_d_idx_1 rebuild tablespace users02;
Index altered.
分区表
分区技术(partitioning)可以将大表、大索引分解为更小、更易管理的块,这些块被称为分区(partition),通过分区技术可以有效地解决大表、大索引带来的问题。用户对分区表执行的 SQL 查询或 DML 语句与对普通数据表的语句一样。但是定义了分区后,DDL 语句可以访问、操作一个单独的分区,而不是整个表或索引,这样通过分区技术就能简化对大数据库对象的管理工作。分区对应用程序是透明的。
表或索引的所有分区必须具备相同的逻辑结构,例如列名(column name),数据类型(datatype),及数据约束(constraint)等,但每个分区的物理属性可以不同,例如 pctfree,pctused,及表空间等。
分区键
分区表(partitioned table)内的每个数据行都能且只能分配到一个分区中(partition)。分区键(partition key)是决定数据行属于哪个分区的一组数据列。Oracle 在执行插入,更新,及删除操作时能根据分区键自动地选择分区。
分区键的特点如下:
- 由 1 至 16 个数据列顺序构成
- 不能包含 LEVEL,ROWID,或MLSLABEL 虚列(pseudocolumn),也不能包含类型为 ROWID 的列
- 不能包含可为空(NULLable)的列
oracle提供6种不同的方法分区表的数据:范围分析、间隔分析、散列分区、列表分区、引用分区、系统分区。此外,还可以使用组合分区,组合两个分区的方法来把数据划分成更小的子分区(subpartition).
分区时,必须指定
- 分区方法:六种分区方式之一
- 分区列: 对表数据进行分区所依据的列,可以单列、多列
- 分区描述:对应于指定的分区方式的在分区列上的具体分区描述
1、范围分区(range partitioning)
范围分区(range partitioning)依据用户创建分区时设定的分区键值(partition key value)范围将数据映射到不同分区。通常针对日期数据使用。
exp:
create table test_f(
id_no number primary key,
sale_code varchar2(10) not null,
sale_name varchar2(10) not null,
op_time date not null)
partition by range (op_time)
(partition op_t1 values less than (to_date('2013-02-01','yyyy-mm-dd')) tablespace users02,
partition op_t2 values less than (to_date('2013-03-01','yyyy-mm-dd')) tablespace users03,
partition op_t3 values less than (to_date('2013-04-01','yyyy-mm-dd')) tablespace users04);
在范围分区中,应使用一个包罗万象的分区作为最后一个分区,最后一个分区包含的值小于maxvalue 大于倒数第二个分区的值。
2、间隔分区(Interval Partitioning) --11g新特性
间隔分区是传统的范围分区的一种扩展。为使用间隔分区,必须先对该表指定一个范围分区的最小数目。范围分区键的高位值成为转变点(transition point),当表中的数据越过转折点之后,数据库自动建立间隔分区。
注意事项:
- 用 interval关键创建间隔分区的表
- 指定间隔分区钱,应该使用分区子句指定至少一个范围分区
- 不能使用包含多于1个的分区键(只能使用单分区键)
- 分区键必须为number或者date类型
- 可以通过create table 包含store in 子句 为分区选择性的指定表空间
exp:
create table test_f(
id_no number,
sale_code varchar2(10),
time_id date,
pid varchar2(4))
partition by range (time_id)
interval (numtoyminterval(1,'MONTH'))
(partition p0 values less than (to_date('2013-01-01','yyyy-mm-dd')) ,
partition p1 values less than (to_date('2014-01-01','yyyy-mm-dd')));
Table created.
上例中,当插入的数据 date超过2013-01-01,数据库自动建立分区,每个月创建一个分区,。因此没有分区数目限制。
3、散列分区 (hash partitioning)
用户可以采用哈希分区(hash partitioning)将不适于采用范围分区(range partitioning)或列表分区(list partitioning)的数据进行分区。哈希分区的语法(syntax)简单且易于实现。
在以下情况时哈希分区比范围分区更适用:
- 用户无法事先确定一个分区可能存储的数据量
- 各范围分区的容量可能相差很大,或很难通过人工进行平衡
- 采用范围分区可能导致数据不正常的集中
- 应用系统对并行 DML(parallel DML),分区剪除(partition pruning),及基于分区的关联(partition-wise joins)等与性能有关的分区特性要求较高
exp:
1 create table test_b(
2 id_no number,
3 name varchar2(10),
4 birth_date date,
5 card_id number )
6 partition by hash(card_id)
7 partitions 4
8* store in (users02,users03,users04,users05);
Table created.
可以通过dba/user_tab_partitions查看刚刚建立的test_b的4个hash分区名称。
SQL> select table_name,partition_name from dba_tab_partitions where table_name='TEST_B';
TABLE_NAME PARTITION_NAME
------------------------------ ------------------------------
TEST_B SYS_P41
TEST_B SYS_P42
TEST_B SYS_P43
TEST_B SYS_P44
--再可以分别查看每个分区存储了哪些数据。
SQL> select * from test_b partition(sys_p41);
no rows selected
SQL> select * from test_b partition(sys_p42);
ID_NO NAME BIRTH_DAT CARD_ID
---------- -------------------- --------- ----------
1 ccc 20-JUL-13 12345
4 eeeee 20-JUL-13 12355
SQL> select * from test_b partition(sys_p43);
ID_NO NAME BIRTH_DAT CARD_ID
---------- -------------------- --------- ----------
2 bbb 20-JUL-13 123
SQL> select * from test_b partition(sys_p44);
ID_NO NAME BIRTH_DAT CARD_ID
---------- -------------------- --------- ----------
3 cdsd 20-JUL-13 7575
4、列表分区
当数据分布在一些有限的离散数据中时,可以使用列表分区。
exp:
create table test_list(
id_no number,
name varchar2(10),
area varchar2(20))
partition by list(area)
(partition p_east values ('shanghai','zhejiang','guangdong') tablespace users02,
partition p_north values ('beijing','dongbei','shanxi') tablespace users03,
partition p_south values ('sichuan','yunan','guizhou') tablespace users04,
partition p_west values ('xijiang','xizang','qinghai') tablespace users05)
5、引用分区--11g新特性
如果两个表示想关联的,可以通过已有的父-子关系上对着两个表进行分区。通过主键和外键约束强制管理的。如果两个表共用一个父-子关系,只需形式上指定父表的分区即可。父表上的任何分区维护都将自动的级联到子表中。
exp:父表
create table test_dept (
dept_no number,
dept_name varchar2(20),
dept_address varchar2(100),
constraint test_dept_pk primary key(dept_no)
)
partition by range(dept_no)
(partition p_dept_1 values less than (20) tablespace users02,
partition p_dept_2 values less than (30) tablespace users03,
partition p_dept_3 values less than (40) tablespace users04,
partition p_dept_4 values less than (100) tablespace users05);
新建子表test_emp,引用父表分区策略
create table test_emp (
id_no number,
name varchar2(10),
salary number,
dept_no number not null ,
constraint test_emp_fk foreign key (dept_no) references test_dept(dept_no)
)
partition by reference(test_emp_fk);
注意:
- 外键列必须为not null 否则会报错ORA-14652: reference partitioning foreign key is not supported
- 可以对引用分区使用除间隔分区以外的所有分区策略
- 引用分区中,不指定分区名称,将沿用父表派生出来的分区名称
查看子表分区的名称
SQL> select table_name,partition_name from dba_tab_partitions where table_name='TEST_EMP';
TABLE_NAME PARTITION_NAME
------------------------------ ------------------------------
TEST_EMP P_DEPT_1
TEST_EMP P_DEPT_2
TEST_EMP P_DEPT_3
TEST_EMP P_DEPT_4
6、 系统分区
系统分区是一种特殊的分区,其中数据由应用程序控制而不是由数据库控制存放放置的。数据库仅仅让你把表分成区,而不管每个分区中存放的具体内容。应用程序插入值的时候,应该制定插入到哪一个分区之中,不然会报错。
exp:
create table test_system (
id_no number,
first varchar2(10))
partition by system
(partition p_1 tablespace users02,
partition p_2 tablespace users03,
partition p_3 tablespace users04);
注:
向系统分区插入数据,必须指定将数据插入到那个分区。insert必须指定插入到的分区但是删除和更新系统分区表里的数据的时候,可以不指定分区进行操作。即delete和update不需要明确指定分区,但是建议指定分区,以便数据库使用分区修建,避免扫描整个表。系统分区不支持create table as select 和insert into table as语句,因为数据行与分区之间没有隐射关系。
7、组合分区
复合分区(composite partitioning)首先根据范围(range)进行分区,再使用哈希或列表方式创建子分区。复合范围-哈希分区既能够发挥范围分区的可管理性优势,也能够发挥哈希分区的数据分布(data placement),条带化(striping),及并行化(parallelism)优势。复合范围-列表分区能够发挥范围分区的可管理性优势,也能利用列表分区的显示控制能力。
1、Range-Hash :
exp:
create table test_composite
(
id_no number,
name varchar2(10),
loc varchar2(100),
birth_date date not null
)
partition by range(birth_date)
subpartition by hash(id_no)
subpartitions 4 store in (users02,users03,users04,users05)
(
partition p_1 values less than (to_date('2013-02-01','yyyy-mm-dd')) tablespace users02,
partition p_2 values less than (to_date('2013-07-01','yyyy-mm-dd')) tablespace users03,
partition p_3 values less than (to_date('2014-01-01','yyyy-mm-dd')) tablespace users04,
partition p_4 values less than (to_date('9999-12-31','yyyy-mm-dd')) tablespace users05
);
SQL> select table_name ,tablespace_name from dba_tables where table_name='TEST_COMPOSITE';
TABLE_NAME TABLESPACE_NAME
------------------------------ ------------------------------
TEST_COMPOSITE
理论上,分区表在user_tables中是没有显示指定的tablespace_name ,分区表必须从user_tab_partition查看分区表的各个分区分布在哪些表空间。
查看组合分区创建的分区,子分区:
SQL> select table_name,partition_name,subpartition_name from dba_tab_subpartitions where table_name ='TEST_COMPOSITE' order by partition_name;
TABLE_NAME PARTITION_NAME SUBPARTITION_NAME
------------------------------ ------------------------------ ------------------------------
TEST_COMPOSITE P_1 SYS_SUBP335
TEST_COMPOSITE P_1 SYS_SUBP334
TEST_COMPOSITE P_1 SYS_SUBP333
TEST_COMPOSITE P_1 SYS_SUBP336
TEST_COMPOSITE P_2 SYS_SUBP339
TEST_COMPOSITE P_2 SYS_SUBP338
TEST_COMPOSITE P_2 SYS_SUBP337
TEST_COMPOSITE P_2 SYS_SUBP340
TEST_COMPOSITE P_3 SYS_SUBP343
TEST_COMPOSITE P_3 SYS_SUBP342
TEST_COMPOSITE P_3 SYS_SUBP341
TABLE_NAME PARTITION_NAME SUBPARTITION_NAME
------------------------------ ------------------------------ ------------------------------
TEST_COMPOSITE P_3 SYS_SUBP344
TEST_COMPOSITE P_4 SYS_SUBP347
TEST_COMPOSITE P_4 SYS_SUBP346
TEST_COMPOSITE P_4 SYS_SUBP345
TEST_COMPOSITE P_4 SYS_SUBP348
16 rows selected.
查看系统中所有分区表
SQL> select count(1) from dba_tables where partitioned='YES';
COUNT(1)
----------
71
几个与分区表相关的数据字典:
显示数据库所有分区表的信息:DBA_PART_TABLES
显示表分区信息 显示数据库所有分区表的详细分区信息:DBA_TAB_PARTITIONS
显示子分区信息 显示数据库所有组合分区表的子分区信息:DBA_TAB_SUBPARTITIONS
显示分区列 显示数据库所有分区表的分区列信息:DBA_PART_KEY_COLUMNS
显示子分区列 显示数据库所有分区表的子分区列信息:DBA_SUBPART_KEY_COLUMNS
--仅仅列出dba组,有对应的user/all组数据字典
2、范围-列表分区示例
create table test_com_rl (
id_no number,
name varchar2(10),
area varchar2(10) not null,
birth_day date)
partition by range(birth_day)
subpartition by list(area)
subpartition template
(subpartition sb_1 values ('shanghai','chongqing','beijing','chengdu') tablespace users02,
subpartition sb_2 values ('shenzhen','jiangxi','nanchang','wuhan') tablespace users03,
subpartition sb_3 values ('hannan','kunming','guiyang','lasha') tablespace users04
)
(partition p1 values less than (to_date('2013-04-01','yyyy-mm-dd')) tablespace users02,
partition p2 values less than (to_date('2013-07-01','yyyy-mm-dd')) tablespace users03,
partition p3 values less than (to_date('2013-10-01','yyyy-mm-dd')) tablespace users04,
partition p4 values less than (to_date('2014-01-01','yyyy-mm-dd')) tablespace users05);
SUBPARTITION TEMPLATE:子分区模板,指定子分区创建方式。
3、间隔-范围分区
create table test_com_ir (
id_no number,
name varchar2(10),
hire_date date,
birth_day date)
partition by range(hire_date) interval(numtoyminterval(1,'MONTH'))
subpartition by range(birth_day)
subpartition template
(
subpartition sp_1 values less than (to_date('2013-03-01','yyyy-mm-dd')) tablespace users02,
subpartition sp_2 values less than (to_date('2013-07-01','yyyy-mm-dd')) tablespace users03,
subpartition sp_3 values less than (to_date('2013-11-01','yyyy-mm-dd')) tablespace users04,
subpartition sp_4 values less than (to_date('2014-01-01','yyyy-mm-dd')) tablespace users05
)
(
partition p_1 values less than (to_date('2013-03-01','yyyy-mm-dd'))
);
分区维护操作
注:
- 范围和列表分区不能接合
- 散列分区不能被删除,分割或者合并
- 只有列表分区能通过增加和删除分区来修改分区
除上述操作特殊外,其他分区维护操作相同
1、增加分区
alter table test_c
add partition p_5 values less than (to_date('2014-01-01','yyyy-mm-dd')) tablespace users05;
2、分割分区
从一个已有的分区取出数据,再将其分不到两个分区中
alter table test_d
split partition p_3 at (2000) into (partition p_4,p_5)
3、合并分区
merge partition 可以将两个邻接分区的内容合并
alter table test_c
merge partition p_1,p_2 into partition p_2;
4、重命名分区
与重命名表一样
alter table test_com_ir rename subpartition p_1_sp_1 to sp1;
5、交换分区
exchange partition 命令可以将一个普通未分区表转换为分区表的一个分区。分区交换不涉及具体的数据移动,oracle重命名原来的表为一个分区,并把目标分区作为原表。数据库完成的是无数据移动的装载过程。
alter table test_c
exchage partition p_2 with test_cc;
test_cc--未分区表
6、删除分区
alter table test_c
drop partition p_3;
7、结合分区
alter table test_c coalesce partition