Oracle : SQL Optiomization - Part 2

How can I optimize this SQL below? this query takes forever to run :(

SELECT DISTINCT CONNECT_BY_ROOT a.dst_ID_key AS root_ID_key, a.src_ID_key
  FROM fact_table a
  CONNECT BY NOCYCLE PRIOR a.src_ID_key = a.dst_ID_key
   START WITH a.dst_ID_key IN (SELECT b.ID_key
                           FROM TableA b
                           JOIN TableB c
                             ON (c.name = b.name AND c.school = b.school)
                          WHERE b.status = 'Active')

The query in the IN clause took less than 5 seconds to ru开发者_C百科n and returns 3000 rows. and FACT_TABLE contains 20M of records.

Thanks in advance. :)

---

Are you licensed for the performance tuning EM pack? If so, I would start by executing the query and running the SQL Tuning Advisor. It's hard to help you too much with the SQL statement without knowing a lot more about the table and indexes on it.

That said, when you're filtering with an IN (SUBQUERY) things tend to run slow. Logically, that subquery is executed for every possible result. If you can rewrite it to not utilize the subquery right there, you may have better luck; I've had luck turning subqueries like that into JOIN statements in non-hierarchical queries.