精华内容
下载资源
问答
  • oracle 不走索引的原因

    千次阅读 2017-03-10 11:42:07
    oracle 不走索引的原因 2013-02-01 13:46 9098人阅读 评论(0) 收藏 举报  分类: oracle(27)  create table tb2 as select * from emp; alter table tb2 modify empno number(4) not ...

    oracle 不走索引的原因

      9098人阅读  评论(0)  收藏  举报
      分类:
    create table tb2 as select * from emp;
    alter table tb2 modify empno number(4) not null;
    翻到20W行

    create index idxtb21 on tb2(empno);

    select INDEX_NAME from dba_indexes where table_name='TB2';--验证index建立起来

    set autotrace on;


    select distinct empno from tb2;--走index

    select distinct ename from tb2 where empno=7934;--未走index


    select distinct ename from tb2 where empno=7934;--hint后,也未走index


    create index idxtb22 on tb2(ename);--难道和ename有关系?(按理说应该没关系,建上吧)


    select    ename from tb2 where empno=7934;--还是不走index

    重建index

    alter index idxtb21 rebuild ;

    1.alter index xxx rebuild [online];
    是否加online,要看你的系统需求。因为不加online时rebuild会阻塞一切DML操作

    2.rebuild不是“将索引删除然后再创建”。rebuild时不会为了排序去走fts,
    而是遍历旧索引,然后在临时段中建立相应结构,完了 后移到新索引中。

    “将索引删除然后再创建”,是最不好的方法。

    select    ename from tb2 where empno=7934;--还是不走index

    真不懂了,难道只是因为我的优化器是CBO吗?
    或者是因为统计信息没有刷新吗?
    或者莫非是因为我的表因为是翻起来的,里面的重复值比较多?(这个可能性非常大)

    借网上文章看看后,还是不知原因,文章如下

    oracle 优化器 不走索引原因

    SQL优化器简介
    基于规则的优化器
    。总是使用索引
    。总是从驱动表开始(from子句最右边的表)
    。只有在不可避免的情况下,才使用全表扫描
    。任何索引都可以
    基于成本的优化器
    。需要表、索引的统计资料
    Analyze table customer compute statistics;
    Analyze table customer estimate statistics sample 5000 rows;
    。表中设置并行度、表分区

    优化器模式
    rule模式
    。总忽略CBO和统计信息而基于规则
    choose模式
    Oracle根据情况选择rule or first_rows or all_rows
    first_rows 模式
    。基于成本,以最快的速度返回记录,会造成总体查询速度的下降或消耗更多的资源,倾向索引扫描,适合OLTP系统
    all_rows模式
    。基于成本,确保总体查询时间最短,倾向并行全表扫描
    例如:
    Select last_name from customer order by last_name;用first_rows时,迅速返回记录,但I/O量大,用all_rows时,返回记录慢,但使用资源少。

    本文的目的:
    1、说一说Oracle的Optimizer及其相关的一些知识。
    2、回答一下为什么有时一个表的某个字段明明有索引,当观察一些SQL的执行计划时,发现确不走索引的问题。
    3、如果你对 FIRST_ROWS、 ALL_ROWS这两种模式有疑惑时也可以看一下这篇文章。

    开始吧:


    Oracle在执行一个SQL之前,首先要分析一下语句的执行计划,然后再按执行计划去执行。分析语句的执行计划的 工作 是 由优化器(Optimizer)来完成的。不同的情况,一条SQL可能有多种执行计划,但在某一时点,一定只有一种执行计划是最优的,花费时间是最少的。 相信你一定会用Pl/sql Developer、Toad等工具去看一个语句的执行计划,不过你可能对Rule、Choose、First rows、All rows这几项有疑问,因为我当初也是这样的,那时我也疑惑为什么选了以上的不同的项,执行计划就变了?

    1、优化器的优化方式

    Oracle的优化器共有两种的优化方式,即基于规则的优化方式(Rule-Based Optimization,简称为RBO)和基于代价的优化方式(Cost-Based Optimization,简称为CBO)。
    A、RBO方式:优化器在分析SQL语句时,所遵循的是Oracle内部预定的一些规则。比如 我们 常见的,当一个where子句中的一列有索引时去走索引。
    B、 CBO方式:依词义可知,它是看语句的代价(Cost)了,这里的代价主要指Cpu和内存。优化器在判断是否用这种方式时,主要参照的是表及索引的统计信 息。统计信息给出表的大小 、有少行、每行的长度等信息。这些统计信息起初在库内是没有的,是你在做analyze后才出现的,很多的时侯过期统计信息会令优化器做出一个错误的执行 计划,因些我们应及时更新这些信息。在Oracle8及以后的版本,Oracle列推荐用CBO的方式。

    我们要明了,不一定走索引就是优的 ,比如一个表只有两行数据,一次IO就可以完成全表的检索,而此时走索引时则需要两次IO,这时对这个表做全表扫描(full table scan)是最好的。

    2、优化器的优化模式(Optermizer Mode)

    优化模式包括Rule,Choose,First rows,All rows这四种方式,也就是我们以上所提及的。如下我解释一下:

    Rule:不用多说,即走基于规则的方式。

    Choolse:这是我们应观注的,默认的情况下Oracle用的便是这种方式。指的是当一个表或或索引有统计信息,则走CBO的方式,如果表或索引没统计信息,表又不是特别的小,而且相应的列有索引时,那么就走索引,走RBO的方式。

    First Rows:它与Choose方式是类似的,所不同的是当一个表有统计信息时,它将是以最快的方式返回查询的最先的几行,从总体上减少了响应时间。

    All Rows:也就是我们所说的Cost的方式,当一个表有统计信息时,它将以最快的方式返回表的所有的行,从总体上提高查询的吞吐量。没有统计信息则走基于规则的方式。

    3、如何设定选用哪种优化模式

    a、Instance级别

    我们可以通过在init.ora 文件中设定OPTIMIZER_MODE=RULE、OPTIMIZER_MODE=CHOOSE、 OPTIMIZER_MODE=FIRST_ROWS、OPTIMIZER_MODE=ALL_ROWS去选用3所提的四种方式,如果你没设定 OPTIMIZER_MODE参数则默认用的是Choose这种方式。

    B、Sessions级别

    通过SQL> ALTER SESSION SET OPTIMIZER_MODE=;来设定。

    C、语句级别

    这些需要用到Hint,比如:
    SQL> SELECT a.userid,
    2 b.name,
    3 b.depart_name
    4 FROM tf_f_yhda a,
    5 tf_f_depart b
    6 WHERE a.userid=b.userid;

    4、为什么有时一个表的某个字段明明有索引,当观察一些语的执行计划确不走索引呢?如何解决呢 ?

    A、不走索引大体有以下几个原因
    ♀你在Instance级别所用的是all_rows的方式
    ♀你的表的统计信息(最可能的原因)
    ♀你的表很小,上文提到过的,Oracle的优化器认为不值得走索引。
    B、解决方法
    ♀可以修改init.ora中的OPTIMIZER_MODE这个参数,把它改为Rule或Choose,重起数据库。也可以使用4中所提的Hint.

    补充:不走索引的原因,甚至加上hint 还不走索引,那可能是因为你要走索引的这列是nullable,虽然这列没有空值。(将字段改为not null)

    备注 : 不走索引的其它原因

    1、建立组合索引,但查询谓词并未使用组合索引的第一列,此处有一个INDEX SKIP SCAN概念。
    2、在包含有null值的table列上建立索引,当时使用select count(*) from table时不会使用索引。
    3、在索引列上使用函数时不会使用索引,如果一定要使用索引只能建立函数索引。

    如:

    Where条件中对字段增加处理函数将不使用该列的索引
    select * from emp where to_char(hire_date,'yyyymmdd')='20080411' (不使用)
    select * from emp where hire_date = to_char('20080411','yyyymmdd') (使用)


    4、当被索引的列进行隐式的类型转换时不会使用索引。如:select * from t where indexed_column = 5,而indexed_column列建立索引但类型是字符型,这时Oracle会产生
    隐式的类型转换,转换后的语句类似于select * from t where to_number(indexed_column) = 5,此时不走索引的情况类似于case3。日期转换也有类似问题,如:
    select * from t where trunc(date_col) = trunc(sysdate)其中date_col为索引列,这样写不会走索引,可改写成select * from t where date_col >= trunc(sysdate)
    and date_col < trunc(sysdate+1),此查询会走索引。
    5、并不是所有情况使用索引都会加快查询速度,full scan table 有时会更快,尤其是当查询的数据量占整个表的比重较大时,因为full scan table采用的是多块读,
    当Oracle优化器没有选择使用索引时不要立即强制使用,要充分证明使用索引确实查询更快时再使用强制索引。

    6、<>

    7、like’�’百分号在前

    8、not in ,not exist.

    展开全文
  • 1. 没有 WHERE 子句  2....  SELECT ...... comm 列的索引会失效 ... 3.... 如果没有使用基于函数的索引,那么 where 子句中对存在索引的列使用函数时,会使优化器忽略掉这些索引。例如:  select

           1. 没有 WHERE 子句

      2. 使用 IS NULL 和 IS NOT NULL

      SELECT ... FROM emp WHERE comm IS NULL; comm 列的索引会失效

      3. WHERE 子句中使用函数

      如果没有使用基于函数的索引,那么 where 子句中对存在索引的列使用函数时,会使优化器忽略掉这些索引。例如:

      select * from staff where trunc(birthdate) = '01-MAY-82';

      但是把函数应用在条件上,索引是可以生效的,把上面的语句改成下面的语句,就可以通过索引进行查找。

      select * from staff where birthdate < (to_date('01-MAY-82') + 0.9999);

      注意:对于 MIN, MAX 函数,Oracle 仍然使用索引。

      4. 使用 LIKE ‘%T’ 进行模糊查询

      5. WHERE 子句中使用不等于操作

      不等于操作包括:<>, !=, NOT colum >= ?, NOT colum <= ?

      对于这个限制条件可以通过 OR 替代,例如: colum <> 0 ===> colum>0 OR colum<0

      6. 等于和范围索引不会被合并使用

      SELECT emp_id, emp_m, salary_q ... FROM emp WHERE job='manager' AND deptno>10

      job 和 deptno 都是非唯一索引,这种条件下 oracle 不会合并索引,它只会使用第一个索引。

      7. 比较不匹配数据类型

      dept_id是一个varchar2型的字段,在这个字段上有索引,但是下面的语句会执行全表扫描。

      select * from dept where dept_id = 900198;

      这是因为 oracle 会自动把 where 子句转换成 to_number(dept_id)=900198,相当于使用函数,这样就限制了索引的使用。正确写法如下:

      select * from dept where dept_id = '900198';

    展开全文
  • 今天在做程序的时候发现原先写的索引竟然莫名奇妙地失效了。去找了一下大概原因如下,做了分析心得写上,希望能帮助大家 ...6,字符型字段为数字时在where条件添加引号. 7,对索引列进行运算.需要建

                   今天在做程序的时候发现原先写的索引竟然莫名奇妙地失效了。去找了一下大概原因如下,做了分析心得写上,希望能帮助大家

    首先了解失效索引的原因

      1,<>

      2,单独的>,<,(有时会用到,有时不会)

      3,like "%_" 百分号在前.

     4,表没分析.

     5,单独引用复合索引里非第一位置的索引列.

    6,字符型字段为数字时在where条件里不添加引号.

    7,对索引列进行运算.需要建立函数索引.

    8,not in ,not exist.

    9,当变量采用的是times变量,而表的字段采用的是date变量时.或相反情况。

    10, 索引失效。

    11,基于cost成本分析(Oracle因为走全表成本会更小):查询小表,或者返回值大概在10%以上

    12,有时都考虑到了 但就是不走索引,drop了从建试试在

    13,B-tree索引 is null不会走,is not null会走,位图索引 is null,is not null   都会走

    14,联合索引 is not null 只要在建立的索引列(不分先后)都会走, 

    in null时   必须要和建立索引第一列一起使用,当建立索引第一位置条件是is null 时,其他建立索引的列可以是is null(但必须在所有列都满足is null的时候),或者=一个值;
    当建立索引的第一位置是=一个值时,其他索引列可以是任何情况(包括is null =一个值),以上两种情况索引都会走。其他情况不会走。

    解决思路

    1,4,5,6,7,8不讲 应该都知道

    第四个:表没分析

    为什么要分析表?

    对于使用CBO很有好处,可以使用更可靠的table信息,从而执行计划也可以更准确一些,在10g会自动analyze,之前的版本需要手动定期

    生成统计信息,,选择合理的执行计划..

    Oracle的online document这样描述analyze的作用:
    Use the ANALYZE statement to collect non-optimizer statistics, for example, to:
    1、Collect or delete statistics about an index or index partition, table or table partition, index-organized table, cluster, or scalar object attribute.
    2、Validate the structure of an index or index partition, table or table partition, index-organized table, cluster, or object reference (REF).
    3、Identify migrated and chained rows of a table or cluster.
    对于收集统计信息这块,我一般使用dbms_stats这个package,收集统计信息的做用使CBO方式的优化选择执行计划更准确

    统计信息最好定期收集,以业务的不同确定不同的收集周期
    在新增的索引有可能没有收集 ,那么就会导致索引失效

      解决方法:

    分析方法有一下几种:

    analyze table t1 compute statistics fortable;  --针对表收集信息

    analyze table t2 compute statistics for allcolumns;  --针对表字段收集信息

    analyze table t3 compute statistics for all indexescolumns;  --收集索引字段信息

    analyze table t4 computestatistics;       --收集表,表字段,索引信息

    analyze table t5 compute statistics for allindexes;         --收集索引信息

    analyze table t6 compute statistics for table for all indexesfor allcolumns;   --

    --收集表,索引,表字段信息

    11.基于COST成本分析 
        在这种条件下 oracle会认为索引更占资源,就默认不走索引了。这种情况如果觉得索引快的 不妨试试强制索引 
     select   /*+INDEX(t IDEX_HZYB_JSJL_BDJSRQ)*/  
     from hzyb_jsjl t
     where BDJSRQ >TO_DATE('','');
    其中T是 表名   IDEX_HZYB_JSJL_BDJSRQ 是表的索引
    展开全文
  • Oracle查询不走索引

    千次阅读 2013-02-28 14:00:00
    网上对oracle查询不走索引的解释包括数据量较小(不值得走索引)、包含统计函数、未建立主键、对应列为nullable等等。后来,在与另外的同事讨论时才知道,原来是因为索引结果过多而进行全表遍历。表中总数据量为1...

    今天在进行sql条有的时候,发现子查询中总是不走索引,对于的字段是建立了索引了的,可是不管怎么折腾就是不通过索引进行查询。网上对oracle查询不走索引的解释包括数据量较小(不值得走索引)、包含统计函数、未建立主键、对应列为nullable等等。后来,在与另外的同事讨论时才知道,原来是因为索引结果过多而进行全表遍历。表中总数据量为1千万,而索引对应条件查询的结果有500多万,在进行执行计划分析时,oracle直接跳过索引而进行了全表查询。

    展开全文
  • oracle 时间条件值范围越大就不走索引问题解决:使用强制索引  在写一个比较复杂的统计语句的时候,其中涉及到了时间的条件。但在执行测试过程中发现开始时间和结束时间的范围在两三天的时候执行计划里是走的索引...
  • 但是有些情况下,即使建立了索引,但是执行写出来的查询还是很慢,然后通过执行计划会发现是索引失效导致的(不走索引,走全表扫描)。所以需要了解一下有哪些些情况会导致索引失效,即查询不走索引的原因。 在写...
  • Oracle SQL不走索引小记

    千次阅读 2017-09-26 23:26:29
    SQL执行计划走索引 一、优化器认为全表扫描更优。 在这种情况下,需要重新分析一遍表,更新表的状态,有助于优化器分析出正确的执行计划。 analyze table tablename compute statistics; analyze index ...
  • 使用oracle索引列时条件类型匹配列类型,将无法触发索引 表结构如下: 问题: AC 列上建立了索引IDX_ATEST_AC,但是却没有使用 衍生问题: 由于使用索引,造成操作时间变长;当此操作的表为主表并且...
  • oracle表强制走索引的方法

    万次阅读 2019-04-13 17:07:38
    在某些时候,即使查询条件索引字段依然不走索引, 这种情况下可以采取添加/*+index(表别名索引名)*/ 的方式,让查询强制走索引。 没走索引的情况下,查了全表: 强制走索引IDX_LOGIN_LOG_1112_TIME后,...
  • Oracle执行计划不走索引的原因总结

    千次阅读 2016-09-29 14:56:31
    Oracle数据库操作中,为什么有时一个表的某个字段明明有索引,当观察一些语的执行计划确不走索引呢?如何解决呢?本文我们主要就介绍这部分内容,接下来就让我们一起来了解一下。 不走索引大体有以下几个...
  • Oracle小于条件导致索引失效

    千次阅读 2020-07-13 11:34:37
    ORACLE索引的小发现基础索引建立创建一般索引创建唯一索引对订单表的ACCT_DATE进行查询:小于条件的执行计划: (小于等于执行计划与小于一样)SQL:执行计划:大于条件的执行计划: (大于等于执行计划与大于一样)SQL:...
  • Oracle如何创建条件索引

    千次阅读 2018-07-23 17:39:26
    首先讲述一个业务场景: 数据库商品表中有goods_id,goods_name,goods_price,status四...要求状态为1 的商品名称允许重复,状态为0的可以无限重复。 首先创建表: -- Create table create table TB_GOODS ...
  • Oracle-index索引解读

    万次阅读 2016-10-27 21:25:05
    概述Oracle-OLAP和OLTP解读Oracle-index索引解读Oracle-分区表解读Oracle-锁解读Oracle-等待事件解读Oracle-procedure/cursor解读 索引是数据库对象之一,用于加快数据的检索 索引是建立在表上的可选对象;索引的...
  • 查找了一个有索引(单列索引)的列的所有数据,结果使用索引快速全扫描或索引全扫描,而使用全表扫描...查找的时候,加个is not null条件,那就走索引了。故is null不会走索引的。 对于组合索引来说,如果其中
  • Oracle查询隐式类型转换不走索引

    千次阅读 2016-02-20 18:43:12
    1. 框架 mybatis,oracle ...查看了一下SQL,根据时间条件查询,时间是有索引的,但是CPU会突然飙高。 SELECT * FROM ( SELECT t1.*,rownum as rn FROM ( SELECT count(:"SYS_B_0") as COUNT ,T.USER_ID, T.USER_N
  • Oracle like使用索引

    千次阅读 2016-09-22 09:58:44
    Oracle 中 like 是否可以使用索引当我们扫描一个特别大的表的时候,如果使用索引,那么查询的效率会非常的慢,当我们使用like条件的时候,是否会触发索引?我们来做一下实验:SELECT * FROM t_ka02 where aka060 ...
  • ORACLE会使索引失效的条件

    千次阅读 2015-12-16 19:04:27
    1. 没有 WHERE 子句  2....  SELECT ...... comm 列的索引会失效 ... 3.... 如果没有使用基于函数的索引,那么 where 子句中对存在索引的列使用函数时,会使优化器忽略掉这些索引。例如:  select
  • Oracle 索引索引失效的原因

    千次阅读 2014-02-24 23:21:19
    oracle 索引有一些限制条件,如果你违反了这些索引限制条件,那么即使你已经加了索引oracle还是会执行一次全表扫描,查询的性能不会比索引有所提高,反而可能由于数据库维护索引的系统开销造成性能更差
  • Oracle索引

    千次阅读 2015-11-26 11:57:13
    Oracle索引 基本介绍: 索引是用于加速数据存取的数据对象,合理的使用索引可以大大降低I/O次数,从而提高数据访问性能。索引有很多种我们主要介绍常用的几种:   PS:因为在某些大表中,包含的数据非常...
  • ORACLE分区与索引

    2014-05-05 09:20:25
    对orcle大数据的查询的优化,ORACLE分区表、分区索引ORACLE对于分区表方式其实就是将表分段存储,一般普通表格是一个段存储,而分区表会分成多个段,所以查找数据过程都是先定位根据查询条件定位分区范围,即数据在...
  • Oracle索引原理

    千次阅读 2017-10-23 17:17:18
    oracle中的索引与mysql中的索引不一样,oracle索引是存储了索引列的值以及rowid值。而mysql索引分为聚集索引,非聚集索引。其中聚集索引只能有一个。 oracle索引的主要分为根,茎,叶子三部分。索引列值都是存放...
  • oracle 怎么使用索引

    千次阅读 2017-08-25 20:40:33
    索引使用
  • oracle数据库索引失效

    2021-01-19 22:45:53
    1. 随着表的增长,where条件出来的数据太多,大于15%,使得索引失效(会导致CBO计算走索引花费大于全表) 2. 统计信息失效 需要重新搜集统计信息 3. 索引本身失效 需要重建索引 下面是一些不会使用到索引的原因 ...
  • oracle left join走不索引问题分析

    千次阅读 2018-07-23 17:09:24
    写了个left join语句,如下:...对A表的a, b字段和B表的a, b字段都建了联合索引, 发现on后的条件怎么也走不索引 查到有个说法是只有where后的语句可以索引,试着改为(+) select A.c,B.d from A, B where ...
  • Oracle 视图和索引

    2020-05-30 21:45:21
    文章目录一、视图二、索引 一、视图 视图的概念:视图就是提供一个查询的窗口,所有数据来自于原表 语法 : CREATE VIEW 视图名称 AS 子查询 ---查询语句创建表 create table emp as select * from scott.emp; ...
  • 我们知道一个结论,一般的,在Oracle Btree索引中,如果索引列的可选择性很好,而你谓词条件过滤后的结果集极小,那么Oracle将通过索引很快的找到这条数据。 那么有一个问题,Oracle的CBO在查询之前,是怎么知道你...
  • oracle 联合索引

    2015-01-28 11:15:18
    oracle 联合索引 比如我有表 TABLE_A 字段ABCD 创建联合索引 create index indx_t on TABLE_A(B,C); 能使用索引条件: where 条件 有B 或者 B 和 C 只有C 无法使用索引...

空空如也

空空如也

1 2 3 4 5 ... 20
收藏数 105,137
精华内容 42,054
关键字:

oracle不走索引的条件