Re: function-based indexes

In article <453952bc.0106290857.17736cca_at_posting.google.com>, michaelmoss123_at_yahoo.com says...
>
>Great, thanks. I see your point about needing other information from
>the tables, but I'm actually just trying to get a count (sorry, didn't
>mention that the first time):
>
>select count(*) from t1,t2 where upper(t1.a)=upper(t2.a);
>
>I don't how this is handled internally - could it be the same or a
>similar issue? In any case I'm already better off than I was before -
>I'd never seen the timing function in sqlplus before! Thanks a lot,
>Mike
>

it is the same issue as:
>> 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

more or less. The CBO sees that all of the stuff will be accessed, just hash it and be done with it.

If you really want to use that index you can use:

tkyte_at_TKYTE816> SELECT /*+ ORDERED USE_NL(e2) INDEX( e2 emp2_upper_idx ) */ count(*)
  2 from emp1 e1, emp2 e2 where upper(e1.ename) = upper(e2.ename)   3 /

Elapsed: 00:00:01.42

Execution Plan

   0 SELECT STATEMENT Optimizer=CHOOSE (Cost=20036 Card=1 Bytes=14)    1 0 SORT (AGGREGATE)

   2    1     NESTED LOOPS (Cost=20036 Card=774897 Bytes=10848558)
   3    2       TABLE ACCESS (FULL) OF 'EMP1' (Cost=23 Card=20013 Bytes=140091)
   4    2       INDEX (RANGE SCAN) OF 'EMP2_UPPER_IDX' (NON-UNIQUE) (Cost=

>Thomas Kyte <tkyte_at_us.oracle.com> wrote in message
>news:<9hg8ci0hs4_at_drn.newsguy.com>...
>> 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
>>
>> 1) select * from t1, t2 where upper(t1.a) = upper(t2.a);
>> blows off the index (rightly so)
>>
>> 2) a first rows hint will let it use it (albeit slower and with more work)
>>
>> 3) a query like:
>>
>> 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 DEPTNO
>> 10 from all_objects
>> 11 where rownum < 10000
>> 12 /
>> 9999 rows created.
>>
>> 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
>> 17032889 bytes sent via SQL*Net to client
>> 1692730 bytes received via SQL*Net from client
>> 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
>> 3 from emp1 e1, emp2 e2 where upper(e1.ename) = upper(e2.ename)
>> 4 /
>>
>> 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
>> 13318438 bytes sent via SQL*Net to client
>> 1692730 bytes received via SQL*Net from client
>> 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 Corp