RE: index hint returns wrong results
Date: Tue, 8 Mar 2016 17:56:02 +0000
Message-ID: <1E24812FBE5611419EFAFC488D7CCDD132AD4E79_at_G9W0741.americas.hpqcorp.net>
Is f2 comparing to a space or is that supposed to be NULL? If NULL what happens if you change your SQL from and f2 = '' to and f2 is NULL ?
-----Original Message-----
From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Terrian, Thomas J CTR (US)
Sent: Tuesday, March 08, 2016 8:51 AM
To: Jonathan Lewis; oracle-l_at_freelists.org
Subject: RE: index hint returns wrong results
That is interesting.
Even when I use the partition name, I still get bad results:
select /*+ index(t1 ind1) */ count(*)
from scott.t1 partition (sys_p14)
where f1 = 'one' and f2 = ' ';
COUNT(*)
2
There is only 1 row in that table where f1 = 'one' and f2 = ' '.
And I see partition pruning in the explain plan when it tries to access the table (not for the global index of course).
-----Original Message-----
From: Jonathan Lewis [mailto:jonathan_at_jlcomp.demon.co.uk]
Sent: Tuesday, March 08, 2016 8:05 AM
To: Terrian, Thomas J CTR (US); oracle-l_at_freelists.org
Subject: [Non-DoD Source] 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
--
http://www.freelists.org/webpage/oracle-l
--
http://www.freelists.org/webpage/oracle-l
Received on Tue Mar 08 2016 - 18:56:02 CET