什么是插入&使用SQL从Oracle数据库中检索BLOB数据？ [英] What are the ways to insert & retrieve BLOB data from Oracle database using SQL?

问题描述

我试图用SQL开发人员插入BLOB数据。但我找不到实际用于插入BLOB数据的insert语句。

I've tried to insert BLOB data with SQL developer. But I can't find the insert statements which are actually used to insert BLOB data.

除此之外，数据库速度真的很慢。对于小文件，它执行得很好。但是当我试图导入50 mb avi文件到BLOB，花了3-4分钟，仍然没有完成。当我试图导出BLOB数据到一个文件，导出过程也很慢。我使用的是Oracle 10g Express Edition。如果数据库速度比文件系统速度慢，那么为什么数据库用于存储BLOB数据？有没有其他方法来优化性能？

Apart from that, the database speed is really slow. For small files, it executes fine. But when I tried to import 50 mb avi file into BLOB, it took 3-4 minutes & still it was not completed. When I tried to export the BLOB data to a file, exporting process was also slow. I was using Oracle 10g Express Edition. If the database speed is slower than even file-system speed, then why database are used for storing BLOB data? Is there any other way to optimize the performance?

推荐答案

首先，你应该期望 ，经常显着地）较慢，但绝对不比将它们存储在文件系统中快。将它们存储在DB中的原因不以性能为中心，而是关于例如：

First of all, you should expect storing BLOBs in a database to be (sometimes a bit, often significantly) slower, but definitly not faster than storing them in a file system. The reasons to store them in a DB do not center about performance, but about e.g.:

• 在（共享）文件系统的不可用性群集或负载平衡的场景


• 易于备份：单个进程，aot 2进程使用文件和数据库时


• 事务安全：BLOB已完成或尚未完成，但尚未完成。


• 其他我现在不能想到的。

• Unavailability of a (shared) file system in a clustered or load-balanced scenario
• Ease of backup: Single process, a.o.t. 2 processes when files and DB are used
• Transaction safety: A BLOB is either there and complete or not, but not in a half-baked stage
• others I can't think of right now.

一般的经验法则是，如果这些都不关心你，你应将您的文件存储为...文件。将元数据和路径名存储在DB中是IMHO好的和常见的做法。

The general rule of thumb is, that if none of these concern you, you should store your files as ... files. Storing the metadata and pathname in a DB is IMHO good and common practice.

关于Oracle调整：有关于这些的书。我怀疑他们总计超过一吨的死树平装书格式。你可能首先看看Oracle进程的内存消耗 - 经验法则：如果它不到一个gig，你使用BLOB，你就麻烦了。在不同的内存池上读取以及如何增加它们。对于快递版本的某些限制可能适用。

Concerning Oracle tuning: There are books written about that. I suspect to total them far over a ton in dead-tree-paperback format. You might first of all look at the Oracle process' memory consumption - rule of thumb: If it is less than a gig and you use BLOBs, you are in trouble. Read up on the different memory pools and how to increase them. Some limits for the express edition might apply.

这篇关于什么是插入&使用SQL从Oracle数据库中检索BLOB数据？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！