I did have an example with value in (-1,2), yes.
For that example it worked with rownum <= {count of -1} + {count of 2} - 1,
and failed with rownum <= {count of -1} + {count of 2}.
Here's what I wrote:
Hmmm... If I query for two values, I get again that it works for one less
row than it would return:
SQL> explain plan for
2 select sd.iddelivery
3 from arrow.splitdelivery sd
4 where sd.delpackoutloc in (-1, 2)
5 and rownum <= 343251 + 525 - 1;
Explained.
And it fails at the exact number of rows for the two values together:
SQL> explain plan for
2 select sd.iddelivery
3 from arrow.splitdelivery sd
4 where sd.delpackoutloc in (-1, 2)
5 and rownum <= 343251 + 525;
where sd.delpackoutloc in (-1, 2)
*
ERROR at line 4:
ORA-03113: end-of-file on communication channel
Process ID: 9016
Session ID: 69 Serial number: 1934
I'll try other combinations.
Hinting dynamic sampling to zero didn't help.
There are 13 indexes on the table.
One peculiarity is that the primary key (two columns) is created USING an
index with 5 columns (the two PK columns plus suffixed with 3 other
columns.)
The column that is queried in the select is the first column of the PK, and
it is indexed by itself in a separate index (the one that the index-join
access plan on the table copy is using.)
But the column in the predicate only exists in a single index - which is
non-unique index containing only that column.
Cheerio
/Kim
On Fri, Mar 26, 2021 at 12:00 PM Jonathan Lewis <jlewisoracle@xxxxxxxxx>
wrote:
I think you had an example with "value in (-1, 2) and rownum <= {count} -
1" that worked.
If it's an index defect then I'd consider the possibility that the problem
is at the end of the -1 range, did you have some comment (I've lost the
trail) about getting failures with other "single value" queries? What about
two-value queries. The other thing I'd try (since there's a gap between -1
and 2 is to query value in (-1,1) which I think was the two consecutive
values.
(I'd have to day that I would be a little surprised if it were a physical
index problem as the crash also appears on the explain plan - but maybe
there's a dynamic-sampling effect that's causing the crash in that case).
Are there any other indexes on the table that Oracle might be considering
as possible alternatives ?
Regards
Jonathan Lewis
On Fri, 26 Mar 2021 at 10:35, Kim Berg Hansen <kibeha@xxxxxxxxx> wrote:
A short update:
@Mark:
I'm guessing too that an index rebuild or similar could fix the issue.
At the moment my main interest is to diagnose (mostly in order to be able
to tell if this might be an issue in other of our environments or tables),
so at the moment I don't want to "destroy the error-causing scenario" by
fixing it ;-)
