On 7/13/06, Jared Still <jkstill@xxxxxxxxx> wrote:
It appears that Oracle is taking your hint quite seriously.
As there is no data in idx2 due to the only value in the id2 column being null, Oracle is not returning any rows.
Take idx2 out of the hint, and you get data.
a classic justification for this being a bug. You cannot (or at least shouldn't be able to) provide a hint to an access path that gives wrong results. Imagine if there were a hint (it would be nice) table_scan (table_name) a writer shouldnt be able to write
select /*+ table_scan(dept) */ empno,ename from emp;
and get a tablescan of dept to answer the query. You want to do that there is always DBMS_ADVANCED_REWRITE! This is the same thing.
-- Niall Litchfield Oracle DBA http://www.orawin.info