oracle表相关知识
堆表数据以堆的形式管理,增加数据时会使用段中找到的第一个能放下数据的自由空间,我们见到的绝大部分的表都是堆表。堆表是数据库的默认表类型。 最简单的情况是 create table test (c1 varchar2(10),c2 varchar2(24),c3 number(9,3)) ;
然后使用tom kyte的方法,尽可能简单的创建表,调用dbms_metadata.get_ddl函数,查看详细定义,然后再根据这个详细版本,定制自己想要的版本。 set long 5000 select dbms_metadata.get_ddl('TABLE','TEST') from dual;
CREATE TABLE "SCOTT"."TEST" ( "C1" VARCHAR2(10),"C2" VARCHAR2(24),"C3" NUMBER(9,3) ) SEGMENT CREATION DEFERRED PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING TABLESPACE "USERS"
延迟段创建特性11g以后的版本,段会延迟到插入数据才创建,如果想立即创建段使用segment creation immediate,默认为defereed create table test (c1 varchar2(10),3)) segment creation immediate ;
表空间如果不指定表空间,则使用表所在用户的默认表空间。 create table test (c1 varchar2(10),3)) tablespace users;
PCTFREE和PCTUSEDPCTUSED如果使用ASSM(自动段空间管理)的话,会被忽略,绝大部分情况是这样的,oracle也推荐这样。 create table test (c1 varchar2(10),3)) pctfree 20;
INITRANSinitrans控制数据库块头为事务预留的事务插槽个数,如果对该表进行插入和更新的事务非常多,建议将该值设置的稍微大一点。 create table test (c1 varchar2(10),3)) initrans 10;
buffer_pool使用什么缓存池来缓存该表的块 create table test (c1 varchar2(10),3)) storage (buffer_pool keep);
logging和nologging无论设置成那个在普通DML语句时都要产生日志文件,只有直接路径DML才有区别。绝大部分情况使用logging(默认)。只有在大量加载数据的情况下使用nologging来加快数据的加载 create table test (c1 varchar2(10),3)) nologging storage (buffer_pool keep);
compress和nocompress在直接路径加载或者传统路径加载启用或者禁用压缩。有nocompress(禁用压缩), 压缩的效果一般都比较好,如果cpu资源允许的情况下,可以使用压缩提高对数据库缓冲区缓存的使用效率,可以放更加多的数据了,对于全表扫描较多的应用程序来说是一个很好的优化。但是相应的会增加cpu的使用,使用前应该进行相应的测试。测试会不会对数据插入,修改造成影响。非常适合一次写入多次修改类型数据。 使用 设数据库的db_block_size为8192
select blocks,avg_row_len*num_rows/8192,compression,compress_for from user_tables where table_name='xxxxx'
blocks为实际块数,后一个大约为未压缩时的块数,
对比这两者就可以大概知道要说效率了
检查压缩前和压缩后效果差异 约束可以在表创建时使用约束或者在表创建后增加、修改或者删除约束 约束分为行内约束和行外约束,行内约束指的是在定义列时候一起定义该列相关的约束。行外约束指的是单独一行定义约束。 create table test ( c1 varchar2(10) primary key,c2 varchar2(24) not null,3) constraint c3_check check(c3>100),constarint c2_check check (c2 in ('China','Japan','USA')) ) ;
其中primary key 、not null、 c3_check为行内约束,c2_check为行外约束 约束的状态enable 表示启用 还有一个表示约束是延迟起效还是立即起效。延迟起效表示在commit完成时进行检查约束是否正确,立即起效表示对该语句处理时进行判断是否满足约束 null和not null约束表示该列可不可以为空 unique约束表示该列的值在表内必须唯一,但是可以为null create table test ( c1 varchar2(10),c3 number(9,3),constraint pk_test primary key (c1,c2), constraint uk_test unique (c3) ) ;
primary key约束这些键表示的值在全表唯一且不为空(主键的所有列都不能为null),可以由单列作为主键或者多列组合作为主键 create table test ( c1 varchar2(10),c2) ) ;
引用约束用于表示父子表,使用引用约束的表为子表,被引用约束的表为父表 create table parent ( c1 varchar2(10),c2) ) ;
create table child ( c1 varchar2(10),c4 number(9,3),constraint fk_child foreign key (c1,c2) references parent(c1,c2) ) ;
//父表删除一行,子表和其关联的行被删除
create table child1 ( c1 varchar2(10),c2) on delete cascade ) ;
//父表删除一行,子表和其关联的行被删除
create table child2 ( c1 varchar2(10),c2) on delete set null ) ;
引用约束还有一个和性能关系很大的条件,如果不对子表的引用列加上索引,当父表更新或者删除时会锁定整个子表。 check约束用于检查某些条件,大于,小于,在一个集合里面等等 组合上述条件的例子create table test (c1 varchar2(10) primary key, c2 varchar2(24),constraint c2_c3_check check(c2 in ('China','USA') and c3>5.0) enable validate) tablespace users pctfree 20 storage (buffer_pool keep);
索引组织表(IOT)索引组织表是将数据存储在索引结构里。索引组织表中的数据按照主键存储和排序。索引组织表首先是对于信息的获取非常有利。其次由于是按索引进行存储的,索引的前缀部分相同的键会存储在一起。IOT表对于信息获取,空间应用和OLAP相当有用。 IOT表有三个属性很重要 compress N和nocompresscompress N表示对索引的前N项提取公因子。在重复度很高的情况下压缩性非常好。 可以对IOT表的主键使用 pctthreshold表示行数据量超过块大小的这个百分比时,剩余的列放到overflow里去。 including行中从第一列到该列为止都存储在叶子块里,剩余的列放到overflow里。 overflow允许你创建另一个段,当数据行太长时溢出到这个段上来,以使IOT的叶子块尽量容纳更多的行。 IOT表的分区分区键必须是主键的子集 create table line (point_id varchar2(20),line_id varchar2(20),x number(10,y number(10,loc varchar2(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')) );
例子使用including控制overflow
create table address ( type varchar2(10),location varchar2(200),phone varchar2(20),detail varchar2(800),constraint pk_address primary key (type,location) ) organization index including location overflow;
使用pctthreshold控制overflow
create table address ( type varchar2(10),location) ) organization index pctthreshold 5 overflow;
使用including和pctthreshold的组合也是可以的,但是一般不是特别有用
外部表外部表即数据存储在数据库之外的表,在数据导入导出时,有的时候会非常方便。 目录和外部表文件外部表需要使用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(4),ename varchar2(10),job varchar2(9),mgr number(4),hiredate date,sal number(7,2),comm number(7,deptno number(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,等等 常见使用方法有两种,常规区间分区和间隔分区 常规区间分区语法结构为 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(255),rksj date ) pctfree 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 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 (1);//设置间隔为1
一般间隔分区只需创建一个起始分区即可. create table log ( text varchar2(255),rksj date ) pctfree 20 storage(buffer_pool default) partition by range(rksj) interval (numtoyminterval(1,'month')) ( partition part_2016_3 values less than (to_date('2016-3-1','yyyy-mm-dd')) )
间隔分区的缺点11g开始oracle增加了interval分区,和range分区最大的区别就是它会根据数据自动去创建分区。 ora-14758错误解决方法 散列分区散列分区是在一个列或者多个列上引用散列函数,行会按散列值放到不同的分区上去。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(10),text varchar2(255),rksj date
)
partition by hash(id)
(
partition part1 tablespace users,partition part3 tablespace ts_test,partition part4
);
create table log
(
id number(10),rksj date
)
partition by hash(id) partitions 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(200),status varchar2(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(10) primary key,order_date date,data varchar2(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(10),item# number(10),price number(5,description varchar2(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分区,在这些分区的基础上再进行分区。 例子 在区间分区的基础上散列分区
create table log ( id number(10),text varchar2(200),logtime date,type varchar2(1) ) partition by range(logtime) subpartition by hash(id) subpartitions 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(10),type varchar2(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(10),type varchar2(1) ) partition by hash(id) subpartition by list(type) subpartition template ( subpartition part_a values ('A') tablespace users,'D') tablespace users ) partitions 8 store in (users,ts_test) ;
聚簇表索引聚簇表聚簇表的理念是将数据按照我们想要的方式(聚簇)将多个表预联结在一起,即放在同一个块上。聚簇也可用于单个表,按某个列 create cluster emp_dept_cluster (deptno number(2)) size 1024;
select dbms_metadata.get_ddl('CLUSTER','EMP_DEPT_CLUSTER') from dual;
DBMS_METADATA.GET_DDL('CLUSTER','EMP_DEPT_CLUSTER')
--------------------------------------------------------------------------------
CREATE CLUSTER "SCOTT"."EMP_DEPT_CLUSTER" ( "DEPTNO" NUMBER(2,0) ) SIZE 1024 PCTFREE 10 PCTUSED 40 INITRANS 2 MAXTRANS 255 STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT) TABLESPACE "USERS" PARALLEL (DEGREE 1 INSTANCES 1)
其中size是聚簇最重要的一个参数,意思是每个键值大概关联多大的空间,这里是1024B,对于大小为8KB的块可以放下7个键。如果设置的太大会浪费空间,如果设置太小又会过度串链,违背了聚簇就是为了将相关数据放在一起的本意。 向聚簇中放数据之前,首先要为聚簇创建索引,然后就可以创建聚簇表了。聚簇索引的任务就是拿到一个键值,然后返回包含这个键值的块地址。 create index idx_emp_dept_cluster on cluster emp_dept_cluster;
create table dept (deptno number(2),dname varchar2(20),loc varchar2(20)) cluster emp_dept_cluster(deptno);
create table emp ( empno number(10) primary key,name varchar2(20),mgr number(10),sal number(8,deptno number(2) ) cluster emp_dept_cluster(deptno);
聚簇表没有tablespace这些段属性,因为这些属性都在聚簇上定义。 不适合索引聚簇表的情况
适合情况主要用于读,且通过索引来读,另外会频繁的把信息联结起来使用。 散列聚簇表基本和索引聚簇表一样,就是将索引换成了散列函数。oracle获取一列的值,通过散列函数得到一个值,然后通过这个值获得数据所在的块。使用散列的缺点是无法进行 扫描,只要是范围扫描则必须执行全表扫描。 块的计算散列聚簇表的块数是预先分配好的。由散列聚簇表的hashkeys和size加上块的大小得到,即trunc(hashkeys*size/blocksize) 例子create cluster hash_cluster (hash_key number(10) ) hashkeys 10000 size 8192 tablespace users;
create table hash_table1 ( x number(10),name varchar2(10) ) cluster hash_cluster(x);
create table hash_table2 ( x number(10),loc varchar2(10) ) cluster hash_cluster(x);
散列聚簇表还可以使用单表散列聚簇表 create cluster hash_cluster 临时表用于保存事务或者会话期间的中间结果。临时表中保存的数据只对当前会话可见,分为两种情况,一种是事务一结束数据就被清空。一种是事务结束后依然存在。使用临时表生成的redo数据要少。 create global temporary med create global temporary med (name varchar2(10),phone varchar2(20) ) on commit preserve rows; (编辑:李大同) 【声明】本站内容均来自网络,其相关言论仅代表作者个人观点,不代表本站立场。若无意侵犯到您的权利,请及时与联系站长删除相关内容! |