Re: function-based indexes

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: 28 Jun 2001 14:42:42 -0700
Message-ID: <9hg8ci0hs4@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

select * from t1, t2 where upper(t1.a) = upper(t2.a); blows off the index (rightly so)
a first rows hint will let it use it (albeit slower and with more work)
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

Received on Thu Jun 28 2001 - 16:42:42 CDT