I created an external table with type as ora_datapump, then used CTAS to extract a table with 8.7 million rows on a sunfire v490, it took 25 seconds, the file is about 1G in size. This is really fast, Let's put this into perspective:
1) A CTAS to create a real table from same source table took 40 seconds.
2) A direct export took 6 minutes.
3) A conventional export tool 10 minutes.
4) My own data extraction utility took 10 minutes.
The export dump file has about 1.3G bytes, which is 30% bigger than the datapump one. Look inside the file, datapump saves space in the places such as using "FF" to represent null, whilst in export dump, "FEFF" is used. And also for each column, datapump use 1 byte, instead of 2 bytes as in export dump, to represent the size. Reducing size might help the performace datapump, but this won't explain how come it is more than 10 times faster than direct export. Seems I should never use export in 10g.
Unfortunately, datapump can't dump data into plain text. It wouldn't be difficult to interpret the format and to write a conversion utility, the challenge would be how to make the conversion as fast as datapump itself. I actually wrote one in java, it ran 7 minutes in a sun machine. A bit slow...
Monday, June 25, 2007
Subscribe to:
Posts (Atom)