Re: Oracle won't take a hint!!! A really ugly solution

From: Gary <gary_at_moneysuite.com>
Date: 25 Feb 2002 11:04:58 -0800
Message-ID: <e7778a4a.0202251104.5b444aba@posting.google.com>

OK, the following is really ugly. It involves doing my own "outer join". But it uses the indexes:

drop view pi;
create view pi as select

  ta_id,
  ta_col1,
  ta_col2,
  ta_child_count
from ta, (select /*+ index(ta_child tac_ta_id) */
          tac_ta_id, count(*) ta_child_count
          from ta_child group by tac_ta_id)

where tac_ta_id=ta_id
union all
select

  ta_id,
  ta_col1,
  ta_col2,

  0
from ta where not exists
(select * from ta_child where tac_ta_id=ta_id);

The explain plan now gives

Query Plan

---

    SELECT STATEMENT Cost = 3
  1>VIEW PI
    1>UNION-ALL

      1>NESTED LOOPS
        1>TABLE ACCESS BY INDEX ROWID TA
          1>INDEX UNIQUE SCAN TA_ID
        2>VIEW
          1>SORT GROUP BY
            1>INDEX RANGE SCAN TAC_TA_ID
      2>TABLE ACCESS BY INDEX ROWID TA
        1>INDEX UNIQUE SCAN TA_ID
          1>INDEX RANGE SCAN TAC_TA_ID

I shouldn't have to do any of this, either using hints or the union! By version 8.0, Oracle should have gotten this right.

A side consequence: If view PI is now used as a source in another join, and that requires hints -- say, using the index ta_id -- the hints won't propagate into the components of the union. Received on Mon Feb 25 2002 - 13:04:58 CST