Maybe I'm reading this too quickly, but are you not getting this behaviour
because your fk can contain nulls and these rows will not be indexed. In my
exeprience oracle ius reasonably likely to ignore indexes even if hinted if
the column is defined as nullable.
--
Niall Litchfield
Oracle DBA
Audit Commission UK
*****************************************
Please include version and platform
and SQL where applicable
It makes life easier and increases the
likelihood of a good answer
******************************************
"Gary" <gary_at_moneysuite.com> wrote in message
news:e7778a4a.0202221608.7a490948_at_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 Mon Feb 25 2002 - 04:58:29 CST
