Re: Performance problem: anything else I can do to make this faster?

Tell me about it. I've computed statistics on all my tables and indexes, and I even did a dbms_stats.gather_schema_stats on my whole schema, and I still get this horrible plan on the count query (same on the record query):

Elapsed: 00:00:13.08

Execution Plan

   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=1566 Card=1 Bytes=22
          )

   1    0   SORT (AGGREGATE)
   2    1     HASH JOIN (Cost=1566 Card=696739 Bytes=15328258)
   3    2       INDEX (FAST FULL SCAN) OF 'E_DEP_TYPE_IDX'
           (UNIQUE) (Cost=219 Card=696739 Bytes=9754346)

   4    2       TABLE ACCESS (FULL) OF 'EMPLOYEE_CATEGORY_LINK'
(Cost=243 Ca
          rd=1345799 Bytes=10766392)

Statistics

          0  recursive calls
          0  db block gets
       4794  consistent gets
       7322  physical reads
          0  redo size
        381  bytes sent via SQL*Net to client
        499  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

As far as I've read, what I've done is supposed to compute the correct histogram info, etc, but I can't get it to stop doing full table scans.

Any tips on how to coax the CBO into using indexes?

-Steve Received on Wed Mar 02 2005 - 17:12:54 CST