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

SELECT count(*) FROM
employee emp,
employee_category_link link
where

    emp.id = link.employee_id
and emp.department_id = 40101
AND link.category_id = 7
AND emp.type_id = 2;

For this query - "if I were optimizer" - I'd

(a) first filter emp or link based on

• which is more selective emp.type_id, emp.department_id OR link.category_id

(b) use a single column or leading column on a composite index on
emp.id (and link.category_id)

Try using an ORDERED hint after figuring out which is more selective. You may also want to generate histograms for these columns (since you're not using bind variables here) - so Oracle can take a better decision regarding which indexes to use. Received on Wed Mar 02 2005 - 15:30:52 CST