Interesting Oracle techniques and investigations

Alberto Dell’Era has some very interesting Oracle techniques and investigations at:

Particularly, his idea of using partition table exchange to replace all rows of a table may be the answer to race condition in MV complete refresh. Essentially, you build another table on the side, commit, and then partition exchange. This is probably the best of two MV refresh approaches as explained by Alberto

The exchange table will make the new rows appear instantaneously (same effect of the commit in a conventional delete+insert+commit) and, most importantly, readers that are currently reading when the exchange is issued will keep reading the old version of the rows until the last fetch of the current statement (ie, the “multiversion read consistency” will be preserved).

Until Oracle implements this type of MV refresh, I have to do it manually. No wonder, partition table exchange is all the rage and buzz in DW now.


Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: