Since ociuldr get 25% speed improve, I want to know the performance difference with oracle export utility. I tested them on my notebook, oracle server and client in the same host, 8 columns (including VARCHAR, NUMBER and DATE types), 3.6 million rows. The table structure are listed as following.
Name Null? Type
------------------------ -------- ------------------
EMPNO NUMBER(4)
ENAME VARCHAR2(10)
JOB VARCHAR2(9)
MGR NUMBER(4)
HIREDATE DATE
SAL NUMBER(7,2)
COMM NUMBER(7,2)
DEPTNO NUMBER(2)
Oracle export utility need about 20 to 24 seconds for exporting full table. When non-fixed length text file, it take ociuldr 30 seconds to unload all the rows, the generated file is 186MB. The unload logs are listed as following.
0 rows exported at 2009-03-22 16:56:23
500000 rows exported at 2009-03-22 16:56:27
1000000 rows exported at 2009-03-22 16:56:30
1500000 rows exported at 2009-03-22 16:56:35
2000000 rows exported at 2009-03-22 16:56:39
2500000 rows exported at 2009-03-22 16:56:43
3000000 rows exported at 2009-03-22 16:56:47
3500000 rows exported at 2009-03-22 16:56:52
3670044 rows exported at 2009-03-22 16:56:53
output file uldrdata.txt closed at 3670044 rows.
It take ociuldr 31 to 32 seconds to unload all the rows, the generated file is 220MB. The unload logs are listed as following.
0 rows exported at 2009-03-22 17:01:38
500000 rows exported at 2009-03-22 17:01:42
1000000 rows exported at 2009-03-22 17:01:46
1500000 rows exported at 2009-03-22 17:01:50
2000000 rows exported at 2009-03-22 17:01:54
2500000 rows exported at 2009-03-22 17:01:59
3000000 rows exported at 2009-03-22 17:02:03
3500000 rows exported at 2009-03-22 17:02:08
3670044 rows exported at 2009-03-22 17:02:09
output file uldrdata.txt closed at 3670044 rows.
Text unloading is still slower than Oracle binary export, only 75% capacity. Because text unload need convert NUMBER and DATE types to text string, it's resonable to be slower. Then I load the text files into the same table with sqlldr in both normal mode and direct mode. Following is the performance log.
-- Normal + Separator
Elapsed time was: 00:00:32.73
CPU time was: 00:00:13.57
-- Normal + Fixed Length
Elapsed time was: 00:00:29.83
CPU time was: 00:00:13.23
-- Direct + Separator
Elapsed time was: 00:00:29.83
CPU time was: 00:00:13.23
-- Direct + Fixed Length
Elapsed time was: 00:00:17.42
CPU time was: 00:00:08.93
For normal loading, whether fixed length format is not so important. but for direct mode, fixed length text file is much faster than normal mode, seems faster than data exporting.
Comments (2)
One note:
A better data process design is, we will read from external table (flat file) and process it directly, such as transformation and then load into target table, save the interim step to load data into a staging table.
That means skip this step: -- Direct path insert + Fixed Length.
Posted by Charlie Yi.Zhu | March 24, 2009 1:50 AM
Fixed length external table provide better access speed in this case.
Posted by anysql | March 24, 2009 8:44 AM