John,
Thanks for the ideas to change the cache params - I will try that!
Here is the SQL and the field types:
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
Field Types:
A.field1 NUMBER(12)
A.field2 VARCHAR2(20)
A.field3 NUMBER(15,3)
A.field4 VARCHAR2(4000)
B.field5 VARCHAR2(5)
B.field6 VARCHAR2(20)
B.field7 VARCHAR2(8)
B.field8 VARCHAR2(8)
B.fkfield1 NUMBER(12)
