Re: can't get join on two large tables to use_nl or indexes

> Woo-hoo, got it to work,
>
> If I reverse the columns in the index I get the performance I'm
> looking for. In other words the behavior I am observing is:
>
> If the column with the handful of distinct values is used first in the
> index the index is ignored in spite of several attemts to get it
> recognized.
>
> If the column with the number of distinct values of about 1/10 the
> number of rows in the table is used first in the index then the index
> is used if I do something like the following:
>
> select
> /*+ index(ccp ccp_sid_pid)*/
> *
> from
> str,
> ccp
> where
> str.sid = ccp.sid
> and property = 'FOO'
>
> SELECT STATEMENT Optimizer=CHOOSE (Cost=3941922 Card=953924
> Bytes=915767040)
> NESTED LOOPS (Cost=3941922 Card=953924 Bytes=915767040)
> TABLE ACCESS (FULL) OF STR (Cost=60153 Card=1293923
> Bytes=1212405851)
> TABLE ACCESS (BY GLOBAL INDEX ROWID) OF CCP (Cost=3 Card=1
> Bytes=23)
> INDEX (UNIQUE SCAN) OF CCP_SID_PID (UNIQUE) (Cost=2 Card=1)
>
> This query returns data in about 30 msec v several minutes!
>
> I am supprized at the cost (3941922) given that I get first rows so
> quickly.
>
> If I do a select count(*) from the above query and add RULE to the
> hint I get back the count (about 1million) in about 15 seconds (I'm
> guessing that this is a good approximation of how long it will take
> Oracle to access all of the rows?). It takes about 1 sec to get back
> the first 10k rows. Fortunately, most of the people looking at these
> data will only be interested in the first rows (generally < the first
> 10k rows.

One more minor detail: the rule uses the alias of the table.

This ignores the index:

select
  /*+ index(my_table my_index) */
  *
from
  my_table mt,
  my_other_table mot
where
  mt.motid = mot.motid
  and some_col = 'FOO'

This uses the index:

select
  /*+ index(mt my_index) */
  *
from
  my_table mt,
  my_other_table mot
where
  mt.motid = mot.motid
  and some_col = 'FOO'

Can someone recommend a good book(s) or other resouce(s) that provides (preferably) comprehensive information on Oracle query (and database) tuning as well as act as a refernce for quick info regarding specific questions for Oracle 10g as well as older implementations (specifically 9i)?

Thanks,
John Received on Thu May 31 2007 - 15:27:45 CDT