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

From: DA Morgan <damorgan_at_psoug.org>
Date: Thu, 31 May 2007 10:23:34 -0700
Message-ID: <1180632212.656912@bubbleator.drizzle.com>

grasp06110 wrote:
> 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).

Small to medium ... not large.

> There is a column CATAGORY with about 10 distinct values.

SQL> select keyword from gv$reserved_words

   2 where keyword like 'CAT%';

KEYWORD

---

CATEGORY SQL>
> 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
> )

I would recommend not building unique indexes but rather unique constraints: Far more flexible.

> 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 */

Have you tried EXPLAIN PLAN using dbms_xplan so that we can see what is actually happening? Or a 10053 trace and TKPROF? RULE hints are likely to be a waste of time as opposed to using DBMS_STATS to provide the optimizer with good numbers.

Post the metrics and we'll see if they provide insight.

-- 
Daniel A. Morgan
University of Washington
damorgan_at_x.washington.edu
(replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org

Received on Thu May 31 2007 - 12:23:34 CDT