将大量数据加载到Oracle SQL数据库 [英] Loading large amounts of data to an Oracle SQL Database
问题描述
我想知道是否有人对我即将从事的工作有任何经验.我有几个csv文件，它们的大小都在一个GB左右，我需要将它们加载到oracle数据库中.尽管加载后我的大部分工作都是只读的，但我仍需要不时加载更新.基本上，我只需要一个很好的工具即可一次将多行数据加载到数据库中.
I was wondering if anyone had any experience with what I am about to embark on. I have several csv files which are all around a GB or so in size and I need to load them into a an oracle database. While most of my work after loading will be read-only I will need to load updates from time to time. Basically I just need a good tool for loading several rows of data at a time up to my db.
这是我到目前为止所发现的:
Here is what I have found so far:
-
我可以使用SQL Loader来完成很多工作
I could use SQL Loader t do a lot of the work
我可以使用批量插入命令
I could use Bulk-Insert commands
某种批量插入.
以某种方式使用准备好的语句可能是一个好主意.我想我想知道每个人都认为是完成此插入操作的最快方法.有提示吗?
Using prepared statement somehow might be a good idea. I guess I was wondering what everyone thinks is the fastest way to get this insert done. Any tips?
推荐答案
如果您能推出自己的实用程序，性能会优于并行直接路径加载，这使您可以同时运行多个直接路径加载过程.
I would be very surprised if you could roll your own utility that will outperform SQL*Loader Direct Path Loads. Oracle built this utility for exactly this purpose - the likelihood of building something more efficient is practically nil. There is also the Parallel Direct Path Load, which allows you to have multiple direct path load processes running concurrently.
从手册中:
代替填充绑定数组缓冲区 并将其传递给Oracle数据库 用SQL INSERT语句，直接 路径加载使用直接路径API来 将要加载的数据传递给加载器 服务器中的引擎.负载引擎 从构建一个列数组结构 传递给它的数据.
Instead of filling a bind array buffer and passing it to the Oracle database with a SQL INSERT statement, a direct path load uses the direct path API to pass the data to be loaded to the load engine in the server. The load engine builds a column array structure from the data passed to it.
直接路径加载引擎使用 列数组结构进行格式化 Oracle数据块和构建索引 键.新格式化的数据库 块直接写入 数据库(每个I/O多个块 如果使用异步写入请求 主机平台支持 异步I/O).
The direct path load engine uses the column array structure to format Oracle data blocks and build index keys. The newly formatted database blocks are written directly to the database (multiple blocks per I/O request using asynchronous writes if the host platform supports asynchronous I/O).
内部使用多个缓冲区 用于格式化的块.虽然一个 缓冲区正在填充，一个或多个 如果正在写入缓冲区 异步I/O在 主机平台.重叠计算 I/O可以提高负载性能.
Internally, multiple buffers are used for the formatted blocks. While one buffer is being filled, one or more buffers are being written if asynchronous I/O is available on the host platform. Overlapping computation with I/O increases load performance.
在某些情况下，直接路径加载无法被使用.
There are cases where Direct Path Load cannot be used.
这篇关于将大量数据加载到Oracle SQL数据库的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
