Skip to Main Content
  • Questions

  • csv output using sqlplus spool is very slower than expdp

Breadcrumb

XMAS_TREE

The AskTOM team is taking a break over the holiday season, so we're not taking questions or responding to comments, but we might still sneak in a few Office Hours sessions! Thanks for being a member of the AskTOM community.

Question and Answer

Connor McDonald

Thanks for the question, Siyavus.

Asked: May 09, 2024 - 1:04 pm UTC

Last updated: May 13, 2024 - 1:29 am UTC

Version: Oracle Databse Ent. 11.2.0.4 G

Viewed 1000+ times

You Asked

Dear friends,

I try to export csv from a patitoned table ( 300GB ) it takes 3 hours (only one table), using following code
set term off
set feed off
set colsep '|'
set echo off
set feedback off
set linesize 1000
set pagesize 0
set trimspool on
SET TERMOUT OFF
spool '/backup/csv/Mytable.csv'

SELECT /*+ parallel */ /*csv*/   col1  || '|' ||  col2  || '|' ||   col3   
         FROM MySchema.MyTable ;

spool off
exit;



but when I export (expdp ) all schema tables & its data (3TB) it takes only 20 minutes!

why expdp is very fast comparing to sql spool ?
what is fast method csv output from oracle table ?
regards
Siya AK

Hardware 4 TB Ram + 196 core cpu + nvme disk
oracle 11g r2

and Connor said...

expdp is reading blocks from the database and dumping them out to file, and can be done in parallel.

sqlplus is reading rows from the database, formatting them, then concatenating a CSV string and then writing that to file.

The "parallel" on your sqlplus query lets the database get the data in parallel, but sqlplus still is a serial process that is spooling out the CSV.

If you're looking for unload options, here's a video that covers some alternatives



We're not taking comments currently, so please try again later if you want to add a comment.
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

Data Pump

All of the database utilities including Data Pump are explained in the Utilities guide.