Oracle表的类型及定义

1、1 表的类型1) 堆组织表 (heap organized tables).当增加数据时 ,将使用在段中找到的第一个适合数据大小的空闲空间.当数据从表中删除时 ,留下的空间允许随后的 insert 和 update重用.2) 索引组织表 .这里表存储在索引结构中 ,利用行本身物理排序 .在堆中 ,数据可能被填到任何适合的地方 ,在索引组织表中 ,根据主关键字 , 以排序顺 序来存储数据 .3) 聚簇表 .这种表完成两件事情 ,第一,许多表物理上连接在一起存储 .通常 ,希望数据在一个数据库块上的一张表里 .对于聚簇表 ,来自许多张表 的数据可能被存储在同一个块上 ;第二,包含相同聚簇码值的所有

2、数据将物理上存储在一起.数据 "聚集"在聚簇码值周围 ,聚簇码用 B*Tree 索引构建 .4) 散列聚簇表 .和上面的聚簇表相似 ,但是不是用 B*Tree 索引有聚簇码定位数据 , 散列聚簇把码散列到簇中 , 来到达数据所在的数据库块 .在散列聚 簇中,数据就是索引 (比喻的说法 ). 这适合用于经常通过码等式来读取的数据 .5) 嵌套表6) 临时表7) 对象表2. 术语1) 高水位标记高水位标记开始在新创建的表的第一个块上 .随着数据不断放到表中 , 使用了更多的块 , 从而高水标记上升 .如果删除一些表中的行 ,高水 标记仍不下移 .即 count(*)100000

3、行和 delete 全部行后 count(*) 所需时间一样 (全扫描情况下 ). 需要对表进行重建 .2) 自由列表 (freelist)在 oracle 中用来跟踪高水标记以下有空闲空间的块对象.每个对象至少有一个 freelist 和它相关 . 当块被使用时 ,oracle 将根据需要放置或取走 freelist. 只有一个对象在高水位标记以下的块才能在 freelist 上发现 . 保留在高水标记以上的块 , 只有 freelist 为空时才能被 用到 .此时 oracle 提高高水标记并把这些块增加到 freelist 中.用这种方式 ,oracle 对一个对象推迟提高高水标记 ,直

4、到必须时才提高 . 一个对象可能不只有一个 freelist, 如果预料会有许多并行用户对一个对象进行大量的 insert 或 update, 配置多个 freelist 能够提高整 体性能 (可能的代价就是增加存储空间 ).3) pctfree 和 pctused如果 pctfree 设置为 10, 那么块在用完 90% 以前 ,都会使用 freelist( 都位于 freelist 中). 一旦达到 90%, 将从 freelist 中移除 ,直到块 上空闲空间超过 60% 以后再使用 (再次进入 freelist 中,当 pctused 为 40 时 ).当 pctfree 设置过小 ,

5、 而经常更新时 ,容易出现行迁移高 pctfree, 低 pctused- 用于插入许多将要更新的数据 ,并且更新经常会增加行的大小 ,这样插入后再块上保留了许多空间 ( 高 pctfree), 在块返回到自由列表之前 ,块必须几乎是空的 ( 低 pctused)低 pctfree, 高 pctused- 用于倾向于对表只使用 insert 或 delete, 或者如果要 update,update 也只是会使行变小 .4) initial,next 和 pctincrease例如使用一个 initial 盘区为 1MB,next 盘区为 2MB,pctincrease 为 50, 可得到盘区

6、应该是 :(1)1mb(2)2mb(2)3mb(2 的 150)(4)4.5mb(3 的 150%). 我认为这些参数是过时的 ,数据库应该使用局部管理并且盘区大小一致的表空间. 在这种方式下 ,initial盘区总是等于 next 盘区的大小 ,并且不必使用 pctincrease, 使用 pctincrease 只会导致表空间产生碎片 . 在没用局部管理表空间的情 况下 ,建议总是设置 initial=next和 pctincrease 等于 zero, 这样可以模拟局部管理表空间的使用 ,为了避免碎片 ,所有在表空间中的对象应该使用相同盘区的分配策略 .5) minextents 和 m

7、axextents 设置控制对象对它自己分配的盘区数 .minextents 为初始分配盘区个数6) logging 和 nologgingnologging 允许对这些对象执行某些操作时不产生重做 . 它只影响一些特定的操作 , 例如开始创建对象或者使用 sqlldr 进行直接路径装 载或者 insert /*+ append */ select 类型的语句 .7) initrans 和 maxtrans对象中的每一块都有一个块头 , 块头的一部分是事务表 ,事务表中的条目描述哪一个事务块上的行/ 元素被锁定了 .事务表的最初大小由对象的 initrans 设置确定 , 对于表 ,默认为 1

8、( 索引默认为 2), 当需要时 ,事务表可以动态地增加 ,大小最多到 maxtrans( 假定在快上有足 够的空闲空间 ), 每一个分配的事务条目在块头上占用 23 字节的存储空间 .2. 堆表 :需要注意的参数为 freelists,pctfree,pctused,initrans 其它参数应该使用局部惯例的表空间 ,不使用 pctincrease,next 等参数3. 索引组织表索引组织表 (index organized tables,iot) 是存储在索引结构中的十分简单的一种表,由于堆方式存储的表是随机组织的,数据存放到任何有空间的地方 , 而数据在 iot 中是根据主码存储和排序

9、的 .iot 特别使用于信息检索 ,空间和 loap 应用程序 对索引块中的数据和溢出段中的数据进行正确的结合是 iot 建立中最关键的部分 . 不同的情形有不同的溢出条件,需要理解它如何影响insert,update,delete 和 select. 如果有一个结构 ,构建一次 , 频繁读取 ,能尽可能地把数据存储在索引块中,如果经常修改结构 ,就必须在让所有数据在索引块上 ( 利用检索 )和在索引中经常重新组织数据 (不利于修改 )之间建立某种平衡 .在堆组织表中堆 freelist 的考虑也适合于 iot. 在 iot 中 ,pctfree和 pctused 有两种作用 ,pctfree

10、 在 iot 中没有在堆组织表中重要,pctused通常不使用.然而当考虑oveflow 段时,pctfree和pctused像在堆组织表中一样有相同的含义，在溢出段中设置条目和在堆组织表中一样使用相同的逻辑 .create table iot(x int,y date,z varchar2(2000),constraint iot_pk primary key(x)organization indexincluding yoveflow;这个表示列 y 及以前的列存储在索引块中create table iot 假设此表有 2kb 大小的块( x int,y date,z varchar2(2

11、000),constraint iot_pk primary key(x)organization indexpctthreshold 10overflow;这个表示 oracle 将从最后一个列向前 ,但是不包括最好一列的主码, 找出哪些列需要存储到溢出段中. 本例中数字列 x 和日期列 y 总是放在索引块中，最后一列乙长度是变化的，当少于约190字节时(2kb块的10%大约是200字节,增加日期的7字节，数字35字节), 将存储到索引块上 ,它超过 190 字节时 ,oracle 将存储 z 的数据列到溢出段中 , 并且设置一个指针指向它 pctthreshold 和 including

12、哪一个好些 ，还是两者的某种结合更好 ?这要更加需要而定 .如果一个应用程序总是或几乎总是使用表中开 始的 4列，并且很少访问最后 5列，这听起来像一个使用 including 的程序.可以包含开始的 4 列，而让其余的 5列存储到溢出段中 .运行 时，如果需要，也可采用像迁移或链接行一样的方法检索.另一方面 ，如果不能确定几乎总是访问这些列和很少访问其它列，可能需要考虑pctthreshold. 一旦确定了每个索引块上平均存储的行数 ，设置 pctthreshold 是很容易的 .假定每个索引块中想存储 20 行，这意味着每 行是块的 1/20(5%)，pctthreshold的值是 5，

13、在索引块上行占用的空间不应超过块的 5%.注意索引组织表一般加上参数 compress 2 比较好 .使用索引组织表的情况 :(1) 当只需访问索引列 ，而不需访问实际的表时 ，即如果表列只有 3 列，而这 3 列都需要是主键时 ，最好采用索引 组织表 .(2) 构建自己的索引结构 ，即用索引组织表建自定义索引eg.create table upper_ename(x$ename，x$rid，primarykey(x$ename，x$rid) organization index as select upper(ename)，rowid from emp;再在 emp 表上创建触发器更新这个索

14、引组织表 .然后就可以用这个索引组织表充当索引delete from (select ename，empno from emp where emp.rowid in (select upper_ename，x$rid from upper_enamewhere x$ename='KING'); 注意如果导出或导入 emp 或在表上使用 alter table move 命令 ，导致 emp 表中行 id 变化 ，则需要重 构索引组织表中的任何索引(3) 当想要加强数据的共同定位 ，或者想要数据按特定的顺序物理存储时 ，iot 就是现成的结构 .4. 索引聚簇表在聚簇中 ，单块上

15、的数据可能来自许多表，概念上可以存储 (预连接)的数据 ，单个表也可以使用聚簇 .现在根据某些列按组存储数据，例如，所有部门10的员工都将存储在同一块上(或者如果不适合，存储在尽可能少的块上),没有存储已排序的数据(这是iot的任务),存储的是 某些码结合的数据 ，但是以堆的方式存储 .因此，部门 100 可能刚好和部门 1 相邻.在聚簇的所有表中 ，关于部门 10 的所有数据都存储到 那个块上 ，如果部门 10 的所有数据在这个块中容纳不下 ，额外的块将链接到最初的块 ，来包容溢出的数据 .这种方式和在 iot 中的溢出块 非常相似 .对象存储的定义 (pctfree，pctused，ini

16、tial等等)是和 cluster 相关的 ，因为在聚簇中有许多表 ，每个表在同一个块中拥有不同的 pctfree是没有意义的 .create cluster emp_dept_cluster(deptno number(2) size 1024;这个聚簇的聚类列是 deptno 列，在表中的这个列不必称为 deptno， 但是必须是 number(2) 来匹配这个定义 .定义中有一个 size1024 选项，是用来告诉 oracle 预计有大约 1024 字节数据和每个聚簇码相关 .oracle 将使用这些信息来计算每个块 能容纳的最大聚簇码数目，假定有一个8kb的块,oracle在每个数据

17、库块中将容纳达七个聚簇码(如果数据比预料的大，数目可能减少).这就是说 ，部门 10，20，30，40，50，60，70 的数据趋向存在于一个块上 ，插入部门 80， 一个新块将会被使用 .这不是说数据以分类方式存 储，只是意味着 ，如果按照这个顺序插入部门，它们自然趋向于被放到一起.数据的大小和插入顺序都会影响每块上可以存储的码数 .因此容量 (size) 参数控制每块上聚簇码的最大数目，太高会浪费空间 ，太低将得到过多的数据链接，这将偏离聚簇的目的 ，把所有数据存 储到一起 ,放到一个块上 ,容量 (size) 参数是聚簇的重要参数 .在把数据放入之前 ,需要索引聚簇 , 可以立刻在聚簇中

18、创建表,但将同时创建表和为表装入数据,这样在拥有任何数据之前 ,需要聚簇索引create index emp_dept_cluster_idx _disibledevent=x.deptno;end loop;end;如果先装载了所有的 dept 行,由于 dept 行非常小 ,只有两个字节 ,很显然已经得到了每个块中的七个码 (基于设置的 size 1024) 当轮 到装载 emp 行时 ,可能发行一些部门有超过1024 字节的数据 ,这将导致这些聚簇码块的过多链接.通过同时使用指定的聚簇码装载所有的数据 , 把块压缩得最紧 ,用完空间才开始使用一个新块,不是让 oracle 再每块中放置七

19、个聚簇码值 , 而是放置尽可能多得聚簇码值.什么时候使用聚簇 ? 实际上可能更容易描述何时不使用聚簇聚簇可能消极地影响 dml 得性能 - 如果预料聚簇中得表会有较大得修改就必须知道索引聚簇将可能有降低性能得副作用在聚簇中 , 全扫描表会受到影响 - 不仅仅全扫描一个表中得数据,而是必须全扫描许多表得数据如果相信将经常 truncate 和装载表 - 聚簇中得表不能截断 .因此 ,如果大部分是读取数据 ,并且通过索引来读取 ,要么是聚簇码索引 ,要么方在聚簇表得其它索引 ,还经常把这种信息放在一起,这样使用聚簇合适 .5. 散列聚簇表和索引聚簇表在概念上很相似, 主要区别为散列函数代替了聚簇码

20、索引.表中得数据就是索引 ,却没有物理索引 .oracle 采用行得码值 ,使用内部函数或提供得函数对它进行散列运算利用这些来指定数据应放在硬盘得位置.使用散列算法来定位数据得副作用是没有在表中增加传统得索引，因此就不能区域(range)扫描散列聚簇中得表.在上面得索引聚簇中，查询:select * from emp where deptno between 10 and 20 能够使用聚簇码索引找到这些行.在散列聚簇中 .除非在 deptno 列上有索引 ,否则这个查询将导致全表扫描.没有使用支持区域扫描得索引 ，只能够在散列码中执行精确得等式搜索 .在完美情况下 ，散列聚簇意味着可以通过一

21、个 i/o 直接从查询中得到 数据，这与散列算法很少甚至没有冲突 .现实情况下 ，大部分情况可能是会发生冲突得 ，并且有周期性得行链接 ，意味着检索一些数据需要 多个 i/o.数据库中得散列表有固定的 "大小"， 当创建表时 ，必须确定表将最终有的散列码数 ，其中并不限制插入的行数 .散列聚簇从开始就需要分配 .oracle 得到 hashkeys/trunc(blocksize/ size) 就会立即分配空间 .只要在聚簇中放置了第一张表 ，任何全 扫描都会达到所有分配的块散列聚簇中的 hashkey 数是固定大小的 .能限制为这个簇产生的唯一散列码的数量.如果设置太低

22、，由于预料不到的散列冲突可能影响性能.在聚簇码上的区域扫描是不能用的 .以下情形散列聚簇是合适的 :在一定程度上精确知道在整个过程中会有多少行 ，或者如果合理的上 限，正确地设置 hashkey 大小 size 参数.对避免重构是关键的 .DML， 尤其是插入 ，不要大量执行 .总是通过 hashkey 值经常访问数据 .6) 临时表create global temporary table temp_table_sessionon commit preserve rowsasselect * from scott.emp where 1=0;on commit preserve rows 语

23、句使之成为基于会话的临时表 ，行将留在此表中 ，直到会话断开或通过 delete 或 truncate 从物理上删 除这些数据 ，只有自己的会话能看到这些行 .其它会话不能看到 "我的"行数据 ，即使是在 commit 之后.create global temporary table temp_table_transaction on commit delete rows asselect * from scott.emp where 1=0;on commit delete rows使之成为基于事务的临时表 . 当会话提交后 , 行消失 .通过简单地恢复分配到表的临时盘区

24、 ,行就会消失 .在这个临时表自动清除过程中不涉及额外开销 .对于每一个数据库 , 创建所有的 temp 表作为全局临时表 .这将作为应用程序安装的一部分完成 .就像创建永久表一样 .只要在过程中简 单使用即可 . 即不要在存储过程中创建临时表 .临时表可能有触发器 ,检查约束 ,索引等等 . 但是不支持如下 :没有用作参照完整性约束 - 既不能是外码的目标 , 也不能在上面定义外码 .不能有 varray 或者 nested table 类型的列 ,不能是索引组织表 ,不能是索引或散列聚簇 不能分区 . 通过 analyze 表命令不能产生统计信息 .在 oracle 中使用临时表是没有必要

25、的 （如果是为了避免查询查询中涉及到的表太多, 而将子查询结果放到临时表中的话 ）然而在其它情况 , 在程序中使用临时表是正确的方法由于 analyze 命令不能在临时表中收集统计信息 ,必须使用手动方法 ,把临时表的有代表性的统计信息装载到数据字典.例如 ,如果临时表中行的平均数量是 500, 平均行大小是 100 字节和块的数量是 7. 只使用begindbms_stats.set_table_stats（ ownname=>user ,tabname=>'T',numrows=>500,numblks=>7,avgrlen=>100）;end

26、;现在 ,优化器不能使用它的猜测 ,而使用我们的猜测结果 .或者 :删除临时表一段时间 ,创建一个名字和结构相同的永久表 ,并用代表性的数据来装载 ,然后尽可能彻底地分析这张表 （也可能产生柱 状图等等 ） 并且使用 dbms_stats 输出这张永久表的统计信息 ,然后删除这张永久表 ,重新创建临时表 ,然后所有需要做的就是输入代表 性的统计信息和让优化器正确地工作 .在任何情况下 , 访问超过表的 10%-20%, 都不应该使用索引 . 取出统计信息begin end;dbms_stats.create_stat_table(ownname=>user,stattab=>

27、9;STATS');dbms_stats.export_table_stats(ownname=>user,tabname=>'TEMP_ALL_OBJECTS',stattab=>'STATS');dbms_stats.export_index_statsownname=>user,indname=>'TEMP_ALL_OBJECTS_IDX',stattab=>'STATS');end;导入统计信息begindbms_stats.import_table_stats(ownname=

28、>user,tabname=>'TEMP_ALL_OBJECTS',stattab=>'STATS');dbms_stats.import_index_stats(ownname=>user,indname=>'TEMP_ALL_OBJECTS_IDX',stattab=>'STATS');在应用程序中临时表是有用处的 ,可以临时存储其它表 ,会话或事务需要的一组集 .这并不意味着用来把单个较大的查询 "分成 "可以重 新连接在一起的小结果集 （ 在其它数据库中 ,这好像是临

29、时表最流行的用法 ）当有机会把一系列到临时表的 insert 写成以一个大查询的 形势的 select 时 ,执行速度会大大加快 。Oracle 索引B*Tree 索引B*Tree索引是最常见的索引结构，默认建立的索引就是这种类型的索引。B*Tree索引在检索高基数数据列(高基数数据列是指该列有很多不同的值)时提供了最好的性能。当取岀的行数占总行数比例较小时B-Tree索引比全表检索提供了更有效的方法。但当检查的范围超过表的10%时就不能提高取回数据的性能。B-Tree索引是基于二叉树的，由分支块(branch block )和叶块(Ieaf block )组成。在树结构中，位于最底层底块被称

30、为叶块，包含每个被索引列的值和行所对应的rowid。在叶节点的上面是分支块，用来导航结构，包含了索引列(关键字)范围和另一索引块的地址，如图26-1所示。假设我们要找索引中值为80的行，从索引树的最上层入口开始，定位到大于等于50，然后往左找，找到第 2个分支块，定位为75 - 100，最后再定位到叶块上，找到80所对应的rowid，然后根据rowid去读取数据块获取数据。如果查询条件是范围选择的，比如 where column >20 and column <80，那么会先定位到第一个包含20的叶块，然后横向查找其他的叶块，直到找到包含80的块为止，不用每次都从入口进去再重新定位。反向索引反