使用Oracle外部表对大文件排序

版权声明：本文为博主原创文章，未经博主允许不得转载。 https://cloud.tencent.com/developer/article/1433235

问题：对一个一列两亿行的无序的文本文件进行排序，生成一个排好序的新文本文件。

1. 生成无序文件，BigFileTest.Java代码如下：

java view plain copy

1. import java.io.BufferedWriter;
2. import java.io.FileWriter;
3. import java.util.Random;
5. public class BigFileTest {
6. static Random random = new Random();
7. public static void main (String[] args) throws Exception {
8. createFile();
9. }
10. public static void createFile() throws exception {
11. BufferedWriter fw = **new** BufferedWriter(**new** FileWriter("D:\\BigFileTest\\bigfile.txt"));
12. for (int i=1; i<200000000; i++) {
13. fw.write(random.nextLong() + "");
14. fw.newLine();
15. if (i % 10000 == 0) {
16. fw.flush();
17. }
18. }
19. }
20. }

javac BigFileTest.java

Java BigFileTest

至此生成了一个两亿行的文本文件bigfile.txt

1. 建立外部表

sql view plain copy

1. create directory data_dir as 'D:\BigFileTest\';
2. create table bt_ext_test(a varchar2(30))
3. organization external
4. (type oracle_loader
5. default directory data_dir
6. access parameters
7. (records delimited by newline characterset zhs16gbk
8. badfile data_dir:'bigfile.bad'
9. discardfile data_dir:'bigfile.dsc'
10. logfile 'bigfile.log'
11. fields terminated by 0x'09' ldrtrim
12. missing field values are null
13. reject rows with all null fields
14. )
15. location ('bigfile.txt')
16. )
17. parallel
18. reject limit unlimited;
19. 使用sqlplus的spool生成排序的新文件

sql view plain copy

1. set echo off
2. set feedback off
3. set termout off
4. set arrarsize 5000
5. set heading off
6. set head off
7. set trimout on
8. set pagesize 0
9. set trimspool on
10. set ;inesize 30
12. spool result.txt
13. select /*+ parallel(bt_ext_test,8) */ * from bt_ext_test order by a;
14. spool off
16. exit;

在4个双核CUP，64位oracle11.2上，用8个并行查询，生成排序文件用时32分钟。