1:索引的创建
自动:在使用primary和unique后系统会自动创建唯一索引
手动:create index 索引名 on 表名(字段1,....)
查询表上有哪些索引(网上找的,能用,表名和索引名要大写)
select t.*,i.index_type from user_ind_columns t,user_indexes i where t.index_name = i.index_name and t.table_name = i.table_name and t.table_name = 要查询的表
2、查找表的主键(包括名称,构成列):
select cu.* from user_cons_columns cu, user_constraints au where cu.constraint_name = au.constraint_name and au.constraint_type = 'P' and au.table_name = 要查询的表
3、查找表的唯一性约束(包括名称,构成列):
select column_name from user_cons_columns cu, user_constraints au where cu.constraint_name = au.constraint_name and au.constraint_type = 'U' and au.table_name = 要查询的表
4、查找表的外键(包括名称,引用表的表名和对应的键名,下面是分成多步查询):
select * from user_constraints c where c.constraint_type = 'R' and c.table_name = 要查询的表
查询外键约束的列名:
select * from user_cons_columns cl where cl.constraint_name = 外键名称
查询引用表的键的列名:
select * from user_cons_columns cl where cl.constraint_name = 外键引用表的键名
5、查询表的所有列及其属性
select t.*,c.COMMENTS from user_tab_columns t,user_col_comments c where t.table_name = c.table_name and t.column_name = c.column_name and t.table_name = 要查询的表
6、根据索引名称查找 目标表
select * from user_indexes where index_name ='索引名称';
2:语法
ON <schema>.<table_name>
(<column_name> | <expression> ASC | DESC,
<column_name> | <expression> ASC | DESC,...)
TABLESPACE <tablespace_name>
STORAGE <storage_settings>
LOGGING | NOLOGGING
COMPUTE STATISTICS
NOCOMPRESS | COMPRESS<nn>
NOSORT | REVERSE
PARTITION | GLOBAL PARTITION<partition_setting>
1)UNIQUE | BITMAP:指定UNIQUE为唯一值索引,BITMAP为位图索引,省略为B-Tree索引。
2)<column_name> | <expression> ASC | DESC:可以对多列进行联合索引,当为expression时即“基于函数的索引”
3)TABLESPACE:指定存放索引的表空间(索引和原表不在一个表空间时效率更高)
4)STORAGE:可进一步设置表空间的存储参数
5)LOGGING | NOLOGGING:是否对索引产生重做日志(对大表尽量使用NOLOGGING来减少占用空间并提高效率)
6)COMPUTE STATISTICS:创建新索引时收集统计信息
7)NOCOMPRESS | COMPRESS<nn>:是否使用“键压缩”(使用键压缩可以删除一个键列中出现的重复值)
8)NOSORT | REVERSE:NOSORT表示与表中相同的顺序创建索引,REVERSE表示相反顺序存储索引值
9)PARTITION | NOPARTITION:可以在分区表和未分区表上对创建的索引进行分区
3:优点及缺点
第一,通过创建唯一性索引,可以保证数据库表中每一行数据的唯一性。
第二,可以大大加快数据的检索速度,这也是创建索引的最主要的原因。
第三,可以加速表和表之间的连接,特别是在实现数据的参考完整性方面特别有意义。
第四,在使用分组和排序子句进行数据检索时,同样可以显著减少查询中分组和排序的时间。
第五,通过使用索引,可以在查询的过程中,使用优化隐藏器,提高系统的性能。
缺点:
第一,创建索引和维护索引要耗费时间,这种时间随着数据量的增加而增加。
第二,索引需要占物理空间,除了数据表占数据空间之外,每一个索引还要占一定的物理空间,如果要建立聚簇索引,那么需要的空间就会更大。
第三,当对表中的数据进行增加、删除和修改的时候,索引也要动态的维护,这样就降低了数据的维护速度。
4:如何判断在哪些字段上加索引
1)在经常需要搜索的列上,可以加快搜索的速度;
2)在作为主键的列上,强制该列的唯一性和组织表中数据的排列结构;
3)在经常用在连接的列上,这些列主要是一些外键,可以加快连接的速度;
4)在经常需要根据范围进行搜索的列上创建索引,因为索引已经排序,其指定的范围是连续的;
5)在经常需要排序的列上创建索引,因为索引已经排序,这样查询可以利用索引的排序,加快排序查询时间;
6)在经常使用在WHERE子句中的列上面创建索引,加快条件的判断速度。
不应该建索引列的特点:
第一,对于那些在查询中很少使用或者参考的列不应该创建索引。这是因为,既然这些列很少使用到,因此有索引或者无索引,并不能提高查询速度。相反,由于增加了索引,反而降低了系统的维护速度和增大了空间需求。
第二,对于那些只有很少数据值的列也不应该增加索引。这是因为,由于这些列的取值很少,例如人事表的性别列,在查询的结果中,结果集的数据行占了表中数据行的很大比例,即需要在表中搜索的数据行的比例很大。增加索引,并不能明显加快检索速度。
第三,对于那些定义为blob数据类型的列不应该增加索引。这是因为,这些列的数据量要么相当大,要么取值很少。
第四,当修改性能远远大于检索性能时,不应该创建索引。这是因为,修改性能和检索性能是互相矛盾的。当增加索引时,会提高检索性能,但是会降低修改性能。当减少索引时,会提高修改性能,降低检索性能。因此,当修改性能远远大于检索性能时,不应该创建索引。
以下网上找的
一,
oracle的索引陷阱
一个表中有几百万条数据,对某个字段加了索引,但是查询时性能并没有什么提高,这主要可能是oracle的索引限制造成的。
oracle的索引有一些索引限制,在这些索引限制发生的情况下,即使已经加了索引,oracle还是会执行一次全表扫描,查询的性能不会比不加索引有所提高,反而可能由于数据库维护索引的系统开销造成性能更差。
下面是一些常见的索引限制问题。
1、使用不等于操作符(<>, !=)
下面这种情况,即使在列dept_id有一个索引,查询语句仍然执行一次全表扫描
select * from dept where staff_num <> 1000;
但是开发中的确需要这样的查询,难道没有解决问题的办法了吗?
有!
通过把用 or 语法替代不等号进行查询,就可以使用索引,以避免全表扫描:上面的语句改成下面这样的,就可以使用索引了。
select * from dept shere staff_num < 1000 or dept_id > 1000;
2、使用 is null 或 is not null
使用 is null 或is nuo null也会限制索引的使用,因为数据库并没有定义null值。如果被索引的列中有很多null,就不会使用这个索引(除非索引是一个位图索引,关于位图索引,会在以后的blog文章里做详细解释)。在sql语句中使用null会造成很多麻烦。
解决这个问题的办法就是:建表时把需要索引的列定义为非空(not null)
3、使用函数
如果没有使用基于函数的索引,那么where子句中对存在索引的列使用函数时,会使优化器忽略掉这些索引。下面的查询就不会使用索引:
select * from staff where trunc(birthdate) = '01-MAY-82';
但是把函数应用在条件上,索引是可以生效的,把上面的语句改成下面的语句,就可以通过索引进行查找。
select * from staff where birthdate < (to_date('01-MAY-82') + 0.9999);
4、比较不匹配的数据类型
比较不匹配的数据类型也是难于发现的性能问题之一。
下面的例子中,dept_id是一个varchar2型的字段,在这个字段上有索引,但是下面的语句会执行全表扫描。
select * from dept where dept_id = 900198;
这是因为oracle会自动把where子句转换成to_number(dept_id)=900198,就是3所说的情况,这样就限制了索引的使用。
把SQL语句改为如下形式就可以使用索引
select * from dept where dept_id = '900198';
二,
各种索引使用场合及建议
(1)B*Tree索引。
常规索引,多用于oltp系统,快速定位行,应建立于高cardinality列(即列的唯一值除以行数为一个很大的值,存在很少的相同值)。
Create index indexname on tablename(columnname[columnname...])
(2)反向索引。
B*Tree的衍生产物,应用于特殊场合,在ops环境加序列增加的列上建立,不适合做区域扫描。
Create index indexname on tablename(columnname[columnname...]) reverse
(3)降序索引。
B*Tree的衍生产物,应用于有降序排列的搜索语句中,索引中储存了降序排列的索引码,提供了快速的降序搜索。
Create index indexname on tablename(columnname DESC[columnname...])
(4)位图索引。
位图方式管理的索引,适用于OLAP(在线分析)和DSS(决策处理)系统,应建立于低cardinality列,
适合集中读取,不适合插入和修改,提供比B*Tree索引更节省的空间。
Create BITMAP index indexname on tablename(columnname[columnname...])
在实际应用中,如果某个字段的值需要频繁更新,那么就不适合在它上面创建位图索引。
在位图索引中,如果你更新或插入其中一条数值为N的记录,
那么相应表中数值为N的记录(可能成百上千条)全部被Oracle锁定,
这就意味着其它用户不能同时更新这些数值为N的记录,其它用户必须要等第一个用户提交后,
才能获得锁,更新或插入数据,bitmap index它主要用于决策支持系统或静态数据。
(5)函数索引。
B*Tree的衍生产物,应用于查询语句条件列上包含函数的情况,
索引中储存了经过函数计算的索引码值。可以在不修改应用程序的基础上能提高查询效率。
索引创建策略
1.导入数据后再创建索引
2.不需要为很小的表创建索引
3.对于取值范围很小的字段(比如性别字段)应当建立位图索引
4.限制表中的索引的数目
5.为索引设置合适的PCTFREE值
6.存储索引的表空间最好单独设定
唯一索引和不唯一索引都只是针对B树索引而言.
Oracle最多允许包含32个字段的复合索引
由此估计出一个查询如果使用某个索引会需要读入的数据块块数。
需要读入的数据块越多,则 cost 越大,Oracle 也就越有可能不选择使用 index
三,
能用唯一索引,一定用唯一索引
能加非空,就加非空约束
一定要统计表的信息,索引的信息,柱状图的信息。
联合索引的顺序不同,影响索引的选择,尽量将值少的放在前面
相关推荐
oracle索引,常见索引问题,详见PPT内容
Oracle 索引 使用方法,索引 使用原理, 索引 使用顺序过程
oracle 索引的原理原理深入理解!
oracle索引类型及扫描方式大整理new
ORACLE索引详解及SQL优化,详细描述了几种常用索引原理以及创建方法,解读索引生效条件,以及在开发中常用的提高数据库效率、降低数据库资源消耗的方法。
oracle索引失效的总结
详细介绍了一些oracle索引被限制的一些情况
Oracle索引优化。Oracle索引优化。Oracle索引优化
Oracle索引的监控.pdfOracle索引的监控.pdfOracle索引的监控.pdfOracle索引的监控.pdf
oracle的索引,非常适合初学者,介绍索引的分类,如何创建,修改等
Oracle优化全攻略一【Oracle 索引概念】.docx
ORACLE 索引技术
oracle索引大全,包括所有的建立oracle索引的方法,不会的就来看看吧
oracle索引唯一一本经典的书,讲述索引类型,索引设计。英文原版~~~
oracle各种索引的区别与使用,由浅入深
Oracle数据库索引机制分析,内容很详细
Oracle索引的创建、简介、技巧,希望可以给学习Oracle的人带来帮助!
oracle索引与分区索引介绍 删除分区 分区合并(从中间删除掉一个分区,或者两个分区需要合并后减少分区数量) 创建新的分区 交换分区
Oracle数据库经典优化之索引原理篇 Oracle中建立索引并强制优化器 基于索引的SQL语句优化之降龙十八掌 30个Oracle语句优化规则详解-性能调优