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

Quick follow-up. I tried dropping the composite index that was getting a fast full scan, and the count query plan is back to looking just like record query (full table scan of EMPLOYEE) - and performance is unchanged. Again here's the query + plan + stats:

alter session set optimizer_index_cost_adj = 50; alter session set optimizer_index_caching = 90;

SELECT count(*) FROM employee
INNER JOIN employee_category_link
  ON employee.id = employee_category_link.employee_id WHERE ((employee.department_id = 40101)
AND (employee_category_link.category_id = 7) AND (employee.type_id = 2));

Elapsed: 00:00:02.07

Execution Plan

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

   1    0   SORT (AGGREGATE)
   2    1     NESTED LOOPS (Cost=1048 Card=696739 Bytes=15328258)
   3    2       TABLE ACCESS (FULL) OF 'EMPLOYEE' (Cost=1046 Ca
          rd=696739 Bytes=9754346)

   4    2       INDEX (UNIQUE SCAN) OF 'EC_EMPID_CATID_IDX' (UNIQUE)

Statistics

        327  recursive calls
          0  db block gets
    1404347  consistent gets
        150  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
          6  sorts (memory)
          0  sorts (disk)
          1  rows processed

The weird thing is I have indexes on each of columns referred to in the where clause, but the CBO insists on a full table scan. Any ideas as to why?

-Steve Received on Thu Mar 03 2005 - 11:57:05 CST