On 7/13/06, Jared Still <jkstill@(protected)> 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.
On 7/13/06, <b class="gmail_sendername">Jared Still</b> <<a href="mailto :jkstill@(protected)">jkstill@(protected)</a>> wrote:<div><span class="gmail _quote"></span><blockquote class="gmail_quote" style="border-left: 1px solid rgb (204, 204, 204); margin: 0pt 0pt 0pt 0.8ex; padding-left: 1ex;"> <div>It appears that Oracle is taking your hint quite seriously.<br><br>As there is no data in idx2 due to the only value in the<br>id2 column being null, Oracle is not returning any rows.<br><br>Take idx2 out of the hint, and you get data. </div></blockquote><div><br>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) <span style="font-family: courier new,monospace;">table_scan (table_name) < /span>a writer shouldnt be able to write<br><span style="font-family: courier new,monospace;"><br>select /*+ table_scan(dept) */ empno,ename<br>from emp; <br><br></span>and get a tablescan of dept to answer the query. You want to do that there is always <span style="font-family: courier new,monospace;">DBMS _ADVANCED_REWRITE! </span>This is the same thing. <br><br><br></div> <br></div><br><br clear="all"><br>-- <br>Niall Litchfield<br>Oracle DBA<br><a href="http://www.orawin.info">http://www.orawin.info</a>
