锐客网

  1. 首页 > 生活百科 > it技术 > >

oracle怎么使用索引 oracle索引

IT技术索引

如何使用oracle中的索引方法如下： Oracle中建立索引 ， 会提高查询速度： create index 索引名 on 表名(列名); 例如： create index index_userid on tbl_detail(userid); 如何找数据库表的主键字段的名称? SELECT * FROM user_constraints WHERE CONSTRAINT_TYPE='P' a...
Oracle数据库强制索引 当where子句对某一列使用函数时 除非利用这个简单的技术强制索引 否则Oracle优化器不能在查询中使用索引
通常情况下 如果在WHERE子句中不使用诸如UPPER REPLACE 或SUBSTRD等函数 就不能对指定列建立特定的条件 但如果使用了这些函数 则会出现一个问题 这些函数会阻碍Oracle优化器对列使用索引 因而与采用索引的情况相比较 查询会花费更多的时间
庆幸的是 如果在使用函数的这些列中包含了字符型数据 可以用这样一种方法修改查询语句 以达到强制性使用索引 更有效地运行查询 这篇文章介绍了涉及的技术 并说明了在两种典型情况下怎样实现
大小写混合情况
在讨论由于函数修改了列的内容 如何强制使用索引前 让我们首先看看为什么Oracle优化器在这种情况下不能使用索引 假定我们要搜寻包含了大小写混合的数据 如在表 中ADDRESS表的NAME列 因为数据是用户输入的 我们无法使用已经统一改为大写的数据 为了找到每一个名为john的地址 我们使用包含了UPPER子句的查询语句 如下所示
SQL select address from address where upper(name) like JOHN ;
在运行这个查询语句前 如果我们运行了命令 set autotrace on 将会得到下列结果 其中包含了执行过程
ADDRESS cleveland row selected Execution Plan SELECT STATEMENT TABLE ACCESS FULL ADDRESS
可以看到 在这种情况下 Oracle优化器对ADDRESS 表作了一次完整的扫描 而没有使用NAME 列的索引 这是因为索引是根据列中数据的实际值建立的 而UPPER 函数已经将字符转换成大写 即修改了这些值 因此该查询不能使用这列的索引 优化器不能与索引项比较 JOHN 没有索引项对应于 JOHN 只有 john
值得庆幸的是 如果在这种情况下想要强制使用索引 有一种简便的方法 只要在WHERE 子句中增加一个或多个特定的条件 用于测试索引值 并减少需要扫描的行 但这并没有修改原来SOL 编码中的条件 以下列查询语句为例
SQL select address from address where upper(name) like JO% AND (name like J% or name like j% );
使用这种查询语句（已设置AUTOTRACE） 可得到下列结果
ADDRESS cleveland row selected Execution Plan SELECT STATEMENT CONCATENATION TABLE ACCESS BY INDEX ROWID ADDRESS INDEX RANGE SCAN ADDRESS_I TABLE ACCESS BY INDEX ROWID ADDRESS INDEX RANGE SCAN ADDRESS_I
现在 优化器为WHERE 子句中AND 联结的两个语句中每一个语句确定的范围进行扫描 第二个语句没有引用函数 因而使用了索引 在两个范围扫描后 将运行结果合并
在这个例子中 如果数据库有成百上千行 可以用下列方法扩充WHERE 子句 进一步缩小扫描范围
select address from address where upper(name) like JOHN AND (name like JO% or name like jo% or name like Jo or name like jO );
得到的结果与以前相同 但是 其执行过程如下所示 表明有 个扫描范围
Execution Plan SELECT STATEMENT CONCATENATION TABLE ACCESS BY INDEX ROWID ADDRESS INDEX RANGE SCAN ADDRESS_I TABLE ACCESS BY INDEX ROWID ADDRESS INDEX RANGE SCAN ADDRESS_I TABLE ACCESS BY INDEX ROWID ADDRESS INDEX RANGE SCAN ADDRESS_I TABLE ACCESS BY INDEX ROWID ADDRESS INDEX RANGE SCAN ADDRESS_I
如果试图进一步提高查询速度 我们可以在特定的 name like 条件中指明 个或更多的字符 然而 这样做会使得WHERE子句十分笨重 因为需要大小写字符所有可能的组合 joh Joh jOh joH等等 除此之外 指定一个或两个字符已足以加快查询的运行速度了
现在让我们看看 当我们引用不同的函数时 怎样运用这个基本技术
使用REPLACE的情况
正如名字不总是以大写输入一样 电话号码也会以许多格式出现 如 （ ） 等等
如果在列名为 PHONE_NUMBER中搜寻上述号码时 可能需要使用函数REPLACE以保证统一的格式 如果在PHONE_NUMBER列中只包含空格 连字符和数字 where 子句可以如下所示
WHERE replace(replace(phone_number ) ) =
WHERE子句两次使用REPLACE 函数去掉了连字符和空格 保证了电话号码是简单的数字串 然而 该函数阻止了优化器在该列使用索引 因此 我们按如下方法修改WHERE子句 以强制执行索引
WHERE replace(replace(phone_number ) ) = AND phone_number like %
如果我们知道数据中可能包含圆括号 WHERE 子句会稍微复杂一点 我们可以再增加REPLACE 函数（去掉圆括号 连字符和空格） 按如下所示扩充增加的条件
WHERE replace(replace(replace(replace(phone_number ) ) ( ) ) ) = AND (phone number like % or phone_number like ( % )
该例强调了巧妙地选用WHERE 子句条件的重要性 而且 这些条件不会改变查询结果 你的选择应基于完全了解该列中存在的信息类型 在该例中 我们需要知道 PHONE_NUMBER 数据中存在几种不同的格式 这样 我们能够修改WHERE 子句而不会影响查询结果
正确的条件 lishixinzhi/Article/program/Oracle/201311/18519
oracle怎么通过索引查询数据语句？oracle对于数据库中的表信息 ， 存储在系统表中 。查询已创建好的表索引，可通过相应的sql语句到相应的表中进行快捷的查询：\x0d\x0a1. 根据表名，查询一张表的索引\x0d\x0a\x0d\x0aselect * from user_indexes where table_name=upper('表名');\x0d\x0a\x0d\x0a2. 根据索引号，查询表索引字段\x0d\x0a\x0d\x0aselect * from user_ind_columns where index_name=('索引名');\x0d\x0a\x0d\x0a3.根据索引名，查询创建索引的语句\x0d\x0a\x0d\x0aselect dbms_metadata.get_ddl('INDEX','索引名', ['用户名']) from dual ; --['用户名']可省，默认为登录用户\x0d\x0a\x0d\x0aPS：dbms_metadata.get_ddl还可以得到建表语句 ， 如：\x0d\x0a\x0d\x0aSELECT DBMS_METADATA.GET_DDL('TABLE','表名', ['用户名']) FROM DUAL ; //取单个表的建表语句，['用户名']可不输入，默认为登录用户\x0d\x0aSELECT DBMS_METADATA.GET_DDL('TABLE',u.table_name) FROM USER_TABLES u; //取用户下所有表的建表语句\x0d\x0a\x0d\x0a当然，也可以用pl/sql developer工具来查看相关的表的各种信息 。
Oracle数据访问和索引的使用 · 通过全表扫描的方式访问数据；
· 通过ROWID访问数据；
· 通过索引的方式访问数据；
· Oracle顺序读取表中所有的行，并逐条匹配WHERE限定条件 。
· 采用多块读的方式进行全表扫描，可以有效提高系统的吞吐量，降低I/O次数 。
· 即使创建索引 ， Oracle也会根据CBO的计算结果，决定是否使用索引 。
注意事项：
· 只有全表扫描时才可以使用多块读 。该方式下，单个数据块仅访问一次 。
· 对于数据量较大的表，不建议使用全表扫描进行访问 。
· 当访问表中的数据量超过数据总量的5%—10%时 ， 通常Oracle会采用全表扫描的方式进行访问 。
· 并行查询可能会导致优化器选择全表扫描的方式 。1.2ROWID访问表
· Rowid是数据存放在数据库中的物理地址，能够唯一标识表中的一条数据 。
· Rowid指出了一条记录所在的数据文件、块号以及行号的位置，因此通过ROWID定位单行数据是最快的方法 。
注意事项：
· Rowid作为一个伪列 ， 其数值并不存储在数据库中，当查询时才进行计算 。
· Rowid除了在同一集簇中可能不唯一外，每条记录的Rowid唯一 。1.3 INDEX访问表
· 通过索引查找相应数据行的Rowid ， 再根据Rowid查找表中实际数据的方式称为“索引查找”或者“索引扫描” 。
· 一个Rowid对应一条数据行（根据Rowid查找结果，仅需要对Rowid相应数据的数据块进行一次I/O操作），因此该方式属于“单块读” 。
· 对于索引 ， 除了存储索引的数据外，还保存有该数据对应的Rowid信息 。
· 索引扫描分为两步：1）扫描索引确定相应的Rowid信息 。2）根据Rowid从表中获得对应的数据 。
注意事项：
· 对于选择性高的数据行 ， 索引的使用会提升查询的性能 。但对于DML操作，尤其是批量数据的操作 ， 可能会导致性能的降低 。
· 全表扫描的效率不一定比索引扫描差，关键看数据在数据块上的具体分布 。
索引是关系数据库中用于存放每一条记录的一种对象，主要目的是加快数据的读取速度和完整性检查 。建立索引是一项技术性要求高的工作 。一般在数据库设计阶段的与数据库结构一道考虑 。应用系统的性能直接与索引的合理直接有关 。
(1) 单列索引
单列索引是基于单个列所建立的索引 。
(2) 复合索引
复合索引是基于两列或是多列的索引，在同一张表上可以有多个索引，但是要求列的组合必须不同 。
(1) 重命名索引
(2) 合并索引
（表使用一段时间后在索引中会产生碎片，此时索引效率会降低，可以选择重建索引或者合并索引,合并索引方式更好些，无需额外存储空间，代价较低）
(3) 重建索引
方式一：删除原来的索引，重新建立索引
当不需要时可以将索引删除以释放出硬盘空间 。命令如下：
例如：
注：当表结构被删除时，有其相关的所有索引也随之被删除 。
方式二： Alter index 索引名称 rebuild;
· 通过创建唯一性索引，可以保证数据库表中每一行数据的唯一性 。
· 索引可以大大加快数据的检索速度，这是创建索引的最主要的原因 。
· 可以加速表和表之间的连接，特别是在实现数据的参考完整性方面特别有意义 。
· 在使用分组和排序子句进行数据检索时，同样可以显著减少查询中分组和排序的时间 。
· 通过使用索引，可以在查询的过程中，使用优化隐藏器，提高系统的性能 。
· 索引的层次不要超过4层 。
· 创建索引和维护索引要耗费时间，这种时间随着数据量的增加而增加 。
· 除了数据表占数据空间之外，每一个索引还要占一定的物理空间，如果要建立聚簇索引，那么需要的空间就会更大 。
· 当对表中的数据进行增加、删除和修改的时候，索引也要动态的维护，这样就降低了数据的维护速度 。
· 更新数据的时候，系统必须要有额外的时间来同时对索引进行更新 ， 以维持数据和索引的一致性 。
1) 不恰当的索引不但于事无补，反而会降低系统性能 。因为大量的索引在进行插入、修改和删除操作时比没有索引花费更多的系统时间 。
1) 应该建索引的列
· 在经常需要搜索的列上，可以加快搜索的速度；
· 在作为主键的列上 ， 强制该列的唯一性和组织表中数据的排列结构；
· 在经常用在连接的列上，这些列主要是一些外键，可以加快连接的速度；
· 在经常需要根据范围进行搜索的列上创建索引，因为索引已经排序，其指定的范围是连续的；
· 在经常需要排序的列上创建索引 ， 因为索引已经排序，这样查询可以利用索引的排序 ， 加快排序查询时间；
· 在经常使用在WHERE子句中的列上面创建索引，加快条件的判断速度 。
2) 不应该建索引的列
· 在大表上建立索引才有意义，小表无意义 。
· 对于那些在查询中很少使用或者参考的列不应该创建索引 。
· 对于那些只有很少数据值的列也不应该增加索引 。比如性别 ， 在查询的结果中，结果集的数据行占了表中数据行的很大比例，。增加索引，并不能明显加快检索速度 。
· 对于那些定义为blob数据类型的列不应该增加索引 。这是因为，这些列的数据量要么相当大，要么取值很少 。
· 当修改性能远远大于检索性能时，不应该创建索引 。
一个表中有几百万条数据，对某个字段加了索引，但是查询时性能并没有什么提高，这主要可能是oracle的索引限制造成的 。Oracle的索引有一些索引限制，在这些索引限制发生的情况下，即使已经加了索引 ， oracle还是会执行一次全表扫描，查询的性能不会比不加索引有所提高，反而可能由于数据库维护索引的系统开销造成性能更差 。
下面的查询即使在djlx列有索引，查询语句仍然执行一次全表扫描 。
把上面的语句改成如下的查询语句，这样，在采用基于规则的优化器而不是基于代价的优化器（更智能）时，将会使用索引 。
特别注意：通过把不等于操作符改成OR条件，就可以使用索引 ， 避免全表扫描 。
使用IS NULL或IS NOT NULL同样会限制索引的使用 。因此在建表时，把需要索引的列设成NOT NULL 。如果被索引的列在某些行中存在NULL值，就不会使用这个索引（除非索引是一个位图索引） 。
如果不使用基于函数的索引 ， 那么在SQL语句的WHERE子句中对存在索引的列使用函数时，会使优化器忽略掉这些索引 。下面的查询不会使用索引（只要它不是基于函数的索引）
也是比较难于发现的性能问题之一 。比如：bdcs_qlr_xz中的zjh是NVARCHAR2类型,在zjh字段上有索引 。如果使用下面的语句将执行全表扫描 。
因为Oracle会自动把查询语句改为
特别注意：不匹配的数据类型之间比较会让Oracle自动限制索引的使用,即便对这个查询执行Explain Plan也不能让您明白为什么做了一次“全表扫描” 。
(1) 索引无效
(2) 索引有效
Oracle创建索引SQL简单的例子，在表中的指定字段和如何使用索引呢？create index index_name on table_name(column_name) ;\x0d\x0a只要你查询使用到建oracle怎么使用索引了索引的字段oracle怎么使用索引，一般都会用到索引 。\x0d\x0a \x0d\x0a--创建表\x0d\x0acreate table aaa\x0d\x0a(\x0d\x0aa number,\x0d\x0ab number\x0d\x0a);\x0d\x0a--创建索引\x0d\x0acreate index idx_a on aaa (a);\x0d\x0a--使用索引\x0d\x0aselect * from aaa where a=1;\x0d\x0a这句查询就会使用索引 idx_a
【oracle怎么使用索引 oracle索引】关于oracle怎么使用索引和oracle索引的介绍到此就结束了 ， 不知道你从中找到你需要的信息了吗 ？如果你还想了解更多这方面的信息，记得收藏关注本站 。

    推荐阅读


    上一篇：视频原创用什么编辑软件，什么原创视频比较好做

    下一篇：如何搭建镜像网站，如何搭建镜像网站