oracle10g系统管理之簇管理（二）

<p class="MsoNormal" style=""><span style="" lang="EN-US"><span style=""><span style="font-family: Times New Roman;"><span style="font-size: small;">1.</span><span style='font: 7pt "Times New Roman";'> </span></span></span></span><span style="font-size: small;"><span style="">散列簇是使用散列函数定位行的位置，通过散列簇，可以将静态表的数据均匀地分布到数据块中。将表组织到散列簇后，如果在</span><span lang="EN-US"><span style="font-family: Times New Roman;">WHERE</span></span><span style="">子句中引用簇健列，</span><span lang="EN-US"><span style="font-family: Times New Roman;">Oracle</span></span><span style="">会根据散列函数结果定位表行数据。合理地使用散列簇。</span></span></p>
<p class="MsoNormal" style=""><span style="" lang="EN-US"><span style=""><span style="font-family: Times New Roman;"><span style="font-size: small;">2.</span><span style='font: 7pt "Times New Roman";'> </span></span></span></span><span style=""><span style="font-size: small;">使用散列表的指导方针</span></span></p>
<p class="MsoNormal" style=""><span style="" lang="EN-US"><span style=""><span style="font-family: Times New Roman;"><span style="font-size: small;">1)</span><span style='font: 7pt "Times New Roman";'> </span></span></span></span><span style="font-size: small;"><span style="">如果经常在等值查询中使用簇健列（</span><span lang="EN-US"><span style="font-family: Times New Roman;">SELECT </span></span><span style="">。。。</span><span lang="EN-US"><span style="font-family: Times New Roman;"> WHERE cluster_key=…</span></span><span style="">），可以将表组织到散列簇；如果经常在范围查询中引用特定列（</span><span lang="EN-US"><span style="font-family: Times New Roman;">SELECT </span></span><span style="">。。。</span><span lang="EN-US"><span style="font-family: Times New Roman;"> WHERE cluster_key>…</span></span><span style="">），则不应将表组织到散列簇。</span></span></p>
<p class="MsoNormal" style=""><span style="" lang="EN-US"><span style=""><span style="font-family: Times New Roman;"><span style="font-size: small;">2)</span><span style='font: 7pt "Times New Roman";'> </span></span></span></span><span style=""><span style="font-size: small;">如果表数据是静态的，可以考虑将该表组织到散列簇；如果表数据变化非常频繁，使用散列簇是不合理的。</span></span></p>
<p class="MsoNormal" style=""><span style="" lang="EN-US"><span style=""><span style="font-family: Times New Roman;"><span style="font-size: small;">3.</span><span style='font: 7pt "Times New Roman";'> </span></span></span></span><span style=""><span style="font-size: small;">建立散列簇</span></span></p>
<p class="MsoNormal" style="margin: 0cm 0cm 0pt 21pt;"><span lang="EN-US"><span style="font-size: small; font-family: Times New Roman;">CREATE CLUSTER employee_clu(id NUMBER(4))</span></span></p>
<p class="MsoNormal" style="margin: 0cm 0cm 0pt 21pt;"><span lang="EN-US"><span style="font-size: small; font-family: Times New Roman;">SIZE 500 HASHKEYS 1000 HASH is MOD(id,1000)</span></span></p>
<p class="MsoNormal" style="margin: 0cm 0cm 0pt 21pt;"><span lang="EN-US"><span style="font-size: small; font-family: Times New Roman;">TABLESAPCE user01;</span></span></p>
<p class="MsoNormal" style="margin: 0cm 0cm 0pt 21pt;"><span style=""><span style="font-size: small;">建立簇表</span></span></p>
<p class="MsoNormal" style="margin: 0cm 0cm 0pt 21pt;"><span style="font-size: small;"><span lang="EN-US"><span style="font-family: Times New Roman;">CREATE TABLE emp1</span></span><span style="">（</span></span></p>
<p class="MsoNormal" style="margin: 0cm 0cm 0pt 21pt;"><span lang="EN-US"><span style="font-size: small; font-family: Times New Roman;">id NUMBER(4),name VARCHAR2(30),sal NUMBER(6,2)</span></span></p>
<p class="MsoNormal" style="margin: 0cm 0cm 0pt 21pt;"><span style="font-size: small;"><span style="">）</span><span lang="EN-US"><span style="font-family: Times New Roman;">CLUSTER employee_clu(id);</span></span></span></p>
<p class="MsoNormal" style=""><span style="" lang="EN-US"><span style=""><span style="font-family: Times New Roman;"><span style="font-size: small;">4.</span><span style='font: 7pt "Times New Roman";'> </span></span></span></span><span style=""><span style="font-size: small;">显示簇信息</span></span></p>
<p class="MsoNormal" style="margin: 0cm 0cm 0pt 21pt;"><span style="font-size: small;"><span style="">建立索引簇或者散列簇后，</span><span lang="EN-US"><span style="font-family: Times New Roman;">Oracle</span></span><span style="">会将簇的相关信息存放到数据字典中，通过查询数据字典视图可以显示簇信息。</span></span></p>
<p class="MsoNormal" style="margin: 0cm 0cm 0pt 21pt;"><span lang="EN-US"><span style="font-size: small; font-family: Times New Roman;">SELECT cluster_name,tablespace_name FROM dba_clusters WHERE owner=’SCOTT’;</span></span></p>