邪恶八进制信息安全团队技术讨论组's Archiver

ring04h 2006-6-27 17:31

[转载]Oracle 数据表分区的策略

<p>信息来源: IT168</p><p>本文描述通过统计分析出医院信息系统需分区的表，对需分区的表选择分区键，即找出包括在你的分区键中的列(表的属性)，对大型数据的管理比较有意义， 本文的工作在Oracle8.1.6下实现。</p><p /><p /><div id="2"> Oracle虽然是一个大型的DBMS，但如果不对记录比较多的表进行处理，仍然发挥不了Oracle管理大型数据的强大功能，因此对某些表进行分区，具有如下优点： </div><p /><p /><div id="3"> 分区表中每个分区可以在逻辑上认为是一个独立的对象； </div><p /><p /><div id="4"> 可以在一个表中的一个或多个分区上进行如删除、移动、析分等维护操作，而不会影响其它分区，具有分区独立性； </div><p /><p /><div id="5"> 如果选择合适的分区策略，会大大的加快数据的查询速度。</div><p /><p /><div id="6"> <strong>一 找出需分区的表</strong></div><p /><p /><div id="7"> 本节描述通过统计分析出医院His系统需分区的表，对需分区的表找出包括在你的分区键中的列(表的属性)，即选择分区键。</div><p /><p /><div id="8"> <strong>1、基于访问频度找出需分区的表</strong> </div><p /><p /><div id="9"> Oracle8i允许访问数据库中的审核信息，借助于收集的审核信息，设计者能够确定哪些表的数据是真正最频繁访问的，即找出那些表，需要进行分区。</div><p /><p /><div id="10"> 打开审核：在作为SYS或SYSSTEM登录到数据库后，动行如下脚本，打开对象的审核功能。</div><p /><div style="SCROLLBAR-HIGHLIGHT-COLOR: buttonhighlight; OVERFLOW: auto; WIDTH: 500px"><pre style="BORDER-RIGHT: black 1px solid; PADDING-RIGHT: 4px; BORDER-TOP: black 1px solid; PADDING-LEFT: 4px; PADDING-BOTTOM: 4px; BORDER-LEFT: black 1px solid; PADDING-TOP: 4px; BORDER-BOTTOM: black 1px solid; BACKGROUND-COLOR: #ededed"><div id="11">
<div><!--
Code highlighting produced by Actipro CodeHighlighter (freeware)
[url]http://www.CodeHighlighter.com/[/url]
--><img src="http://cms.it168.com/Images/OutliningIndicators/None.gif" align="top" /><span style="COLOR: #000000"></span><span style="COLOR: #0000ff">set</span><span style="COLOR: #000000"> echo </span><span style="COLOR: #0000ff">off</span><span style="COLOR: #000000"> feed </span><span style="COLOR: #0000ff">off</span><span style="COLOR: #000000"> ver </span><span style="COLOR: #0000ff">off</span><span style="COLOR: #000000"> pages </span><span style="FONT-WEIGHT: bold; COLOR: #800000">0</span><span style="COLOR: #000000">
<img src="http://cms.it168.com/Images/OutliningIndicators/None.gif" align="top" />    spool audon.sql
<img src="http://cms.it168.com/Images/OutliningIndicators/None.gif" align="top" />    </span><span style="COLOR: #0000ff">select</span><span style="COLOR: #000000"> ’audit </span><span style="COLOR: #0000ff">select</span><span style="COLOR: #000000"></span><span style="COLOR: #0000ff">on</span><span style="COLOR: #000000"> ’</span><span style="COLOR: #808080">||</span><span style="COLOR: #000000">owner</span><span style="COLOR: #808080">||</span><span style="COLOR: #000000">’.’</span><span style="COLOR: #808080">||</span><span style="COLOR: #ff00ff">object_name</span><span style="COLOR: #808080">||</span><span style="COLOR: #000000">’ </span><span style="COLOR: #0000ff">by</span><span style="COLOR: #000000">
<img src="http://cms.it168.com/Images/OutliningIndicators/None.gif" align="top" />    access;’
<img src="http://cms.it168.com/Images/OutliningIndicators/None.gif" align="top" />    </span><span style="COLOR: #0000ff">from</span><span style="COLOR: #000000"> dba_objects
<img src="http://cms.it168.com/Images/OutliningIndicators/None.gif" align="top" />    </span><span style="COLOR: #0000ff">where</span><span style="COLOR: #000000"> object_type </span><span style="COLOR: #808080">in</span><span style="COLOR: #000000"> (’</span><span style="COLOR: #0000ff">VIEW</span><span style="COLOR: #000000">’，’</span><span style="COLOR: #0000ff">TABLE</span><span style="COLOR: #000000">’) </span><span style="COLOR: #808080">and</span><span style="COLOR: #000000"> owner </span><span style="COLOR: #808080">in</span><span style="COLOR: #000000">
<img src="http://cms.it168.com/Images/OutliningIndicators/None.gif" align="top" />    (’ORDADM’);
<img src="http://cms.it168.com/Images/OutliningIndicators/None.gif" align="top" />    spool </span><span style="COLOR: #0000ff">off</span><span style="COLOR: #000000">
<img src="http://cms.it168.com/Images/OutliningIndicators/None.gif" align="top" />    </span><span style="COLOR: #0000ff">set</span><span style="COLOR: #000000"> echo </span><span style="COLOR: #0000ff">on</span><span style="COLOR: #000000"> feed </span><span style="COLOR: #0000ff">on</span><span style="COLOR: #000000"> ver </span><span style="COLOR: #0000ff">on</span><span style="COLOR: #000000"></span></div>
</div></pre></div><p /><div id="12"> 这些代码的运行将产生“audon.sql”的输出文件，它包含下面清单中所示格式的语句</div><p /><div style="SCROLLBAR-HIGHLIGHT-COLOR: buttonhighlight; OVERFLOW: auto; WIDTH: 500px"><pre style="BORDER-RIGHT: black 1px solid; PADDING-RIGHT: 4px; BORDER-TOP: black 1px solid; PADDING-LEFT: 4px; PADDING-BOTTOM: 4px; BORDER-LEFT: black 1px solid; PADDING-TOP: 4px; BORDER-BOTTOM: black 1px solid; BACKGROUND-COLOR: #ededed"><div id="13">
<div><!--
Code highlighting produced by Actipro CodeHighlighter (freeware)
[url]http://www.CodeHighlighter.com/[/url]
--><img src="http://cms.it168.com/Images/OutliningIndicators/None.gif" align="top" /><span style="COLOR: #000000">    audit </span><span style="COLOR: #0000ff">select</span><span style="COLOR: #000000"></span><span style="COLOR: #0000ff">on</span><span style="COLOR: #000000"> ORDADM.DOCTOR_ORDERS </span><span style="COLOR: #0000ff">by</span><span style="COLOR: #000000"> access;
<img src="http://cms.it168.com/Images/OutliningIndicators/None.gif" align="top" />    audit </span><span style="COLOR: #0000ff">select</span><span style="COLOR: #000000"></span><span style="COLOR: #0000ff">on</span><span style="COLOR: #000000"> ORDADM.GROUP_ORDER_ITEMS </span><span style="COLOR: #0000ff">by</span><span style="COLOR: #000000"> access;
<img src="http://cms.it168.com/Images/OutliningIndicators/None.gif" align="top" />    audit </span><span style="COLOR: #0000ff">select</span><span style="COLOR: #000000"></span><span style="COLOR: #0000ff">on</span><span style="COLOR: #000000"> ORDADM.GROUP_ORDER_MASTER </span><span style="COLOR: #0000ff">by</span><span style="COLOR: #000000"> access;
<img src="http://cms.it168.com/Images/OutliningIndicators/None.gif" align="top" />    audit </span><span style="COLOR: #0000ff">select</span><span style="COLOR: #000000"></span><span style="COLOR: #0000ff">on</span><span style="COLOR: #000000"> ORDADM.ORDERS </span><span style="COLOR: #0000ff">by</span><span style="COLOR: #000000"> access;
<img src="http://cms.it168.com/Images/OutliningIndicators/None.gif" align="top" />    audit </span><span style="COLOR: #0000ff">select</span><span style="COLOR: #000000"></span><span style="COLOR: #0000ff">on</span><span style="COLOR: #000000"> ORDADM.ORDERS_COSTS </span><span style="COLOR: #0000ff">by</span><span style="COLOR: #000000"> access;
<img src="http://cms.it168.com/Images/OutliningIndicators/None.gif" align="top" />    audit </span><span style="COLOR: #0000ff">select</span><span style="COLOR: #000000"></span><span style="COLOR: #0000ff">on</span><span style="COLOR: #000000"> ORDADM.ORDERS_SHEET_IMAGE </span><span style="COLOR: #0000ff">by</span><span style="COLOR: #000000"> access;
<img src="http://cms.it168.com/Images/OutliningIndicators/None.gif" align="top" />    audit </span><span style="COLOR: #0000ff">select</span><span style="COLOR: #000000"></span><span style="COLOR: #0000ff">on</span><span style="COLOR: #000000"> ORDADM.VITAL_SIGNS_REC </span><span style="COLOR: #0000ff">by</span><span style="COLOR: #000000"> access;  </span></div>
</div></pre></div><p /><div id="14"> 使用命令@audon.sql激活上述代码以打开审核功能收集收集审核信息，建立一个表以保存概要信息： </div><p /><div style="SCROLLBAR-HIGHLIGHT-COLOR: buttonhighlight; OVERFLOW: auto; WIDTH: 500px"><pre style="BORDER-RIGHT: black 1px solid; PADDING-RIGHT: 4px; BORDER-TOP: black 1px solid; PADDING-LEFT: 4px; PADDING-BOTTOM: 4px; BORDER-LEFT: black 1px solid; PADDING-TOP: 4px; BORDER-BOTTOM: black 1px solid; BACKGROUND-COLOR: #ededed"><div id="15">
<div><!--
Code highlighting produced by Actipro CodeHighlighter (freeware)
[url]http://www.CodeHighlighter.com/[/url]
--><img src="http://cms.it168.com/Images/OutliningIndicators/None.gif" align="top" /><span style="COLOR: #000000"></span><span style="COLOR: #0000ff">create</span><span style="COLOR: #000000"></span><span style="COLOR: #0000ff">table</span><span style="COLOR: #000000"> aud_summary (
<img src="http://cms.it168.com/Images/OutliningIndicators/None.gif" align="top" />    obj_name </span><span style="FONT-WEIGHT: bold; COLOR: #000000">varchar2</span><span style="COLOR: #000000">(</span><span style="FONT-WEIGHT: bold; COLOR: #800000">30</span><span style="COLOR: #000000">)，
<img src="http://cms.it168.com/Images/OutliningIndicators/None.gif" align="top" />    owner </span><span style="FONT-WEIGHT: bold; COLOR: #000000">varchar2</span><span style="COLOR: #000000">(</span><span style="FONT-WEIGHT: bold; COLOR: #800000">30</span><span style="COLOR: #000000">)，
<img src="http://cms.it168.com/Images/OutliningIndicators/None.gif" align="top" />    hits </span><span style="FONT-WEIGHT: bold; COLOR: #000000">number</span><span style="COLOR: #000000">);  </span></div>
</div></pre></div><p /><div id="16"> 将审核信息从dba_audit_object表中取出并装入概要表中： </div><p /><div style="SCROLLBAR-HIGHLIGHT-COLOR: buttonhighlight; OVERFLOW: auto; WIDTH: 500px"><pre style="BORDER-RIGHT: black 1px solid; PADDING-RIGHT: 4px; BORDER-TOP: black 1px solid; PADDING-LEFT: 4px; PADDING-BOTTOM: 4px; BORDER-LEFT: black 1px solid; PADDING-TOP: 4px; BORDER-BOTTOM: black 1px solid; BACKGROUND-COLOR: #ededed"><div id="17">
<div><!--
Code highlighting produced by Actipro CodeHighlighter (freeware)
[url]http://www.CodeHighlighter.com/[/url]
--><img src="http://cms.it168.com/Images/OutliningIndicators/None.gif" align="top" /><span style="COLOR: #000000"></span><span style="COLOR: #0000ff">insert</span><span style="COLOR: #000000"></span><span style="COLOR: #0000ff">into</span><span style="COLOR: #000000"> aud_summary
<img src="http://cms.it168.com/Images/OutliningIndicators/None.gif" align="top" />    </span><span style="COLOR: #0000ff">select</span><span style="COLOR: #000000"> obj_name，owner，</span><span style="COLOR: #ff00ff">count</span><span style="COLOR: #000000">(</span><span style="COLOR: #808080">*</span><span style="COLOR: #000000">)
<img src="http://cms.it168.com/Images/OutliningIndicators/None.gif" align="top" />    </span><span style="COLOR: #0000ff">from</span><span style="COLOR: #000000"> dba_audit_object
<img src="http://cms.it168.com/Images/OutliningIndicators/None.gif" align="top" />    </span><span style="COLOR: #0000ff">group</span><span style="COLOR: #000000"></span><span style="COLOR: #0000ff">by</span><span style="COLOR: #000000"> obj_name，owner;  </span></div>
</div></pre></div><p /><div id="18"> 关闭审核： </div><p /><div style="SCROLLBAR-HIGHLIGHT-COLOR: buttonhighlight; OVERFLOW: auto; WIDTH: 500px"><pre style="BORDER-RIGHT: black 1px solid; PADDING-RIGHT: 4px; BORDER-TOP: black 1px solid; PADDING-LEFT: 4px; PADDING-BOTTOM: 4px; BORDER-LEFT: black 1px solid; PADDING-TOP: 4px; BORDER-BOTTOM: black 1px solid; BACKGROUND-COLOR: #ededed"><div id="19">
<div><!--
Code highlighting produced by Actipro CodeHighlighter (freeware)
[url]http://www.CodeHighlighter.com/[/url]
--><img src="http://cms.it168.com/Images/OutliningIndicators/None.gif" align="top" /><span style="COLOR: #000000"></span><span style="COLOR: #0000ff">set</span><span style="COLOR: #000000"> echo </span><span style="COLOR: #0000ff">off</span><span style="COLOR: #000000"> feed </span><span style="COLOR: #0000ff">off</span><span style="COLOR: #000000"> ver </span><span style="COLOR: #0000ff">off</span><span style="COLOR: #000000"> pages </span><span style="FONT-WEIGHT: bold; COLOR: #800000">0</span><span style="COLOR: #000000">
<img src="http://cms.it168.com/Images/OutliningIndicators/None.gif" align="top" />    spool audoff.sql
<img src="http://cms.it168.com/Images/OutliningIndicators/None.gif" align="top" />    </span><span style="COLOR: #0000ff">select</span><span style="COLOR: #000000"> ’noaudit </span><span style="COLOR: #0000ff">select</span><span style="COLOR: #000000"></span><span style="COLOR: #0000ff">on</span><span style="COLOR: #000000"> ’</span><span style="COLOR: #808080">||</span><span style="COLOR: #000000">owner</span><span style="COLOR: #808080">||</span><span style="COLOR: #000000">’.’</span><span style="COLOR: #808080">||</span><span style="COLOR: #ff00ff">object_name</span><span style="COLOR: #808080">||</span><span style="COLOR: #000000"> ’
<img src="http://cms.it168.com/Images/OutliningIndicators/None.gif" align="top" />    </span><span style="COLOR: #0000ff">by</span><span style="COLOR: #000000"> access;’
<img src="http://cms.it168.com/Images/OutliningIndicators/None.gif" align="top" />    </span><span style="COLOR: #0000ff">from</span><span style="COLOR: #000000"> dba_objects
<img src="http://cms.it168.com/Images/OutliningIndicators/None.gif" align="top" />    </span><span style="COLOR: #0000ff">where</span><span style="COLOR: #000000"> object_type </span><span style="COLOR: #808080">in</span><span style="COLOR: #000000"> (’</span><span style="COLOR: #0000ff">VIEW</span><span style="COLOR: #000000">’，’</span><span style="COLOR: #0000ff">TABLE</span><span style="COLOR: #000000">’) </span><span style="COLOR: #808080">and</span><span style="COLOR: #000000"> owner </span><span style="COLOR: #808080">in</span><span style="COLOR: #000000">
<img src="http://cms.it168.com/Images/OutliningIndicators/None.gif" align="top" />    (’ORDADM’);
<img src="http://cms.it168.com/Images/OutliningIndicators/None.gif" align="top" />    spool </span><span style="COLOR: #0000ff">off</span><span style="COLOR: #000000">
<img src="http://cms.it168.com/Images/OutliningIndicators/None.gif" align="top" />    </span><span style="COLOR: #0000ff">set</span><span style="COLOR: #000000"> echo </span><span style="COLOR: #0000ff">on</span><span style="COLOR: #000000"> feed </span><span style="COLOR: #0000ff">on</span><span style="COLOR: #000000"> ver </span><span style="COLOR: #0000ff">on</span><span style="COLOR: #000000"></span></div>
</div></pre></div><p /><div id="20"> 这些代码的运行将产生“audoff.sql”的输出文件。</div><p /><p /><div id="21"> 使用命令@ audoff.sql激活上述代码以关闭上述对象的审核功能。</div><p /><p /><div id="22"> 清除审核信息： </div><p /><p /><div id="23"> delete sys.aud$ </div><p /><p /><div id="24"> 分析审核信息</div><p /><div style="SCROLLBAR-HIGHLIGHT-COLOR: buttonhighlight; OVERFLOW: auto; WIDTH: 500px"><pre style="BORDER-RIGHT: black 1px solid; PADDING-RIGHT: 4px; BORDER-TOP: black 1px solid; PADDING-LEFT: 4px; PADDING-BOTTOM: 4px; BORDER-LEFT: black 1px solid; PADDING-TOP: 4px; BORDER-BOTTOM: black 1px solid; BACKGROUND-COLOR: #ededed"><div id="25">
<div><!--
Code highlighting produced by Actipro CodeHighlighter (freeware)
[url]http://www.CodeHighlighter.com/[/url]
--><img src="http://cms.it168.com/Images/OutliningIndicators/None.gif" align="top" /><span style="COLOR: #000000">    col obj_name form a30
<img src="http://cms.it168.com/Images/OutliningIndicators/None.gif" align="top" />    col owner form a20
<img src="http://cms.it168.com/Images/OutliningIndicators/None.gif" align="top" />    col hits form </span><span style="FONT-WEIGHT: bold; COLOR: #800000">99</span><span style="COLOR: #000000">，</span><span style="FONT-WEIGHT: bold; COLOR: #800000">990</span><span style="COLOR: #000000">
<img src="http://cms.it168.com/Images/OutliningIndicators/None.gif" align="top" />    selec obj_name，owner，hits </span><span style="COLOR: #0000ff">from</span><span style="COLOR: #000000"> aud_summary;
<img src="http://cms.it168.com/Images/OutliningIndicators/None.gif" align="top" />    OBJ_NAME OWNER </span><span style="COLOR: #ff00ff">COUNT</span><span style="COLOR: #000000">(</span><span style="COLOR: #808080">*</span><span style="COLOR: #000000">)
<img src="http://cms.it168.com/Images/OutliningIndicators/None.gif" align="top" />    </span><span style="COLOR: #008080">--</span><span style="COLOR: #008080">--------- ------------- ---------- </span><span style="COLOR: #008080">
<img src="http://cms.it168.com/Images/OutliningIndicators/None.gif" align="top" /></span><span style="COLOR: #000000">    DOCTOR_ORDERS ORDADM </span><span style="FONT-WEIGHT: bold; COLOR: #800000">30309</span><span style="COLOR: #000000">
<img src="http://cms.it168.com/Images/OutliningIndicators/None.gif" align="top" />    DRUG_STOCK PHARMACY </span><span style="FONT-WEIGHT: bold; COLOR: #800000">11094</span><span style="COLOR: #000000">
<img src="http://cms.it168.com/Images/OutliningIndicators/None.gif" align="top" />    GROUP_ORDER_ITEMS ORDADM </span><span style="FONT-WEIGHT: bold; COLOR: #800000">1030</span><span style="COLOR: #000000">
<img src="http://cms.it168.com/Images/OutliningIndicators/None.gif" align="top" />    GROUP_ORDER_MASTER ORDADM </span><span style="FONT-WEIGHT: bold; COLOR: #800000">1196</span><span style="COLOR: #000000">
<img src="http://cms.it168.com/Images/OutliningIndicators/None.gif" align="top" />    ORDERS ORDADM </span><span style="FONT-WEIGHT: bold; COLOR: #800000">40421</span><span style="COLOR: #000000">
<img src="http://cms.it168.com/Images/OutliningIndicators/None.gif" align="top" />    ORDERS_COSTS ORDADM </span><span style="FONT-WEIGHT: bold; COLOR: #800000">10109</span><span style="COLOR: #000000"></span></div>
</div></pre></div><p /><div id="26"> 以上是HIS系统临床医嘱部分24小时内对表的访问情况，从上面的查询得表1-1。</div><p /><p /><div id="27"><table cellspacing="0" cellpadding="2" width="61%" align="center" border="1"><tbody><tr><td>表</td><td>行数</td><td>命中率</td></tr><tr><td>doctor_orders</td><td>2052709</td><td>30309 </td></tr><tr><td>drug_stock</td><td>2511</td><td>11094 </td></tr><tr><td>group_order_item</td><td>3800</td><td>1030 </td></tr><tr><td>group_order_master</td><td>186</td><td>1196 </td></tr><tr><td>orders</td><td>1633010</td><td>40421 </td></tr><tr><td>orders_costs</td><td>2403214 </td><td>10109 </td></tr></tbody></table></div><p /><p /><div id="28"> group_order_master( 医嘱套攴主记录) 、group_order_item(医嘱套攴明细)， 表的行数比较少，不适合分区；drug_stock（药品库存）虽然存取频率比较高，但表的行数比较少，因此也不适合分区。我们选择表的行数比较多、存取频率比较高的表作分区处理，如 doctor_orders、orders、orders_costs，考虑到doctor_orders是医生工作站上医生开的医嘱，orders是由doctor_orders生成、护士工作站上执行的医嘱，两个表结构类似，而医嘱与药品、卫生材料、计费联系比较密切的是Orders，因此重点介绍对表orders的处理。</div><p /><p /><div id="29"><strong> 2、基于列值选择分区键</strong></div><p /><p /><div id="30"> 使用Sql*plus下用命令Analyze收集末分区表的统计信息，按照Oracle推荐的取样20%进行分析，并将统计结果保存在数据字典中。 </div><p /><div style="SCROLLBAR-HIGHLIGHT-COLOR: buttonhighlight; OVERFLOW: auto; WIDTH: 500px"><pre style="BORDER-RIGHT: black 1px solid; PADDING-RIGHT: 4px; BORDER-TOP: black 1px solid; PADDING-LEFT: 4px; PADDING-BOTTOM: 4px; BORDER-LEFT: black 1px solid; PADDING-TOP: 4px; BORDER-BOTTOM: black 1px solid; BACKGROUND-COLOR: #ededed"><div id="31">
<div><!--
Code highlighting produced by Actipro CodeHighlighter (freeware)
[url]http://www.CodeHighlighter.com/[/url]
--><img src="http://cms.it168.com/Images/OutliningIndicators/None.gif" align="top" /><span style="COLOR: #000000">   Analyze </span><span style="COLOR: #0000ff">table</span><span style="COLOR: #000000"> ORDADM.ORDERS estimate </span><span style="COLOR: #0000ff">statistics</span><span style="COLOR: #000000">
<img src="http://cms.it168.com/Images/OutliningIndicators/None.gif" align="top" />    sample </span><span style="FONT-WEIGHT: bold; COLOR: #800000">20</span><span style="COLOR: #000000"></span><span style="COLOR: #0000ff">percent</span><span style="COLOR: #000000">;  </span></div>
</div></pre></div><p /><div id="32"> 对DBA_TAB_COLUMNS数据字典视图进行查询</div><p /><p /><div id="33"> 产生表1-2 </div><p /><div style="SCROLLBAR-HIGHLIGHT-COLOR: buttonhighlight; OVERFLOW: auto; WIDTH: 500px"><pre style="BORDER-RIGHT: black 1px solid; PADDING-RIGHT: 4px; BORDER-TOP: black 1px solid; PADDING-LEFT: 4px; PADDING-BOTTOM: 4px; BORDER-LEFT: black 1px solid; PADDING-TOP: 4px; BORDER-BOTTOM: black 1px solid; BACKGROUND-COLOR: #ededed"><div id="34">
<div><!--
Code highlighting produced by Actipro CodeHighlighter (freeware)
[url]http://www.CodeHighlighter.com/[/url]
--><img src="http://cms.it168.com/Images/OutliningIndicators/None.gif" align="top" /><span style="COLOR: #000000"></span><span style="COLOR: #0000ff">select</span><span style="COLOR: #000000"> table_name，column_name，num_distinct
<img src="http://cms.it168.com/Images/OutliningIndicators/None.gif" align="top" />    </span><span style="COLOR: #0000ff">from</span><span style="COLOR: #000000"> DBA_TAB_COLUMNS
<img src="http://cms.it168.com/Images/OutliningIndicators/None.gif" align="top" />    </span><span style="COLOR: #0000ff">where</span><span style="COLOR: #000000"> owner </span><span style="COLOR: #808080">like</span><span style="COLOR: #000000"> ’ORDADM’;  </span></div>
</div></pre></div><p /><div id="35"><table cellspacing="0" cellpadding="2" width="28%" align="center" border="1"><tbody><tr><td>列</td><td>不同的值</td></tr><tr><td>PATIENT_ID </td><td>28720 </td></tr><tr><td>VISIT_ID</td><td>2 </td></tr><tr><td>ORDER_NO</td><td>395 </td></tr><tr><td>ORDER_SUB_NO </td><td>10 </td></tr><tr><td>ORDER_CLASS </td><td>9 </td></tr><tr><td>ORDER_CODE </td><td>825 </td></tr><tr><td>ORDER_TEXT</td><td>1551 </td></tr><tr><td>ORDERING_DEPT </td><td>15 </td></tr><tr><td>START_DATE_TIME </td><td>1194176 </td></tr><tr><td>STOP_DATE_TIME </td><td>636798 </td></tr></tbody></table></div><p /><p /><div id="36"> 从表1-2中，我们可以看到欲分区表的各个候选分区键的分布频谱，ORDER_CODE（医嘱代码）键值没有出现一种均匀分布，用它作分区键，明显不合适； </div><p /><p /><div id="37"> ORDER_CLASS（医嘱类别代码）、ORDERING_DEPT（开医嘱科室代码），键值出现均匀分布，如用它的各个键值作基于范围的分区，每个分区具有的记录数比较均匀，但这种方法对于每天增加上万条记录的表来看，显然不是最优的。如果选用START_DATE_TIME（医嘱开始时间）建立范围分区，每月的数据建立一个分区，在每个分区内基于ORDERING_DEPT建立散列子分区，每月的数据形成一个组合分区，会使每个分区的记录数分布均匀、查询速度提高、易于备份和删除。因为大多数的统计和查询是在一个月的范围内，而且从实际的查询效果看，跨月和跨年数据的统计和查询速度，也比未分区的时候大大缩短，效果非常明显。</div>

页: [1]
© 1999-2008 EvilOctal Security Team