oracle会隐式转换吗,Oracle like、不等于、隐式转换走索引与不走索引情况

最新推荐文章于 2024-01-23 18:18:56 发布
最新推荐文章于 2024-01-23 18:18:56 发布
阅读量854 收藏
点赞数
文章标签： oracle会隐式转换吗

1. 概述

# like

(1)当使用like查询时，后模糊匹配，则走索引，如like 'test%'

(2)当使用like查询时，前模糊匹配，则不走索引，如like '%test'

# <> 不走索引

因为不等于，即等于大量数据，所以不走索引

# 隐式转换，当发生在索引列时，不走索引，发生在条件值列时，走索引

(1)如果隐式转换发生在值列，则走索引，例如查询使用日期查询时，

select * from test_implic where bir_date = '20180122 14:22:32';

(2)如果索引列发生了隐式转换，则不走索引，如列数据类型为varchar2，使用如下查询时

select bir_date from test_implic where id = 2000;

(3)当number列等于字符串时，走索引

2.测试

(1)

like 后模糊匹配走索引

like 前模糊匹配走全表

# 创建测试表

create table test_bind(id number,name varchar2(20));

#插入数据

declare

i number;

begin

for i in 1..100000

loop

insert into test_bind values(i,'haha');

end loop;

end;

/

declare

i number;

begin

for i in 100000..100010

loop

insert into test_bind values(i,'test');

end loop;

end;

/

# 创建索引

create index IDX_TEST_BIND on test_bind(name);

# 收集统计信息

exec dbms_stats.gather_table_stats('LIBAI','TEST_BIND');

# 查询，后模糊匹配，可以看到走了索引

LIBAI@honor1 > set autotrace on

LIBAI@honor1 > select * from test_bind where name like 'te%';

ID NAME

---------------------------------------- ----------------------------------------

100001 test

100002 test

100003 test

100004 test

100005 test

100006 test

100007 test

100008 test

100009 test

100010 test

10 rows selected.

Elapsed: 00:00:00.00

Execution Plan

----------------------------------------------------------

Plan hash value: 2889536435

---------------------------------------------------------------------------------------------

| Id  | Operation                   | Name          | Rows  | Bytes | Cost (%CPU)| Time     |

---------------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT            |               |     9 |    90 |     3   (0)| 00:00:01 |

|   1 |  TABLE ACCESS BY INDEX ROWID| TEST_BIND     |     9 |    90 |     3   (0)| 00:00:01 |

|*  2 |   INDEX RANGE SCAN          | IDX_TEST_BIND |     9 |       |     2   (0)| 00:00:01 |

---------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

2 - access("NAME" LIKE 'te%')

filter("NAME" LIKE 'te%')

Statistics

----------------------------------------------------------

1  recursive calls

0  db block gets

5  consistent gets

0  physical reads

0  redo size

782  bytes sent via SQL*Net to client

519  bytes received via SQL*Net from client

2  SQL*Net roundtrips to/from client

0  sorts (memory)

0  sorts (disk)

10  rows processed

# 前模糊匹配，可以看到走了全表扫描

LIBAI@honor1 > select * from test_bind where name like '%st';

ID NAME

---------------------------------------- ----------------------------------------

100001 test

100002 test

100003 test

100004 test

100005 test

100006 test

100007 test

100008 test

100009 test

100010 test

10 rows selected.

Elapsed: 00:00:00.02

Execution Plan

----------------------------------------------------------

Plan hash value: 3519963602

-------------------------------------------------------------------------------

| Id  | Operation         | Name      | Rows  | Bytes | Cost (%CPU)| Time     |

-------------------------------------------------------------------------------

|   0 | SELECT STATEMENT  |           |  5001 | 50010 |    69   (2)| 00:00:01 |

|*  1 |  TABLE ACCESS FULL| TEST_BIND |  5001 | 50010 |    69   (2)| 00:00:01 |

-------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

1 - filter("NAME" LIKE '%st' AND "NAME" IS NOT NULL)

Statistics

----------------------------------------------------------

1  recursive calls

0  db block gets

236  consistent gets

0  physical reads

0  redo size

734  bytes sent via SQL*Net to client

519  bytes received via SQL*Net from client

2  SQL*Net roundtrips to/from client

0  sorts (memory)

0  sorts (disk)

10  rows processed

(2)

<> 不走索引

LIBAI@honor1 > select * from test_bind where name <> 'test';

ID NAME

---------------------------------------- ----------------------------------------

100001 test

100002 test

100003 test

100004 test

100005 test

100006 test

100007 test

100008 test

100009 test

100010 test

10 rows selected.

Elapsed: 00:00:00.01

Execution Plan

----------------------------------------------------------

Plan hash value: 3519963602

-------------------------------------------------------------------------------

| Id  | Operation         | Name      | Rows  | Bytes | Cost (%CPU)| Time     |

-------------------------------------------------------------------------------

|   0 | SELECT STATEMENT  |           |    18 |   180 |    69   (2)| 00:00:01 |

|*  1 |  TABLE ACCESS FULL| TEST_BIND |    18 |   180 |    69   (2)| 00:00:01 |

-------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

1 - filter("NAME"<>'haha')

Statistics

----------------------------------------------------------

1  recursive calls

0  db block gets

236  consistent gets

0  physical reads

0  redo size

734  bytes sent via SQL*Net to client

519  bytes received via SQL*Net from client

2  SQL*Net roundtrips to/from client

0  sorts (memory)

0  sorts (disk)

10  rows processed

(3)

隐式转换

# 构造测试环境

create table test_implic (id varchar2(20),name varchar2(20),bir_date date default sysdate);

declare

i varchar2(10);

begin

for i in 1..10000

loop

insert into test_implic values(i,'czh',sysdate);

end loop;

commit;

end;

/

create index idx_test_implic_id on test_implic(id);

create index idx_test_implic_bir_date on test_implic(bir_date);

exec dbms_stats.gather_table_stats('LIBAI','TEST_IMPLIC');

# 当varchar2类型等于数字时，不走索引

LIBAI@honor1 > select bir_date from test_implic where id = 2000;

BIR_DATE

-------------------

2020-01-19 20:00:51

Execution Plan

----------------------------------------------------------

Plan hash value: 965190314

---------------------------------------------------------------------------------

| Id  | Operation         | Name        | Rows  | Bytes | Cost (%CPU)| Time     |

---------------------------------------------------------------------------------

|   0 | SELECT STATEMENT  |             |     1 |    13 |    11   (0)| 00:00:01 |

|*  1 |  TABLE ACCESS FULL| TEST_IMPLIC |     1 |    13 |    11   (0)| 00:00:01 |

---------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

1 - filter(TO_NUMBER("ID")=2000)

Statistics

----------------------------------------------------------

1  recursive calls

0  db block gets

38  consistent gets

0  physical reads

0  redo size

531  bytes sent via SQL*Net to client

519  bytes received via SQL*Net from client

2  SQL*Net roundtrips to/from client

0  sorts (memory)

0  sorts (disk)

1  rows processed

LIBAI@honor1 > select bir_date from test_implic where id = to_char(2000);

BIR_DATE

-------------------

2020-01-19 20:00:51

Execution Plan

----------------------------------------------------------

Plan hash value: 3908402167

--------------------------------------------------------------------------------------------------

| Id  | Operation                   | Name               | Rows  | Bytes | Cost (%CPU)| Time     |

--------------------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT            |                    |     1 |    13 |     2   (0)| 00:00:01 |

|   1 |  TABLE ACCESS BY INDEX ROWID| TEST_IMPLIC        |     1 |    13 |     2   (0)| 00:00:01 |

|*  2 |   INDEX RANGE SCAN          | IDX_TEST_IMPLIC_ID |     1 |       |     1   (0)| 00:00:01 |

--------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

2 - access("ID"='2000')

Statistics

----------------------------------------------------------

1  recursive calls

0  db block gets

4  consistent gets

4  physical reads

0  redo size

531  bytes sent via SQL*Net to client

519  bytes received via SQL*Net from client

2  SQL*Net roundtrips to/from client

0  sorts (memory)

0  sorts (disk)

1  rows processed

# 当number等于字符串时，走索引

LIBAI@honor1 > select * from test_bind where id = '1000';

ID NAME

---------------------------------------- ----------------------------------------

1000 haha

Execution Plan

----------------------------------------------------------

Plan hash value: 2345277976

------------------------------------------------------------------------------------------------

| Id  | Operation                   | Name             | Rows  | Bytes | Cost (%CPU)| Time     |

------------------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT            |                  |     1 |    10 |     2   (0)| 00:00:01 |

|   1 |  TABLE ACCESS BY INDEX ROWID| TEST_BIND        |     1 |    10 |     2   (0)| 00:00:01 |

|*  2 |   INDEX RANGE SCAN          | IDX_TEST_BIND_ID |     1 |       |     1   (0)| 00:00:01 |

------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

2 - access("ID"=1000)

Statistics

----------------------------------------------------------

14  recursive calls

0  db block gets

33  consistent gets

0  physical reads

0  redo size

595  bytes sent via SQL*Net to client

519  bytes received via SQL*Net from client

2  SQL*Net roundtrips to/from client

1  sorts (memory)

0  sorts (disk)

1  rows processed

# 当日期等于字符串时，走索引

LIBAI@honor1 > select * from test_implic where bir_date = '20180122 14:22:32';

no rows selected

Execution Plan

----------------------------------------------------------

Plan hash value: 3390782276

--------------------------------------------------------------------------------------------------------

| Id  | Operation                   | Name                     | Rows  | Bytes | Cost (%CPU)| Time     |

--------------------------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT            |                          |     1 |    17 |     2   (0)| 00:00:01 |

|   1 |  TABLE ACCESS BY INDEX ROWID| TEST_IMPLIC              |     1 |    17 |     2   (0)| 00:00:01 |

|*  2 |   INDEX RANGE SCAN          | IDX_TEST_IMPLIC_BIR_DATE |     1 |       |     1   (0)| 00:00:01 |

--------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

2 - access("BIR_DATE"='20180122 14:22:32')

Statistics

----------------------------------------------------------

1  recursive calls

0  db block gets

2  consistent gets

4  physical reads

0  redo size

466  bytes sent via SQL*Net to client

508  bytes received via SQL*Net from client

1  SQL*Net roundtrips to/from client

0  sorts (memory)

0  sorts (disk)

0  rows processed

小小小簪娘
关注
  • 0
    点赞
  • 踩
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
oracle10g不走索引,not in 和 <> 不走索引
weixin_39866087的博客
04-06 1987
首先我们要知道的一点就是CBO的代码oracle是不会对我们公开的，起码现在是。所以本文中的结论不一定适用所有的版本。在应用本文的结论之前最好先试一下。ok 下面就是本文的结论，当你在where语句中使用不等于或者not in时候，oracle 倾向于忽略索引。 比如：SQL> Select * from test where text<>‘star‘;IDTEXT-------...
MySQL索引失效之隐式转换
虚幻私塾
01-09 728
Python微信订餐小程序课程视频 https://edu.csdn.net/course/detail/36074 Python实战量化交易理财系统 https://edu.csdn.net/course/detail/35475 常见索引失效： 条件索引字段"不干净"：函数操作、运算操作 隐式类型转换：字符串转数值；其他类型转换 隐式字符编码转换：按字符编码数据长度大的方向转换，避免数据截取 一、常见索引失效场景 root@test 10:50 > show create ta
模糊搜索c1 like '%a%'真的都不能走索引么
最新发布
gjc592的博客
01-23 1045
点击上方蓝字关注我某DBA：like '%a%'肯定走不了索引的。。。在MySQL数据库使用规范或优化建议中都明确说类似 like '%a%'的写法不走索引。那么，真的是在任何条件下这种写法都不能走索引么？1. 不走索引的情况创建一个测试表并插入测试数据CREATE TABLE test_tb1( id INT PRIMARY KEY , c1 VARCHAR(10), c2 VARCHAR(20...
浅析oracle数据库的SQL中存在隐式转换性能问题
shairvin的博客
07-04 472
对于发生隐式转换的SQL，可通过查看其执行计划中的谓词信息发现隐式转换发生的具体位置，特征关键字为TO_NUMBER、SYS_OP_C2C，对应执行步骤通常为全表扫描，即使有索引也将无法使用。当发生隐式转换时，可能会导致对应字段上的索引无法使用（即索引失效），进而产生性能问题。隐式转换不仅发生在where条件中，还会发生在表连接中，常常导致无法使用索引，严重影响SQL执行效率。在实践中ORACLE数据库的SQL中存在隐式转换（即执行平均处理行数：查询出的行数，更新的行数）会引发性能问题。
关于不等于是否可以走索引的问题
liuzhilong的博客
04-26 7591
以前在学oracle的时候，不等于不能走索引似乎是一条铁律 SQL> select a,count(*) from unequal_oracle group by a; A COUNT(*) ---------- ---------- 1 2048 2 1 create index idx_unequal on unequal_oracle(a); --a=2时，索引的过滤性是比较好的，这里我们取a<&
Oracle隐式类型转换导致索引失效
cpsl26415的博客
01-21 713
Oracle隐式类型转换导致索引失效隐式类型转换就是Oracle在判断类型是自动将varchar2类型转换为number类型，或者将number类型转换为varchar2类型，隐式类型转换必须要求是可以转化的，比如varchar...
Oracle执行计划不走索引的原因总结
12-14
在Oracle数据库中，执行计划不走索引是一个常见的性能问题，这可能会导致查询效率降低，尤其是在处理大量数据时。以下是一些可能导致Oracle执行计划不选择使用索引的原因及其解决策略。 1. **优化器模式设置**：...
oracle 索引不能使用深入解析
12-15
如果索引列的值分布不均匀，Oracle可能会认为全表扫描比使用索引更有效。可以通过单独分析索引列或创建直方图来提供更精确的统计信息。 第九，索引列是否允许为空值（NULL）。在SQL查询中，涉及到包含NULL值的计算...
在OracleE数据库的字段上建立索引的方法
12-16
除了大小写混合的问题，`REPLACE`函数也会引发类似的索引不被使用的状况。例如，如果`DESCRIPTION`列包含一些需要替换的文本，如下所示的查询将不会使用索引： ```sql SELECT * FROM table WHERE REPLACE...
oracle数据库索引失效
09-10
还有一些特定情况会导致索引不被使用： 1. 没有在查询条件中使用索引，或者查询条件与索引列不匹配。 2. 引导列未在查询中正确使用，这在复合索引中尤为重要。 3. 查询的数据量接近或超过表的30%。 4. 索引列使用了...
oracle使用索引与不使用索引的性能详析
12-16
前言 Oracle存储索引的数据结构是B*树。位图索引也是如此，仅仅只是是叶子节点不同B*数索引； 索引由根节点、分支节点和叶子节点组成。上级索引块包括下级索引块的索引数据，叶节点包括索引数据和确定行实际位置的rowid。 使用索引的目的： 加快查询速度 降低I/O操作 消除磁盘排序 何时使用索引： 查询返回的记录数 排序表<40% 非排序表 <7% 表的碎片较多（频繁添加、删除） 索引的种类 非唯一索引（最经常使用） 唯一索引 位图索引 局部有前缀分区索引 局部无前缀分区索引 全局有前缀分区索引 散列分区索引 基于函数的索引 ora
Oracle 11g新特性索引不可见
05-31
"Oracle 11g 新特性索引不可见" Oracle 11g 中引入了一个新特性，称为索引不可见（Index Invisible），它允许 DBA 将索引设置为不可见状态，使得优化器在选择查询计划时忽视该索引，而不影响索引的正常更新。 索引...
Day882.隐式函数转换索引问题 -MySQL实战
阿昌爱Java
02-07 577
对索引字段做函数操作，可能会破坏索引值的有序性，因此优化器就决定放弃走树搜索功能。第二个例子是隐式类型转换，第三个例子是隐式字符编码转换，它们都跟第一个例子一样，因为要求在索引字段上做函数操作而导致了全索引扫描。MySQL 的优化器确实有“偷懒”的嫌疑，即使简单地把 where id+1=1000 改写成 where id=1000-1 就能够用上索引快速查找，也不会主动做这个语句重写。因此，每次业务代码升级时，把可能出现的、新的 SQL 语句 explain 一下，是一个很好的习惯。
oracle常见错误及操作
lcfeng1982的专栏
03-05 3660
1、ORA-12519:TNS:没有找到适用的服务处理程序 最近oracle10g频繁报出ORA-12519的错,查看数据库状态基本判定为oracle进程数不够导致报错。 根据Oracle文档，SESSIONS和TRANSACTIONS的初始化参数应该源于PROCESSES参数，根据默认设置SESSIONS= PROCESSES * 1.1 + 5。oracle 10g默认session数为1
oracle数据不等于,Oracle中不等于号问题详解
weixin_34952098的博客
04-02 3004
在Oracle中，不等于号有以下几种方式：<>，!=，^=测试SQLcreate table test(id int,name varchar2(10),age int)insert into test(id,name,age) values(1,'zhangsan',23);insert into test(id,name,age) values(2,'lisi','');inser...
数据库位图索引的优缺点
pzqingchong的专栏
03-24 9764
数据库BTree索引、Hash索引、Bitmap位图索引的优缺点  (2016-01-05 17:13:40) 转载▼ http://www.devnote.cn/article/360.html 测试于：MySQL 5.5.25 当前测试的版本是Mysql 5.5.25只有BTree和Hash两种索引类型，默认为BTree。Oracle或其他类型
位图索引介绍
minwang593的专栏
10-09 1254
位图索引是指使用计算机的最小单位bit来存储列值，并使用它自动生成rowid的一种索引结构。直接将列值转换为位存储不仅在很大程度上节约了存储空间，而且通过各种位运算还可以解决现有索引不能解决的很多问题。      尽管它有很多其他索引无法比拟的优点，但同时也有很多难以克服的缺陷，致使它很难被灵活运用到所有的领域。位图索引主要被使用在数据仓库（DATA Warehouse）中。尽管创建和维护位图索
oracle数据库的隐式转换
weixin_43897241的博客
05-30 558
隐式转换无效数字
Oracle隐式数据类型转换对索引的影响
DK的专栏
11-03 1396
对于oracle数据库来说，在条件允许的情况下，oracle可以帮我们自动做数据类型的转换，但是oracle还是建议我们最好使用转换函数（to_char()、to_date()、to_number()等）做显示的转换 下面这个例子简单的测试了，由于oracle自动执行隐式转换 ，导致索引无法正确使用的例子 建表T，其中ID列是字符类型 scott@DB01> create table
oracle regexp_like走索引吗
06-01
Oracle的`REGEXP_LIKE`函数在某些情况下可以走索引，但并不是所有情况都可以。 如果正则表达式模式以一个锚定字符（如^或$）开头或结尾，那么Oracle可以使用索引来优化查询。例如，如果你使用`REGEXP_LIKE(column_...

“相关推荐”对你有帮助么？

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值