第十一章 索引的管理与维护
<p>[TOC]</p>
<h2>oracle引入索引的目的</h2>
<p>提高查询的效率</p>
<h2>索引的分类</h2>
<p>按逻辑和物理分类</p>
<h2>B-树索引</h2>
<p>简单原理</p>
<h2>位图索引</h2>
<p>简单原理</p>
<h2>B-树索引和位图索引的比较</h2>
<table>
<thead>
<tr>
<th style="text-align: left;">B-树(B-tree)索引</th>
<th style="text-align: left;">位图(Bitmap)索引</th>
</tr>
</thead>
<tbody>
<tr>
<td style="text-align: left;">适合于high-cardinality列(高枚举)</td>
<td style="text-align: left;">适合于low-cardinality列(低枚举)</td>
</tr>
<tr>
<td style="text-align: left;">对关键字列的修改相对不算昂贵</td>
<td style="text-align: left;">对关键字列的修改非常昂贵</td>
</tr>
<tr>
<td style="text-align: left;">使用谓词AND/OR查询效率不高</td>
<td style="text-align: left;">使用谓词AND/OR查询效率高</td>
</tr>
<tr>
<td style="text-align: left;">行一级的锁</td>
<td style="text-align: left;">位图段一级的锁</td>
</tr>
<tr>
<td style="text-align: left;">较多的存储</td>
<td style="text-align: left;">较少的存储</td>
</tr>
<tr>
<td style="text-align: left;">用于OLTP</td>
<td style="text-align: left;">用于DSS</td>
</tr>
</tbody>
</table>
<h2>创建索引</h2>
<ul>
<li>
<p>常用的数据字典及查询</p>
<blockquote>
<p>dba_indexes
dba_ind_columns
dba_indexes</p>
</blockquote>
</li>
<li>创建索引实例</li>
</ul>
<pre><code>--查询用户拥有的表、创建压缩表、查看表的数据块
select * from dba_objects where object_type='TABLE' and owner='HR'
create table hr.emp row store compress advanced as select * from hr.employees
select SEGMENT_NAME,blocks from dba_segments where owner='HR' and segment_name in('EMP','EMPLOYEES');
select num_rows,blocks,empty_blocks,row_movement from dba_tables where owner='HR' and table_name in('EMP','EMPLOYEES');
analyze table hr.employees compute statistics;
--为emp表last_name,job_id创建索引
select * from dba_tablespaces ;--选择indx表空间作为索引表空间
create index hr.emp_last_name_idx
on hr.emp(last_name)
pctfree 20
storage(initial 100K next 100K
pctincrease 0 maxextents 100)
tablespace indx;
create index hr.emp_job_id_idx
on hr.emp(job_id)
pctfree 20
storage(initial 100K next 100K
pctincrease 0 maxextents 100)
tablespace indx;
--查看创建的索引的属性
select index_name,table_name,tablespace_name,index_type,uniqueness,status from dba_indexes where owner='HR' and index_name like 'EMP_%';
select index_name,table_name,column_name,index_owner,table_owner from dba_ind_columns where table_owner='HR' and index_name like 'EMP_%';
select index_name,pct_free,pct_increase,initial_extent,next_extent from dba_indexes where owner='HR' and index_name like 'EMP_%';</code></pre>
<h2>重建和维护索引</h2>
<ul>
<li>重建索引</li>
</ul>
<pre><code>--重建hr.emp_last_name_idx索引，pctfree改为40
alter index hr.emp_last_name_idx rebuild pctfree 40 storage (next 300K);
select index_name,pct_free,pct_increase,initial_extent,next_extent from dba_indexes where owner='HR' and index_name like 'EMP_%';</code></pre>
<ul>
<li>维护索引</li>
</ul>
<pre><code>--当前索引段的磁盘使用情况，extent=2
select segment_name,segment_type,tablespace_name,extents,blocks from dba_segments where owner='HR' and segment_name like 'EMP%';
--手工增加 extent 的磁盘空间，extent=3
alter index hr.emp_last_name_idx allocate extent ;
--手工回收索引段中的磁盘空间
alter index hr.emp_last_name_idx deallocate unused;
--手工合并段中磁盘碎片
alter index hr.emp_last_name_idx coalesce;</code></pre>
<h2>标识索引的使用情况</h2>
<ul>
<li>监控使用过的索引后台记录</li>
</ul>
<pre><code>--开启监控索引(HR用户下完成)
alter index emp_last_name_idx monitoring usage;
--使用索引
select * from hr.emp where last_name like '%A%';
--查看监控的情况
select * from v$object_usage;--只显示当前用户的对象监控
select * from dba_object_usage;
--停止监控(HR用户下完成)，查看监控情况已经停止
alter index emp_last_name_idx nomonitoring usage;</code></pre>
<ul>
<li>监控没使用过的索引后台记录</li>
</ul>
<pre><code>--开启另一个索引的监控，观察，关闭，观察（观察索引不使用的情况）
alter index emp_job_id_idx monitoring usage;
select * from dba_object_usage;
alter index emp_job_id_idx nomonitoring usage;</code></pre>
<h2>删除索引</h2>
<ul>
<li>删除索引</li>
</ul>
<pre><code>--查看索引、删除索引、再查看索引
select index_name,table_name,tablespace_name,index_type,uniqueness,status from dba_indexes where owner='HR' and index_name like 'EMP_%';
drop index hr.emp_last_name_idx ;
drop index hr.emp_job_id_idx;</code></pre>
<h2>不可见索引</h2>
<ul>
<li>索引使用的监控没有用，试验不了。</li>
</ul>
<pre><code>--创建一个不可见索引，查看visibility=invisible
create index hr.emp_last_name_idx
on hr.emp(last_name) invisible
pctfree 20
storage(initial 100K next 100K
pctincrease 0 maxextents 100)
tablespace indx;
select index_name,table_name,tablespace_name,index_type,visibility,status from dba_indexes where owner='HR' and index_name like 'EMP_%';
--设置索引为不可见
alter index hr.emp_last_name_idx invisible;
alter index hr.emp_job_id_idx invisible;
--该值为true，oracle优化器不忽略不可见索引
show parameter optimizer_use_invisible_indexes;
--设置索引可用
alter index hr.emp_last_name_idx visible;
alter index hr.emp_job_id_idx visible;
--使用强制索引也没用
select /*+ index(hr.emp_last_name_idx)*/ * from hr.emp where last_name like '%A%';
select /*+ index(hr.emp_job_id_idx)*/ * from hr.emp where job_id like '%S%';
alter index hr.emp_last_name_idx monitoring usage;
alter index hr.emp_job_id_idx monitoring usage;
select * from dba_object_usage;
--修改如下参数不可见索引也可以使用
alter session set optimizer_use_invisible_indexes=true;
alter system set optimizer_use_invisible_indexes=true;
alter session set optimizer_use_invisible_indexes=false;
alter system set optimizer_use_invisible_indexes=false;</code></pre>
<h2>创建索引的应用实例</h2>
<ul>
<li>创建两个索引，再用两个视图查一下</li>
</ul>