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

From: Gary <gary_at_moneysuite.com>
Date: 22 Feb 2002 16:08:21 -0800
Message-ID: <e7778a4a.0202221608.7a490948@posting.google.com>

I've been writing pretty complex SQL for sometime, and the following still stumps me:

I have a parent table, TA, indexed on its primary key TA_ID, and a child table, TA_CHILD, also indexed on both its primary key and its foreign key. I want to select (and do it in a view) a list of columns from TA and the count(*) of the entries in TA_CHILD, on the primary (unique) key for TA, in a single statement. Using an inline view in the view works:

CREATE VIEW PI AS SELECT
TA_ID, col1, col2, . . . . countval childcnt  FROM TA,
  (SELECT /*+ INDEX(TA_CHILD TA_CHILD_FK) */

     TA_CHILD_FK, COUNT(*) countval
     FROM TA_CHILD group by TA_CHILD_FK)

 WHERE TA_ID=TA_CHILD_FK; where col1, col2, . . . are from the parent table, TA. I've named the indexes on the columns the same as the columns, obviously.

For this -- and only if the hint is present -- for

SELECT * FROM PI WHERE TA_ID=1234;  . . . I get the explain plan . . .

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 TA_CHILD_FK

So far so good. Except: If there are no rows for the requested foreign key in TA_CHILD, that query does not return its requested key value and a count of zero; rather it returns no rows at all.

So, make it an outer join:

CREATE VIEW PI AS SELECT
TA_ID, col1, col2, . . . . NVL(countval,0) childcnt  FROM TA,
   (SELECT /*+ INDEX(TA_CHILD TA_CHILD_FK) */

      TA_CHILD_FK, COUNT(*) countval
      FROM TA_CHILD group by TA_CHILD_FK)

 WHERE TA_ID=TA_CHILD_FK(+); Now the explain plan gives:

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

A full table scan on the child! No matter what combination of hints I try, on the inline view's SELECT or the outer SELECT, that's all I can get.

Does anybody have any idea on how I can get Oracle to use the index on the foreign key when doing the inner SELECT, when its result is being outer-joined? A simple outer join of the two tables directly, and a group-by in that, won't work; it would have to name all the many columns from TA, then the SORT is on a monstrous collection of values, exceeds the Oracle limit. And, even if the limit was not reached, when there are no columns in the child table, it returns a count of 1, not zero, since one row is found. Yes, I could do a DECODE on a null for the child table's foreign key to a 0, and the actual count otherwise, since that foreign key would come back null when no child rows are present -- but that large sort key persists, and I have situations where the length of all those group-by elements exceeds a block (the Oracle limit); many are good-sized VARCHAR2 values.

Any suggestions? Received on Fri Feb 22 2002 - 18:08:21 CST