性能优化表设计.ppt

《性能优化表设计.ppt》由会员分享，可在线阅读，更多相关《性能优化表设计.ppt（18页珍藏版）》请在淘文阁 - 分享文档赚钱的网站上搜索。

1、SQL性能调优之表结构信息技术中心应用集成研究所2011年2月28日1表简介表简介1集群因子集群因子 （聚簇因子）（聚簇因子）2表分区表分区(主要是纵向分区）主要是纵向分区）3表重构表重构4目 录2索引重构索引重构5堆表堆表以一种随机的方式管理。数据会放在最合适的地方，而不是以某种特定顺序来放置.存储快，读取慢。一般需要增加索引来提高数据检索效率.3索引组织表把索引和一般数据列全部存储在相同位置上的表结构.存储慢，通过主键读取快.OVERFLOW子句(行溢出)因为所有数据都放入索引，所以当表的数据量很大时，会降低索引组织表的查询性能。此时设置溢出段将主键和溢出数据分开来存储以提高效率。溢出段的

2、设置有两种格式：PCTTHRESHOLDn：制定一个数据块的百分比，当行数据占用大小超出时，该行的其他列数据放入溢出段INCLUDINGcolumn_name：指定列之前的列都放入索引块，之后的列都放到溢出段当行中某字段的数据量无法确定时使用PCTTHRESHOLD。若所有行均超出PCTTHRESHOLD规定大小，则考虑使用INCLUDING。4索引组织表的适用情况：1、代码查找表。2、经常通过主码访问的表。3、构建自己的索引结构。4、加强数据的共同定位，要数据按特定顺序物理存储。5、经常用betweenand对主码或唯一码进行查询。数据物理上分类查询。如一张订单表，按日期装载数据，想查单个客

3、户不同时期的订货和统计情况。5TableaccessbyROWIDNon-keycolumnsKeycolumnRowheader堆表与索引组织表堆表索引组织表聚簇表聚簇表是指：如果一组表有一些共同的列，则将这样一组表存储在相同的数据库块中。把相关数据存储在同一个块上。可以通过设置Size值来限制一个块上最多只能存储多少个不同的聚簇。7使用索引聚簇指南考虑对经常在连接语句中访问的表建立聚簇如果表只是偶尔被连接或者它们的公共列经常被修改，则不要聚簇表。（修改记录的聚簇键值比在非聚簇的表中修改此值要花费更多的时间，因为Oracle必须将修改的记录移植到其他的块中以维护聚簇）。如果经常需要在一个表上

4、进行完全搜索，则不要聚簇这个表（对一个聚簇表进行完全搜索比在非聚簇表上进行完全搜索的时间长，Oracle可能要读更多的块，因为表是被一起存储的。）如果经常从一个父表和相应的子表中查询记录，则考虑给1对多（1:*）关系创建聚簇表。（子表记录存储在与父表记录相同的数据块中，因此当检索它们时可以同时在内存中，因此需要Oracle完成较少的I/O）。如果经常查询同一个父表中的多个子记录，则考虑单独将子表聚簇。（这样提高了从相同的父表查询子表记录的性能，而且也没有降低对父表进行完全搜索的性能）。如果从所有有相同聚簇键值的表查询的数据超过一个或两个Oracle块，则不要聚簇表。（要访问在一个聚簇表中的记录

5、，Oracle读取所有包含那个记录值的全部数据块，如果记录占据了多个数据块，则访问一个记录需要读的次数比一个非聚簇的表中访问相同的记录读的次数要多）。8使用哈希聚簇指南当经常使用有相同列的包含相等条件的查询子句访问表时，考虑使用哈希聚簇来存储表。使用这些列作为聚簇键。如果可以确定存放具有给定聚簇键值的所有记录所需的空间（包括现在的和将来的），则将此表以哈希聚簇存储。如果空间不够，并且不能为将要插入的新记录分配额外的空间，那么不要使用哈希聚簇。如果偶尔创建一个新的、很大的哈希聚簇来保存这样的表是不切实际的，那么不要用哈希聚簇存储经常增长的表。如果经常需要进行全表搜索，并且必须要为表的预期增长中的

6、哈希聚簇分配足够的空间，则不要将此表以哈希聚簇存储。（这样的完全检索必须要读分配给哈希聚簇的全部块，即使有些块可能只包含很少的记录。单独地存储表将减少由完全的表检索读取的块的数量。）如果你的应用程序经常修改聚簇键的值，则不要将表以哈希聚簇方式存储。不管这个表是否经常与其他表连接，只要进行哈希对于基于以前的指南的表是合适的，那么在哈希聚簇中存储一个表可能是有用的。9聚簇表Clusteredordersandorder_itemtablesCluster Key(ORD_NO)101 ORD_DTCUST_CD 05-JAN-97 R01 PROD QTYA4102 20A5675 19 W082

7、4 10 102 ORD_DTCUST_CD 07-JAN-97 N45PROD QTYA2091 11G7830 20 N9587 26Unclusteredordersandorder_itemtablesORD_NOORD_DT CUST_CD-10105-JAN-97 R0110207-JAN-97 N45ORD_NOPRODQTY.-101A410220102A209111102G7830 20 102N9587 26101A567519101W082410 比较项比较项堆表堆表索引组织表索引组织表存储存储随机存储(快)按索引排序存储(慢)行的唯一识别行的唯一识别ROWID主键主健是

8、否必须指定主健是否必须指定可以不指定必须指定行的读取行的读取使用ROWID进行读取使用主键读取全表扫描全表扫描按任意顺序返回行(慢)按主键顺序返回行(快)能否使用聚簇能否使用聚簇可以不能分区的应用分区的应用允许版本不同有所差别并行处理并行处理允许版本不同有所差别11表对比集群因子（聚簇因子）12集群因子：集群因子：指按照索引列值进行了索引排序和对应表中数据行序的相似程度。00010001000100010001000200020003000400050001AAA1000001DDD2000001CCC1000002BBB3000001EEE3000003DDD4000001AAA600000

9、2FFF5500120NNN1200340OOO3300900SDF2200190DSS5800005AAA9800008TTT3200009RRR3300010HHH560AAAAAAAAABBBCCCDDDDDDEEE数据块索引1索引213集群因子越低通过索引读取的块越少，速度越快！我们建表时需要考虑表的特性，对不同的表制定不同的建表策略来达到数据存取的最优。分区表(主要是纵向分区）14分区有横向和纵向分区，一般所说的分区都是指横向分区，而忽略纵向分。横向分区有：区间分区、散列分区、列表分区、组合分区，这些内容很多此次不打算讲。主要讲纵向分区。纵向分区非常简单就是把表里的不常用的字段移到另

10、一个表，这样可减少检索数据读取的数据块，达到优化目的。表重构表重构就是把表里的数据重新插入一遍，这样做的好处有回收垃圾空间、使用表里的数据存储便有连续性、减少行迁移，方便I/O的读取。1512333344449537534894953753484刚初始化后数据存储情况使用一段时间后，因为删除了数据和添加了数据的存储情况表重构后，表空间收缩后334487595能否把存储情况改成和索引顺序一至，提高集群因子，减少读取的数据块索引重构和表重构是一样的道理，经过长时间的修改、删除会造成很多没有用的空块.通过使用nologging和paralleldegree8，创建索引大多情况下不需要记录log。Alterindexindex_namerebuildnologging;Alterindexindex_namerebuildpartitionpartition_namenologging;16 从理论上来看，根本就不存在一种能够满足从理论上来看，根本就不存在一种能够满足所有读取要求的数据存储方式所有读取要求的数据存储方式 17谢谢！thanks