oracle匯出多CSV檔案的靠譜的

標籤：term   執行   test   命令   head   好的   get   feed   add   

oracle匯出多CSV檔案的問題

----------------------------------------------------------------------

用ksh指令碼從oracle資料庫中匯出80w資料到csv檔案，如使用者給定名字為a.csv(檔案最大4000行記錄），則自動生產檔案為a_1.csv,a_2.csv,...., a_200.csv

我已經實現了一個方法，但80w要導5小時，使用者沒法接受。如下：
sqlplus -s user/pwd @${SqlDir}/select_log.sql >/tmp/a.tmp
然後每read 4000行到檔案a_1.csv,並且每隔4000行換一個檔案名稱。

大家有什麼好方法嗎？注意效率

----------------------------------------------------------------------

先一次性產生一個csv檔案
再用split分割檔案

--------------------------------------------------------

完全贊同樓上說的。

關於效能問題，用oracle spool出80萬行紀錄是非常快的，我剛試了一下，再hp-ux下，spool出某表中100萬紀錄（2列）耗時大約2分鐘，輸出檔案80m, 用split命令分割為每個4000行的檔案耗時數秒而已。 下附測試代碼:

/* sql file:  spool_test.sql */
set pages 0
set head off
set echo off
set feedback off
spool spool_text.csv
select col1, col2 from my_table where rownum<1000000;
spool off
exit

## shell script to spool out:
sqlplus -s user/password @spool_test.sql > /dev/null

## split file:
split -l 4000 spool_text.csv splited


--------------------------------------------------------

export.sh

#Generate the sql language
echo "SET HEADING OFF;">${SqlDir}/select_log.sql
echo "SET FEEDBACK OFF;">>${SqlDir}/select_log.sql
echo "SET LINESIZE 10000;">>${SqlDir}/select_log.sql
echo "SPOOL ${SqlDir}/EXPORT_ALL.CSV;" >> ${SqlDir}/select_log.sql
echo "SELECT SALESMAN_ID||‘,‘||">>${SqlDir}/select_log.sql
echo "SALESMAN_NAME||‘,‘||">>${SqlDir}/select_log.sql
echo "SALES_AMOUNT||‘,‘||">>${SqlDir}/select_log.sql
echo "SALES_DATE from sales_range;">>${SqlDir}/select_log.sql
echo "SPOOL OFF;" >> ${SqlDir}/select_log.sql
echo exit | ${ORACLE_HOME}/bin/sqlplus -s omc_sbardz812/omc_sbardz812 @${SqlDir}
/select_log.sql >/dev/null

split -l 4000 -a 5 ${SqlDir}/EXPORT_ALL.CSV log_20060606_
=============================================================

以上是我的指令碼，80w資料我用了3個多小時還沒完呢。有問題嗎？
另外，很重要的一點，split後，再不是csv尾碼了。看來還得檔案遍曆，然後一個一個的mv 成csv檔案。



--------------------------------------------------------

先整個匯出罷，然後在處理

--------------------------------------------------------

你的代碼看起來沒有問題，但80萬條記錄導3小時沒完成很難讓人理解。

你用sqlplus做一下測試，比如匯出10000條記錄需要多少時間，如果確實很慢，那可能是串連太慢（網路速度？）。

--------------------------------------------------------

匯出為csv檔案很快，就幾分鐘而已，但是split要花3個小時，共250個檔案，每個檔案4000行。我的機器是SUN Blade 150，還有我在server上啟動並執行，不存在網路速度的問題。

--------------------------------------------------------

根據需要匯出的列，單獨建個暫存資料表，比如：create table sales_range_tmp as select salesman_id,salesman_name,sales_amount,sales_date from sales;
再利用指令碼匯出來，看看速度應該有所提高。不過有一點必須明白，全掃描8w的表資料肯定不是幾分鐘可以完成的，何況要寫成物理檔案。


--------------------------------------------------------

KingSunSha(弱水三千),我用spool匯出的檔案大小為9G(8900828448byte on solaris),而你的才80M

--------------------------------------------------------

SELECT 中不要拼接字串，非常影響速度

--------------------------------------------------------

大家還有好的辦法嗎？

--------------------------------------------------------

9GB = 9,216 MB = 9,437,184 KB = 9,663,676,416 Bytes
800,000 records
size of each record (line) = 9GB/800,000 = 12,079 Bytes

select 中只有 4 列（SALESMAN_ID, SALESMAN_NAME, SALES_AMOUNT, SALES_DATE
），你覺得每行紀錄佔12KB合理嗎？

--------------------------------------------------------

問題是你在sqlplus中用了set linesize=100000，所以每行都會spool出100000bytes。
解決這個問題很簡單，在spool開始之前加上兩行set命令

set trimspool on
set termout off

第一個set是指定trim spool出的每行在行末的空格
第二個set是指定只spool而不回顯，所以在運行sqlplus的時候不需要 >/dev/null。


--------------------------------------------------------

非常感謝KingSunSha(弱水三千),現在我把linesize改為100，非常快，2分鐘就完成了。太好了。不過還有一個問題，匯出的檔案，每隔13行就會自動化佈建一個空行，能把這個去掉嗎？因為我匯出的檔案會被我的程式開啟的。

--------------------------------------------------------

just add 
set pagesize 0
before spool to suppress all headings, page breaks, titles, the initial blank line, and other formatting information.


--------------------------------------------------------

just add
set pagesize 0
before spool to suppress all headings, page breaks, titles, the initial blank line, and other formatting information.

--------------------------------------------------------

非常感謝KingSunSha(弱水三千),搞定了，貼出來吧，共用！

echo "SET HEADING OFF;">${SqlDir}/select_log.sql 
echo "SET FEEDBACK OFF;">>${SqlDir}/select_log.sql 
echo "SET LINESIZE 500;">>${SqlDir}/select_log.sql 
echo "SET trimspool on;">>${SqlDir}/select_log.sql 
echo "SET termout off;" >>${SqlDir}/select_log.sql 
echo "SET NEWPAGE NONE;" >>${SqlDir}/select_log.sql 
echo "SPOOL ${SqlDir}/EXPORT_ALL.CSV;" >> ${SqlDir}/select_log.sql 
echo "SELECT SALESMAN_ID||‘,‘||">>${SqlDir}/select_log.sql 
echo "SALESMAN_NAME||‘,‘||">>${SqlDir}/select_log.sql 
echo "SALES_AMOUNT||‘,‘||">>${SqlDir}/select_log.sql 
echo "SALES_DATE from sales_range;">>${SqlDir}/select_log.sql 
echo "SPOOL OFF;" >> ${SqlDir}/select_log.sql 
echo exit | ${ORACLE_HOME}/bin/sqlplus -s user/pwd @${SqlDir}/select_log.sql 

split -l 4000 -a 3 ${SqlDir}/EXPORT_ALL.CSV log_20060606_ 

#mv these splitted file to be csv file 
for file in $(ls ./) 
do 
    echo $file | grep log_20060606_ > /dev/null 
    status=$? 
    if [ $status -eq 0 ]; then 
        mv $file $file.csv 
    fi 
    status=1 
done 

80萬10分鐘就可以搞定了。

ksh可以執行，由於有這句：$(ls ./),sh不支援。

--------------------------------------------------------

謝謝大家！

 

http://blog.csdn.net/studyvcmfc/article/details/7047986

 

oracle匯出多CSV檔案的靠譜的