以通配符(%)开始的like字符串,走索引
索引 字符串 开始 like 通配符
2023-09-14 08:57:45 时间
在对oracle的SQL优化过程中经常会遇到【like'%abc'】破坏索引的问题,但是如果真有此类需求,该如何在不破坏索引的基础上进行查询呢。
[sql] view plain copy sys@mescp> select reverse('123') from dual; REVERSE('123') -------------------------------- 321 1 row selected. sys@mescp> create table test_like as select object_id,object_name from dba_objects; Table created. sys@mescp> create index test_like__name on test_like(object_name); Index created. sys@mescp> create index test_like__name_reverse on test_like(reverse(object_name)); Index created. sys@mescp> analyze table test_like compute statistics for table for all indexes; Table analyzed. sys@mescp> set autotrace trace exp -- '常量开头的like , 会利用index ,没问题…… ' sys@mescp> select * from test_like where object_name like AS%'; Execution Plan ---------------------------------------------------------- 0 SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=655 Bytes=15720) 1 0 TABLE ACCESS (BY INDEX ROWID) OF 'TEST_LIKE' (Cost=2 Card=655Bytes=15720) 2 1 INDEX (RANGE SCAN) OF 'TEST_LIKE__NAME' (NON-UNIQUE) (Cost=2 Card=118) --'开头和结尾都是 % ,对不起,很难优化' sys@mescp> select * from test_like where object_name like '%%'; Execution Plan ---------------------------------------------------------- 0 SELECT STATEMENT Optimizer=CHOOSE (Cost=6 Card=655 Bytes=15720) 1 0 TABLE ACCESS (FULL) OF 'TEST_LIKE' (Cost=6 Card=655 ytes=15720) -- '以常量结束,直接写的时候是不能应用index的' sys@mescp> select * from test_like where object_name like '%S'; Execution Plan ---------------------------------------------------------- 0 SELECT STATEMENT Optimizer=CHOOSE (Cost=6 Card=655 Bytes=15720) 1 0 TABLE ACCESS (FULL) OF 'TEST_LIKE' (Cost=6 Card=655 Bytes=15720) --'以常量结束的,加个reverse 函数,又可以用上index了' sys@mescp> select * from test_like where reverse(object_name)like reverse('%AS'); Execution Plan ---------------------------------------------------------- 0 SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=655 Bytes=15720) 1 0 TABLE ACCESS (BY INDEX ROWID) OF 'TEST_LIKE' (Cost=2 Card=655 Bytes=15720) 2 1 INDEX (RANGE SCAN) OF 'TEST_LIKE__NAME_REVERSE' (NON-UNIQUE) (Cost=2 Card=118)
了解SQL的同学,都知道,like关键字可以走索引,只要字符串不是以通配符(%)开始。
如果类似 like "%xxx" 的sql,如何走索引呢?基于REVERSE()函数来创建一个函数索引。
1、准备数据:
CREATE TABLE jka AS SELECT ROWNUM id, dbms_random.string('x',10) v FROM dual CONNECT BY LEVEL <= 10000;
2、创建原始索引:
CREATE INDEX jka_normal ON jka (v); BEGIN dbms_stats.gather_table_stats (USER ,'JKA' ,estimate_percent=>100 ,cascade=>TRUE); END;
3、以下SQL基于jka_normal索引走范围扫描:
SELECT * FROM jka WHERE v LIKE 'ABC%';
4、但是下面的SQL将走全表扫描(不会使用索引):
SELECT * FROM jka WHERE v LIKE '%ABC';
5、现在,创建一个函数索引(不要与反向索引[REVERSE INDEX]混淆):
CREATE INDEX jka_reverse ON jka(REVERSE(v)); BEGIN dbms_stats.gather_table_stats (USER ,'JKA' ,estimate_percent=>100 ,cascade=>TRUE); END;
6、下面的SQL将基于jka_reverse索引走范围扫描:
SELECT * FROM jka WHERE REVERSE(v) LIKE REVERSE('%ABC');
问题解决。
相关文章
- MySQL索引优化(索引单表优化案例)
- 【华为云技术分享】HBase与AI/用户画像/推荐系统的结合:CloudTable标签索引特性介绍
- SQLServer中使用索引视图(物化视图)
- com.microsoft.sqlserver.jdbc.SQLServerException: 索引 7 超出范围。
- MySQL单列索引和组合索引(联合索引)的区别详解
- 数据库索引的实现原理(面试问题:请说出数据库索引实现原理)
- 删除指定表的所有索引,包括主键索引,唯一索引和普通索引 ,适用于sql server 2005 .
- 数据库索引使用数据结构及算法, 及MySQL不同引擎索引实现
- mysql 重新整理——索引优化explain字段介绍二 [十]
- Java客户端操作elasticsearch--查询索引库(带分页)
- 深入浅出PostgreSQL B-Tree索引结构
- 索引失效的情况及解决(超详细) 滴滴面试-
- 主键就是聚集索引吗?
- 【转】MySQL索引背后的数据结构及算法原理
- 华为OD机试 - 输出指定字母在字符串的中的索引(Java & JS & Python)
- Py之shap:shap.explainers.shap_values函数的简介、解读(shap_values[1]索引为1的原因)、使用方法之详细攻略
- PostgreSQL 索引扫描offset内核优化 - case
- 兜底方案只能用来兜底,而不能完全依靠它---记一次数据库唯一索引DuplicateKeyException异常的优化
- js数组索引值为小数,数组长度为多少
- Oracle使用并行建索引须要注意的问题
- 空间数据索引RTree完全解析及Java实现
- Mysql实战篇之怎么给字符串加索引--03