Oracleへのデータ投入

Oracleに大量のデータ投入で試行錯誤してようやく満足できる形になりました。


データ数
20万件/時間
１ファイル20MB程度。

現在
6,000万件以上のレコードあり。

しばらくの間削除はしない。



投入するデータ方式
１　INSERT文
２　従来型パス・ロード
３　ダイレクト・パス・ロード
４　ダイレクト・パス・インサート

今回は、パーティション単位ではないし、初期データ投入でもないし、Enterprise Editionでもないため、１～３の順に試しました。


１は、12万件ぐらいまでは20分程度でしたが、表示を速くするため、カラムに色々Indexを作成すると、投入に90分ほど掛かるようになり、60分のデータを投入するのに90分掛かるようになったため、断念しました。


２は、sqlldrを使用するために、CSV方式にデータを出力する必要があります。
また同じくIndexを使えるために使用していました。15万件で20分程度かかり、Indexを作成した後も30分ほどで投入できましたが、後々問題になってくると思い、３の方法を試すことに。




３は、最初は、Indexが使えないと勘違いして困っていました。トリガーも無効になるというので困っていました。
このトリガーは、ユニークIDを保持するためシーケンスを作成し、入力トリガーで追加するようにしていました。

しかし、sqlldrでは、http://oracle.se-free.com/utl/C2_seq.htmlを参考にしたところ、

ID SEQUENCE(MAX, 1),
※1は省略可能

でうまくいきました。

この時、CSVファイルには、ID番号の項目は記載しません。



また、Indexを無効にし、投入後Indexを再構築するというのを知りましたが、時間が掛かるというのを知り、やはり避けていましたが、背に腹は代えられないということで、試しました。



その結果

20万件が3分
40万件が3分(2時間分のファイルをひとまとめ)
250万件が5分(12時間分のファイルをひとまとめ)
480万件が5分(24時間分のファイルをひとまとめ)
で終了しました。

素晴らしい～～～～～。

感動しました。こんなに速くなるのかと！


というわけで、躊躇している方がいましたら、是非試してみてください。

MySQLのテーブルをエクスポートしてインポートする

MySQLのテーブルをエクスポートしてインポートする方法

エクスポート
#mysqldump データベース名(test_db) > 保存ファイル(test.sql) -u root -p
Enter password:パスワード

インポート
#mysql インポートデータベース名(test_db) < 保存ファイル(test.sql) -u root -p
Enter password:パスワード

SQLで重複データを抽出する

SQLで重複データを出力させないのは、DISTINCTを使用すればよいが、逆に重複データを抽出するのは、下記のようにしなければなりません。

SELECT * FROM テーブル名
WHERE (列名A, 列名B) IN (
SELECT 列名A,列名B FROM テーブル名
GROUP BY 列名A,列名B
HAVING COUNT(*)>1)


マシンにもよるでしょうが、データ量が多いとかなりの時間掛かります。