Re: Oracle won't take a hint!!! Outer join to a group-by inline view. Any suggestions?

From: Gary <gary_at_moneysuite.com>
Date: 25 Feb 2002 10:43:48 -0800
Message-ID: <e7778a4a.0202251043.448cb41d@posting.google.com>

Actual cut-and-paste, to assist anyone who wishes to try alternatives:

drop table ta;
create table ta (

  ta_id		number(12),
  ta_col1	varchar2(200),
  ta_col2	varchar2(200),

constraint ta_id primary key (ta_id));

drop table ta_child;
create table ta_child (

  tac_id	number(12),
  tac_ta_id	number(12),
  tac_data	varchar2(200),

constraint tac_id primary key (tac_id)); create index tac_ta_id on ta_child(tac_ta_id);

First try on the view:

drop view pi;
create view pi as select

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

where tac_ta_id=ta_id;

Test query:

select * from pi where ta_id=123;

Explain Plan:

Query Plan

---

>SELECT STATEMENT Cost =

  1>MERGE JOIN
    1>TABLE ACCESS BY INDEX ROWID TA
      1>INDEX UNIQUE SCAN TA_ID
    2>FILTER

      1>VIEW
        1>SORT GROUP BY
          1>TABLE ACCESS FULL TA_CHILD

Full table scan is unacceptable.

Second try: Added the hint.

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;

Explain Plan returns

Query Plan

---

    SELECT STATEMENT Cost = 2
  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

But inner join returns no rows when there are zero children. So, outer join, with hint (without does the same):

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;

Explain Plan yields:

Query Plan

---

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

      1>SORT GROUP BY
        1>TABLE ACCESS FULL TA_CHILD

Back to full table scan on ta_child!! Tried many more hints; nothing worked.

Will be quite happy with a different way of defining the view, so long as the net result is (1) all the data including the count coming from the view, and (2) no full table scans. Received on Mon Feb 25 2002 - 12:43:48 CST