1 个查询中 3 个 like 语句的速度问题 (oracle)
这是我的查询的一个片段：
and (
ADDR_FULLTEXT
like upper(:streets1)
)
每当我执行该查询的一部分时，它都会在大约 0.05 秒内执行。这太完美了！ 但是，当我执行以下操作时：
and (
ADDR_FULLTEXT
like upper(:streets1)
AND
ADDR_FULLTEXT
like upper(:streets2)
)
它会导致 20 秒的查询。为什么差异这么大，如何解决？
查询的其余部分基本上所做的是从视图中选择多个字段（ADDR_FULLTEXT 是其中之一）。 还有一种类似的，就像这个一样。
也许我在这里做了一些非常错误的事情，因此也许有更好的方法来做到这一点。
仅供参考：PHP 循环生成此查询，该查询是搜索查询爆炸的结果。在此查询中解析每个单词，从而在 1 个查询中产生 1 个或多个相同的“like”。另一个点赞的生成方式相同，但这始终是 1 个点赞，不会多也不会少。
This is a snippet of my query:
and (
ADDR_FULLTEXT
like upper(:streets1)
)
Whenever i execute the query this is a part of, it executes in about 0.05 seconds. This is perfect!
But, when I do the following:
and (
ADDR_FULLTEXT
like upper(:streets1)
AND
ADDR_FULLTEXT
like upper(:streets2)
)
it results in a 20 second query. Why is the difference this big, and how can solve this?
What the rest of the query basically does, is selecting multiple fields from a view (ADDR_FULLTEXT is one of them).
There also is another like, just like this one.
Maybe i'm doing something really wrong here, and maybe therefor there is a better way of doing this.
FYI: A PHP loop generates this query, which results from an explode of a search query. Every word is parsed in this query resulting in 1 or more of the same "like" in 1 query. Another like is generated the same way, but this is always 1 like, never more, never less.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（3）
尝试将其分解为两个不同的查询，并取交集，例如：
参见 Intersect
try breaking it down into two different queries, and taking the intersection, like:
see Intersect
也许您将对每个 LIKE 语句进行全表扫描，而查询的其余部分则使用索引。
Probably you will have a full table scan for each LIKE statement, and the rest of your query uses indexes.
对此谓词的更改可能会更改 CBO 对表将返回的行数的估计。这反过来可能会导致计划的重大重组，从而导致绩效发生变化。
进一步的分析必须等到您提供查询（包括视图）和查询计划。
The change to this predicate is probably changing the CBO's estimate of the number of rows that will be returned by the table. This in turn may cause a significant restructuring of the plan, resulting in a change in performance.
Further analysis will have to wait until you provide the query (including views) and query plans.