Oracle数据库中建⽴索引的基本⽅法讲解怎样建⽴最佳索引?
1、明确地创建索引
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
);
2、创建基于函数的索引
常⽤与UPPER、LOWER、TO_CHAR(date)等函数分类上，例：
create index idx_func on emp(UPPER(ename)) tablespace tablespace_name;
3、创建位图索引
对基数较⼩，且基数相对稳定的列建⽴索引时，⾸先应该考虑位图索引，例：
create bitmap index idx_bitm on class (classno) tablespace tablespace_name;
4、明确地创建唯⼀索引
可以⽤create unique index语句来创建唯⼀索引，例：
create unique index dept_unique_idx on dept(dept_no) tablespace idx_1;
5、创建与约束相关的索引
可以⽤using index字句，为与unique和primary key约束相关的索引，例：
alter table table_name
add constraint PK_primary_keyname primary key(field_name)
using index tablespace tablespace_name;
如何创建局部区索引？
1)基础表必须是分区表
2)分区数量与基础表相同
3)每个索引分区的⼦分区数量与相应的基础表分区相同
4)基础表的⾃分区中的⾏的索引项，被存储在该索引的相应的⾃分区中，例如
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
/
如何创建范围分区的全局索引？
基础表可以是全局表和分区表
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'))
/
如何重建现存的索引？
重建现存的索引的当前时刻不会影响查询
重建索引可以删除额外的数据块
提⾼索引查询效率
alter index idx_name rebuild nologging;
对于分区索引
alter index idx_name rebuild partition partition_name nologging;
删除索引的原因？
1)不再需要的索引
2)索引没有针对其相关的表所发布的查询提供所期望的性能改善
3)应⽤没有⽤该索引来查询数据
4)该索引⽆效，必须在重建之前删除该索引
5)该索引已经变的太碎了，必须在重建之前删除该索引
语句：
drop index idx_name;
drop index idx_name partition partition_name;
建⽴索引的代价？
基础表维护时，系统要同时维护索引，不合理的索引将严重影响系统资源，
主要表现在CPU和I/O上。
插⼊、更新、删除数据产⽣⼤量db file sequential read锁等待。
关于创建索引的建议
虽然说，在表中是否创建索引，不会影响到Oracle数据库的使⽤，也不会影响数据库语句的使⽤。这就好像即使字典没有⽬录的话，⽤户仍然可以使⽤它⼀样。可是，若字典没有⽬录，那么可想⽽知，⽤户要查某个条⽬的话，其不得不翻遍整本字典。数据库也是如此。若没有建⽴相关索引的话，则数据库在查询记录的时候，不得不去查询整个表。当表中的记录⽐较多的时候，其查询效率就会很低。所以，合适的索引，是提⾼数据库运⾏效率的⼀个很好的⼯具。
不过，并不是说表上的索引越多越好。过之⽽不及。故在数据库设计过程中，还是需要为表选择⼀些合适的索引。宁缺勿滥，这是建⽴索引时的⼀个遵循标准。在理论上，虽然⼀个表可以设置⽆限的索引。但是，数据库管理员需要知道，表中的索引越多，维护索引所需要的开销也就越⼤。每当数据表中记录有增加、删除、更新变化的时候，数据库系统都需要对所有索引进⾏更新。故数据库表中的索引绝对不是多多益善。具体来说，在索引建⽴上，笔者对⼤家有如下建议。
建议⼀：在基数⼩的字段上要善于使⽤位图索引。
基数是位图索引中的⼀个基本的定义，它是指数据库表中某个字段内容中不重复的数值。如在员⼯信息表中的性别字段，⼀般就只有男跟⼥两个值，所以，其基数为2；婚姻状况字段的话，则其只有已婚、未婚、离婚三种状态，其基数就为3；民族⼀览内也是只有有限的⼏个值。
对于要查询基数⼩的字段，如现在⽤户想查所有婚姻状况为“已婚”的“⼥性”时，利⽤位图索引可以提⾼查询的效率。这主要是因为标准索引是通过在索引中保存排序过的索引列以及对应的ROWID来实现的。若我们在基数⼩的列上建⽴标准索引的话，则其会返回⼤量的记录。oracle数据库表结构怎么看
⽽当我们在创建位图索引的时候，在Oracle会对整个表进⾏扫描，并且会为索引列的每个取值建⽴⼀个位图。若内容相同，则在位图上会以⼀个相同的数字表⽰。此时，若这个字段的基数⽐较⼩的话，则若需要实现对整个字段的查询的话，效率就会⾮常的⾼。因为此时，数据库只要位图中数字相同的内容
出来即可。
除了在数据表某列基数⽐较⼩的情况下，采⽤位图索引外，我们往往在⼀些特殊的情况下，也会建议采⽤位图索引。最常见的情况是，在Where限制条件中，若我们多次采⽤AND或者OR条件时，也建议采⽤位图索引。因为当⼀个查询饮⽤了⼀些部署了位图索引的列的时候，这些位图可以很⽅便的与AND或者Or 运算符操作结合以快速的出⽤户所需要的记录。
但是，这⾥要注意，不是在条件语句中包含运算符的时候，采⽤位图索引都能够提供⽐较⾼的效率。⼀般来说，只有AND 或者OR运算符的时候，位图索引才会⽐较具有优势。若此时⽤户采⽤⼤于号或者不等于号作为条件语句中的限制条件的时候，则往往采⽤标准索引具有更⼤的优势。
所以，笔者在数据库设置中，⼀般只有在三种情况下才采⽤位图索引。⼀是列的基数⽐较⼩，⽽有可能需要根据这些字段的内容查相关的记录;⼆是在条件语句中，⽤到了AND或者OR运算符的时候。除了这两种情况外，最好能够采⽤其他适合的索引。第三种情况是，需要⽤到NULL作为查询的限制条件。因为标准查询⼀般情况下，会忽略所有的NULL值列。也就是说，若需要查询“所有没有⾝份证号码”的员⼯的信息的时候，标准索引并不能够起到加速查询速度的作⽤。此时，就需要采⽤位图索引。因为位图索引会记录相关的NULL值列信息。
建议⼆：创建索引的⼀些限制条件。
并不说，表或者列建⽴的索引越多越好。相反，索引建的越多，有时会反⽽会影响数据库运⾏的整体性能。所以，在建⽴索引的时候，仍然会有⼀些限制条件。
⼀是不要对⼀些记录内容⽐较少的表建⽴索引。在⼀个应⽤系统设计的时候，如设计⼀个ERP系统的数据库，其虽然有⼏千张表。但是，并不是每张表都有⼤量记录的。相反，其中有近⼀半左右的数据表，可能其存储的数据不会超过百条。如员⼯登陆帐户密码表、企业部门信息表等等。对于这些记录内容⽐较少的表，我们建⽴最好不要为其建⽴索引。⽆论是表上的，还是字段上，都不要建⽴索引。
⼆是若表中的内容⽐较⼤，但是，这个表基本上不怎么查询的时候，则只需要在表上建⽴索引即可;⽽不需要在字段上建⽴索引。如现在在ERP系统中，有⼀张表是“AD_Table”。其存储的是这个数据库中相关表的信息。这张表只有在数据库设计的时候才会⽤到。故这张表中的记录虽然⽐较多，但是由于⽤户⽤的⽐较少，所以，⼀般没有必要为这张表建⽴列级别上的索引。⽽直接⽤表索引来代替。
三是在⼀些NULL字段上，要根据实际情况来判断是否要建⽴索引。如现在有⼀张⼈事档案的表格，其上⾯有两个字段，分别为“⾝份证号码”与“地区”。有时会为了某个原因，企业需要所有员⼯都在系统中登记他们的⾝份证号码，以⽅便他们办⼯资
卡、社会保险等等。所以⼈事管理可能需要经常的查询系统，看看有没有没有⾝份证号码的员⼯信息。此时，就需要利⽤条件“IS NULL”来查询我们所需要的记录。故为了提⾼查询效率，若某个记录可能为空，
并且经常需要以NULL为条件进⾏查询的时候，则最好给这个字段添加⼀个索引，并且最好建⽴位图索引。相反，若虽然可能会以NULL这个条件作为查询的限制语句，但是，⽤的不是很多的时候，则就没有必要为其建⽴索引。
建议三：多表连接查询的索引设计。
如现在有⼀个⼈事管理系统。⼈事经理想知道员⼯的社保缴纳情况。他需要知道员⼯的姓名、职务、户籍性质(农民户⼝跟居民户⼝费⽤不⼀样)、缴纳的情况等等。但是，这些信息包含在不同的表中。因为为了提⾼数据库的性能，在表中存储的可能只是某些序号，⽽不是具体的内容。如在社保表中，存储的是员⼯对应的编号，⽽不是员⼯的名字。所以，要得到这份报表的话，就可能需要关联员⼯基本信息表、公司组织结构表等表格，才能够查询到⽤户所需要的内容。
为此，就需要利⽤Join语句，把这些表格关联起来。为了提⾼数据库的查询效率，这些⽤来关联的字段，最好能够建⽴索引。这可以显著的提⾼查询的速度。
建议四：在表的更新速度与查询速度之间寻求⼀个平衡点。
众所周知，索引本⾝并不影响数据库的使⽤，其主要是为了提⾼数据库的查询效率。但是，由于当数据库的表中的数据更新的时候，包括记录的增加、删除、更改等等，都会对虽有的索引进⾏更新。
很明显，索引虽然可以提⾼查询速度。但是，也会对⼀些表的更新操作产⽣不良的影响。当在表中建⽴的索引越多，这个不利影响也会越⼤。故数据库管理员在设置索引的时候，还需要注意，在这两个之间需要⼀个均衡点。
按照⼀般的理论来说，当某个表多数⽤来查询、更新相对来说⽐较上的话，则要多多采⽤索引。相反，当某个表记录更新居主导，查询相对来说⽐较少的话，则不要建⽴太多的索引，避免对更新的速度差⽣不利影响。
在实际⼯作中，若某个表频繁的被视图所调⽤的话，则最好就好设置⽐较多的索引。在选择数据库索引时⼤家可以参考上⽂给出的四点建议，相信掌握了上⽂中的四点建议，⼤家⼀定能选择⼀款合适的索引类型。

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。