Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: function-based indexes
In article <453952bc.0106281239.9407768_at_posting.google.com>,
michaelmoss123_at_yahoo.com says...
>
>I'm trying to create a case-insensitive match on two tables using
>function-based indexes, but can't get the optimizer to use the
>indexes. I've set the 2 query_rewrite parameters and analyzed both
>tables, and both indexes work fabulously when searching only one table
>at a time. In other words
>
>select /*+ index(f1) */ count(*) from t1 where upper(a)='TEST' and
>select /*+ index(f2) */ count(*) from t2 where upper(a)='TEST'
>
>both work great and use the indexes. It's when I try to join the
>tables on upper(t1.a)=upper(t2.a) that I'm getting full-table scans.
>Does anyone know what I've missed? Thanks very much,
>
>Mike
I can sort of guess why --
you might be running a query like:
select * from t1, t2 where upper(t1.a) = upper(t2.a);
using CBO, that would almost certainly blow off the index and go for a hash join (even with "normal" indexes). It understands it'll be accessing every row in the table so the index would be not as good. A first rows hint would change its mind.
Here is an example showing that
select * from t1, t2 where upper(t1.a) = upper(t2.a) and upper(t1.a) = :x;
will naturally use all available indexes without anyhelp:
tkyte_at_TKYTE816> create table emp1
2 as
3 select * from scott.emp;
Table created.
tkyte_at_TKYTE816> create table emp2
2 as
3 select * from scott.emp;
Table created.
tkyte_at_TKYTE816> set timing on
tkyte_at_TKYTE816> insert into emp1
2 select -rownum EMPNO,
3 initcap(substr(object_name,1,10)) ENAME, 4 substr(object_type,1,9) JOB, 5 -rownum MGR, 6 created hiredate, 7 rownum SAL, 8 rownum COMM, 9 (mod(rownum,4)+1)*10 DEPTNO10 from all_objects
tkyte_at_TKYTE816> insert into emp2 select * from emp1; 10013 rows created.
tkyte_at_TKYTE816> commit;
Commit complete.
tkyte_at_TKYTE816> create index emp1_upper_idx on emp1(upper(ename)); Index created.
tkyte_at_TKYTE816> create index emp2_upper_idx on emp2(upper(ename)); Index created.
tkyte_at_TKYTE816> analyze table emp1 compute statistics
2 for table
3 for all indexed columns
4 for all indexes;
Table analyzed.
tkyte_at_TKYTE816> analyze table emp2 compute statistics
2 for table
3 for all indexed columns
4 for all indexes;
Table analyzed.
tkyte_at_TKYTE816> alter session set QUERY_REWRITE_ENABLED=TRUE; Session altered.
tkyte_at_TKYTE816> alter session set QUERY_REWRITE_INTEGRITY=TRUSTED; Session altered.
tkyte_at_TKYTE816> set autotrace traceonly
tkyte_at_TKYTE816> select e1.ename, e1.empno, e1.sal,
2 e2.ename, e2.empno, e2.sal
3 from emp1 e1, emp2 e2 where upper(e1.ename) = upper(e2.ename)
4 /
228705 rows selected.
Elapsed: 00:00:05.99
Execution Plan
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=225 Card=221562 1 0 HASH JOIN (Cost=225 Card=221562 Bytes=14623092) 2 1 TABLE ACCESS (FULL) OF 'EMP1' (Cost=12 Card=10013 Bytes=330429) 3 1 TABLE ACCESS (FULL) OF 'EMP2' (Cost=12 Card=10027 Bytes=330891)
Statistics
132 recursive calls 8 db block gets 176 consistent gets 193 physical reads 0 redo size
15248 SQL*Net roundtrips to/from client 6 sorts (memory) 0 sorts (disk) 228705 rows processed tkyte_at_TKYTE816> select /*+ first_rows */ e1.ename, e1.empno, e1.sal, 2 e2.ename, e2.empno, e2.sal
228705 rows selected.
Elapsed: 00:00:07.10
Execution Plan
0 SELECT STATEMENT Optimizer=HINT: FIRST_ROWS (Cost=20038 Card=221562 1 0 NESTED LOOPS (Cost=20038 Card=221562 Bytes=14623092)
2 1 TABLE ACCESS (FULL) OF 'EMP1' (Cost=12 Card=10013 Bytes=330429) 3 1 TABLE ACCESS (BY INDEX ROWID) OF 'EMP2' (Cost=2 Card=10027 4 3 INDEX (RANGE SCAN) OF 'EMP2_UPPER_IDX' (NON-UNIQUE) (Cost=1
Statistics
0 recursive calls 4 db block gets 64698 consistent gets 0 physical reads 0 redo size
15248 SQL*Net roundtrips to/from client 2 sorts (memory) 0 sorts (disk) 228705 rows processed
See -- it'll use the index if you twist its arm but it isn't the right thing to do, compare the consistent gets and the time.
tkyte_at_TKYTE816> select e1.ename, e1.empno, e1.sal,
2 e2.ename, e2.empno, e2.sal
3 from emp1 e1, emp2 e2
4 where upper(e1.ename) = upper(e2.ename)
5 and upper(e1.ename) = 'ALL_OBJECT'
6 /
4 rows selected.
Elapsed: 00:00:00.11
Execution Plan
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=20 Card=81 Bytes=5346) 1 0 MERGE JOIN (CARTESIAN) (Cost=20 Card=81 Bytes=5346)
2 1 TABLE ACCESS (BY INDEX ROWID) OF 'EMP1' (Cost=2 Card=9 Bytes=297) 3 2 INDEX (RANGE SCAN) OF 'EMP1_UPPER_IDX' (NON-UNIQUE) (Cost=1 Car 4 1 SORT (JOIN) (Cost=18 Card=9 Bytes=297) 5 4 TABLE ACCESS (BY INDEX ROWID) OF 'EMP2' (Cost=2 Card=9 6 5 INDEX (RANGE SCAN) OF 'EMP2_UPPER_IDX' (NON-UNIQUE) (Cost=1
And when it makes sense, it'll use all of the indexes.
-- Thomas Kyte (tkyte@us.oracle.com) http://asktom.oracle.com/ Expert one on one Oracle, programming techniques and solutions for Oracle. http://www.amazon.com/exec/obidos/ASIN/1861004826/ Opinions are mine and do not necessarily reflect those of Oracle CorpReceived on Thu Jun 28 2001 - 16:42:42 CDT