In you r case FTS doesn't make any sense, it has to be an index scan. Make sure that you have the following criterias being satisfied in your query. They would have a good perfomance change.

When you are using the "AND" clause, oracle would use "BOTTOM-UP" approach. On that case, the first statement at the bottom of the and clause would be evaluated first and then the one above that and so on. I would suggest try revamping your query to choose the driving table and put your subquery at the top.

Example:
SELECT . . .
FROM emp E
WHERE emp_salary > 50000
AND emp_type = 'MANAGER'
AND 25 < ( SELECT COUNT(*)
FROM emp
WHERE emp_mgr = E.emp_no)

Changed it to be:
SELECT . . .
FROM emp E
WHERE 25 < ( SELECT COUNT(*)
FROM emp
WHERE emp_mgr = E.emp_no)
AND emp_salary > 50000
AND emp_type = 'MANAGER'



On the other hand when you use the OR it would be a top-dow approach that oracle would use to query the tables.


Now start tuning the queries individually, one by one before you make them to be a composit one. That way you would know the performance of each query and would give you a good leaway to juggle them accordingly.

Good luck,
Sam


[Edited by sambavan on 03-20-2001 at 11:37 AM]