Ok . . . I am already benefiting from the support from this list : - )
I noticed that I was actually ordering my query BY rownum (which doesn't make
much sense . . . and perhaps oracle's optimizer recognized this and ignored the
pseudo column.)
Just to be sure, I change the query and then reran my tests - the results,
however, did not change
New SQL
SELECT
d.ROW_NUMBER,
d.f1,
d.f2,
d.f3,
d.f4,
d.f5
FROM
(
SELECT /*+ FULL(A) PARALLEL(A 6) */
rownum ROW_NUMBER,
A.field1 f1 ,
A.field2 f2,
A.field3 f3,
A.field4 f4,
B.field5 f5
FROM
tableA A,
tableB B
WHERE
B.field6 IN ( 'TOK3', 'TOK4', 'TOK5' )
AND B.field7 LIKE 'A%'
AND B.field8 IN ('TOK1', 'TOK2')
AND B.fkfield1 = A.field1
ORDER BY
2, 3, 4, 5, 6
) d
WHERE
d.row_number < 2020000
AND d.row_number >= 1000000
Here is a summary of the test results (I have a test harness which uses ssh to
run the exact same tests on more than one host).
INPUT: ( ( HOSTNAME = sandbox ) ( RANGE = 2000000 0 ) ) OUTPUT: (
ORACLE_ERROR_COUNT = 2 )
INPUT: ( ( HOSTNAME = prod ) ( RANGE = 2000000 0 ) ) OUTPUT: (
ORACLE_ERROR_COUNT = 0 )
INPUT: ( ( HOSTNAME = sandbox ) ( RANGE = 1000000 0 ) ) OUTPUT: (
ORACLE_ERROR_COUNT = 0 )
INPUT: ( ( HOSTNAME = prod ) ( RANGE = 1000000 0 ) ) OUTPUT: (
ORACLE_ERROR_COUNT = 0 )
INPUT: ( ( HOSTNAME = sandbox ) ( RANGE = 2000000 1000000 ) ) OUTPUT: (
ORACLE_ERROR_COUNT = 0 )
INPUT: ( ( HOSTNAME = prod ) ( RANGE = 2000000 1000000 ) ) OUTPUT: (
ORACLE_ERROR_COUNT = 0 )
INPUT: ( ( HOSTNAME = sandbox ) ( RANGE = 2020000 1000000 ) ) OUTPUT: (
ORACLE_ERROR_COUNT = 0 )
INPUT: ( ( HOSTNAME = prod ) ( RANGE = 2020000 1000000 ) ) OUTPUT: (
ORACLE_ERROR_COUNT = 0 )
INPUT: ( ( HOSTNAME = sandbox ) ( RANGE = 2030000 1000000 ) ) OUTPUT: (
ORACLE_ERROR_COUNT = 2 )
INPUT: ( ( HOSTNAME = prod ) ( RANGE = 2030000 1000000 ) ) OUTPUT: (
ORACLE_ERROR_COUNT = 0 )
