loading huge data into a fact table from a huge source table
Posted on 2011-03-09
I have a souce table xyz which contains around 23 million records with a lot of attributes. I need to load fact table xyz_f which is basically a select of xyz with some records filtered by which will insert around 23 million records by some id_cds..The table xyz_f being truncated and indexes in the fact table is dropped..
it is taking now close to an hr to load the data. the task is decrease the load time..
Please make any recommendations..
For now bulk collect is being used to load the data with a frequency of 10000.
the records that goes into the target fact table is
select * from xyz
id not in(124,234,2345,2345.....)
which results in close to 23 million record everyday