DBA Tools

    Supposed that we want to unload a huge table (100gb+) to text file, how to do it quickly? The answer is parallel unloading. I will suggest you perform a manual parallel by split the big table into multiple pieces with different where clause. But now I think the manual parallel is too complex, and it may perform multiple times full table scan, which brings extra IO overhead to the storage system, so I add the native parallel feature.

    In parallel SQLULDR2, we can split huge table into multiple piece by rowid range, each parallel slave just take piece of the data with extra IO overhead to the storage system. You just need to tell the parallel degree and the big table name in the SQL to do the parallel. Supposed that we have the following SQL query.

SELECT E.EMPNO, E.ENAME, D.DNAME
  FROM EMP E, DEPT D
WHERE E.DEPTNO = D.DEPTNO

    Supposed that the EMP is a huge table, we want to parallize the unload process. I will introduce two new options for parallel unloading.

  split  = table name for automatically parallelization.
  degree  = parallelize data copy degree (2-128).

    We also need to change the SQL query for parallel unloading, you can use two bind variables in the SQL, "MINRID" for the lower rowid range and "MAXRID" for the upper rowid range. So I rewrite the SQL as following.

SELECT E.EMPNO, E.ENAME, D.DNAME
  FROM EMP E, DEPT D
WHERE E.DEPTNO = D.DEPTNO AND
  E.ROWID >= :MINRID AND E.ROWID < :MAXRID

    If we want to unload the EMP table only, we just need pass the following options to SQLULDR2 for parallel unloading.

user=scott/tiger
query=select * from emp where rowid >= :minrid and rowid < :maxrid
split=emp
degree=4
file=uldrdata.%p.txt
log=log.%p.log

    The "%p" will be replaced by the parallel slave id (start from 1). With the parallel unloading feature, We can unload data very quickly. In a PC server with local disk only, I can unload data to text file with 32MB per second.