将LIKE运算符与DETERMINISTIC函数一起使用时的Oracle执行计划 [英] Oracle execution plans when using the LIKE operator with a DETERMINISTIC function

问题描述

现在，当我在LIKE运算符的右侧使用DETERMINISTIC函数时，Oracle执行计划会遭受严重破坏，这真是一件棘手的事情.这是我的情况:

Now I have a really tricky thing with Oracle execution plans running havoc, when I use a DETERMINISTIC function on the right hand side of the LIKE operator. This is my situation:

我认为执行这样的查询(简化)是明智的:

I thought it to be wise to execute a query like this (simplified):

SELECT [...]
FROM customers cust
JOIN addresses addr ON addr.cust_id = cust.id
WHERE special_char_filter(cust.surname) like special_char_filter(?)

然后我将?绑定到'Eder%'之类的东西.现在customers和addresses是非常大的表.这就是使用索引很重要的原因.当然，在addresses.cust_id上有一个常规索引.但是我还在special_char_filter(customers.surname)上创建了基于函数的索引，效果很好.

And I would bind ? to something like 'Eder%'. Now customers and addresses are very large tables. That's why it's important to use indexes. Of course, there is a regular index on addresses.cust_id. But I have also created a function-based index on special_char_filter(customers.surname), which works quite nicely.

问题是，上述涉及like子句的查询在addresses上使用FULL TABLE SCANS创建了执行计划.看来此查询中的某些内容使Oracle无法使用addresses.cust_id上的索引.

The trouble is, the above query involving a like clause creates execution plans with FULL TABLE SCANS on addresses. It looks like something in this query keeps Oracle from using indexes on addresses.cust_id.

我发现，解决我的问题的方法是:

I found out, that the solution to my problem is this:

SELECT [...]
FROM customers cust
JOIN addresses addr ON addr.cust_id = cust.id
WHERE special_char_filter(cust.surname) like ?

我从类似运算符的右侧删除了(DETERMINISTIC！)函数，并预先计算了Java中的bind变量.现在，此查询非常快速，没有任何FULL TABLE SCANS.这也非常快(尽管不尽相同):

I removed the (DETERMINISTIC !) function from the like operator's right hand side and pre-calculated the bind variable in Java. Now this query is hyper-fast, without any FULL TABLE SCANS. This, too, is very fast (although not equivalent):

SELECT [...]
FROM customers cust
JOIN addresses addr ON addr.cust_id = cust.id
WHERE special_char_filter(cust.surname) = special_char_filter(?)

混乱

我不明白这一点.在like运算符的右侧具有确定性函数有什么问题?我在Oracle 11.2.0.1.0中已经观察到了这一点

The Confusion

I don't understand this. What's wrong with having deterministic functions on the right hand side of the like operator? I have observed this in Oracle 11.2.0.1.0

推荐答案

以下脚本显示了我用于对ADDRESSES索引进行索引范围扫描的步骤.在查看细节之前，您可能只想运行整个过程.如果没有两次索引范围扫描 对于最后两个查询，则可能是我们的版本，设置等有所不同.我使用的是10.2.0.1.0.

The script below shows the steps I used to get an index range scan on the ADDRESSES index. Before you look at the details, you may want to just run the whole thing. If you don't get two index range scans for the last two queries then it's probably a difference in our versions, settings, etc. I'm using 10.2.0.1.0.

如果确实看到了所需的计划，则可能需要逐步修改我的脚本以使其更准确地反映实际数据，并尝试查找导致其中断的确切更改.希望我的设置至少接近真实的事物，并且不会丢失任何可能使 与您的确切问题无关.

If you do see the desired plan, then you may want to gradually modify my script to make it more accurately reflect the real data, and try to find the exact change that makes it break. Hopefully my setup is at least close to the real thing, and isn't missing any details that would make it irrelevant to your exact problem.

这是一个奇怪的问题，我不了解这里发生的一切.例如，我不知道为什么use_nl有效，但索引提示却无效.

This is a weird issue, and I don't understand everything that's going on here. For example, I don't know why use_nl works but index hints don't.

(请注意，我的执行时间基于重复执行.第一次运行此查询时，某些查询可能会变慢，因为未缓存数据.)

(Note that my execution times are based on repeated executions. The first time you run this some queries may be slower because the data isn't cached.)

--create tables
create table customers (id number, surname varchar2(100), other varchar2(100));
create table addresses (cust_id number, other varchar2(100));

--create data and indexes
insert into customers select level, 'ASDF'||level, level from dual connect by level <= 1000000;
insert into addresses select level, level from dual connect by level <= 1000000;
create index customers_id on customers(id);
create index addresses_cust_id on addresses(cust_id);
create index customers_special_char_filter on customers(special_char_filter(surname));

--create function
create or replace function special_char_filter(surname in varchar) return varchar2 deterministic is
begin
    return replace(surname, 'bad value!', null);
end;
/

--gather stats
begin
    dbms_stats.gather_table_stats(ownname => user, tabname => 'CUSTOMERS', cascade => true);
    dbms_stats.gather_table_stats(ownname => user, tabname => 'ADDRESSES', cascade => true);
end;
/

set autotrace on;

--Index range scan on CUSTOMERS_SPECIAL_CHAR_FILTER, but full table scan on ADDRESSES
--(0.2 seconds)
SELECT *
FROM customers cust
JOIN addresses addr ON addr.cust_id = cust.id
WHERE special_char_filter(cust.surname) like special_char_filter('ASDF100000bad value!%');

--This uses the addresses index but it does an index full scan.  Not really what we want.
--I'm not sure why I can't get an index range scan here.
--Various other index hints also failed here.  For example, no_index_ffs won't stop an index full scan.
--(1 second)
SELECT /*+ index(addr addresses_cust_id) */ *
FROM customers cust
JOIN addresses addr ON addr.cust_id = cust.id
WHERE special_char_filter(cust.surname) like special_char_filter('ASDF100000bad value!%');


--Success!  With this hint both indexes are used and it's super-fast.
--(0.02 seconds)
SELECT /*+ use_nl(cust addr) */ *
FROM customers cust
JOIN addresses addr ON addr.cust_id = cust.id
WHERE special_char_filter(cust.surname) like special_char_filter('ASDF100000bad value!%');


--But forcing the index won't always be a good idea, for example when the value starts with '%'.
--(1.2 seconds)
SELECT /*+ use_nl(cust addr) */ *
FROM customers cust
JOIN addresses addr ON addr.cust_id = cust.id
WHERE special_char_filter(cust.surname) like special_char_filter('%ASDF100000bad value!%');

这篇关于将LIKE运算符与DETERMINISTIC函数一起使用时的Oracle执行计划的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！