Re: function-based indexes

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

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.
Received on Fri Jun 29 2001 - 11:57:14 CDT