堆表

数据以堆的形式管理，增加数据时会使用段中找到的第一个能放下数据的自由空间，我们见到的绝大部分的表都是堆表。堆表是数据库的默认表类型。

最简单的情况是

create table test （c1 varchar2(@H_618_13@10),c2 varchar2(@H_618_13@24),c3 number(@H_618_13@9,@H_618_13@3)) ;

然后使用tom kyte的方法，尽可能简单的创建表，调用DBMS_R_261_11845@etadata.get_ddl函数，查看详细定义，然后再根据这个详细版本，定制自己想要的版本。

set long @H_618_13@5000 SELEct DBMS_R_261_11845@etadata.get_ddl('table','TEST') from dual;

 CREATE table "SCott"."TEST" ( "C1" VARCHAR2(@H_618_13@10),"C2" VARCHAR2(@H_618_13@24),"C3" numbER(@H_618_13@9,@H_618_13@3) ) SEGMENT CREATION DEFERRED PCTFREE @H_618_13@10 PCTUSED @H_618_13@40 INITRANS @H_618_13@1 MAXTRANS @H_618_13@255 NOCOMPRESS LOGGING tableSPACE "USERS"

延迟段创建特性

11g以后的版本，段会延迟到插入数据才创建，如果想立即创建段使用segment creation immediate，默认为defereed
可以使用数据字典表dba_segments或者all_segments确认

create table test （c1 varchar2(@H_618_13@10),@H_618_13@3)) segment creation immediate ;

表空间

如果不指定表空间,则使用表所在用户的默认表空间。
指定表存储在users表空间里：

create table test （c1 varchar2(@H_618_13@10),@H_618_13@3)) tablespace users;

PCTFREE和PCTUSED

PCTUSED如果使用ASSM（自动段空间管理）的话，会被忽略，绝大部分情况是这样的，Oracle也推荐这样。
PCTFREE用于在数据库块里预留空间用于更新，单到达设置比例后不会有新行插入该块，以免产生行迁移.

create table test （c1 varchar2(@H_618_13@10),@H_618_13@3)) pctfree @H_618_13@20;

INITRANS

initrans控制数据库块头为事务预留的事务插槽个数，如果对该表进行插入和更新的事务非常多，建议将该值设置的稍微大一点。

create table test （c1 varchar2(@H_618_13@10),@H_618_13@3)) initrans @H_618_13@10;

buffer_pool

使用什么缓存池来缓存该表的块
一般使用默认设置即可， 即default
对于访问非常频繁的表可以使用 keep池，(一般在其他优化手段测试后才使用这种方法),须首先设置db_keep_cache_size参数。

create table test （c1 varchar2(@H_618_13@10),@H_618_13@3)) storage (buffer_pool keep);

logging和nologging

无论设置成那个在普通DML语句时都要产生日志文件，只有直接路径DML才有区别。绝大部分情况使用logging(默认)。只有在大量加载数据的情况下使用Nologging来加快数据的加载

create table test （c1 varchar2(@H_618_13@10),@H_618_13@3)) nologging storage (buffer_pool keep);

compress和nocompress

在直接路径加载或者传统路径加载启用或者禁用压缩。有nocompress(禁用压缩），
compress对直接路径插入有效
compress for oltp对直接路径和一般路径插入都有效

压缩的效果一般都比较好，如果cpu资源允许的情况下，可以使用压缩提高对数据库缓冲区缓存的使用效率，可以放更加多的数据了，对于全表扫描较多的应用程序来说是一个很好的优化。但是相应的会增加cpu的使用，使用前应该进行相应的测试。测试会不会对数据插入，修改造成影响。非常适合一次写入多次修改类型数据。

使用

设数据库的db_BLOCK_SIZE为@H_618_13@8192
SELEct blocks,avg_row_len*num_rows/@H_618_13@8192,compression,compress_for from user_tables where table_name='xxxxx' 

blocks为实际块数，后一个大约为未压缩时的块数，
对比这两者就可以大概知道要说效率了

检查压缩前和压缩后效果差异

约束

可以在表创建时使用约束或者在表创建后增加、修改或者删除约束

约束分为行内约束和行外约束,行内约束指的是在定义列时候一起定义该列相关的约束。行外约束指的是单独一行定义约束。

create table test （ c1 varchar2(@H_618_13@10) priMary key,c2 varchar2(@H_618_13@24) not null,@H_618_13@3) consTraint c3_check check(c3>@H_618_13@100),constarint c2_check check (c2 in ('China','Japan','USA')) ) ;

其中priMary key 、not null、 c3_check为行内约束，c2_check为行外约束

约束的状态

enable 表示启用
disable 表示不启用
valIDate 表示当前表中所有数据都被验证了
novalIDate 表示当前表中的数据没有被验证。
一般情况只需设置enable valIDate（默认）
其它组合用于对大数据进行ETL时使用节省时间

还有一个表示约束是延迟起效还是立即起效。延迟起效表示在commit完成时进行检查约束是否正确，立即起效表示对该语句处理时进行判断是否满足约束
默认情况是立即起效
延迟起效的语法为
deferred initially immediate|deferred 只有这样设置了的约束才能在事务控制时使用约束控制延迟。

null和not null约束

表示该列可不可以为空

unique约束

表示该列的值在表内必须唯一，但是可以为null

create table test （ c1 varchar2(@H_618_13@10),c3 number（@H_618_13@9,@H_618_13@3）,consTraint pk_test priMary key (c1,c2)， consTraint uk_test unique (c3) ) ;

priMary key约束

这些键表示的值在全表唯一且不为空（主键的所有列都不能为NULL）,可以由单列作为主键或者多列组合作为主键

create table test （ c1 varchar2(@H_618_13@10),c2) ) ;

引用约束

用于表示父子表，使用引用约束的表为子表，被引用约束的表为父表

create table parent （ c1 varchar2(@H_618_13@10),c2) ) ;

create table child （ c1 varchar2(@H_618_13@10),c4 number(@H_618_13@9,@H_618_13@3),consTraint fk_child foreign key (c1,c2) references parent(c1,c2) ) ;
//父表删除一行，子表和其关联的行被删除
create table child1 （ c1 varchar2(@H_618_13@10),c2) on delete cascade ) ;


//父表删除一行，子表和其关联的行被删除
create table child2 （ c1 varchar2(@H_618_13@10),c2) on delete set null ) ;

引用约束还有一个和性能关系很大的条件，如果不对子表的引用列加上索引，当父表更新或者删除时会锁定整个子表。

check约束

用于检查某些条件，大于，小于，在一个集合里面等等

组合上述条件的例子

create table test (c1 varchar2(@H_618_13@10) priMary key， c2 varchar2(@H_618_13@24),consTraint c2_c3_check check(c2 in ('China','USA') and c3>@H_618_13@5.0) enable valIDatE) tablespace users pctfree @H_618_13@20 storage (buffer_pool keep);

索引组织表（IOT）

索引组织表是将数据存储在索引结构里。索引组织表中的数据按照主键存储和排序。索引组织表首先是对于信息的获取非常有利。其次由于是按索引进行存储的，索引的前缀部分相同的键会存储在一起。IOT表对于信息获取，空间应用和olAP相当有用。

IOT表有三个属性很重要

compress N和nocompress

compress N表示对索引的前N项提取公因子。在重复度很高的情况下压缩性非常好。

可以对IOT表的主键使用
analyze index iot_pk valIDate structure;
然后查看index_stats表的opt_cmpr_count获取最优N值。

pctthreshold

表示行数据量超过块大小的这个百分比时，剩余的列放到overflow里去。

including

行中从第一列到该列为止都存储在叶子块里，剩余的列放到overflow里。

@H_109_450@overflow

允许你创建另一个段，当数据行太长时溢出到这个段上来，以使IOT的叶子块尽量容纳更多的行。

IOT表的分区

分区键必须是主键的子集

create table line (point_ID varchar2(@H_618_13@20),line_ID varchar2(@H_618_13@20),x number(@H_618_13@10,y number(@H_618_13@10,loc varchar2(@H_618_13@20),time date,consTraint pk_line priMary key(line_ID,point_ID,time) ) organization index partition by range(time) (partition p0 values less than (to_date('2016-1-1','yyyy-mm-dd')) );

@H_374_502@例子

使用including控制overflow
create table address ( type VARCHAR2(@H_618_13@10),LOCATIOn varchar2(@H_618_13@200),phone varchar2(@H_618_13@20),detail varchar2(@H_618_13@800),consTraint pk_address priMary key (type,LOCATIOn) ) organization index including LOCATIOn overflow;
使用pctthreshold控制overflow
create table address ( type VARCHAR2(@H_618_13@10),LOCATIOn) ) organization index pctthreshold @H_618_13@5 overflow;

使用including和pctthreshold的组合也是可以的，但是一般不是特别有用

外部表

外部表即数据存储在数据库之外的表，在数据导入导出时，有的时候会非常方便。
外部表的引擎有两种一种是较新的OracLE_DATAPUMP，另一种是历史悠久的OracLE_LOADER。其中又以OracLE_DATAPUMP使用起来很方便，以这种引擎为例讲解外部表的创建。

目录和外部表文件

外部表需要使用Oracle里的目录，外部表文件需要放在该目录下，且创建外部表的用户要有目录的读写权限。

在有权限创建目录的用户下使用以下命令。
 create directory dir_external as 'd:\external';
 grant read,write on directory dir_external to scott;

创建外部表

使用OracLE_DATAPUMP引擎的外部表可以对数据库导入也可以导出。可以先从一个数据库卸载你需要的数据，然后将数据带走然后重新装载。

导出

create table emp_ext (empno,ename,job,mgr,hiredate,sal,comm,deptno) organization external ( type Oracle_datapump default directory dir_external LOCATIOn('emp.dmp') ) as SELEct empno,deptno from emp ;
使用时d:\external必须要已经存在，否则会报错。

导入

create table emp_ext (empno number(@H_618_13@4),ename varchar2(@H_618_13@10),job varchar2(@H_618_13@9),mgr number(@H_618_13@4),hiredate date,sal number(@H_618_13@7,@H_618_13@2),comm number(@H_618_13@7,deptno number(@H_618_13@2)) organization external ( type Oracle_datapump default directory dir_external LOCATIOn('emp.dmp') );

SELEct count(*) from emp_ext;
验证数据

分区表

分区是将一个表或者索引分成多个更小，更可管理的部分。逻辑上将只有一个表或索引，对外部使用该表的人而言，就是一个表和普通表没有任何分别，但是在物理上这一个表可以由多个分区组成，每个分区都是一个独立的对象，可以单独处理，或是作为一个更大的部分被处理。

分区表

区间分区

指定存储在一起的数据的区间，比如2016-3-1到2016-4-1的放在分区1,2016-4-1到2016-5-1的放在分区2,等等

常见使用方法有两种，常规区间分区和间隔分区
但是不管是哪种分区方式，和普通表一样，可以在表后面指定PCTFREE、INITRANS、存储性质（基本上就是buffer_pool)、表空间等物理属性。如果分区没有覆写这些属性，则分区和表的这些性质保持一致
当然，每个分区后都可以指定PCTFREE、INITRANS、存储性质（基本上就是buffer_pool)、表空间等物理属性

常规区间分区

语法结构为

partition by range (column_Name)
(partition name1 values less than (value1),partition name2 values less than (valuee2),....
partition last_part values less than (maxvalue));

最后一个小于maxvalue是为了让所有情况都可以被表所包含。这里的小于，指的是严格小于，等于不包含在内。

举个例子

create table log ( text varchar2(@H_618_13@255),rksj date ) pctfree @H_618_13@20 storage(buffer_pool default) partition by range(rksj) ( partition part_2016_3 values less than (to_date('2016-3-1','yyyy-mm-dd')),partition part_2016_4 values less than (to_date('2016-4-1','yyyy-mm-dd')) pctfree @H_618_13@10 storage(buffer_pool keep),partition part_other values less than (maxvalue) ) 

可以使用alter table修改分区的物理属性

alter table log modify partition part_2016_3 storage(buffer_pool keep);

间隔区间分区

间隔分区是从Oracle 11gr1开始新增加的一个特性，以一个分区为起点，设置一个规则（间隔），让Oracle根据该规则知道以后该怎么增加分区。这样就不需要预先设置好所有的分区了，Oracle在插入数据时知道自己去创建分区。间隔分区的键值应该是可以和number、interval进行相加的列。

对于任何合适的区间分区表和间隔分区表，可以使用alter table set interval语句进行相互转化。

语法

使用alter table命令转化间隔分区为范围分区 alter table interval_tab set interval ();//将间隔去除
将范围分区转化为间隔分区
alter table range_tab set interval (@H_618_13@1);//设置间隔为1

一般间隔分区只需创建一个起始分区即可.

create table log ( text varchar2(@H_618_13@255),rksj date ) pctfree @H_618_13@20 storage(buffer_pool default) partition by range(rksj) interval (numtoyminterval(@H_618_13@1,'month')) ( partition part_2016_3 values less than (to_date('2016-3-1','yyyy-mm-dd')) )

间隔分区的缺点

11g开始Oracle增加了interval分区，和range分区最大的区别就是它会根据数据自动去创建分区。
但是它有以下缺点
1. 第一个分区不能删除，因为它是参考，删除会报ora-14758错误。
2. 分区没有便于管理的名称
3. 如果创建索引时指定了多个分区，则这些分区之间是不会应用interval继续分区了。只有大于这其中的值才会开始分区，这样子如果已经有maxvalue的分区了，则不会再生成分区了，这一点一定要注意。

ora-14758错误解决方法
先将间隔分区表转化为普通range分区表
alter table table_name set interval ();
删除指定分区后再将间隔设置回来
alter table table_name set interval (numtodsinterval(1，’DAY’));

@H_605_801@散列分区

散列分区是在一个列或者多个列上引用散列函数，行会按散列值放到不同的分区上去。Oracle建议分区数应该是2的一个幂次方（2,4,8,16，。。。）。

散列分区的目的是让数据很好的分布在多个不同的设备上，或者将数据聚集到更可管理的块上，所以散列键应该是唯一的列或者至少有足够的相异值。以便数据能在多个分区上均匀的分布。

partition  by hash (column1,column2,...) 
(
partition part1 tablespace ts_name1,partition part2,.....

)

其它物理属性不能在这里设置。

或者

partition by hash (column1,...) partitions n store in （ts_name1,ts_name2,...);

create table log
(
ID number(@H_618_13@10),text varchar2(@H_618_13@255),rksj date
)
partition by hash(ID)
(
partition part1  tablespace users,partition part3 tablespace ts_test,partition part4
);


create table log
(
ID number(@H_618_13@10),rksj date
)
partition by hash(ID) partitions @H_618_13@4
store in (users,ts_test);

列表分区

根据离散的值决定数据该放在哪个分区里。

partition by List(column1,...)
(
partition part1 values (value1,value2),partition part2 values (value3,value4),.....
partition part_default (default)
)

如果设置了default分区，则不能再增加分区了，只能删除default分区才能增加分区

create table data ( rawdata raw(@H_618_13@200),STATUS VARChar2(@H_618_13@1) ) partition by List(status) ( partition part_u values ('u') tablespace users storage(buffer_pool keep),partition part_p values ('p')tablespace ts_test,partition part_def values (default) );

引用分区

引用分区是Oracle 11gr1引入的新特性，要以某种方式
对子表进行分区，使得子表的分区和父表的保持一对一的关系。

create table orders ( order# number(@H_618_13@10) priMary key,order_date date,data varchar2(@H_618_13@100) ) partition by range(order_datE) ( partition part_2015 values less than (to_date('2016-1-1',partition part_2016 values less than (to_date('2017-1-1','yyyy-mm-dd')) );

create table order_items ( order# number(@H_618_13@10),item# number(@H_618_13@10),price number(@H_618_13@5,description varchar2(@H_618_13@200),consTraint pk_order_items priMary key(order#,item#),consTraint fk_order_items foreign key(order#) references orders(order#) ) partition by reference (fk_order_items);

组合分区表

的range，List和hash分区，在这些分区的基础上再进行分区。
每种分区都可以进行range，List和hash子分区。

例子

在区间分区的基础上散列分区
create table log ( ID number(@H_618_13@10),text varchar2(@H_618_13@200),logtime date,type VARCHAR2(@H_618_13@1) ) partition by range(logtimE) subpartition by hash(ID) subpartitions @H_618_13@10 store in (users,ts_test) (partition part_2015 values less than (to_date('2016-1-1','yyyy-mm-dd')) );

区间分区上列表分区
create table log ( ID number(@H_618_13@10),type VARCHAR2(@H_618_13@1) ) partition by range(logtimE) subpartition by List(typE) subpartition template ( subpartition part_a values ('A') tablespace users,subpartition parT_B values ('B') tablespace ts_test,subpartition part_cd values ('C','D') tablespace users ) (partition part_2015 values less than (to_date('2016-1-1','yyyy-mm-dd')) );


create table log ( ID number(@H_618_13@10),type VARCHAR2(@H_618_13@1) ) partition by hash(ID) subpartition by List(typE) subpartition template ( subpartition part_a values ('A') tablespace users,'D') tablespace users ) partitions @H_618_13@8 store in (users,ts_test) ;

聚簇表

索引聚簇表

聚簇表的理念是将数据按照我们想要的方式（聚簇）将多个表预联结在一起，即放在同一个块上。聚簇也可用于单个表，按某个列
将数据分组存储。

create cluster emp_dept_cluster (deptno number(@H_618_13@2)) size @H_618_13@1024;

SELEct DBMS_R_261_11845@etadata.get_ddl('CLUSTER','EMP_DEPT_CLUSTER') from dual;

DBMS_R_261_11845@etaDATA.GET_DDL('CLUSTER','EMP_DEPT_CLUSTER')
--------------------------------------------------------------------------------

   CREATE CLUSTER "SCott"."EMP_DEPT_CLUSTER" ( "DEPTNO" numbER(@H_618_13@2,@H_618_13@0) ) SIZE @H_618_13@1024 PCTFREE @H_618_13@10 PCTUSED @H_618_13@40 INITRANS @H_618_13@2 MAXTRANS @H_618_13@255 STORAGE(INITIAL @H_618_13@65536 NEXT @H_618_13@1048576 minextents @H_618_13@1 MAXextents @H_618_13@2147483645 PCTinCREASE @H_618_13@0 FREELIstS @H_618_13@1 FREELIst GROUPS @H_618_13@1 BUFFER_POol DEFAulT FLASH_CACHE DEFAulT CELL_FLASH_CACHE DEFAulT) tableSPACE "USERS" ParaLLEL (DEGREE @H_618_13@1 INSTANCES @H_618_13@1)

其中size是聚簇最重要的一个参数，意思是每个键值大概关联多大的空间，这里是1024B，对于大小为8KB的块可以放下7个键。如果设置的太大会浪费空间，如果设置太小又会过度串链，违背了聚簇就是为了将相关数据放在一起的本意。

向聚簇中放数据之前，首先要为聚簇创建索引，然后就可以创建聚簇表了。聚簇索引的任务就是拿到一个键值，然后返回包含这个键值的块地址。

create index IDx_emp_dept_cluster on cluster emp_dept_cluster;

create table dept (deptno number(@H_618_13@2),dname varchar2(@H_618_13@20),loc varchar2(@H_618_13@20)) cluster emp_dept_cluster(deptno);

create table emp ( empno number(@H_618_13@10) priMary key,name varchar2(@H_618_13@20),mgr number(@H_618_13@10),sal number(@H_618_13@8,deptno number(@H_618_13@2) ) cluster emp_dept_cluster(deptno);

聚簇表没有tablespace这些段属性，因为这些属性都在聚簇上定义。

不适合索引聚簇表的情况

1. 有大量修改的情况
2. 经常需要全表扫描的情况
3. 需要进行分区，聚簇和聚簇表都是不能分区的。
4. 需频繁的truncate和加载，聚簇中的表不能truncate，这是因为聚簇中有多个表放在一起，显然就不能truncate。

适合情况

主要用于读，且通过索引来读，另外会频繁的把信息联结起来使用。

散列聚簇表

基本和索引聚簇表一样，就是将索引换成了散列函数。Oracle获取一列的值，通过散列函数得到一个值，然后通过这个值获得数据所在的块。使用散列的缺点是无法进行 扫描，只要是范围扫描则必须执行全表扫描。

@H_673_1246@块的计算

散列聚簇表的块数是预先分配好的。由散列聚簇表的hashkeys和size加上块的大小得到，即trunc(hashkeys*size/blocksizE)

例子

create cluster hash_cluster (hash_key number(@H_618_13@10) ) hashkeys @H_618_13@10000 size @H_618_13@8192 tablespace users;

create table hash_table1 ( x number(@H_618_13@10),name varchar2(@H_618_13@10) ) cluster hash_cluster(X);

create table hash_table2 ( x number(@H_618_13@10),loc varchar2(@H_618_13@10) ) cluster hash_cluster(X);

散列聚簇表还可以使用单表散列聚簇表

create cluster hash_cluster
(hash_key number(10)
)
hashkeys 10000
size 8192
single table
tablespace users;

临时表

用于保存事务或者会话期间的中间结果。临时表中保存的数据只对当前会话可见，@R_788_6962@，一种是事务一结束数据就被清空。一种是事务结束后依然存在。使用临时表生成的redo数据要少。

create global temporary med
（name varchar2(10),
phone varchar2(20)
)
on commit delete rows;

create global temporary med （name varchar2(10),phone varchar2(20) ) on commit preserve rows;