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

Hi Everybody,

I've looked for info on this from several directions (e.g. searches on partition, bitmap index, etc.) but still can't seem to come up with a solution.

On oracle 9i:

/* table names have been changed to protect the innocent */ I have a large table (about 10 million records). There is a column CATAGORY with about 10 distinct values. There is an other table PRODUCT that has about 1 million records. PRODUCT has a foreign key to CATAGORY over catagory_id. I have the following indexes (plus the one I get for the foreign key)

create unique index catagory_cid_pid on catagory (   catagory_id,
  product_id
)

create unique index catagory_pid_cid on catagory (   product_id,
  catagory_id
)

The following query is <1sec:

select
  *
from
  catagory cat
where
  cat.catagory_id = 'CAT_X'

I cannot get the following to use any thing but hash joins and takes several minutes.

select
  *
from
  catagory cat,
  product prod
where
  prod.product_id = cat.product_id
  and cat.catagory_id = 'CAT_X'

I've tried things like:
/*+ rule */
/*+ index(catagory catagory_cid_pid */
/*+ rule index(catagory catagory_cid_pid) */
/*+ rule use_nl index(catagory catagory_cid_pid) */
/*+ rule use_nl index(catagory catagory_cid_pid) index(product
product_id_index) */
/*+ use_nl */

I've also tried narowing down the number of rows (to about 1000) by including things such as "where product.product_id like '123%'" but still no luck.

I would think that this would be a quick query as the cid/pid index could be used: move through the index until you get to the 'CAT_X' records and then the product_id values are in order from there so you can use the product_id index on the product table to join with the cid_pid index on catagory where there is a 1:1 relationship.

Any info on what I need to do to get this query to run more quickly would be greatly appreciated.

Thanks,
John Received on Thu May 31 2007 - 11:15:34 CDT