I'm new to oracle and I have to fight against this problem.
I have a table with approximately 520 millions of rows inside. I have to fetch all rows and import them (denormalizing) inside a NoSQL db.
The table has two integer fields C_ID and A_ID and 3 indexes, one over C_ID, one over A_ID and one on both fields.
I've tried this way at the beginning:
SELECT C_ID, A_ID FROM M_TABLE;
and this has never given to me any result in reasonable time (I had no possibility to measure the time because it seemed to never complete).
I changed the query in this way:
SELECT /*+ ALL_ROWS */ C_ID, A_ID FROM (SELECT
rownum rn, C_ID, A_ID
FROM
M_TABLE WHERE rownum < ((:1 * :2 ) +1 )) WHERE rn >= (((:1 -1) * :2 ) +1 );
I run this query in parallel using 3 threads and paginating using pages with size 1000.
I tried to introduce three optimization:
1) I created statistics over the table:
ANALYZE TABLE TABLE_M ESTIMATE STATISTICS SAMPLE 5 PERCENT;
2) I partitioned the table in 8 partitions.
3) I created the table with parallel option.
Now I am able to fetch 10000 rows per second and so the whole process takes about 15 hours to complete (the DB is running on a 4 cores, 8 GB machine).
The problem is that I need to complete all in maximum 5 hours.
I am out of ideas and so, before I ask for a new machine, you know any way to improve performance in such a situation.