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=""><span style="font-family: Times New Roman;"> </span></span></span><span style="">索引簇是指使用索引定位簇健列数据的方法。如果用户需要经常执行连接查询显示主从表信息，应该将主从表组织到索引簇。</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 lang="EN-US"><span style="font-family: Times New Roman;">DEPT_EMP</span></span><span style="">，并将</span><span lang="EN-US"><span style="font-family: Times New Roman;">DEPT</span></span><span style="">和</span><span lang="EN-US"><span style="font-family: Times New Roman;">EMP</span></span><span style="">表组织到索引簇后，</span><span lang="EN-US"><span style="font-family: Times New Roman;">Oracle</span></span><span style="">会按照簇健</span><span lang="EN-US"><span style="font-family: Times New Roman;">DEPTNO</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;">INSERT</span></span><span style="">和</span><span lang="EN-US"><span style="font-family: Times New Roman;">UPDATE</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=""><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=""><span style="" lang="EN-US"><span style=""><span style="font-family: Times New Roman;"><span style="font-size: small;">5)</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;">6)</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 dept_emp_clu(deptno NUMBER(3))</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;">PCTFREE 20 PCTUSED 60</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 TABLESPACE user01;</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;">deptno</span></span><span style="">，</span><span lang="EN-US"><span style="font-family: Times New Roman;">PCTFREE</span></span><span style="">关键字用于指定数据块内为</span><span lang="EN-US"><span style="font-family: Times New Roman;">UPDATE</span></span><span style="">操作所预留空间的百分比，</span><span lang="EN-US"><span style="font-family: Times New Roman;">PCTUSED</span></span><span style="">用于指定将数据块标记为可重新插入数据的已用空间最低百分比，</span><span lang="EN-US"><span style="font-family: Times New Roman;">SIZE</span></span><span style="">用于指定每个粗键值相关行数据所占用的总计空间，其默认值为一个数据块的尺寸，</span><span lang="EN-US"><span style="font-family: Times New Roman;">TABLESPACE</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;">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=""><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 department</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(3) PRIMARY KEY,</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;">dname VARCHAR2(14),loc VARCHAR2(14)</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 dept_emp_clu(id)</span></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 employee</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;">eno NUMBER(4) PRIMARY KEY,</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;">ename VARCHAR2(14),</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;">job VARCHAR2(9),</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;">dept_id NUMBER(3) REFERENCES department</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 dept_emp_clu(dept_id)</span></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 lang="EN-US"><span style="font-size: small; font-family: Times New Roman;">CREATE INDEX dept_emp_idx ON CLUSTER dept_emp_clu</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 user02;</span></span></p>