详解ORACLE簇表、堆表、IOT表、分区表
oracle分表机制
Oracle的分表机制主要是通过分区表（Partitioned Table）来实现的。
分区表是一种特殊的数据库对象，它可以将一张大表分割成多个小表，这些小表在逻辑上是一个整体，在物理上可以存储在不同的表空间中。
分区表可以提高查询效率，简化数据管理，提高数据可用性。
Oracle的分区表主要有如下特点：
1. 分区表的数据可以分散存储在多个表空间中，可以提高并发处理能力和查询性能。
2. 分区表可以按照时间、范围或者其他方式进行分区，便于数据的管理和维护。
3. 分区表在逻辑上是一个表，对应用来说是透明的，应用程序可以直接访问分区表，无需知道数据具体存储在哪里。
Oracle的分区表可以通过两种方式创建：
1. 静态分区：在创建表的时候就定义好分区，分区键必须是在创建表时定义的列。
2. 动态分区：在表创建后还可以进行分区，分区键可以是表中的任何列。
总的来说，Oracle的分表机制通过分区表可以提高数据库的处理能力，提高数据的可用性和管理性。
数据库优化与表
11G新特性：oracle的几种表1、堆表数据零散的分在表各个位置，数据没有顺序最常见的表好处：再分配空间的时候比较随意坏处：在存放数据的时候随意存放对于堆表来说，通过索引访问表代价索引访问表的代价集群因子的代价集群因子说白了就是索引访问表集群因子理想值是表的块数，最不理想值是等于表的行数平台数据抽取导致索引的集群因子=表的行数，而且还导致了oracle优化器在计算成本的时候，错误的将集群因子高的表数据，认为在磁盘上--查看集群因子select*from user_indexes;--多块读的参数db_file_multiblock--查看表UNIT的信息，块数行数等select*from user_tables t where t.TABLE_NAME='UNIT';表数据整理就是整理集群因子SQL> show parameter mulNAME TYPEVALUE------------------------------------ ----------- ------------------------------db_file_multiblock_read_count integer 16parallel_adaptive_multi_user boolean TRUE2、IOT表索引组织表里面存在主键，这个表在存储的时候按照主键列排序，后面的数据跟着主键列一块排，在主键列上直接建一颗树，这样的话没有集群因子的说法，这样的话成本非常低，坏处是导致这个树很高对于IOT表来讲，如果说是做insert插入的时候如果是按照主键列的顺序插入这个时候很低的，但是如果说不是按照主键列顺序不规律的插入这样成本比较高好处：如果通过主键列取数据，特别是批量取数据，性能极高，尤其是超过了10%可以通过溢出来解决树高的问题，也就是将不经常访问的列可以放在别的表中，IOT表中只是存放的是经常访问的列这样的话树高就会降低坏处：1、如果在insert的时候，出现主键列不规律的状况，会出现块分裂的状况，影响insert性能2、如果不走主键列，性能相对较差3、可能导致树很高，因为表的数据列很多，而且有可能有大列，比如说blob、clob，clob一般用来放字符串，blob一般用来放图片，声音可以放在磁盘上没有办法告诉oracle有多少索引数据在内存中，但是 oracle有相关的参数告诉oracle有索引在内存中，这个参数是optimizer_index_caching假如是这个是100那就说明所有的索引都在内存中，默认的是0SQL> alter system set optimizer_index_caching=100; 可以这样来设置下SQL> show parameter indNAME TYPEVALUE------------------------------------ ----------- ------------------------------optimizer_index_caching integer 0optimizer_index_cost_adj integer 100skip_unusable_indexes boolean TRUEuse_indirect_data_buffers boolean FALSE3、cluster table在产生表关联的时候会出现很大的负荷，如果说一个数据库中经常发生有两个表的关联那么这个时候，oracle就会将这两个表整合在一起，将他们关联的结果集放在一个表中，这张表就是一个关联表，这样将来取的时候直接查这个表就可以了，但是将来插入数据的时候就会出现问题，所以这样的情况只适合数据仓库，假如说数据仓库有6张表经常做关联，那么就会将这六张表整合成一张表，将来在数据仓库中取数据的时候，就可以直接在这里面取了特别适合不怎么更新的表，因为他的更新性能很差OLTP里面一般不用虚拟列这个是不存储的，但是将来可以查到的，虚拟列主要是为了可读性在11G的时候还可以在虚拟列上建索引，可以在这个列上收集统计信息虚拟列的限制表压缩正常的情况下满足第一范式的时候是没有重复列的，压缩的意义不是很大4、临时表多个会话使用临时表的时候只能看到自己的数据，会话断了后里面的数据就没有了，会话commit后数据也没有了什么时候使用临时表？如果说一个业务流程多个地方要使用到相同的数据，这个时候可以使用临时表，如果说只有一个地方使用，这个时候就没有必要建立临时表了，因为只需要查一次就可以了，这就不需要使用临时表，并不是查询一次多次使用临时表使用要谨慎创建会话临时表on commit preserve rows表示commit的时候不删除数据，断开连接的时候删除创建事务临时表5、外部表1、数据库里面有一个空表2、对应着os上有一个txt文件，列是一一对应的可以正常的访问外部表，数据从os上的文件中读取1、节省了数据导入到数据库的时间2、增加了日常读取数据的时间3、每次物理读，而且没有统计信息，容易造成执行时间不准确特别适合，数据库会读取，但是很少读取的情况，而且不在意读取花费的时间这个适合数据仓库6、分区表做出来的分区是独立的段，可以单独访问一个段，甚至可以将其中一个段删除，整理，分区的好处是分成了物理上独立的段全分区扫描分区1、对于分区，可以物理上独立管理2、对于分区，如果出现一个分区损坏，可以正常访问其他分区3、对于分区，可以实现分区裁剪，这是对我们意义最大的，特别是集群因子很高，oracle经常走全表扫描，导致IO居高不下，影响生产全表扫描变成了实现分区裁剪后的全表扫描，只是扫描几个分区，IO大幅降低性能往往大于走索引走索引、走嵌套（每次读取数据量比较大、而且集群因子很高的情况下）1、只是访问内存数据、性能高2、但是往往会导致逻辑读高如果实现了分区消除与上面相比1、往往访问内存数据，性能高2、同时逻辑读低分区可以避免全表扫描全表扫描的特点：1、物理IO很高2、逻辑IO相对较低实现了分区既兼顾了物理IO又兼顾了逻辑IOCREATE TABLE "SCOTT"."T10" ( "ID" NUMBER, "NAME" VARCHAR2(20), CONSTRAINT "P_1" PRIMARY KEY ("ID")NOVALIDATE ) TABLESPACE "U SERS" PCTFREE 20 PARALLEL 4oracle 11g的分区表1、根据范围分区最经典的就是按照时间分区可以符合分区键2、按照间隔分区p0、p1、p2、p3是范围分区，像p3这个分区是只要是大于1-7-2008小于1-1-2009的时间都放在这个分区中只要是大于1-1-2009这个时间的就再建一个分区一般是按照时间的间隔分区的较多11g新特性：适合按天建立分区，节省建立分区的操作3、hash 分区现在有一个表，要批量的往里面导数据，这个时候就很容易发生段头块的争用，因为有很多人在批量的往里面导数据，这个时候建hash分区，hash分区一般是2的n次方，这个时候这个表就有了4个分区，有了4个分区后就有了4个段头块，这样的话有人再批量的往里面导数据的时候就会均匀的分布到4个分区中，再有人批量导数据的时候，也是这样主要解决段头块的争用1、分区键没有明显的分区依据2、分区数目2的幂次方3、范围查找，不能实现分区的消除分区消除是分区必须在where条件里where id between 1 and 10;where id=1;hash 分区是为了纯粹的分区而分区，一般不作为主分区4、散列分区散列分区只要指定了在哪个列上，分区的个数，不要指定范围，ts1、ts2、ts3、ts4是4个分区分别对应在4个表空间上，要是这4个表空间在4个磁盘上的话，可以实现IO的负载均衡，不过对于现在的存储来说没有多大的意义，因为本身就实现了虚拟化，在底实现了条带化5、列表list分区就像我们的生产中可以按照我们的机台来划分（赛轮）主要集中类似于地域的情况6、引用分区 11g中比较好的主表和子表都是用了分区消除主表进行分区子表进行分区根据外键来进行分区，外键引用了主表的主键，主键如何进行分区，那么外键就如何进行分区6、虚拟列分区7、系统分区就是在建表的时候将一个表分为4个分区，也没有分区列，但是在插入数据的时候要给它指定插入到哪个分区中，但是访问更新的时候不需要指定是哪个分区列，不容易实现分区消除，好处是不论对哪个行更新都不存在行移动的问题，很灵活8、组合分区1、先根据范围再根据散列2、现根据范围再根据列表3、间隔范围分区说白了就是范围分区的自动化分区4、间隔-列表分区分区是独立的可以drop掉的分区在管理上的特点1、独立操作2、分区置换对分区的操作：1、增加分区2、分割一个分区split partition 这个是比较消耗资源的，因为这个是真真正正要将数据搬家的3、合并分区使用merge partition 命令4、重命名分区rename partition5、交换分区exchange partition6、删除分区drop partition7、接合分区coalesce partition 这个用的比较少管理表的数据字典视图1、DBA_TABLES数据字典视图2、DBA_TAB_PARTITIONS3、DBA_TAB_CULUMNS全局分区索引的性能就是比一般的索引的性能好一点点建局部索引的时候最好加上分区键，并且在查询的时候在条件中使用这一列局部分区索引的维护成本是最低的一个正在使用的索引失效可能带来灾难如何找到一些分区的信息SQL> selectdbms_metadata.get_ddl('TABLE','OREDRS',schema=>'U2') from dual;--查索引状态select*from dba_indexes where status !='VALID';如果说这个索引的状态status是N/A的话那么说明是分区索引--进一步详细查看索引的情况select*from dba_ind_partitions d where d.index_owne r='U2';--进一步确认我们索引的范围分区select*from dba_views where view_name like'%IND%'; select*from dba_part_indexes i1 where i1.owner='U2'; --在这里面还有一列是locality这里面一定要知道是global还是local知道了上面的才能对一个分区进行正确的操作，比如说drop，如果说不知道具体的情况，就把这个区drop掉，这个时候索引就会失效，导致出现灾难性的状况。
堆表（HOT）和索引组织表（IOT）优缺点
堆表（HOT）和索引组织表（IOT）优缺点⼀、堆表和索引组织表NOTE堆表也可以称之为 HOT，索引组织表也可以称之为 IOT，下⾯没有特别说明，两者都是⼀个意思。
堆（heap）组织表数据⾏在堆中存储，没有任何特定顺序，向⼀个全新的没有做过更新和删除的堆中插⼊⼀⾏时候，总是 append 到堆表⽂件的最后⼀页当中。
因为不⽤考虑排序，所以插⼊速度会⽐较快。
但是要查找符合某个条件的记录，就必须得读取全部的记录以便筛选。
⽽这个时候为了加快查询速度，索引就出现了，索引是针对少量特定字段的值拿出来进⾏排序存储，存储索引 key 以及数据⾏在堆表上⾯的绝对位置（页号，页内偏移），⽽因为索引是有序的，所以就会很容易通过索引查询到具体的记录位置（普遍使⽤⼆分查找法），然后再根据记录位置直接从表中读取该记录。
同时因为索引的字段较少，所以索引通常会⽐其基表⼩得多。
从上⾯通过索引访问表记录的⽅式可以看出，当要访问的数据量较⼤时，通过每⼀条记录的位置去访问原始记录，每⼀条符合条件的记录都需要经过索引访问后再访问基表这样⼀个复杂的过程，这会花费很多时间。
同样，如果不经过索引⽽直接查询表，也可能因为表字段太多，记录较⼤的情况下把全部的数据读取进来，这也会花费很多时间。
那怎么办呢？这个时候就会想到，如果表中数据本⾝就是有序的，这样查询表的时候就可以快速的找到符合条件的记录位置，⽽很容易判断符合条件记录的位置，这样只需要读取⼀⼩部分数据出来就可以了，不需要全表记录都读取出来进⾏判断。
索引组织表就这样产⽣了，当然索引表中插⼊，更新的时候可能会因为需要排序⽽将数据重组，这时候数据插⼊或更新速度会⽐堆组织表慢⼀些。
如果堆组织表上有索引，那么对堆组织表的插⼊也会因为要修改索引⽽变慢。
⼆、堆表和索引组织表的⽐较所以，堆表的特点就是索引和数据分开，所有索引都是⼆级索引，或叫辅助索引。
所以主键索引也是⼆级索引，没有完整记录，区别只有唯⼀或⾮唯⼀。
oracle分区表的用法
oracle分区表的用法Oracle分区表是在Oracle数据库中一种高效管理和处理大量数据的技术。
通过将表按照特定的分区方案进行拆分，可以将数据存储在多个分区中，从而提高查询和维护的效率。
下面是对Oracle分区表的用法的详细介绍。
1. 分区表的概念和优势分区表是将表按照特定规则进行拆分存储的一种技术。
拆分的依据可以是数据的范围、列表、哈希或者设备。
分区表的优势主要包括：- 提高查询效率：分区表可以仅查询特定分区的数据，从而加速查询操作。
- 提高维护效率：对于数据的增加、删除、修改等操作，分区表可以仅针对特定分区进行操作，减少操作的范围和影响。
- 增加可用性：通过在不同的物理存储设备上存储不同的分区，可以提高系统的可用性和容灾能力。
- 支持历史数据归档：可以将历史数据存储在不同的分区中，并设置不同的存储周期和归档策略。
2. 分区表的创建和管理创建分区表的语法格式如下：```CREATE TABLE table_name(column1 datatype [ NULL | NOT NULL ],column2 datatype [ NULL | NOT NULL ],...)PARTITION BY partitioning_method (partitioning_columns)(PARTITION partition_name VALUES (partition_value),PARTITION partition_name VALUES (partition_value),...)```其中，partitioning_method可以是范围分区（RANGE）、列表分区（LIST）、哈希分区（HASH）或者设备分区（SYSTEM）等。
partition_value是分区依据的取值。
管理分区表可以使用以下命令：- 增加分区：ALTER TABLE table_name ADD PARTITION partition_name VALUES (partition_value);- 删除分区：ALTER TABLE table_name DROP PARTITION partition_name;- 合并分区：ALTER TABLE table_name MERGE PARTITIONS partition_name1, partition_name2 INTO partition_name;- 分离分区：ALTER TABLE table_name SPLIT PARTITION partition_name1 AT (value) INTO PARTITION partition_name2, PARTITION partition_name3;3. 分区表的查询查询分区表可以使用普通的SELECT语句，也可以根据需要仅查询特定的分区，以提高查询效率。
Oracle表的类型及定义
1表的类型1)堆组织表(heap organized tables).当增加数据时,将使用在段中找到的第一个适合数据大小的空闲空间.当数据从表中删除时,留下的空间允许随后的insert和update 重用.2)索引组织表.这里表存储在索引结构中,利用行本身物理排序.在堆中,数据可能被填到任何适合的地方,在索引组织表中,根据主关键字,以排序顺序来存储数据.3)聚簇表.这种表完成两件事情,第一,许多表物理上连接在一起存储.通常,希望数据在一个数据库块上的一张表里.对于聚簇表,来自许多张表的数据可能被存储在同一个块上;第二,包含相同聚簇码值的所有数据将物理上存储在一起.数据"聚集"在聚簇码值周围,聚簇码用B*Tree索引构建.4)散列聚簇表.和上面的聚簇表相似,但是不是用B*Tree索引有聚簇码定位数据,散列聚簇把码散列到簇中,来到达数据所在的数据库块.在散列聚簇中,数据就是索引(比喻的说法).这适合用于经常通过码等式来读取的数据.5)嵌套表6)临时表7)对象表2.术语1)高水位标记高水位标记开始在新创建的表的第一个块上.随着数据不断放到表中,使用了更多的块,从而高水标记上升.如果删除一些表中的行,高水标记仍不下移.即count(*)100000行和delete全部行后count(*)所需时间一样(全扫描情况下).需要对表进行重建.2)自由列表(freelist)在oracle中用来跟踪高水标记以下有空闲空间的块对象.每个对象至少有一个freelist和它相关.当块被使用时,oracle将根据需要放置或取走freelist.只有一个对象在高水位标记以下的块才能在freelist上发现.保留在高水标记以上的块,只有freelist为空时才能被用到.此时oracle提高高水标记并把这些块增加到freelist中.用这种方式,oracle对一个对象推迟提高高水标记,直到必须时才提高.一个对象可能不只有一个freelist,如果预料会有许多并行用户对一个对象进行大量的insert或update,配置多个freelist能够提高整体性能(可能的代价就是增加存储空间).3)pctfree和pctused如果pctfree设置为10,那么块在用完90%以前,都会使用freelist(都位于freelist中).一旦达到90%,将从freelist中移除,直到块上空闲空间超过60%以后再使用(再次进入freelist中,当pctused为40时).当pctfree设置过小,而经常更新时,容易出现行迁移高pctfree,低pctused---用于插入许多将要更新的数据,并且更新经常会增加行的大小,这样插入后再块上保留了许多空间(高pctfree),在块返回到自由列表之前,块必须几乎是空的(低pctused)低pctfree,高pctused---用于倾向于对表只使用insert或delete,或者如果要update,update也只是会使行变小.4)initial,next和pctincrease例如使用一个initial盘区为1MB,next盘区为2MB,pctincrease为50,可得到盘区应该是:(1)1mb(2)2mb(2)3mb(2的150)(4)4.5mb(3的150%).我认为这些参数是过时的,数据库应该使用局部管理并且盘区大小一致的表空间.在这种方式下,initial盘区总是等于next盘区的大小,并且不必使用pctincrease,使用pctincrease只会导致表空间产生碎片.在没用局部管理表空间的情况下,建议总是设置initial=next和pctincrease等于zero,这样可以模拟局部管理表空间的使用,为了避免碎片,所有在表空间中的对象应该使用相同盘区的分配策略.5)minextents和maxextents设置控制对象对它自己分配的盘区数.minextents为初始分配盘区个数6)logging和nologgingnologging允许对这些对象执行某些操作时不产生重做.它只影响一些特定的操作,例如开始创建对象或者使用sqlldr进行直接路径装载或者insert /*+ append */ select类型的语句.7)initrans和maxtrans对象中的每一块都有一个块头,块头的一部分是事务表,事务表中的条目描述哪一个事务块上的行/元素被锁定了.事务表的最初大小由对象的initrans设置确定,对于表,默认为1(索引默认为2),当需要时,事务表可以动态地增加,大小最多到maxtrans(假定在快上有足够的空闲空间),每一个分配的事务条目在块头上占用23字节的存储空间.2.堆表:需要注意的参数为freelists,pctfree,pctused,initrans其它参数应该使用局部惯例的表空间,不使用pctincrease,next等参数3.索引组织表索引组织表(index organized tables,iot)是存储在索引结构中的十分简单的一种表,由于堆方式存储的表是随机组织的,数据存放到任何有空间的地方,而数据在iot中是根据主码存储和排序的.iot特别使用于信息检索,空间和loap应用程序对索引块中的数据和溢出段中的数据进行正确的结合是iot建立中最关键的部分.不同的情形有不同的溢出条件,需要理解它如何影响insert,update,delete和select.如果有一个结构,构建一次,频繁读取,能尽可能地把数据存储在索引块中,如果经常修改结构,就必须在让所有数据在索引块上(利用检索)和在索引中经常重新组织数据(不利于修改)之间建立某种平衡.在堆组织表中堆freelist的考虑也适合于iot.在iot中,pctfree和pctused有两种作用,pctfree在iot中没有在堆组织表中重要,pctused通常不使用.然而当考虑overflow段时,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(2000),constraint iot_pk primary key(x))organization indexpctthreshold 10overflow;这个表示oracle将从最后一个列向前,但是不包括最好一列的主码,找出哪些列需要存储到溢出段中.本例中数字列x和日期列y总是放在索引块中,最后一列z,长度是变化的,当少于约190字节时(2kb块的10%大约是200字节,增加日期的7字节,数字3~5字节),将存储到索引块上,它超过190字节时,oracle将存储z的数据列到溢出段中,并且设置一个指针指向它pctthreshold和including哪一个好些,还是两者的某种结合更好?这要更加需要而定.如果一个应用程序总是或几乎总是使用表中开始的4列,并且很少访问最后5列,这听起来像一个使用including的程序.可以包含开始的4列,而让其余的5列存储到溢出段中.运行时,如果需要,也可采用像迁移或链接行一样的方法检索.另一方面,如果不能确定几乎总是访问这些列和很少访问其它列,可能需要考虑pctthreshold.一旦确定了每个索引块上平均存储的行数,设置pctthreshold是很容易的.假定每个索引块中想存储20行,这意味着每行是块的1/20(5%),pctthreshold的值是5,在索引块上行占用的空间不应超过块的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表上创建触发器更新这个索引组织表.然后就可以用这个索引组织表充当索引delete from (select ename,empno from emp where emp.rowid in (select upper_ename,x$rid from upper_ename where x$ename='KING'));注意如果导出或导入emp或在表上使用alter table move命令,导致emp表中行id变化,则需要重构索引组织表中的任何索引(3)当想要加强数据的共同定位,或者想要数据按特定的顺序物理存储时,iot就是现成的结构.4.索引聚簇表在聚簇中,单块上的数据可能来自许多表,概念上可以存储(预连接)的数据,单个表也可以使用聚簇.现在根据某些列按组存储数据,例如,所有部门10的员工都将存储在同一块上(或者如果不适合,存储在尽可能少的块上),没有存储已排序的数据(这是iot的任务),存储的是某些码结合的数据,但是以堆的方式存储.因此,部门100可能刚好和部门1相邻.在聚簇的所有表中,关于部门10的所有数据都存储到那个块上,如果部门10的所有数据在这个块中容纳不下,额外的块将链接到最初的块,来包容溢出的数据.这种方式和在iot中的溢出块非常相似.对象存储的定义(pctfree,pctused,initial等等)是和cluster相关的,因为在聚簇中有许多表,每个表在同一个块中拥有不同的pctfree 是没有意义的.create cluster emp_dept_cluster(deptno number(2)) size 1024;这个聚簇的聚类列是deptno列,在表中的这个列不必称为deptno,但是必须是number(2)来匹配这个定义.定义中有一个size1024选项,是用来告诉oracle预计有大约1024字节数据和每个聚簇码相关.oracle将使用这些信息来计算每个块能容纳的最大聚簇码数目,假定有一个8kb的块,oracle在每个数据库块中将容纳达七个聚簇码(如果数据比预料的大,数目可能减少).这就是说,部门10,20,30,40,50,60,70的数据趋向存在于一个块上,插入部门80,一个新块将会被使用.这不是说数据以分类方式存储,只是意味着,如果按照这个顺序插入部门,它们自然趋向于被放到一起.数据的大小和插入顺序都会影响每块上可以存储的码数.因此容量(size)参数控制每块上聚簇码的最大数目,太高会浪费空间,太低将得到过多的数据链接,这将偏离聚簇的目的,把所有数据存储到一起,放到一个块上,容量(size)参数是聚簇的重要参数.在把数据放入之前,需要索引聚簇,可以立刻在聚簇中创建表,但将同时创建表和为表装入数据,这样在拥有任何数据之前,需要聚簇索引. create index emp_dept_cluster_idx _disibledevent=x.deptno;end loop;end;如果先装载了所有的dept行,由于dept行非常小,只有两个字节,很显然已经得到了每个块中的七个码(基于设置的size 1024)当轮到装载emp行时,可能发行一些部门有超过1024字节的数据,这将导致这些聚簇码块的过多链接.通过同时使用指定的聚簇码装载所有的数据,把块压缩得最紧,用完空间才开始使用一个新块,不是让oracle再每块中放置七个聚簇码值,而是放置尽可能多得聚簇码值. 什么时候使用聚簇?实际上可能更容易描述何时不使用聚簇聚簇可能消极地影响dml得性能---如果预料聚簇中得表会有较大得修改就必须知道索引聚簇将可能有降低性能得副作用在聚簇中,全扫描表会受到影响---不仅仅全扫描一个表中得数据,而是必须全扫描许多表得数据如果相信将经常truncate和装载表---聚簇中得表不能截断.因此,如果大部分是读取数据,并且通过索引来读取,要么是聚簇码索引,要么方在聚簇表得其它索引,还经常把这种信息放在一起,这样使用聚簇合适.5.散列聚簇表和索引聚簇表在概念上很相似,主要区别为散列函数代替了聚簇码索引.表中得数据就是索引,却没有物理索引.oracle采用行得码值,使用内部函数或提供得函数对它进行散列运算利用这些来指定数据应放在硬盘得位置.使用散列算法来定位数据得副作用是没有在表中增加传统得索引,因此就不能区域(range)扫描散列聚簇中得表.在上面得索引聚簇中,查询:select * from emp where deptno between 10 and 20能够使用聚簇码索引找到这些行.在散列聚簇中.除非在deptno列上有索引,否则这个查询将导致全表扫描.没有使用支持区域扫描得索引,只能够在散列码中执行精确得等式搜索.在完美情况下,散列聚簇意味着可以通过一个i/o直接从查询中得到数据,这与散列算法很少甚至没有冲突.现实情况下,大部分情况可能是会发生冲突得,并且有周期性得行链接,意味着检索一些数据需要多个i/o.数据库中得散列表有固定的"大小",当创建表时,必须确定表将最终有的散列码数,其中并不限制插入的行数.散列聚簇从开始就需要分配.oracle得到hashkeys/trunc(blocksize/size)就会立即分配空间.只要在聚簇中放置了第一张表,任何全扫描都会达到所有分配的块散列聚簇中的hashkey数是固定大小的.能限制为这个簇产生的唯一散列码的数量.如果设置太低,由于预料不到的散列冲突可能影响性能.在聚簇码上的区域扫描是不能用的.以下情形散列聚簇是合适的:在一定程度上精确知道在整个过程中会有多少行,或者如果合理的上限,正确地设置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语句使之成为基于会话的临时表,行将留在此表中,直到会话断开或通过delete或truncate从物理上删除这些数据,只有自己的会话能看到这些行.其它会话不能看到"我的"行数据,即使是在commit之后.create global temporary table temp_table_transactionon commit delete rowsasselect * from scott.emp where 1=0;on commit delete rows 使之成为基于事务的临时表.当会话提交后,行消失.通过简单地恢复分配到表的临时盘区,行就会消失.在这个临时表自动清除过程中不涉及额外开销.对于每一个数据库,创建所有的temp表作为全局临时表.这将作为应用程序安装的一部分完成.就像创建永久表一样.只要在过程中简单使用即可.即不要在存储过程中创建临时表.临时表可能有触发器,检查约束,索引等等.但是不支持如下:没有用作参照完整性约束---既不能是外码的目标,也不能在上面定义外码.不能有varray或者nested table类型的列,不能是索引组织表,不能是索引或散列聚簇,不能分区.通过analyze表命令不能产生统计信息.在oracle中使用临时表是没有必要的(如果是为了避免查询查询中涉及到的表太多,而将子查询结果放到临时表中的话)然而在其它情况,在程序中使用临时表是正确的方法由于analyze命令不能在临时表中收集统计信息,必须使用手动方法,把临时表的有代表性的统计信息装载到数据字典.例如,如果临时表中行的平均数量是500,平均行大小是100字节和块的数量是7.只使用begindbms_stats.set_table_stats(ownname=>user,tabname=>'T',numrows=>500,numblks=>7,avgrlen=>100); end;现在,优化器不能使用它的猜测,而使用我们的猜测结果.或者:删除临时表一段时间,创建一个名字和结构相同的永久表,并用代表性的数据来装载,然后尽可能彻底地分析这张表(也可能产生柱状图等等)并且使用dbms_stats输出这张永久表的统计信息,然后删除这张永久表,重新创建临时表,然后所有需要做的就是输入代表性的统计信息和让优化器正确地工作.在任何情况下,访问超过表的10%-20%,都不应该使用索引.取出统计信息begindbms_stats.create_stat_table(ownname=>user,stattab=>'STATS');dbms_stats.export_table_stats(ownname=>user,tabname=>'TEMP_ALL_OBJECTS',stattab=>'STATS');dbms_stats.export_index_statsownnam e=>user,indname=>'TEMP_ALL_OBJECTS_IDX',stattab=>'STATS');end;导入统计信息begindbms_stats.import_table_stats(ownname=>user,tabname=>'TEMP_ALL_OBJECTS',stattab=>'STATS');dbms_stats.import_index_stats(ownname=>user,indname=>'TEMP_ALL_OBJECTS_IDX',stattab=>'STATS'); end;在应用程序中临时表是有用处的,可以临时存储其它表,会话或事务需要的一组集.这并不意味着用来把单个较大的查询"分成"可以重新连接在一起的小结果集(在其它数据库中,这好像是临时表最流行的用法)当有机会把一系列到临时表的insert写成以一个大查询的形势的select时,执行速度会大大加快。
详解ORACLE簇表、堆表、IOT表、分区表
详解ORACLE簇表、堆表、IOT表、分区表簇和簇表簇其实就是一组表，是一组共享相同数据块的多个表组成。
将经常一起使用的表组合在一起成簇可以提高处理效率。
在一个簇中的表就叫做簇表。
建立顺序是：簇→簇表→数据→簇索引1、创建簇的格式CREATE CLUSTER cluster_name(column date_type [,column datatype]...)[PCTUSED 40 | integer] [PCTFREE 10 | integer][SIZE integer][INITRANS 1 | integer] [MAXTRANS 255 | integer][TABLESPACE tablespace][STORAGE storage]SIZE：指定估计平均簇键，以及与其相关的行所需的字节数。
2、创建簇create cluster my_clu (deptno number)pctused60pctfree10size1024tablespace usersstorage(initial128knext128kminextents2maxextents20);3、创建簇表create table t1_dept(deptno number,dname varchar2(20))cluster my_clu(deptno);create table t1_emp(empno number,ename varchar2(20),birth_date date,deptno number)cluster my_clu(deptno);4、为簇创建索引create index clu_index on cluster my_clu;注：若不创建索引，则在插入数据时报错：ORA-02032: clustered tables cannot be used before the cluster index is built管理簇使用ALTER修改簇属性(必须拥有ALTER ANY CLUSTER的权限)1、修改簇属性可以修改的簇属性包括：* PCTFREE、PCTUSED、INITRANS、MAXTRANS、STORAGE* 为了存储簇键值所有行所需空间的平均值SIZE* 默认并行度注：* 不能修改INITIAL和MINEXTENTS的值* PCTFREE、PCTUSED、SIZE参数修改后适用于所有数据块* INITRANS、MAXTRANS仅适用于以后分配的数据块* STORAGE参数修改后仅影响以后分配给簇的盘区格式：alter cluster my_clupctused402、删除簇drop cluster my_clu;--仅适用于删除空簇drop cluster my_clu including tables;--删除簇和簇表drop cluster my_clu including tables cascade constraints;--同时删除外键约束注：簇表可以像普通表一样删除。
Oracle表分区详解
Oracle表分区详解oracle表分区详解此⽂从以下⼏个⽅⾯来整理关于分区表的概念及操作:1.表空间及分区表的概念2.表分区的具体作⽤3.表分区的优缺点4.表分区的⼏种类型及操作⽅法5.对表分区的维护性操作.(1.) 表空间及分区表的概念表空间： 是⼀个或多个数据⽂件的集合，所有的数据对象都存放在指定的表空间中，但主要存放的是表，所以称作表空间。
分区表：当表中的数据量不断增⼤，查询数据的速度就会变慢，应⽤程序的性能就会下降，这时就应该考虑对表进⾏分区。
表进⾏分区后，逻辑上表仍然是⼀张完整的表，只是将表中的数据在物理上存放到多个表空间(物理⽂件上)，这样查询数据时，不⾄于每次都扫描整张表。
( 2).表分区的具体作⽤Oracle的表分区功能通过改善可管理性、性能和可⽤性，从⽽为各式应⽤程序带来了极⼤的好处。
通常，分区可以使某些查询以及维护操作的性能⼤⼤提⾼。
此外,分区还可以极⼤简化常见的管理任务，分区是构建千兆字节数据系统或超⾼可⽤性系统的关键⼯具。
分区功能能够将表、索引或索引组织表进⼀步细分为段，这些数据库对象的段叫做分区。
每个分区有⾃⼰的名称，还可以选择⾃⼰的存储特性。
从数据库管理员的⾓度来看，⼀个分区后的对象具有多个段，这些段既可进⾏集体管理，也可单独管理，这就使数据库管理员在管理分区后的对象时有相当⼤的灵活性。
但是，从应⽤程序的⾓度来看，分区后的表与⾮分区表完全相同，使⽤ SQL DML 命令访问分区后的表时，⽆需任何修改。
什么时候使⽤分区表：1、表的⼤⼩超过2GB。
2、表中包含历史数据，新的数据被增加都新的分区中。
(3).表分区的优缺点表分区有以下优点：1、改善查询性能：对分区对象的查询可以仅搜索⾃⼰关⼼的分区，提⾼检索速度。
2、增强可⽤性：如果表的某个分区出现故障，表在其他分区的数据仍然可⽤；3、维护⽅便：如果表的某个分区出现故障，需要修复数据，只修复该分区即可；4、均衡I/O：可以把不同的分区映射到磁盘以平衡I/O，改善整个系统性能。
Oracle表的类型及定义
1表的类型1)堆组织表(heap organized tables).当增加数据时,将使用在段中找到的第一个适合数据大小的空闲空间.当数据从表中删除时,留下的空间允许随后的insert和update 重用.2)索引组织表.这里表存储在索引结构中,利用行本身物理排序.在堆中,数据可能被填到任何适合的地方,在索引组织表中,根据主关键字,以排序顺序来存储数据.3)聚簇表.这种表完成两件事情,第一,许多表物理上连接在一起存储.通常,希望数据在一个数据库块上的一张表里.对于聚簇表,来自许多张表的数据可能被存储在同一个块上;第二,包含相同聚簇码值的所有数据将物理上存储在一起.数据"聚集"在聚簇码值周围,聚簇码用B*Tree索引构建.4)散列聚簇表.和上面的聚簇表相似,但是不是用B*Tree索引有聚簇码定位数据,散列聚簇把码散列到簇中,来到达数据所在的数据库块.在散列聚簇中,数据就是索引(比喻的说法).这适合用于经常通过码等式来读取的数据.5)嵌套表6)临时表7)对象表2.术语1)高水位标记高水位标记开始在新创建的表的第一个块上.随着数据不断放到表中,使用了更多的块,从而高水标记上升.如果删除一些表中的行,高水标记仍不下移.即count(*)100000行和delete全部行后count(*)所需时间一样(全扫描情况下).需要对表进行重建.2)自由列表(freelist)在oracle中用来跟踪高水标记以下有空闲空间的块对象.每个对象至少有一个freelist和它相关.当块被使用时,oracle将根据需要放置或取走freelist.只有一个对象在高水位标记以下的块才能在freelist上发现.保留在高水标记以上的块,只有freelist为空时才能被用到.此时oracle提高高水标记并把这些块增加到freelist中.用这种方式,oracle对一个对象推迟提高高水标记,直到必须时才提高.一个对象可能不只有一个freelist,如果预料会有许多并行用户对一个对象进行大量的insert或update,配置多个freelist能够提高整体性能(可能的代价就是增加存储空间).3)pctfree和pctused如果pctfree设置为10,那么块在用完90%以前,都会使用freelist(都位于freelist中).一旦达到90%,将从freelist中移除,直到块上空闲空间超过60%以后再使用(再次进入freelist中,当pctused为40时).当pctfree设置过小,而经常更新时,容易出现行迁移高pctfree,低pctused---用于插入许多将要更新的数据,并且更新经常会增加行的大小,这样插入后再块上保留了许多空间(高pctfree),在块返回到自由列表之前,块必须几乎是空的(低pctused)低pctfree,高pctused---用于倾向于对表只使用insert或delete,或者如果要update,update也只是会使行变小.4)initial,next和pctincrease例如使用一个initial盘区为1MB,next盘区为2MB,pctincrease为50,可得到盘区应该是:(1)1mb(2)2mb(2)3mb(2的150)(4)4.5mb(3的150%).我认为这些参数是过时的,数据库应该使用局部管理并且盘区大小一致的表空间.在这种方式下,initial盘区总是等于next盘区的大小,并且不必使用pctincrease,使用pctincrease只会导致表空间产生碎片.在没用局部管理表空间的情况下,建议总是设置initial=next和pctincrease等于zero,这样可以模拟局部管理表空间的使用,为了避免碎片,所有在表空间中的对象应该使用相同盘区的分配策略.5)minextents和maxextents设置控制对象对它自己分配的盘区数.minextents为初始分配盘区个数6)logging和nologgingnologging允许对这些对象执行某些操作时不产生重做.它只影响一些特定的操作,例如开始创建对象或者使用sqlldr进行直接路径装载或者insert /*+ append */ select类型的语句.7)initrans和maxtrans对象中的每一块都有一个块头,块头的一部分是事务表,事务表中的条目描述哪一个事务块上的行/元素被锁定了.事务表的最初大小由对象的initrans设置确定,对于表,默认为1(索引默认为2),当需要时,事务表可以动态地增加,大小最多到maxtrans(假定在快上有足够的空闲空间),每一个分配的事务条目在块头上占用23字节的存储空间.2.堆表:需要注意的参数为freelists,pctfree,pctused,initrans其它参数应该使用局部惯例的表空间,不使用pctincrease,next等参数3.索引组织表索引组织表(index organized tables,iot)是存储在索引结构中的十分简单的一种表,由于堆方式存储的表是随机组织的,数据存放到任何有空间的地方,而数据在iot中是根据主码存储和排序的.iot特别使用于信息检索,空间和loap应用程序对索引块中的数据和溢出段中的数据进行正确的结合是iot建立中最关键的部分.不同的情形有不同的溢出条件,需要理解它如何影响insert,update,delete和select.如果有一个结构,构建一次,频繁读取,能尽可能地把数据存储在索引块中,如果经常修改结构,就必须在让所有数据在索引块上(利用检索)和在索引中经常重新组织数据(不利于修改)之间建立某种平衡.在堆组织表中堆freelist的考虑也适合于iot.在iot中,pctfree和pctused有两种作用,pctfree在iot中没有在堆组织表中重要,pctused通常不使用.然而当考虑overflow段时,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(2000),constraint iot_pk primary key(x))organization indexpctthreshold 10overflow;这个表示oracle将从最后一个列向前,但是不包括最好一列的主码,找出哪些列需要存储到溢出段中.本例中数字列x和日期列y总是放在索引块中,最后一列z,长度是变化的,当少于约190字节时(2kb块的10%大约是200字节,增加日期的7字节,数字3~5字节),将存储到索引块上,它超过190字节时,oracle将存储z的数据列到溢出段中,并且设置一个指针指向它pctthreshold和including哪一个好些,还是两者的某种结合更好?这要更加需要而定.如果一个应用程序总是或几乎总是使用表中开始的4列,并且很少访问最后5列,这听起来像一个使用including的程序.可以包含开始的4列,而让其余的5列存储到溢出段中.运行时,如果需要,也可采用像迁移或链接行一样的方法检索.另一方面,如果不能确定几乎总是访问这些列和很少访问其它列,可能需要考虑pctthreshold.一旦确定了每个索引块上平均存储的行数,设置pctthreshold是很容易的.假定每个索引块中想存储20行,这意味着每行是块的1/20(5%),pctthreshold的值是5,在索引块上行占用的空间不应超过块的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表上创建触发器更新这个索引组织表.然后就可以用这个索引组织表充当索引delete from (select ename,empno from emp where emp.rowid in (select upper_ename,x$rid from upper_ename where x$ename='KING'));注意如果导出或导入emp或在表上使用alter table move命令,导致emp表中行id变化,则需要重构索引组织表中的任何索引(3)当想要加强数据的共同定位,或者想要数据按特定的顺序物理存储时,iot就是现成的结构.4.索引聚簇表在聚簇中,单块上的数据可能来自许多表,概念上可以存储(预连接)的数据,单个表也可以使用聚簇.现在根据某些列按组存储数据,例如,所有部门10的员工都将存储在同一块上(或者如果不适合,存储在尽可能少的块上),没有存储已排序的数据(这是iot的任务),存储的是某些码结合的数据,但是以堆的方式存储.因此,部门100可能刚好和部门1相邻.在聚簇的所有表中,关于部门10的所有数据都存储到那个块上,如果部门10的所有数据在这个块中容纳不下,额外的块将链接到最初的块,来包容溢出的数据.这种方式和在iot中的溢出块非常相似.对象存储的定义(pctfree,pctused,initial等等)是和cluster相关的,因为在聚簇中有许多表,每个表在同一个块中拥有不同的pctfree 是没有意义的.create cluster emp_dept_cluster(deptno number(2)) size 1024;这个聚簇的聚类列是deptno列,在表中的这个列不必称为deptno,但是必须是number(2)来匹配这个定义.定义中有一个size1024选项,是用来告诉oracle预计有大约1024字节数据和每个聚簇码相关.oracle将使用这些信息来计算每个块能容纳的最大聚簇码数目,假定有一个8kb的块,oracle在每个数据库块中将容纳达七个聚簇码(如果数据比预料的大,数目可能减少).这就是说,部门10,20,30,40,50,60,70的数据趋向存在于一个块上,插入部门80,一个新块将会被使用.这不是说数据以分类方式存储,只是意味着,如果按照这个顺序插入部门,它们自然趋向于被放到一起.数据的大小和插入顺序都会影响每块上可以存储的码数.因此容量(size)参数控制每块上聚簇码的最大数目,太高会浪费空间,太低将得到过多的数据链接,这将偏离聚簇的目的,把所有数据存储到一起,放到一个块上,容量(size)参数是聚簇的重要参数.在把数据放入之前,需要索引聚簇,可以立刻在聚簇中创建表,但将同时创建表和为表装入数据,这样在拥有任何数据之前,需要聚簇索引. create index emp_dept_cluster_idx _disibledevent=x.deptno;end loop;end;如果先装载了所有的dept行,由于dept行非常小,只有两个字节,很显然已经得到了每个块中的七个码(基于设置的size 1024)当轮到装载emp行时,可能发行一些部门有超过1024字节的数据,这将导致这些聚簇码块的过多链接.通过同时使用指定的聚簇码装载所有的数据,把块压缩得最紧,用完空间才开始使用一个新块,不是让oracle再每块中放置七个聚簇码值,而是放置尽可能多得聚簇码值. 什么时候使用聚簇?实际上可能更容易描述何时不使用聚簇聚簇可能消极地影响dml得性能---如果预料聚簇中得表会有较大得修改就必须知道索引聚簇将可能有降低性能得副作用在聚簇中,全扫描表会受到影响---不仅仅全扫描一个表中得数据,而是必须全扫描许多表得数据如果相信将经常truncate和装载表---聚簇中得表不能截断.因此,如果大部分是读取数据,并且通过索引来读取,要么是聚簇码索引,要么方在聚簇表得其它索引,还经常把这种信息放在一起,这样使用聚簇合适.5.散列聚簇表和索引聚簇表在概念上很相似,主要区别为散列函数代替了聚簇码索引.表中得数据就是索引,却没有物理索引.oracle采用行得码值,使用内部函数或提供得函数对它进行散列运算利用这些来指定数据应放在硬盘得位置.使用散列算法来定位数据得副作用是没有在表中增加传统得索引,因此就不能区域(range)扫描散列聚簇中得表.在上面得索引聚簇中,查询:select * from emp where deptno between 10 and 20能够使用聚簇码索引找到这些行.在散列聚簇中.除非在deptno列上有索引,否则这个查询将导致全表扫描.没有使用支持区域扫描得索引,只能够在散列码中执行精确得等式搜索.在完美情况下,散列聚簇意味着可以通过一个i/o直接从查询中得到数据,这与散列算法很少甚至没有冲突.现实情况下,大部分情况可能是会发生冲突得,并且有周期性得行链接,意味着检索一些数据需要多个i/o.数据库中得散列表有固定的"大小",当创建表时,必须确定表将最终有的散列码数,其中并不限制插入的行数.散列聚簇从开始就需要分配.oracle得到hashkeys/trunc(blocksize/size)就会立即分配空间.只要在聚簇中放置了第一张表,任何全扫描都会达到所有分配的块散列聚簇中的hashkey数是固定大小的.能限制为这个簇产生的唯一散列码的数量.如果设置太低,由于预料不到的散列冲突可能影响性能.在聚簇码上的区域扫描是不能用的.以下情形散列聚簇是合适的:在一定程度上精确知道在整个过程中会有多少行,或者如果合理的上限,正确地设置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语句使之成为基于会话的临时表,行将留在此表中,直到会话断开或通过delete或truncate从物理上删除这些数据,只有自己的会话能看到这些行.其它会话不能看到"我的"行数据,即使是在commit之后.create global temporary table temp_table_transactionon commit delete rowsasselect * from scott.emp where 1=0;on commit delete rows 使之成为基于事务的临时表.当会话提交后,行消失.通过简单地恢复分配到表的临时盘区,行就会消失.在这个临时表自动清除过程中不涉及额外开销.对于每一个数据库,创建所有的temp表作为全局临时表.这将作为应用程序安装的一部分完成.就像创建永久表一样.只要在过程中简单使用即可.即不要在存储过程中创建临时表.临时表可能有触发器,检查约束,索引等等.但是不支持如下:没有用作参照完整性约束---既不能是外码的目标,也不能在上面定义外码.不能有varray或者nested table类型的列,不能是索引组织表,不能是索引或散列聚簇,不能分区.通过analyze表命令不能产生统计信息.在oracle中使用临时表是没有必要的(如果是为了避免查询查询中涉及到的表太多,而将子查询结果放到临时表中的话)然而在其它情况,在程序中使用临时表是正确的方法由于analyze命令不能在临时表中收集统计信息,必须使用手动方法,把临时表的有代表性的统计信息装载到数据字典.例如,如果临时表中行的平均数量是500,平均行大小是100字节和块的数量是7.只使用begindbms_stats.set_table_stats(ownname=>user,tabname=>'T',numrows=>500,numblks=>7,avgrlen=>100); end;现在,优化器不能使用它的猜测,而使用我们的猜测结果.或者:删除临时表一段时间,创建一个名字和结构相同的永久表,并用代表性的数据来装载,然后尽可能彻底地分析这张表(也可能产生柱状图等等)并且使用dbms_stats输出这张永久表的统计信息,然后删除这张永久表,重新创建临时表,然后所有需要做的就是输入代表性的统计信息和让优化器正确地工作.在任何情况下,访问超过表的10%-20%,都不应该使用索引.取出统计信息begindbms_stats.create_stat_table(ownname=>user,stattab=>'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=>user,tabname=>'TEMP_ALL_OBJECTS',stattab=>'STATS');dbms_stats.import_index_stats(ownname=>user,indname=>'TEMP_ALL_OBJECTS_IDX',stattab=>'STATS'); end;在应用程序中临时表是有用处的,可以临时存储其它表,会话或事务需要的一组集.这并不意味着用来把单个较大的查询"分成"可以重新连接在一起的小结果集(在其它数据库中,这好像是临时表最流行的用法)当有机会把一系列到临时表的insert写成以一个大查询的形势的select时,执行速度会大大加快。
- 1、下载文档前请自行甄别文档内容的完整性，平台不提供额外的编辑、内容补充、找答案等附加服务。
- 2、"仅部分预览"的文档,不可在线预览部分如存在完整性等问题,可反馈申请退款(可完整预览的文档不适用该条件!)。
- 3、如文档侵犯您的权益，请联系客服反馈,我们会尽快为您处理(人工客服工作时间：9:00-18:30)。
详解ORACLE簇表、堆表、IOT表、分区表簇和簇表簇其实就是一组表，是一组共享相同数据块的多个表组成。
将经常一起使用的表组合在一起成簇可以提高处理效率。
在一个簇中的表就叫做簇表。
建立顺序是：簇→簇表→数据→簇索引1、创建簇的格式CREATE CLUSTER cluster_name(column date_type [,column datatype]...)[PCTUSED 40 | integer] [PCTFREE 10 | integer][SIZE integer][INITRANS 1 | integer] [MAXTRANS 255 | integer][TABLESPACE tablespace][STORAGE storage]SIZE：指定估计平均簇键，以及与其相关的行所需的字节数。
2、创建簇create cluster my_clu (deptno number)pctused60pctfree10size1024tablespace usersstorage(initial128knext128kminextents2maxextents20);3、创建簇表create table t1_dept(deptno number,dname varchar2(20))cluster my_clu(deptno);create table t1_emp(empno number,ename varchar2(20),birth_date date,deptno number)cluster my_clu(deptno);4、为簇创建索引create index clu_index on cluster my_clu;注：若不创建索引，则在插入数据时报错：ORA-02032: clustered tables cannot be used before the cluster index is built管理簇使用ALTER修改簇属性(必须拥有ALTER ANY CLUSTER的权限)1、修改簇属性可以修改的簇属性包括：* PCTFREE、PCTUSED、INITRANS、MAXTRANS、STORAGE* 为了存储簇键值所有行所需空间的平均值SIZE* 默认并行度注：* 不能修改INITIAL和MINEXTENTS的值* PCTFREE、PCTUSED、SIZE参数修改后适用于所有数据块* INITRANS、MAXTRANS仅适用于以后分配的数据块* STORAGE参数修改后仅影响以后分配给簇的盘区格式：alter cluster my_clupctused402、删除簇drop cluster my_clu;--仅适用于删除空簇drop cluster my_clu including tables;--删除簇和簇表drop cluster my_clu including tables cascade constraints;--同时删除外键约束注：簇表可以像普通表一样删除。
散列聚簇表在簇表中，Oracle使用存储在索引中的键值来定位表中的行，而在散列聚簇表中，使用了散列函数代替了簇索引，先通过内部函数或者自定义的函数进行散列计算，然后再将计算得到的码值用于定位表中的行。
创建散列簇需要用到HASHKEYS子句。
1、创建散列簇create cluster my_clu_two(empno number(10))pctused70pctfree10tablespace usershash is empnohashkeys150;说明：* hash is 子句指明了进行散列的列，如果列是唯一的标示行，就可以将列指定为散列值* hashkeys 指定和限制散列函数可以产生的唯一的散列值的数量2、创建散列表create table t2_emp (empno number(10),ename varchar2(20),birth_date date,deptno number)cluster my_clu_two(empno);注意：* 必须设置数值的精度(具体原因不详)* 散列簇不能也不用创建索引* 散列簇不能ALTER:size、hashkeys、hash is参数堆表1.基本概念执行CREATE TABLE语句时，默认得到的表类型就是堆组织表。
其他类型的表结构需要在CREATE TABLE语句本身中指定它。
堆组织表中，数据以堆的方式管理。
增加数据时，会使用段中找到的第一个能放下此数据的自由空间。
从表中删除数据后，允许以后的INSERT和UPDATE重用这部分空间。
堆（heap）是一组空间，以一种随机的方式使用。
因此，无法保证按照放入表中的顺序取得数据。
有1个简单的技巧，来查看对于给定类型的表，CREATE TABLE语句中主要有哪些可用的选项。
首先，尽可能简单地创建表，然后使用DBMS_METADATA来查询这个表的定义。
1.> create table t(x int primary key, y clob);2.3.Table created.4.5.> select dbms_metadata.get_ddl( 'TABLE', 'T') from dual;6.7.DBMS_METADATA.GET_DDL('TABLE','T')8.------------------------------------------------------------------------9.10.CREATE TABLE"TONY"."T"11.( "X"NUMBER(*,0),12."Y"CLOB,13.PRIMARY KEY("X")ING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 NOCOMPRESS LOGGING15.TABLESPACE "USERS"ENABLE16.) SEGMENT CREATION DEFERRED17.PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING18.TABLESPACE "USERS"19.LOB ("Y") STORE AS BASICFILE (20.TABLESPACE "USERS"ENABLE STORAGE IN ROW CHUNK 8192 RETENTION21.NOCACHE LOGGING )现在可以根据需要，修改某些参数。
对于ASSM有3个重要选项，对于MSSM有5个重要选项。
随着本地管理表空间的引入（推荐做法），其余的参数已经没什么意义了。
· FREELIST：仅适用于MSSM。
· PCTFREE：ASSM和MSSM都适用。
· PCTUS ED：仅适用于MSSM。
· INITRANS：ASSM 和MSSM 都适合。
为块初始分配的事务槽数。
如果会对同样的块完成多个并发更新，就应该考虑增大这个值。
· COMPRESS/NOCOMPRESS：ASSM 和MSSM 都适合。
11g之前，选项是COMPRESS或者NOCOMPRESS，只有直接路径操作（例如CREATE TABLE AS SELECT, INSERT /*+ APPEND*/, ALTER TABLE T MOVE以及SQL*Loader直接路径加载）才能利用压缩。
11g之后，选项是COMPRESS FOR OLTP，COMPRESS BASIC或者NOCOMPRESS。
COMPRESS FOR OLTP 启用所有操作的压缩（包括直接路径和常规路径），COMPRESS BASIC则只针对直接路径操作。
注意：单独存储在LOB段中的LOB数据并不使用表的PCTFREE/PCTUSED参数设置。
这些LOB块以不同的方式管理：它们总是会填入，直至达到最大容量，而且仅当完全为空时才返回FREELIST。
2. 堆表总结堆表具有的唯一优点是插入数据不需要采取任何措施，只需要顺其自然地安装插入的顺序存储，减少了插入大量数据的代价。
索引组织表IOT1. 基本概念索引组织表（index organized table）简称IOT。
IOT中，数据要根据主键有序地存储。
适合使用IOT的几种情况：· 表完全由主键组成或者只通过主键来访问一个表。
使用IOT，表就是索引，可以节约空间，提高效率。
· 通过外键访问子表，子表使用IOT。
通过IOT将相同外键的子表数据物理的存储在同一个位置，查询所需要的物理I/O更少，因为数据都在同一个（几个）块上。
· 经常在主键或者或惟一键上使用BETWEEN查询。
数据以某种特定的顺序物理存储，所以获取这些数据时所需的物理I/O更少。
查看创建IOT时候的参数选项。
1. > create table t(x int primary key, y clob) organization index;2.3.Table created.4.5. > select dbms_metadata.get_ddl( 'TABLE', 'T') from dual;6.7.DBMS_METADATA.GET_DDL('TABLE','T')8.--------------------------------------------------------------------------------9.10.CREATE TABLE"TONY"."T"11.( "X"NUMBER(*,0),12."Y"CLOB,13.PRIMARY KEY("X") ENABLE14.) ORGANIZATION INDEX NOCOMPRESS PCTFREE 10 INITRANS 2 MAXTRANS 255LOGGING15.STORAGE(INITIAL 65536 NEXT1048576 MINEXTENTS 1 MAXEXTENTS 214748364516.PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DE17.FAULT CELL_FLASH_CACHE DEFAULT)18.TABLESPACE "USERS"19.PCTTHRESHOLD 5020.LOB ("Y") STORE AS BASICFILE (21.TABLESPACE "USERS"DISABLE STORAGE IN ROW CHUNK 8192 RETENTION22.NOCACHE LOGGING23.STORAGE(INITIAL 65536 NEXT1048576 MINEXTENTS 1 MAXEXTENTS 214748364524.PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CAC25.HE DEFAULT))IOT没有PCTUSED子句，但是有PCTFREE。