Indexes Ignored

I am running Oracle 8i on Solaris 2.6.

When I run the follwing query with a simple join, none of my indexes on the inventory table are utilized. The table currently has four indexes.

SQL> select inventory_name from inventory i, pageinventory pi, page p   2 where p.page_name = 'P1HW211010-0-M00800-1---AL2002'   3 and p.page_id = pi.page_id
  4 and pi.inventory_id = i.inventory_id;

Execution Plan

   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=6274 Card=3333670 By
          tes=286695620)

   1    0   HASH JOIN (Cost=6274 Card=3333670 Bytes=286695620)
   2    1     TABLE ACCESS (BY INDEX ROWID) OF 'PAGE' (Cost=2 Card=366
          1 Bytes=109830)

   3    2       INDEX (RANGE SCAN) OF 'PAGE_CI' (NON-UNIQUE) (Cost=1 C
          ard=3661)

   4    1     HASH JOIN (Cost=6245 Card=91059 Bytes=5099304)
   5    4       INDEX (FAST FULL SCAN) OF 'PAGEINVENTORY_UNIQ' (UNIQUE
          ) (Cost=4 Card=91060 Bytes=2367560)

   6    4       TABLE ACCESS (FULL) OF 'INVENTORY' (Cost=4909 Card=378
          702 Bytes=11361060)

Statistics

          8  recursive calls
         12  db block gets
      38083  consistent gets
      16874  physical reads
          0  redo size
       1121  bytes sent via SQL*Net to client
        817  bytes received via SQL*Net from client
          4  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
          1  rows processed

I have the same database installed on a more powerful server but with the same table and indexes installed and it does not suffer from this problem.

Anyone have any iead what might be wrong?

By the way, the query above will yield three vales from the inventory table.
The total count of rows in the inventory table currently is 378734.

Thanks for any suggestions.
Regards,
Frank Received on Sun Oct 13 2002 - 03:59:48 CDT