Hi All,
I am on Oracle 11gr2.
TABLE A
nulll
| DATE | ID | Security | Outstanding_HS | Outstanding_VR | OUTSTANDING_ACTUAL |
|---|
| 19-JUL-2013 | 1 | 111 | null | NULL | .125 |
| 20-JUL-2013 | 1 | 111 | null | null | .368 |
I have a partitioned table(INTERVAL PARTITONED ON DAY) that is loaded with an average of 4.5million data per day. The table currently has around 1.5 Years of data minus all the weekends. Now we are adding two new columns into this table(Outstanding_HS, Outstanding_VR). Lets call this as TABLE A. And I need to update these columns based on the logic as explained below.
There is another partitioned table(INTERVAL PARTITONED ON DAY) which is even bigger than TABLE A, as its loaded with around 30 million of data every day. This table too has around 1.5 Years of data, same duration as table A. Lets call this as TABLE B.
| DATE | ID | SECURITY | VOTING_RIGHTS |
|---|
| 19-JUL-2013 | 1 | 111 | 12 |
| 19-JUL-2013 | 1 | 111 | 12 |
| 20-JUL-2013 | 2 | 1222 | 15 |
| 20-JUL-2013 | 2 | 1222 | 15 |
| | | |
Please note that the above structure is just a subset of the actual table structure but these are all the columns that are required in the process. Actual column count in both the tables would be around 40 columns.
All OUTSTANDING_* and VOTING_RIGHT columns are FLOAT(126).
SECURITY and ID are NUMBER fields.
DATE is DATE datatype.
Here's the requirement.
I need to join TABLE A with TABLE B, based on three columns(DATE, ID, SECURITY). After joining, take column (VOTING_RIGHTS) from TABLE B multiply it with another column in TABLE A(OS_ACTUAL) and the result should be updated in the newly added (Outstanding_VR) column in TABLE A. One important point to note here is that TABLE B, can have multiple records for the above said combination of three columns(DATE, ID, SECURITY) but will have same value of VOTING_RIGHTS across the records, so if we can take any one record for getting the value of VOTING RIGHTS, that should suffice.
I can't use CTAS approach as it's not a straight forward update. Tried to use bulk collect. but it's not getting through. Read somewhere that BULK COLLECT with ROWID is fast but also read that its not a good technique to use ROWID. The bigger issue than update is to collect the VOTING_RIGHTS value from TABLE B as its a huge table with multiple records for the same combination. Taking the VOTING_RIGHTS values itself is taking hell lot of time.
Also, to make it bit easier, we can do the entire table update in chunks, meaning, in one shot we can go for updating few weeks of data and then go for the other set an so on. This way we can achieve updating over 900 million rows of data.
If anybody can help me in achieving the fastest possible update for few weeks of data, it would be job done for me.
Thanks,
Ishan