Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.misc -> Re: Oracle won't take a hint!!! Outer join to a group-by inline view. Any suggestions?

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

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: 23 Feb 2002 09:06:52 -0800
Message-ID: <a58i7c014u4@drn.newsguy.com>


In article <e7778a4a.0202221608.7a490948_at_posting.google.com>, gary_at_moneysuite.com says...

try

create view p1
as
select ......, (select count(*) from child where child.fk = parent.pk) childcnt   from parent
/

>
>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?

--
Thomas Kyte (tkyte@us.oracle.com)             http://asktom.oracle.com/ 
Expert one on one Oracle, programming techniques and solutions for Oracle.
http://www.amazon.com/exec/obidos/ASIN/1861004826/  
Opinions are mine and do not necessarily reflect those of Oracle Corp 
Received on Sat Feb 23 2002 - 11:06:52 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US