| -2- |
|---|
| А если substr(f, 1,...)? |
А в каких версиях оптимизатор настолько умный, что догадается в таком случае использовать индекс?
create table t (id varchar2(30) primary key);
Table created.
insert into t
select dbms_random.string('X',30)
from dual
connect by level <= 10000;
10000 rows created.
exec dbms_stats.gather_table_stats(user,'T',method_opt=>'FOR ALL COLUMNS SIZE SKEWONLY',cascade=>true);
PL/SQL procedure successfully completed.
explain plan for
select id
from t
where substr(id,1,2) = :X;
Explained.
select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 1601196873
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 100 | 3100 | 15 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| T | 100 | 3100 | 15 (0)| 00:00:01 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
1 - filter(SUBSTR("ID",1,2)=:X)
13 rows selected.