数据库索引原理

0
SELECT STATEMENT Optimizer=CHOOSE
1 0 TABLE ACCESS (BY INDEX ROWID) OF 'I_CM_POWER'
2
1
INDEX (RANGE SCAN) OF 'IDX_I_CM_POWER_SJH'
(NON-UNIQUE)
Statistics ----------------------------------------------------------
创建基于函数的索引
。常用与 UPPER、LOWER、TO_CHAR(date)等函数分类上，例： create index idx_func on emp (UPPER(ename)) tablespace tablespace_name;
创建位图索引
。对基数较小，且基数相对稳定的列建立索引时，首先应该考虑位图索引， 例：
索引数据块空间使用
。创建索引时指定表空间，特别是在建立主键时，应明确指定表空间； 。合理设定 pctfress，注意：不能给索引指定 pctused； 。估计索引的大小和合理地设置存储参数，默认为表空间大小，或 initial 与 next 设置成一样大。
考虑并行创建索引
。对大表可以采用并行创建索引，在并行创建索引时，存储参数被每个查询 服务器进程分别使用，例如：initial 为 1M，并行度为 8，则创建索引期间至少 要消耗 8M 空间；
2
from
dba_segments
segment_name=upper('idx_i_cm_power_sjh');
where
SEGMENT_NAME
MB
-------------------- ----------
IDX_I_CM_POWER_SJH
1360
SQL>
无疑这个索引对于这样的简单查询是大有益处的:
from dba_segments where segment_name=upper('i_cm_power');
SEGMENT_NAME
BYTES/1024/1024/1024
-------------------- --------------------
I_CM_POWER
5.28173828125
如何创建局部分区索引
。基础表必须是分区表； 。分区数量与基础表相同； 。每个索引分区的子分区数量与相应的基础表分区相同； 。基础表的子分区中的行的索引项，被存储在该索引的相应的子分区中,例 如: Create Index TG_CDR04_SERV_ID_IDX On TG_CDR04(SERV_ID) Pctfree 5 Tablespace TBS_AK01_IDX Storage ( MaxExtents 32768 PctIncrease 0 FreeLists 1 FreeList Groups 1 ) local /
考虑用 nologging 创建索引
。对大表创建索引可以使用 nologging 来减少重做日志； 。节省重做日志文件的空间； 。缩短创建索引的时间； 。改善了并行创建大索引时的性能。
怎样建立最佳索引
明确地创建索引 create index index_name on table_name(field_name) tablespace tablespace_name pctfree 5 initrans 2 maxtrans 255 storage ( minextents 1 maxextents 16382 pctincrease 0 );
索引概述
概述 索引在各种关系型数据库系统中都是举足轻重的组成部分，其对于提高检索
数据的速度起至关重要的作用。在 Oracle 中，索引基本分为以下几种：B*Tree 索引，反向索引，降序索引，位图索引，函数索引，interMedia 全文索引等。 本文主要就前 6 种索引进行分析。
首先给出各种索引的简要解释： b*tree index：几乎所有的关系型数据库中都有 b*tree 类型索引，也是被 最多使用的。其树结构与二叉树比较类似，根据 rid 快速定位所访问的行。 反向索引：反转了 b*tree 索引码中的字节，是索引条目分配更均匀，多用 于并行服务器环境下，用于减少索引叶的竞争。 降序索引：8i 中新出现的索引类型，针对逆向排序的查询。 位图索引：使用位图来管理与数据行的对应关系，多用于 OLAP 系统。 函数索引：这种索引中保存了数据列基于 function 返回的值，在 select * from table where function（column）=value 这种类型的语句中起作用。 ORACLE 索引的分类：按照索引的基本分类可以分为 b*tree 索引，反向索引，降 序索引，位图索引,函数索引， interMedia 全文索引。
创建一个索引再说: SQL> create index idx_i_cm_power_sjh on i_cm_power(sjh);
Index created.
Elapsed: 00:20:50.73
SQL> col segment_name for a20
SQL> select segment_name,bytes/1024/1024 MB
如何创建范围分区的全局索引
。基础表可以是全局表和分区表。 create index idx_start_date on tg_cdr01(start_date) global partition by range(start_date) (partition p01_idx vlaues less than (‘0106’) partition p01_idx vlaues less than (‘0111’) … partition p01_idx vlaues less than (‘0401’ )) /
SQL> select * from i_cm_power t WHERE T.SJH='13911xxxxx6';
Elapsed: 00:00:00.07
Execution Plan
----------------------------------------------------------
SQL_TEXT ---------------------------------------------------------------select * from i_cm_power t WHERE T.SJH='13911xxxxx6'
检查了一下该查询访问的数据表,居然一个索引都没有: SQL> select index_name from dba_indexes table_name=upper('i_cm_power');
where
INDEX_NAME ------------------------------
没有索引意味着，即使为了获取这一条记录，Oracle 也必须对 5.28G 的一 个表进行全表扫描，如果不慢那就怪了:
SQL> col segment_name for a20 SQL> select segment_name,bytes/1024/1024/1024
索引并非总能带来性能提升，但是通常情况下，索引能加快访问，所以建表 的时候，你一定要知道还有索引这样一类对象。
下面这个案例是我们绝对不应该和不想看到的。
今天一个部门报数据库巨慢无比，上去看了一下，抓到如下的 SQL: SQL> select sql_text
2 from v$sqltext a 3 where a.hash_value = ( 4 select sql_hash_value from v$session b 5 where b.sid='&sid' 6) 7 order by piece asc 8/
为性能而百度文库排索引列
。经常一起使用多个字段检索记录，组合索引比单索引更有效； 。把最常用的列放在最前面，例：dx_groupid_serv_id(groupid,serv_id)， 在 where 条件中使用 groupid 或 groupid,serv_id，查询将使用索引，若仅用到 serv_id 字段，则索引无效； 。合并/拆分不必要的索引。
0 recursive calls 0 db block gets 6 consistent gets 0 physical reads 0 redo size 1022 bytes sent via SQL*Net to client 503 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 2 rows processed
Oracle 存储索引的数据结构是 B*树，位图索引也是如此，只不过是叶子节 点不同 B*数索引；
索引由根节点、分支节点和叶子节点组成，上级索引块包含下级索引块的索 引数据，叶节点包含索引数据和确定行实际位置的 rowid。
使用索引的目的 加快查询速度 减少 I/O 操作 消除磁盘排序
何时使用索引 查询返回的记录数 排序表<40% 非排序表 <7% 表的碎片较多（频繁增加、删除）
然而在实际中，你需要考虑更多的因素。
增加索引会占用更多的存储空间；索引的维护会增加数据库的负担，如果有 海量的数据加载，可能会极大影响性能...
所以事实可能总是比你想象的更复杂，你只有知道的更多...
ORACLE 索引与高性能 SQL 介绍
什么是索引
索引是建立在表的一列或多个列上的辅助对象，目的是加快访问表中的数 据；
create bitmap index idx_bitm on class (classno) tablespace tablespace_name;
明确地创建唯一索引
。可以用 create unique index 语句来创建唯一索引，例： create unique index dept_unique_idx on dept(dept_no) tablespace idx_1;
限制每个表索引的数量
。一个表可以有几百个索引（你会这样做吗？），但是对于频繁插入和更新 表，索引越多系统 CPU，I/O 负担就越重；
。建议每张表不超过 5 个索引。
删除不再需要的索引
。索引无效，集中表现在该使用基于函数的索引或位图索引，而使用了 B* 树索引；
。应用中的查询不使用索引； 。重建索引之前必须先删除索引，若用 alter index … rebuild 重建索引， 则不必删除索引。
创建与约束相关的索引
。可以用 using index 字句，为与 unique 和 primary key 约束相关的索引， 例如：
alter table table_name add constraint PK_primary_keyname primary key (field_name) using index tablespace tablespace_name；
索引的种类 非唯一索引（最常用） 唯一索引 位图索引 局部有前缀分区索引 局部无前缀分区索引 全局有前缀分区索引 散列分区索引 基于函数的索引
管理索引的准则
在表中插入数据后创建索引
。在用 SQL*Loader 或 import 工具插入或装载数据后，建立索引比较有效；
索引正确的表和列
。经常检索排序大表中 40%或非排序表 7%的行，建议建索引； 。为了改善多表关联，索引列用于联结； 。列中的值相对比较唯一； 。取值范围（大：B*树索引，小：位图索引）； 。Date 型列一般适合基于函数的索引； 。列中有许多空值，不适合建立索引
Oracle 初学者入门指南-索引是干什么用的?
关于索引是什么的最简单的比喻是，索引好像是表数据前面的目录表，里面 记录着各个章节的页码。
通过目录的页码我们可以快速的定位一个内容，同样通过索引记录的 rowid 我们可以快速的定位一条数据。
如同目录很难针对书中每个字词一样，索引也很难针对所有字段。 我们通常索引最能代表章节，记录属性的内容。