Edit Question - KnowledgeHub

Title Body

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.

Tags (comma-separated) Cancel