We have a standard FACT table with approx 400M records. The other day, we added one more dimension column and then went about updating that new column with values. It took 18 hours to finish that job which seemed extremely long. I know 400M records is a lot, but it isn't really that much in the world of big data.
Some more info on the table.
- The table has 14 columns of dimension columns, 6 measure columns, and 4 reference columns (to tag information will run our ETL)
- The table is partitioned by year by month, with 5 years of data, with a total of about 60 partitions
- Each dimension column has a data type of Number(38,0) and is indexed
- Each measure column has a data type of Number(12,4)
- Each reference column has a data type of Varchar2(100)
How we ran it:
- We ran the update process each month at a time using the "UPDATE <partition>" method
- The update statement is fairly straight forward. It's just updating the integer value in the number field, based on a fairly basic query with a few joins. In other words, the select statement the update is based on is very fast.
My questions are:
- Is 18 hours normal for Oracle this number of records?
- Is it possible to run the update process in parallel by opening multiple sessions and sending the by month statement multiple times or through a stored procedure somehow or...?
- Let's say we ran If the process is run in parallel (all 60 months at once), would they conflict with each other since they are all hitting the same table?
Any assistance in improving the performance is much appreciated.
Thanks in advance