1、表的
功能：存储、管理数据的基本单元（二维表：有行和列组成） 2、表的类型： 1）堆表：heap table ：数据存储时，行是无序的，对它的访问采用全表扫描。 2）分区表 表>2G 3）索引组织表（IOT） 4）簇表 5）临时表 6）压缩表 7）嵌套表 3、如何将普通表转换为分区表； 11g以前，1）create 分区表， 2）insert into 分区表 select * from 普通表; 3)rename 分区表名; 4)重建约束、索引、触发器。 11g以后，在线重定义分区表 12.1 分区表及其种类（10g) 1）Range Partitioning (范围分区） scott:
sql>create table sale( product_id varchar2(5),sales_count number(10,2) ) partition by range(sales_count) ( partition p1 values less than(1000),partition p2 values less than(2000),partition p3 values less than(3000) ); 查看信息： select * from user_tab_partitions where table_name='SALE'; insert into sale values('1',600); insert into sale values('2',1000); insert into sale values('3',2300); insert into sale values('4',6000); commit; select * from sale partition(p1); select * from sale partition(p2);
增加一个分区 alter table sale add partition p4 values less than(maxvalue); 再看一下， 可以插入6000值了 select * from user_tab_partitions where table_name='SALE'; insert into sale values('4',6000); 看一下段的分配
sql> select segment_name,segment_type,partition_name from user_segments; 12.1.1 默认情况下，如果对分区表的分区字段做超范围（跨段）update操作，会报错——ORA-14402: 。如果一定要改，可以通过打开表的row movement
属性来完成。
sql> select rowid,t1.* from sale partition(p1) t1; ROWID PRODU SALES_COUNT ------------------ ----- ----------- AAASvUAAEAAAAGVAAA 1 600
sql> update sale set sales_count=1200 where sales_count=600; update sale set sales_count=1200 where sales_count=600 * 第 1 行出现
错误: ORA-14402: 更新分区关键字列将导致分区的更改
sql> alter table sale enable row movement;
sql> update sale set sales_count=1200 where sales_count=600; 已更新 1 行。
sql> select rowid,t1.* from sale partition(p2) t1; ROWID PRODU SALES_COUNT ------------------ ----- ----------- AAASvVAAEAAAAGdAAA 2 1000 AAASvVAAEAAAAGdAAB 1 1200 一般来说范围分区的分区字段使用数字类型或日期类型，使用字符类型的语法是可以的，实际工作中使用较少。这或许跟values less than 子句有关。 12.1.2 关于建立分区索引 一般使用分区都会建立索引，分区索引有local与global之分。 Local Parfixed Index |----------------------------- Local Partitioned Index | |-----------------------------| Partitioned Index | |Local Nonparfixed Index |----------------------------------| |------------------------------ | | | |Global Partitioned Index | |------------------------------ | |Nonpartitioned Index |------------------------ 1）local：一个索引分区对应一个表分区，分区key就是索引key，分区边界就是索引边界。更新一个表分区时仅仅影响该分区的索引。
sql>create index sale_idx on sale(sales_count) local;
sql>select * from user_ind_partitions; Local Parfixed Index，所谓前缀索引，是指组合索引中的first column使用的是分区key。 global：全局索引： 2）分区全局索引：索引分区不与表分区对应，分区key是索引key。另外一定要将maxvalue关键字做上限。 create index sale_global_idx on sale(sales_count) global partition by range (sales_count) ( partition p1 values less than(1500),partition p2 values less than(maxvalue) );
sql>select * from user_ind_partitions; 12.1.3
删除一个分区，其中的数据全部清除，并且
包括相关索引等
sql> alter table sale drop partition p3; 12.1.4 Hash Partitioning (散列分区,也叫hash分区) 实现均匀的
负载值分配，
增加HASH分区可以重新分布数据。 create table my_emp( empno number,ename varchar2(10) ) partition by hash(empno) ( partition p1,partition p2 ); select * from user_tab_partitions where table_name='MY_EMP'; 插入几个值，看是否均匀插入。 insert into my_emp values(1,'A'); insert into my_emp values(2,'B'); insert into my_emp values(3,'C'); select * from my_emp partition(P1); select * from my_emp partition(P2); 12.1.5 列表分区(list)： 将不相关的数据组织在一起 create table personcity( id number,name varchar2(10),city varchar2(10) ) partition by list(city) ( partition east values('tianjin','dalian'),partition west values('xian'),partition south values ('shanghai'),partition north values ('herbin'),partition other values (default) ); insert into personcity values(1,'sohu','tianjin'); insert into personcity values(2,'sina','herbin'); insert into personcity values(3,'yahoo','dalian'); insert into personcity values(4,'360','zhengzhou'); insert into personcity values(5,'baidu','xian'); 看结果 select * from personcity partition(east); 12.1.6 Composite Partitioning（复合分区） 把范围分区和散列分区相结合或者 范围分区和列表分区相结合。 create table student( sno number,sname varchar2(10) ) partition by range(sno) subpartition by hash(sname) subpartitions 4 ( partition p1 values less than(1000),partition p3 values less than(maxvalue) ); 有三个range分区，对每个分区会有4个hash分区，共有12个分区。
sql> select * from user_tab_partitions where table_name='STUDENT';
sql> select * from user_tab_subpartitions where table_name='STUDENT'; 用EM查看，看scott的student table子分区里的名字是由oracle取名。 12.2 Oracle11g新增分区 Partition（分区），一直是Oracle
数据库引以为荣的一项技术，正是分区的存在让Oracle高效的处理海量数据成为可能。在Oracle11g在 10g的分区技术基础上又有了新的发展，使分区技术在易用性和可扩展性上再次得到了增强。 12.2.1 Interval Partitioning （间隔分区） 实际上是由range分区引申而来，最终实现了range分区的
自动化。 scott:
sql> create table interval_sales (s_id int,d_1 date) partition by range(d_1) interval (numtoyminterval(1,'MONTH')) ( partition p1 values less than ( to_date('2010-02-01','yyyy-mm-dd') ) );
sql> insert into interval_sales values(1,to_date('2010-01-21','yyyy-mm-dd') );
sql> insert into interval_sales values(2,to_date('2010-02-01','yyyy-mm-dd') ); --越过p1分区上线，将
自动建立一个分区
sql> select partition_name from user_tab_partitions; PARTITION_NAME ------------------------------ P1 SYS_P61 注意：interval (numtoyminterval(1,'MONTH'))的意思就是每个月有一个分区，每当输入了新的月份的数据，这个分区就会
自动建立，而 不同年的相同月份是两个分区。 12.2.2 System Partitioning (系统分区） 这是一个人性化的分区类型，System Partitioning，在这个新的类型中，不需要指定任何分区键，数据会进入哪个分区完全由应用程序决 定，即在Insert语句中决定记录行插入到哪个分区。 先建立三个表空间 tbs1,tbs2,tbs3， 然后建立三个分区的system分区表，分布在三个表空间上。 create table test (c1 int,c2 int) partition by system ( partition p1 tablespace tbs1,partition p2 tablespace tbs2,partition p3 tablespace tbs3 ); 现在由
sql语句来指定插入哪个分区：
sql> INSERT INTO test PARTITION (p1) VALUES (1,3);
sql> INSERT INTO test PARTITION (p3) VALUES (4,5);
sql> select * from test; C1 C2 ---------- ---------- 1 3 4 5 注意：如果要
删除以上表空间，必须先
删除其上的分区表，否则会报错ORA-14
404: 分区表包含不同表空间中的分区。 12.2.3 Reference Partitioning (引用分区) 当两个表是主外键约束关联时，我们可以利用父子关系对这两个表进行分区。只要对父表做形式上的分区，然后子表就可以继承父表的分 区键。 如果没有11g的引用分区，你想在两个表上都建立对应的分区，那么需要使两表分别有相同
名称的键值列。引用分区的好处是避免了在子表 上也建立父表同样的一个分区键列，父表上的任何分区维护操作都将
自动的级联到子表上。 例：
sql> CREATE TABLE purchase_orders (po_id NUMBER(4),po_date TIMESTAMP,supplier_id NUMBER(6),po_total NUMBER(8,2),CONSTRAINT order_pk PRIMARY KEY(po_id)) PARTITION BY RANGE(po_date) (PARTITION Q1 VALUES LESS THAN (TO_DATE('2007-04-01','yyyy-mm-dd')),PARTITION Q2 VALUES LESS THAN (TO_DATE('2007-06-01',PARTITION Q3 VALUES LESS THAN (TO_DATE('2007-10-01',PARTITION Q4 VALUES LESS THAN (TO_DATE('2008-01-01','yyyy-mm-dd'))); //父表做了一个Range分区（可对引用分区使用除间隔分区外的所有分区策略）
sql> CREATE TABLE purchase_order_items (po_id NUMBER(4) NOT NULL,product_id NUMBER(6) NOT NULL,unit_price NUMBER(8,quantity NUMBER(8),CONSTRAINT po_items_fk FOREIGN KEY (po_id) REFERENCES purchase_orders(po_id)) PARTITION BY REFERENCE(po_items_fk); //主表使用po_date键值列做范围分区，子表中没有po_date列，也想做相应的分区，那么使用引用分区吧。 //子表最后一句PARTITION BY REFERENCE()子句给出了引用分区约束名，使用的是子表的外键约束名。 //子表的po_id列必须是NOT NULL。这与通常的外键可以是NULL是有区别的。
sql> select TABLE_NAME,PARTITION_NAME,HIGH_VALUE from user_tab_partitions; TABLE_NAME PARTITION_NAME HIGH_VALUE ------------------------------ ------------------------------ -------------------------------------------------------------------------------- PURCHASE_ORDERS Q1 TIMESTAMP' 2007-04-01 00:00:00' PURCHASE_ORDERS Q2 TIMESTAMP' 2007-06-01 00:00:00' PURCHASE_ORDERS Q3 TIMESTAMP' 2007-10-01 00:00:00' PURCHASE_ORDERS Q4 TIMESTAMP' 2008-01-01 00:00:00' PURCHASE_ORDER_ITEMS Q1 PURCHASE_ORDER_ITEMS Q2 PURCHASE_ORDER_ITEMS Q3 PURCHASE_ORDER_ITEMS Q4 8 rows selected //子表purchase_order_items也
自动产生了四个分区，Q1,Q2,Q3,Q4.高值为空，意味者此处边界由父表派生。
sql> select TABLE_NAME,PARTITIONING_TYPE,REF_PTN_CONSTRAINT_NAME from user_part_tables; TABLE_NAME PARTITIONING_TYPE REF_PTN_CONSTRAINT_NAME ------------------------------ ----------------- ------------------------------ PURCHASE_ORDERS RANGE PURCHASE_ORDER_ITEMS REFERENCE PO_ITEMS_FK // PO_ITEMS_FK列是外键约束
名称 12.2.4 Virtual Column-Based Partitioning（虚拟列分区） 先了解一下什么叫虚拟列。 虚拟列是11g的新特性： 1> 只能在堆组织表（普通表）上创建虚拟列 2> 虚拟列的值并不是真实存在的，只有用到时，才根据表达式计算出虚拟列的值，磁盘上并不存放。 3> 可在虚拟列上建立索引。 4> 如果在已经创建的表中
增加虚拟列时，若没有指定虚拟列的字段类型，ORACLE会根据 generated always as 后面的表达式计算的结 果
自动设置该字段的类型。 5> 虚拟列的值由ORACLE根据表达式
自动计算得出，不可以做UPDATE和INSERT操作，可以对虚拟列做DELETE 操作。 6> 表达式中的所有列必须在同一张表。 7> 表达式不能使用其他虚拟列。 8> 可把虚拟列当做分区关键字建立虚拟列分区表，这正是我们要讲的虚拟列分区。 create table emp1 (empno number(4) primary key,ename char(10) not null,salary number(5) not null,bonus number(5) not null,total_sal AS (salary+bonus)) partition by range (total_sal) (partition p1 values less than (5000),partition p2 values less than (maxvalue)) enable row movement; insert into emp1(empno,ename,salary,bonus) values(7788,'SCOTT',3000,1000); insert into emp1(empno,bonus) values(7902,'FORD',4000,1500); insert into emp1(empno,bonus) values(7839,'KING',5000,3500); commit;
sql> select * from user_tab_partitions;
sql> select * from user_part_key_columns;
sql> select * from emp1 partition (p1); EMPNO ENAME SALARY BONUS TOTAL_SAL ---------- ---------- ---------- ---------- ---------- 7788 SCOTT 3000 1000 4000
sql> select * from emp1 partition (p2); EMPNO ENAME SALARY BONUS TOTAL_SAL ---------- ---------- ---------- ---------- ---------- 7902 FORD 4000 1500 5500 7839 KING 5000 3500 8500
sql> update emp1 set bonus=500 where empno=7902; 在建表时就使能了行移动（enable row movement），当更新分区键值时就不会报错（ORA-14402: 更新分区关键字列将导致分区 的更改） 12.2.5 More Composite Partitioning 在10g中，我们知道复合分区只
支持Range-List和Range-Hash，而在在11g中复合分区的类型大大
增加，现在Range，List，Interval都可 以作为Top level分区，而Second level则可以是Range，List，Hash，也就是在11g中可以有3*3=9种复合分区，满足更多的业务需求。 12.3 Oracle11g 的联机重定义
功能 联机条件下把普通的堆表转换成分区表（11g新特性） 例：联机创建分区表：将emp1表联机重定义，要求完成两个任务，使其按照 sal分区（以2500为界），并去掉comm列。这个过程需要建 立一个临时分区表emp1_temp完成复制转换。 sys下执行 create table scott.emp1 as select * from scott.emp; alter table scott.emp1 add constraint pk_emp1 primary key(empno); 1) 检查原始表是否具有在线重定义资格，（要求表自包含及之前没有建立实体化视图及日志）
sql> BEGIN DBMS_REDEFINITION.CAN_REDEF_TABLE('scott','emp1'); 该包要求表要有primary key END; / 2) 创建一个临时分区表:emp1_temp,含有7列（删去comm列），然后range分区，两个区以sal=2500为界。
sql> CREATE TABLE scott.emp1_temp (empno number(4) not null,ename varchar2(10),job varchar2(9),mgr number(4),hiredate date,sal number(7,deptno number(2)) PARTITION BY RANGE(sal) (PARTITION sal_low VALUES LESS THAN(2500),PARTITION sal_high VALUES LESS THAN (maxvalue)); 3）启动联机重定义处理过程
sql> BEGIN dbms_redefinition.start_redef_table('scott','emp1','emp1_temp','empno empno,ename ename,job job,mgr mgr,hiredate hiredate,sal sal,deptno deptno'); END; /
sql> select count(*) from scott.emp1_temp; COUNT(*) ---------- 14
sql> select * from scott.emp1_temp partition(sal_low); EMPNO ENAME JOB MGR HIREDATE SAL DEPTNO ---------- ---------- --------- ---------- ------ ------------- ---------- ---------- 7369 SMITH CLERK 7902 1980-12-17 00:00:00 800 20 7499 ALLEN SALESMAN 7698 1981-02-20 00:00:00 1600 30 7521 WARD SALESMAN 7698 1981-02-22 00:00:00 1250 30 7654 MARTIN SALESMAN 7698 1981-09-28 00:00:00 1250 30 7782 CLARK MANAGER 7839 1981-06-09 00:00:00 2450 10 7844 TURNER SALESMAN 7698 1981-09-08 00:00:00 1500 30 7876 ADAMS CLERK 7788 1987-05-23 00:00:00 1100 20 7900 JAMES CLERK 7698 1981-12-03 00:00:00 950 30 7934 MILLER CLERK 7782 1982-01-23 00:00:00 1300 10 已选择9行。
sql> select * from scott.emp1_temp partition(sal_high); EMPNO ENAME JOB MGR HIREDATE SAL DEPTNO ---------- ---------- --------- ---------- ------------------- ---------- ---------- 7566 JONES MANAGER 7839 1981-04-02 00:00:00 2975 20 7698 BLAKE MANAGER 7839 1981-05-01 00:00:00 2850 30 7788 SCOTT ANALYST 7566 1987-04-19 00:00:00 3000 20 7839 KING PRESIDENT 1981-11-17 00:00:00 5000 10 7902 FORD ANALYST 7566 1981-12-03 00:00:00 3000 20 已选择5行。 这个时候emp1_temp的主键，索引，触发器，授权等还没有从原始表继承过来，
sql> select constraint_name,constraint_type,table_name from user_constraints where table_name like 'EMP1%'; CONSTRAINT_NAME CONSTRAINT_TYPE TABLE_NAME ------------------------------ --------------- ------------------------------ PK_EMP1 P EMP1 SYS_C009652 C EMP1_TEMP 4) 复制依赖对象 这一步的作用是：临时分区表emp1_temp继承原始表emp1的全部
属性：
包括索引、约束和授权以及触发器。
sql> DECLARE num_errors PLS_INTEGER; BEGIN DBMS_REDEFINITION.COPY_TABLE_DEPENDENTS('scott',DBMS_REDEFINITION.CONS_ORIG_PARAMS,TRUE,num_errors); END; /
sql> select constraint_name,table_name from user_constraints where table_name like 'EMP1%'; CONSTRAINT_NAME CONSTRAINT_TYPE TABLE_NAME ------------------------------ --------------- ------------------------------ PK_EMP1 P EMP1 SYS_C009652 C EMP1_TEMP TMP$$_PK_EMP10 P EMP1_TEMP 这时候原始表emp1还没有分区，
sql> select table_name,partition_name,high_value from user_tab_partitions; TABLE_NAME PARTITION_NAME HIGH_VALUE ------------------------------ ------------------------------ -------------------------------------------------------------- EMP1_TEMP SAL_HIGH MAXVALUE EMP1_TEMP SAL_LOW 2500 5) 完成重定义过程。
sql> EXECUTE dbms_redefinition.finish_redef_table('scott','emp1_temp');
sql> select table_name,high_value from user_tab_partitions; TABLE_NAME PARTITION_NAME HIGH_VALUE ------------------------------ ------------------------------ -------------------------------------------------------------- EMP1 SAL_HIGH MAXVALUE EMP1 SAL_LOW 2500 最后一步发生了什么事情：原始表emp1与临时分区表emp1_temp互换
名称。 12.4 索引组织表(IOT表：如果表经常以主键
查询，可以考虑建立索引组织表，加快表的访问速度 heap table 数据的存放是
随机的，
获取表中的数据时没有明确的先后之分，在进行全表扫描的时候，并不是先插入的数据就先
获取。而IOT 表是一个完全B_tree索引结构的表，表结构按照索引（主键）有序组织，因此数据存放在插入以前就已经确定了其位置。 由于IOT表是把普通表和索引合二而一了,这样在进行
查询的时候就可以少访问很多基表的blocks，但是插入和
删除的时，速度比普通的表要 慢一些。 IOT表的叶子节点存储了所有表列，因为已按主键排序，所以叶子节点上不需要再存储rowid。 表列较多时，设置溢出段将主键和其他字段数据分开来存储以提高效率。 溢出段是个可选项，如果选择了溢出段,Oracle将为一个IOT表分配两个段，一个是索引段，另一个是溢出段。 溢出段有两个子句pctthreshold和including 说明： pctthreshold给出行大小和块大小的百分比，当行数据在叶子节点占用大小超出这个阈值时，就以这个阈值将索引entry一分为二，包含 主键的一部分列值保留在索引段，而其他列值放入溢出段，即overflow到指定的存储空间去。 including 后面指定一个或多个列名（不含主键列），将这些列都放入索引段。即让主键和一些常用的列在索引段，其余的列在溢出段。 例： create table iot_timran(id int,name char(50),sal int,constraint pk_timran primary key (id)) organization index pctthreshold 30 overflow tablespace users; 使用select * from user_indexes 查看是否单独有索引。
sql> select index_name,index_type,table_name from user_indexes; INDEX_NAME INDEX_TYPE TABLE_NAME ------------------------------ --------------------------- ------------------------------ PK_TIMRAN IOT - TOP IOT_TIMRAN PK_EMP NORMAL EMP PK_DEPT NORMAL DEPT 通过user_segments视图查看产生了两个段。
sql> select segment_name,partition_name from user_segments; 12.5 簇表(cluster table)： 两个相互关联的表的数据，物理上同时组织到一个簇块中，当以后进行关联读取时，只要扫描一个数据块就可以了，可以提高了IO效率。 建立簇表的三个步骤： 1）建立簇段cluster segment 2）基于簇，创建两个相关表，这两个表不建立单独的段，每个表都关联到cluster segment上。 3）为簇创建索引，
生成索引段。 create cluster cluster1(code_key number); create table student(sno1 number,sname varchar2(10)) cluster cluster1(sno1); create table address(sno2 number,zz varchar2(10)) cluster cluster1(sno2); create index index1 on cluster cluster1;
生成了cluster1段和index1段。 查看簇的信息： select * from user_clusters; select * from user_clu_columns;
删除簇： drop table student; drop table address; drop cluster cluster1; 12.6 临时表 (Temporary Table) 临时表存放在当前
登录的临时表空间下，它被每个session单独使用，即隔离session间的数据，不同session看到的临时表中的数据不一样。 每个session独立
支持rollback，基于事务的临时段在事务结束后收回临时段，基于会话的临时段在会话结束后收回临时段，总之没有 DML锁，没有约束，可以建索引，视图和触发器，由于会产生少量UNDO信息所以会产生少量redo,节省资源，访问数据快。 两种模式： 1）基于事务的临时段：在事务提交时，就会
自动删除记录，on commit delete rows。 2）基于会话的临时段：当
用户退出session 时，才会
自动删除记录,on commit preserve rows。 例：scott： create global temporary table tmp_student(sno int,sname varchar2(10),sage int) on commit preserve rows; 再用Scott开一个session 两边插入记录看看， 你可以在两个session里插入同样的记录，井水不犯河水！ 要
删除临时表，要所有session断开连接，再做
删除。 drop table tmp_table; 12.7 只读表 (11g新特性） 在以前版本中，有只读表空间但没有只读表。11g中
增加了新特性----只读表。
sql> alter table t read only;
sql> update t set id=2; update t set id=2 * 第 1 行出现
错误: ORA-12081: 不允许对表 "SCOTT"."T" 进行更新操作
sql> alter table t read write; 注意点：只读表可以drop，因为只需要在数据字典做
标记，但是不能做DML，另外，truncate也不行，因为它们都在对只读表做写操作。 12.8 压缩表 （11g新特性） 目的：去掉表列中数据存储的重复值，提高空间利用率。对数据仓库类的OLAP有意义（频繁的DML操作的表可能不适用做压缩表） 可以压缩：堆表（若指定表空间则压缩该表空间下所有表），索引表，分区表，物化视图。 主要压缩形式有两种： Advanced 11gR2较之前版本在语法上有了变化 1)Basic table compression 使用direct path loads(缺省），典型的是建立大批量的数据，如：create table as select...结构 Basic对应的语法是： CREATE TABLE ... COMPRESS BASIC; 替换 COMPRESS FOR DIRECT_LOAD OPERATIONS（旧） 2)Advanced row compression 针对OLTP的任何
sql操作。 CREATE TABLE ... COMPRESS FOR OLTP... 代替 CREATE TABLE ... COMPRESS FOR ALL OPERATIONS（旧） 两种压缩的原理类似(PPT-II-481-482)：当insert达到pctfree=阀值（basic对应的pctfree=0,Advanced对应的是pctfree=10)，触发 compress，之后可以继续insert,再达到pctfree，再触发compress....直至compress数据填满block的pctfree以下部分。 压缩的是block中的冗余数据，这对节省db buffer有益。例如一个表有7个columns，5 rows，其中的一些column有重复的行值 2190,13770,25-NOV-00,S,9999,23,161 2225,15720,28-NOV-00,25,1450 34005,120760,29-NOV-00,P,44,2376 9425,4750,I,11,979 1675,46750,19,1121 压缩这个表后，存储形式成为如下，重复值用符号替代。 2190,%,*,1121 那么自然要对这些符号做些说明，相当于有个符号表 Symbol Value Column Rows * 29-NOV-00 3 958-960 % 9999 5 956-960
