A bit of creativity helps performance
We need to transfer trade information from one database to another using a database link on a regular basis. There are around 1 million rows to transfer. Transfer time is around 8 hours. Wow! Sounds really bad. Possibility of network issue has been ruled out. OK, what could it be?
Checking the size of the table reveals the the table contains raw XML data that is stored as CLOB. This data alone amounts to almost 50GB. No wonder it is taking ages.
At the moment, the workflow is done as follows:
- raw trade data (including the XML) is loaded to Database A, which is a central trade repository
- there will be multiple subscribers reading this data from Database A, some in the form of direct copy via database link, some will extract data via files, and reload to other databases.
Regardless of the method, the same fact exists. 50GB worth of data has to be shipped around. Not a small task. Further questioning reveals that the XML data is never manipulated inside the database. This fact has a significant importance.
So what if we store the XML in the database as compressed? Easy enough to do using a little bit of java and some PL/SQL code. See this link from Ask Tom.
After applying this compression method, the total size of zipped XML is a miniscule 3GB (from the original 50GB). The benefit is instant.
The copy via database link is now taking a mere 4 minutes.
Storage requirement is reduced in the source database, as well as in the subscribers’ databases.
Job done… for now..