Hi,

For the following query, the cost of table join between t0, t1 and t3 is very expensive. For all t1's columns used in the where clauses, I created one composite index on t1 to improve the table join. The execution plan shows the index is being used. However, the cost only reduces 10%. The actual execution time does not change too much, either. Any suggestion? Thank you.

What's your general indexing strategy? Assuming I am not concerned about the cost of maintaining the index, is it the best strategy to create indexes on ALL the columns used in the where clauses?


SELECT t1.proj_id
FROM client t0, proj t1, ref t2, proj_case t3, background_report t4
WHERE t0.comp_id IN (:sys_b_00)
AND t0.case_id = t1.assigned_to_case_id
AND t1."STATUS" = :sys_b_01
AND t1.ref_id = t2.ref_id
AND t2.ref_status IN (:sys_b_02, :sys_b_03, :sys_b_04)
AND t1.primary_case_id = t3.proj_case_id
AND t3.background_report_id = t4.background_report_id(+)