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

From: Gary <gary_at_moneysuite.com>
Date: 25 Feb 2002 14:27:55 -0800
Message-ID: <e7778a4a.0202251427.423d6b8a@posting.google.com>

Again, why must I program my own outer join? Arghhh!

drop view pi3;
create view pi3 as select

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

  tac_ta_id ita_id, count(*) ta_child_count from ta_child group by tac_ta_id   UNION ALL
        select
  ta_id, 0 from ta where
  not exists (select * from ta_child where tac_ta_id=ta_id)) where ta_id=ita_id;

then

select * from pi3 where ta_id=123;

gives

Query Plan

---

    SELECT STATEMENT Cost = 3
  1>NESTED LOOPS
    1>TABLE ACCESS BY INDEX ROWID TA
      1>INDEX UNIQUE SCAN TA_ID
    2>VIEW

      1>UNION-ALL
        1>SORT GROUP BY
          1>INDEX RANGE SCAN TAC_TA_ID
        2>INDEX UNIQUE SCAN TA_ID
          1>INDEX RANGE SCAN TAC_TA_ID

Received on Mon Feb 25 2002 - 16:27:55 CST