RE: index hint returns wrong results

If the table is partitioned then I think it's possible for the error to appear as a result of procedural error on the part of the application.  

I'd have to check but I think you might be able to do an exchange partition including indexes without validation that got data into the wrong partition which wouldn't be seen by a (partition eliminating) tablescan but was seen by a range scan of a global index.

This may be version dependent, of course thanks to enhancements in global index usage.



Regards
Jonathan Lewis
http://jonathanlewis.wordpress.com
_at_jloracle

________________________________________
From: oracle-l-bounce_at_freelists.org [oracle-l-bounce_at_freelists.org] on behalf of Terrian, Thomas J CTR (US) [Tom.Terrian.ctr_at_dla.mil]
Sent: 08 March 2016 12:32
To: oracle-l_at_freelists.org
Subject: index hint returns wrong results

Oracle 12c.

Am I missing something or is this a bug?  Using an index hint returns the wrong results.  Does it matter if the table is partitioned and the index is global or even what fields are in the index?  I can't see how any of that should matter.  The results should be the same:


select /*+ index(t1 ind1) */ count(*)
from scott.t1
where f1 = 'one' and f2 = ' ';

  COUNT(*)
----------
         2

select count(*)
from scott.t1
where f1 = 'one' and f2 = ' ';

  COUNT(*)
----------
         1


--
http://www.freelists.org/webpage/oracle-l


--