LIKE陈述时间过长 [英] LIKE statement taking too long

问题描述

请参见下面的DDL:

create table #VRMs (ID INT, VRM VARCHAR(10))
INSERT INTO #VRMs VALUES (1,'VRM1')
INSERT INTO #VRMs VALUES (1,'VRM2')
INSERT INTO #VRMs VALUES (1,'VRM3')

用户希望能够像这样搜索VRM:

The users want to be able to search for VRMs like this:

select * from #VRMs where VRM LIKE '%VRM1'

问题是此表中有100,000,000行，查询花费的时间太长.我遇到了这个问题: https://stackoverflow.com/questions/33210269/like-statement -takeing-too-long

The problem is that there are 100,000,000 rows in this table and the queries are taking far too long. I have come across this question: https://stackoverflow.com/questions/33210269/like-statement-taking-too-long

不幸的是，我正在使用Oracle DBMS.我已使用SSIS将所有VRM导入到SQL数据库中，并尝试了以下查询:

Unfortunately I am using an Oracle DBMS. I have imported all the VRMs into an SQL database using SSIS and have tried the following query:

select * from #VRMs where reverse(VRM) LIKE reverse('%VRM1')

查询在不到一秒钟的时间内运行.但是，它可以在487秒内在Oracle数据库上运行.如何优化它以使其在Oracle数据库中运行得更快?

The query runs in a fraction of a second. However, it runs in 487 seconds on the Oracle database. How can I optimise it to run faster in the Oracle database?

推荐答案

使用基于函数的索引

 create index tst_rev on tst(reverse(txt));

查询

 select * from tst where reverse(txt) like 'x10%';

 101x
 201x
 301x
 401x

导致索引范围扫描

 ---------------------------------------------------------------------------------------
 | Id  | Operation                   | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
 ---------------------------------------------------------------------------------------
 |   0 | SELECT STATEMENT            |         |   100 | 10400 |     0   (0)| 00:00:01 |
 |   1 |  TABLE ACCESS BY INDEX ROWID| TST     |   100 | 10400 |     0   (0)| 00:00:01 |
 |*  2 |   INDEX RANGE SCAN          | TST_REV |    18 |       |     0   (0)| 00:00:01 |
 ---------------------------------------------------------------------------------------

 ....

    2 - access(REVERSE("TXT") LIKE 'x10%')
   filter(REVERSE("TXT") LIKE 'x10%')

这篇关于LIKE陈述时间过长的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！