精华内容
下载资源
问答
  • 当where子句对一列使用函数时,除非利用这个简单的技术强制索引,否则Oracle优化器不能在查询中使用索引。 通常情况下,如果在WHERE子句中不使用诸如UPPER、REPLACE 或SUBSTRD等函数,就不能对指定列建立特定的...

    当where子句对某一列使用函数时,除非利用这个简单的技术强制索引,否则Oracle优化器不能在查询中使用索引。

    通常情况下,如果在WHERE子句中不使用诸如UPPER、REPLACE 或SUBSTRD等函数,就不能对指定列建立特定的条件。但如果使用了这些函数,则会出现一个问题:这些函数会阻碍Oracle优化器对列使用索引,因而与采用索引的情况相比较,查询会花费更多的时间。

    庆幸的是,如果在使用函数的这些列中包含了字符型数据,可以用这样一种方法修改查询语句,以达到强制性使用索引,更有效地运行查询。这篇文章介绍了涉及的技术,并说明了在两种典型情况下怎样实现。

    大小写混合情况

    在讨论由于函数修改了列的内容,如何强制使用索引前,让我们首先看看为什么Oracle优化器在这种情况下不能使用索引。假定我们要搜寻包含了大小写混合的数据,如在表1中ADDRESS表的NAME列。因为数据是用户输入的,我们无法使用已经统一改为大写的数据。为了找到每一个名为john的地址,我们使用包含了UPPER子句的查询语句。如下所示:

    SQL> seleCT address from address where upper(name) like 'JOHN';

    在运行这个查询语句前,如果我们运行了命令"set autotrace on", 将会得到下列结果,其中包含了执行过程:

    ADDRESS
        cleveland
        1 row selected.
        Execution Plan
        SELECT STATEMENT
        TABLE ACCESS FULL ADDRESS

    可以看到,在这种情况下,Oracle优化器对ADDRESS 表作了一次完整的扫描,而没有使用NAME 列的索引。这是因为索引是根据列中数据的实际值建立的,而UPPER 函数已经将字符转换成大写,即修改了这些值,因此该查询不能使用这列的索引。优化器不能与索引项比较"JOHN",没有索引项对应于"JOHN"-只有"john" 。

    值得庆幸的是,如果在这种情况下想要强制使用索引,有一种简便的方法:只要在WHERE 子句中增加一个或多个特定的条件,用于测试索引值,并减少需要扫描的行,但这并没有修改原来SQL 编码中的条件。以下列查询语句为例:

    SQL> select address from address where upper(name) like 'JO%' AND (name 
      like 'J%' or name like 'j%');

    使用这种查询语句(已设置AUTOTRACE),可得到下列结果:

    ADDRESS
        cleveland
        1 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' );

    得到的结果与以前相同,但是,其执行过程如下所示,表明有4个扫描范围。

    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"条件中指明3个或更多的字符。然而,这样做会使得WHERE子句十分笨重。因为需要大小写字符所有可能的组合-joh ,Joh,jOh,joH等等。除此之外,指定一个或两个字符已足以加快查询的运行速度了。

    现在让我们看看,当我们引用不同的函数时,怎样运用这个基本技术。

    使用REPLACE的情况

    正如名字不总是以大写输入一样,电话号码也会以许多格式出现: 如 123-456-7890, 123 456 7890,(123)456-7890 等等。

    如果在列名为 PHONE_NUMBER中搜寻上述号码时,可能需要使用函数REPLACE以保证统一的格式。如果在PHONE_NUMBER列中只包含空格、连字符和数字,where 子句可以如下所示:

    WHERE replace(replace(phone_number , '-' ) , ' ' ) = '1234567890'

    WHERE子句两次使用REPLACE 函数去掉了连字符和空格,保证了电话号码是简单的数字串。然而,该函数阻止了优化器在该列使用索引。因此,我们按如下方法修改WHERE子句,以强制执行索引。

    WHERE replace(replace(phone_number, '-' ) , ' ' ) = '1234567890'

    AND phone_number like '123% '如果我们知道数据中可能包含圆括号,WHERE 子句会稍微复杂一点。我们可以再增加REPLACE 函数(去掉圆括号、连字符和空格),按如下所示扩充增加的条件:

    WHERE replace(replace(replace(replace(phone_number , ' - ' ) ,' '), '( ' ) 
      , ' ) ' ) = '1234567890'
       AND (phone number like ' 123% ' or phone_number like ' (123% ' ) '

    该例强调了巧妙地选用WHERE 子句条件的重要性,而且,这些条件不会改变查询结果。你的选择应基于完全了解该列中存在的信息类型。在该例中,我们需要知道 PHONE_NUMBER 数据中存在几种不同的格式,这样,我们能够修改WHERE 子句而不会影响查询结果。

    正确的条件

    以后当你遇到包含CHARACTER 数据修改函数列的WHERE 子句时,应考虑怎样利用增加一个或两个特定的条件,迫使优化器使用索引。适当地选择一组特定的条件能减少扫描行,并且强制使用索引不会影响查询结果----但却提高了查询的执行速度。

    展开全文
  • Oracle某一个字段上,空值很多,能建立索引吗?如果可以,是否合理,是否失效?
  • <br />当where子句对一列使用函数时,除非利用这个简单的技术强制索引,否则Oracle优化器不能在查询中使用索引。 通常情况下,如果在WHERE子句中不使用诸如UPPER、REPLACE 或SUBSTRD等函数,就不能对...

    where子句对某一列使用函数时,除非利用这个简单的技术强制索引,否则Oracle优化器不能在查询中使用索引。

    通常情况下,如果在WHERE子句中不使用诸如UPPERREPLACE SUBSTRD等函数,就不能对指定列建立特定的条件。但如果使用了这些函数,则会出现一个问题:这些函数会阻碍Oracle优化器对列使用索引,因而与采用索引的情况相比较,查询会花费更多的时间。


    庆幸的是,如果在使用函数的这些列中包含了字符型数据,可以用这样一种方法修改查询语句,以达到强制性使用索引,更有效地运行查询。这篇文章介绍了涉及的技术,并说明了在两种典型情况下怎样实现。


    大小写混合情况


    在讨论由于函数修改了列的内容,如何强制使用索引前,让我们首先看看为什么Oracle优化器在这种情况下不能使用索引。假定我们要搜寻包含了大小写混合的数据,如在表1ADDRESS表的NAME列。因为数据是用户输入的,我们无法使用已经统一改为大写的数据。为了找到每一个名为john的地址,我们使用包含了UPPER子句的查询语句。如下所示:

    SQL> select address from address where upper(name) like 'JOHN';

    在运行这个查询语句前,如果我们运行了命令"set autotrace on", 将会得到下列结果,其中包含了执行过程:

    ADDRESS
    cleveland
    1 row selected.
    Execution Plan
    SELECT STATEMENT
    TABLE ACCESS FULL ADDRESS

     

    可以看到,在这种情况下,Oracle优化器对ADDRESS 表作了一次完整的扫描,而没有使用NAME 列的索引。这是因为索引是根据列中数据的实际值建立的,而UPPER 函数已经将字符转换成大写,即修改了这些值,因此该查询不能使用这列的索引。优化器不能与索引项比较"JOHN",没有索引项对应于"JOHN"-只有"john"


    值得庆幸的是,如果在这种情况下想要强制使用索引,有一种简便的方法:只要在WHERE 子句中增加一个或多个特定的条件,用于测试索引值,并减少需要扫描的行,但这并没有修改原来SQL 编码中的条件。以下列查询语句为例:

    SQL> select address from address where upper(name) like 'JO%' AND (name
    like 'J%' or name like 'j%');


    使用这种查询语句(已设置AUTOTRACE),可得到下列结果:

    ADDRESS
    cleveland
    1 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' );


    得到的结果与以前相同,但是,其执行过程如下所示,表明有4个扫描范围。

    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"条件中指明3个或更多的字符。然而,这样做会使得WHERE子句十分笨重。因为需要大小写字符所有可能的组合-joh ,Joh,jOh,joH等等。除此之外,指定一个或两个字符已足以加快查询的运行速度了。


    现在让我们看看,当我们引用不同的函数时,怎样运用这个基本技术。


    使用REPLACE的情况


    正如名字不总是以大写输入一样,电话号码也会以许多格式出现: 如 123-456-7890 123 456 7890,(123456-7890 等等。


    如果在列名为 PHONE_NUMBER中搜寻上述号码时,可能需要使用函数REPLACE以保证统一的格式。如果在PHONE_NUMBER列中只包含空格、连字符和数字,where 子句可以如下所示:

    WHERE replace(replace(phone_number , '-' ) , ' ' ) = '1234567890'


    WHERE子句两次使用REPLACE 函数去掉了连字符和空格,保证了电话号码是简单的数字串。然而,该函数阻止了优化器在该列使用索引。因此,我们按如下方法修改WHERE子句,以强制执行索引。


    WHERE replace(replace(phone_number, '-' ) , ' ' ) = '1234567890'

    AND phone_number like '123% '

     

    如果我们知道数据中可能包含圆括号,WHERE 子句会稍微复杂一点。我们可以再增加REPLACE 函数(去掉圆括号、连字符和空格),按如下所示扩充增加的条件:

    WHERE replace(replace(replace(replace(phone_number , ' - ' ) ,' '), '( ' )
    , ' ) ' ) = '1234567890'
    AND (phone number like ' 123% ' or phone_number like ' (123% ' ) '


    该例强调了巧妙地选用WHERE 子句条件的重要性,而且,这些条件不会改变查询结果。你的选择应基于完全了解该列中存在的信息类型。在该例中,我们需要知道 PHONE_NUMBER 数据中存在几种不同的格式,这样,我们能够修改WHERE 子句而不会影响查询结果。


    正确的条件


    以后当你遇到包含CHARACTER 数据修改函数列的WHERE 子句时,应考虑怎样利用增加一个或两个特定的条件,迫使优化器使用索引。适当地选择一组特定的条件能减少扫描行,并且强制使用索引不会影响查询结果----但却提高了查询的执行速度。

    展开全文
  • 当where子句对一列使用函数时,除非利用这个简单的技术强制索引,否则Oracle优化器不能在查询中使用索引。 通常情况下,如果在WHERE子句中不使用诸如UPPER、REPLACE 或SUBSTRD等函数,就不能对指定列建立特定的...
  • Oracle查询sql中即使某个字段加入索引,在查询字段 is null的情况下,也会索引失效。本文目的是如何在判断字段为空的情况下也使用索引。因实操环境公司封闭开发环境,无法展示执行计划,具体的需要大家实际操作...

    记录目的

    在Oracle查询sql中即使某个字段加入索引,在查询字段 is null的情况下,也会索引失效。本文目的是如何在判断字段为空的情况下也使用索引。因实操环境为公司封闭开发环境,无法展示执行计划,具体的需要大家实际操作。

    查询sql select * from mobile where phone is null;

    下策:使用联合索引

    在创建索引时使用联合索引,讲查询字段作为第一个字段,第二个随便加一个字段或者默认值,在数据量少的情况下,此方法有效。在我实验中查询的结果在300条时,会使用索引查询数据,在查询数据量为5w条时,不会使用此索引。

    create index mobile_phone_index on mobile(phone,1);
    第二个字段可以为某个字段或一个固定的数字,例如1、-1等

    建立此索引,查询sql情况下,数量少的情况会此索引 mobile_phone_index 会起作用。若数据量大,第一个字段 null 的量太多的情况下索引将不起作用。

    中策:使用函数索引—decode

    decode函数只有oracle数据库中有
    create index mobile_phone_index on mobile(decode(phone,null,’-1’,’-2’));

    此索引decode使用函数解释:当phone字段为null时,此字段为“-1”,其余情况下为“-2”。
    注:可以自定义扩展学习decode

    此情况下查询sql,一定会使用索引,但查询效率一般,具体可以查看执行计划。

    上策:使用位图索引加函数索引组合

    简单介绍位图索引,当一个表内数据量很大,但某个字段的只有某几个值,例如性别:男、女(只讨论这2种)。若此字段加普通索引(B+TREE)的情况将存储很大,效率极低,若使用位图索引,将极大的提高效率,存储只有男、女2种,由此就生成了长度为总数量,只包含01的字符串。具体的可以查看此文索引:位图索引理解解释。

    创建索引sql
    create bitmap index mobile_phone_index on mobile(decode(phone,null,’-1’,’-2’));
    此情况下查询sql,会索引的同时,查询效率比中策高,具体大家可以查看执行计划。

    尾言

    这只是在工作中碰到整理笔记,若存在错误的地方,请大家指正留言。

    展开全文
  • 数据库索引:索引有单列索引复合索引之说如何表的某个字段有主键约束和唯一性约束,则Oracle 则会自动在相应的约束列上建议唯一索引。数据库索引主要进行提高访问速度。建设原则:1、索引应该经常建在Where 子句经常...

    数据库索引:

    索引有单列索引

    复合索引之说

    如何某表的某个字段有主键约束和唯一性约束,则Oracle 则会自动在相应的约束列上建议唯一索引。数据库索引主要进行提高访问速度。

    建设原则:

    1、索引应该经常建在Where 子句经常用到的列上。如果某个大表经常使用某个字段进行查询,并且检索行数小于总表行数的5%。则应该考虑。

    2、对于两表连接的字段,应该建立索引。如果经常在某表的一个字段进行Order By 则也经过进行索引。

    3、不应该在小表上建设索引。

    优缺点:

    1、索引主要进行提高数据的查询速度。 当进行DML时,会更新索引。因此索引越多,则DML越慢,其需要维护索引。 因此在创建索引及DML需要权衡。

    创建索引:

    单一索引:Create Index On (Column_Name);

    复合索引: Create Index i_deptno_job on emp(deptno,job); —>在emp表的deptno、job列建立索引。

    select * from emp where deptno=66 and job='sals' ->走索引。

    select * from emp where deptno=66 OR job='sals' ->将进行全表扫描。不走索引

    select * from emp where deptno=66 ->走索引。

    select * from emp where job='sals' ->进行全表扫描、不走索引。

    如果在where 子句中有OR 操作符或单独引用Job 列(索引列的后面列) 则将不会走索引,将会进行全表扫描。

    sql 优化:

    当Oracle数据库拿到sql语句时,其会根据查询优化器分析该语句,并根据分析结果生成查询执行计划。

    也就是说,数据库是执行的查询计划,而不是sql语句。

    查询优化器有rule-based-optimizer(基于规则的查询优化器) 和Cost-Based-optimizer(基于成本的查询优化器)。

    其中基于规则的查询优化器在10g版本中消失。

    对于规则查询,其最后查询的是全表扫描。而CBO则会根据统计信息进行最后的选择。

    1、先执行From ->Where ->Group By->Order By

    2、执行From 字句是从右往左进行执行。因此必须选择记录条数最少的表放在右边。这是为什么呢?

    3、对于Where字句其执行顺序是从后向前执行、因此可以过滤最大数量记录的条件必须写在Where子句的末尾,而对于多表之间的连接,则写在之前。

    因为这样进行连接时,可以去掉大多不重复的项。

    4. SELECT子句中避免使用(*)ORACLE在解析的过程中,会将’*’ 依次转换成所有的列名,这个工作是通过查询数据字典完成的,这意味着将耗费更多的时间

    5、索引失效的情况:

    ① Not Null/Null 如果某列建立索引,当进行Select * from emp where depto is not null/is null。 则会是索引失效。

    ② 索引列上不要使用函数,SELECT Col FROM tbl WHERE substr(name,1,3 ) = 'ABC'

    或者SELECT Col FROM tbl WHERE name LIKE '%ABC%' 而SELECT Col FROM tbl WHERE name LIKE 'ABC%' 会使用索引。

    ③ 索引列上不能进行计算SELECT Col FROM tbl WHERE col / 10 > 10 则会使索引失效,应该改成

    SELECT Col FROM tbl WHERE col > 10 * 10

    ④ 索引列上不要使用NOT ( != 、 <> )如:SELECT Col FROM tbl WHERE col ! = 10

    应该 改成:SELECT Col FROM tbl WHERE col > 10 OR col < 10 。

    6、用UNION替换OR(适用于索引列)

    union:是将两个查询的结果集进行追加在一起,它不会引起列的变化。 由于是追加操作,需要两个结果集的列数应该是相关的,

    并且相应列的数据类型也应该相当的。union 返回两个结果集,同时将两个结果集重复的项进行消除。 如果不进行消除,用UNOIN ALL.

    通常情况下,用UNION替换WHERE子句中的OR将会起到较好的效果. 对索引列使用OR将造成全表扫描. 注意,以上规则只针对多个索引列有效.

    如果有column没有被索引,查询效率可能会因为你没有选择OR而降低. 在下面的例子中,LOC_ID 和REGION上都建有索引.

    高效:

    SELECT LOC_ID,LOC_DESC,REGION

    FROM LOCATION

    WHERE LOC_ID = 10

    UNION

    SELECT LOC_ID,REGION

    FROM LOCATION

    WHERE REGION = “MELBOURNE”

    低效:

    SELECT LOC_ID,REGION

    FROM LOCATION

    WHERE LOC_ID = 10 OR REGION = “MELBOURNE”

    如果你坚持要用OR,那就需要返回记录最少的索引列写在最前面.

    7. 用EXISTS替代IN、用NOT EXISTS替代NOT IN

    在许多基于基础表的查询中,为了满足一个条件,往往需要对另一个表进行联接. 在这种情况下,使用EXISTS(或NOT EXISTS)通常将提高查询的效率.

    在子查询中,NOT IN子句将执行一个内部的排序和合并. 无论在哪种情况下,NOT IN都是最低效的(因为它对子查询中的表执行了一个全表遍历).

    为了避免使用NOT IN,我们可以把它改写成外连接(Outer Joins)或NOT EXISTS.

    例子:

    高效: SELECT * FROM EMP (基础表) WHERE EMPNO > 0 AND EXISTS (SELECT ‘X’ FROM DEPT WHERE DEPT.DEPTNO = EMP.DEPTNO AND LOC = ‘MELB’)

    低效: SELECT * FROM EMP (基础表) WHERE EMPNO > 0 AND DEPTNO IN(SELECT DEPTNO FROM DEPT WHERE LOC = ‘MELB’)

    总结

    如果觉得编程之家网站内容还不错,欢迎将编程之家网站推荐给程序员好友。

    本图文内容来源于网友网络收集整理提供,作为学习参考使用,版权属于原作者。

    展开全文
  • 查看张表已经存在的索引以及类型 SELECT  b.uniqueness, a.index_name, a.table_name, a.column_name  FROM all_ind_columns a, all_indexes b WHERE a.index_name=b.index_name  AND a.table_name = ...
  • 概述关于优化这个体系博大精深,例如内核优化、系统优化、中间件优化、oracle优化、sql优化、网络优化等等,网上... 若没有索引,搜索某个记录时(例如查找name='wish')需要搜索所有的记录,因为不能保证只有一个wis...
  • 数据库索引: 索引有单列索引 复合索引之说 如何表的某个字段有主键约束和唯一性约束,则Oracle 则会自动在相应的约束列上建议唯一索引。... 2、对于两表连接的字段,应该建立索引。如果经常在表的一个
  • 当where子句对一列使用函数时,除非利用这个简单的技术强制索引,否则Oracle优化器不能在查询中使用索引。 通常情况下,如果在WHERE子句中不使用诸如UPPER、REPLACE 或SUBSTRD等函数,就不能对指定列建立特定的...
  • Oracle 建立索引

    2016-08-25 17:03:58
    Oracle 建立索引及SQL优化 数据库索引: 索引有单列索引 复合索引之说 如何表的某个字段有主键约束和唯一性约束,则Oracle 则会自动在相应的约束列上建议唯一索引。数据库索引主要进行提高访问速度。...
  • 3、查询用户表的索引(非聚集索引): 4、查询用户表的主键(聚集索引): 5、查询表的索引 6、查询表的主键 7、查找表的唯一性约束(包括名称,构成列): 8、查找表的外键 查询外键约束的列名: 查询引用表的键的...
  • Oracle 建立索引及SQL优化 数据库索引: 索引有单列索引 复合索引之说 如何表的某个字段有主键约束和唯一性约束,则Oracle 则会自动在相应的约束列上建议唯一索引。数据库索引主要进行提高访问速度。 建设原则...
  •  但是除了在每个字段建立索引之外,还可以有如下的建立方法: 1. 建立查询条件的联合索引:  把查询条件中 某个表用到的全部字段 合在一起,建立一个索引,可以提高查询效率 2. 建立查询结果的联合...
  • 时间字段索引

    千次阅读 2020-03-24 18:29:58
    文章目录时间字段索引(待更进)一、问题1、描述:日期不一致2、原因:时区不同3、解决方法: 时区修改二、datetime和varchar类型效率比较1、背景2、开始三、时间字段索引1、聚集索引2、非聚集索引3、时间字段...
  • 3、经常与其他表进行连接的表,在连接字段上应该建立索引;4、经常出现在Where子句中的字段,特别是大表的字段,应该建立索引;5、索引应该建在选择性高的字段上;6、索引应该建在小字段上,对于大的文本字段甚至...
  • oracle大数据表 如何建立索引

    万次阅读 2006-02-26 15:01:00
    大数据表,按某字段T(Varchar型的时间字段)分区,一个月一个分区。有常用业务字段A和B(其它字段并不重要),字段A值的重复率不高,B的重复率稍高。建有索引1:A,组合索引2:T,B。对该表的常用查询有两种:通过...
  • 工作中处理数据时,发现某个表的数据达近亿条,所以要表建索引提高查询性能,以下两篇文章总结的很好,记录一下,以备后用。 数据库建立索引常用的规则如下: 1、表的主键、外键必须有索引;2、数据量超过300的...

空空如也

空空如也

1 2 3 4 5 ... 20
收藏数 45,702
精华内容 18,280
关键字:

oracle为某个字段建立索引