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屋!