SQL & PL/SQL

There is a very complex query that I need to optimize in an Oracle 10gR2 environment. I am deconstructing it into layers to see what is causing the first bottleneck. The innermost portion is fine, with an explain plan cost of 54. With a typical value for the bind variable, it returns 125 zero_id values. There are over 100,000 rows in table T_ONE in my test database, but my customer has over one million rows in their production instance.

              WITH t_merged_id AS (SELECT DISTINCT zero_id FROM t_zero WHERE NVL(column2, zero_id) = :i_id)
              SELECT   t_one.one_id
                  FROM t_one
                      INNER JOIN t_two
                          ON t_one.column1 = t_two.two_id
                      INNER JOIN t_merged_id
                          ON t_two.column10 = t_merged_id.zero_id
              UNION ALL
              SELECT   t_one.one_id
                  FROM t_one
                      INNER JOIN t_two
                          ON t_one.column2 = t_two.two_id
                      INNER JOIN t_merged_id
                          ON t_two.column10 = t_merged_id.zero_id
              UNION ALL
              SELECT   t_one.one_id
                  FROM t_one
                      INNER JOIN t_three
                          ON t_one.column3 = t_three.three_id
                      INNER JOIN t_merged_id
                          ON t_three.column10 = t_merged_id.zero_id
              UNION ALL
              SELECT   t_one.one_id
                  FROM t_one
                      INNER JOIN t_four
                          ON t_one.column4 = t_four.four_id
                      INNER JOIN t_two
                          ON t_four.column1 = t_two.two_id
                      INNER JOIN t_merged_id
                          ON t_two.two_id = t_merged_id.zero_id
              UNION ALL
              SELECT   t_one.one_id
                  FROM t_one INNER JOIN t_merged_id ON t_one.column5 = t_merged_id.zero_id
              UNION
              SELECT   t_one.one_id
                  FROM t_one INNER JOIN t_merged_id ON t_one.column6 = t_merged_id.zero_id

However, the next step is to obtain a bunch of columns from T_ONE for each of those ONE_ID values. Adding that looks like the following, which causes a table full scan on T_ONE (and an explain plan cost over 1,500 for this query in my test system) and it takes far too long to return the results.

SELECT   t_one.*
    FROM     t_one
         INNER JOIN
             (--This is the start of the query shown above
              WITH t_merged_id AS (SELECT DISTINCT zero_id FROM t_zero WHERE NVL(column2, zero_id) = :i_id)
              SELECT   t_one.one_id
                  FROM t_one
                      INNER JOIN t_two
                          ON t_one.column1 = t_two.two_id
                      INNER JOIN t_merged_id
                          ON t_two.column10 = t_merged_id.zero_id
              UNION ALL
              SELECT   t_one.one_id
                  FROM t_one
                      INNER JOIN t_two
                          ON t_one.column2 = t_two.two_id
                      INNER JOIN t_merged_id
                          ON t_two.column10 = t_merged_id.zero_id
              UNION ALL
              SELECT   t_one.one_id
                  FROM t_one
                      INNER JOIN t_three
                          ON t_one.column3 = t_three.three_id
                      INNER JOIN t_merged_id
                          ON t_three.column10 = t_merged_id.zero_id
              UNION ALL
              SELECT   t_one.one_id
                  FROM t_one
                      INNER JOIN t_four
                          ON t_one.column4 = t_four.four_id
                      INNER JOIN t_two
                          ON t_four.column1 = t_two.two_id
                      INNER JOIN t_merged_id
                          ON t_two.two_id = t_merged_id.zero_id
              UNION ALL
              SELECT   t_one.one_id
                  FROM t_one INNER JOIN t_merged_id ON t_one.column5 = t_merged_id.zero_id
              UNION
              SELECT   t_one.one_id
                  FROM t_one INNER JOIN t_merged_id ON t_one.column6 = t_merged_id.zero_id
               --This is the end of the query shown above
               ) t_list
         ON t_one.one_id = t_list.one_id

My question is, why wouldn’t Oracle use the existing index PK_T_ONE, which is keyed on T_ONE.ONE_ID? I tried refactoring the query using a “WHERE t_one.one_id IN” construct instead of the INNER JOIN but it didn’t make any difference. Neither did adding an index hint, which I hoped would force the use of the PK index.

Any ideas?