Just for the avoidance of any doubt, are you saying that the following code
demonstrates an Oracle bug ?
SQL> drop table t1 purge;
SQL> create table t1 (id number(2), constraint t1_pk primary key(id) rely
disable novalidate);
SQL> insert into t1 values (1);
SQL> insert into t1 values (1);
SQL> commit;
SQL> select * from t1;
ID
----------
1
1
SQL>
Regards
Jonathan Lewis
http://jonathanlewis.wordpress.com
@jloracle
________________________________________
From: rajendra.pande@xxxxxxx [rajendra.pande@xxxxxxx]
Sent: 08 March 2016 17:50
To: Jonathan Lewis; mwf@xxxxxxxx; Tom.Terrian.ctr@xxxxxxx;
oracle-l@xxxxxxxxxxxxx
Subject: RE: index hint returns wrong results
As they say in the movies - :) with all due respect Jonathan - I would side
with Mark
In the example that you have given - I used "Rely disable novalidate" - which
is an ORACLE provided option/solution
If subsequently - as you put it there is some option - again provided by ORACLE
- that enables me to "break it" which then results in wrong results then that
in my opinion is a bug.
We rely on a consistent set of rules in the database to produce the right
results, and if there is some possibility that a combination of these could
result in wrong results then that "feature" needs to be fixed.
-----Original Message-----
From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx] On ;
Behalf Of Jonathan Lewis
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
Please visit our website at
http://financialservicesinc.ubs.com/wealth/E-maildisclaimer.html
for important disclosures and information about our e-mail
policies. For your protection, please do not transmit orders
or instructions by e-mail or include account numbers, Social
Security numbers, credit card numbers, passwords, or other
personal information.
--
//www.freelists.org/webpage/oracle-l
