I agree there are circumstances where you have instructed Oracle to ignore
errors such that during the period when those errors are allowed Oracle may
reasonably deliver an incorrect result. I would not call that a bug since
Oracle is behaving according to your special instructions.
It is also not an Oracle bug to destroy the integrity of your data when
directed to gloss over anything during update or recovery. Ignoring
corrupted rollback segments comes immediately to mind, but there are plenty
of other things (some of which require fairly subtle analysis to reveal how
it is you've actually directed Oracle to allow an inconsistency.)
However even if your data is corrupt, in lieu of such settings being set in
the current system or session, I believe it is incumbent on Oracle to
deliver either correct results reflecting the contents of your tables or
else toss an error.
EXCEPT, as you point out, if you have explicitly allowed data into the wrong
partitions.
My opinion in that case is that Oracle should set a "no pruning" directive
on any partition so abused and only ignore that that setting by a session or
system exception when delivering results. However that [bug fix |
enhancement request] has not been accepted into the product set. I regard
that as a bug, but I cannot really argue with Oracle tossing a tuxedo on it.
It is not apparent to me the OP is currently allowing any such errors, ergo
my request for a reproducible case from creation that might reveal
something. (Which is a superset that includes your data in wrong partition
[currently not bug] evolution of the data.)
I do thank you for reminding me and alerting us all to the fact there are
several combinations of instructions to Oracle that literally request
delivery of questionable result sets. And especially I thank you for
reminding me of the data in wrong partition exception currently in force
regarding non-errored results on the table viewed as a whole when pruning
takes place.
In that context, I stand corrected. It is not apparent to me these apply to
the OP, but your precise question may drive out the truth of the situation
short of a full re-creation scenario.
Thanks,
mwf
-----Original Message-----
From: Jonathan Lewis [mailto:jonathan@xxxxxxxxxxxxxxxxxx] ;
Sent: Tuesday, March 08, 2016 12:06 PM
To: Mark W. Farnham; Tom.Terrian.ctr@xxxxxxx; oracle-l@xxxxxxxxxxxxx
Subject: RE: index hint returns wrong results
I'm not sure that I can agree with you, Mark.
What if you get "wrong results" because you set the query_rewrite_integrity
?
It's not quite the same, but you've said to Oracle that you will be
responsible for wrong results.
Similarly what if you set a constraint to RELY disable novalidate and then
break it. You volunteered to get wrong results.
If you tell Oracle that you don't want it to validate the data that you've
put into the wrong partition why should Oracle be at fault when you get the
wrong results ?
(That's assuming my suggested scenario is correct, of course - I'll try to
build a model this evening).
Regards
Jonathan Lewis
http://jonathanlewis.wordpress.com
@jloracle
________________________________________
From: Mark W. Farnham [mwf@xxxxxxxx]
Sent: 08 March 2016 16:23
To: Tom.Terrian.ctr@xxxxxxx; Jonathan Lewis; oracle-l@xxxxxxxxxxxxx
Subject: RE: index hint returns wrong results
First, let's be clear: If Oracle returns incorrect results, then there most
definitely is a bug in the stack somewhere. Even if the data has been
FUBAR'd due to PEBKAC, it is incumbent on the RDBMS to prevent delivery of a
response without tossing an error.
Second, mmm, it seems to me it would be very useful to try to develop a
degenerate (meaning no more complicated than required) case from creation
and population scripts to wrong results so others can help isolate which
versions may be affected and any required "avoid doing this" work-around
advice to the community until the bug is fixed.
mwf
-----Original Message-----
From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx]
On Behalf Of Terrian, Thomas J CTR (US)
Sent: Tuesday, March 08, 2016 8:51 AM
To: Jonathan Lewis; oracle-l@xxxxxxxxxxxxx
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@xxxxxxxxxxxxxxxxxx]
Sent: Tuesday, March 08, 2016 8:05 AM
To: Terrian, Thomas J CTR (US); oracle-l@xxxxxxxxxxxxx
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
@jloracle
________________________________________
From: oracle-l-bounce@xxxxxxxxxxxxx [oracle-l-bounce@xxxxxxxxxxxxx] on
behalf of Terrian, Thomas J CTR (US) [Tom.Terrian.ctr@xxxxxxx]
Sent: 08 March 2016 12:32
To: oracle-l@xxxxxxxxxxxxx
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
--
//www.freelists.org/webpage/oracle-l
--
//www.freelists.org/webpage/oracle-l
=
--
//www.freelists.org/webpage/oracle-l
