以下脚本显示了我用于对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!%');