在Oracle中使用小blob是否合理？
在Oracle中，LongRaw和Varchar2在Oracle中的最大长度为4kb，但我需要存储8kb和Varchar2的对象。 16kb，所以我想知道什么是好的解决方案。 我知道我可以使用 Blob，但 Blob 具有可变长度，如果我是正确的话，它基本上是幕后的额外文件，是我对支付对象不感兴趣的功能和价格。 还有其他更适合这种需求的解决方案或数据类型吗？
谢谢
In Oracle LongRaw and Varchar2 have a max length of 4kb in Oracle, but I need to store objects of 8kb & 16kb, so I'm wondering what's a good solution. I know I could use a Blob, but a Blob has variable length and is basically an extra file behind the scenes if I'm correct, a feature and a Price I'm not interested in paying for my Objects.
Are there any other solutions or datatypes that are more suited to this kind of need?
Thanks
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（5）
Blob 不是幕后的文件。 它存储在数据库中。 为什么它的长度可变很重要？ 您可以只使用 blob 列（如果您的数据是文本数据，则使用 clob），并将其存储在自己的段中。
A blob is not a file behind the scene. It is stored in the database. Why does it matter that it has variable length? You can just use a blob column (or clob if your data is text data) and it gets stored in its own segment.
您应该使用 BLOB。
BLOB 不存储为额外文件，而是作为块存储在数据文件之一中（就像其他数据一样）。 如果 BLOB 对于单个块来说太大（在您的情况下可能不会发生），那么它将在另一个块中继续。
如果您的 BLOB 数据非常小，您可以让 Oracle 将其与行中的其他数据内联存储（例如 varchar2）。
在内部，Oracle 正在做类似于 PAX 建议的事情。 这些块与数据库块一样大，减去一些开销。 如果您尝试在 Oracle 之上重新发明 Oracle 功能，它只会比本机功能慢。
您还必须重新实现 DBMS_LOB 中已提供的一整套功能（长度、比较等）。
You should use a BLOB.
A BLOB is not stored as an extra file, it's stored as a block in one of your datafiles (just like other data). If the BLOB becomes too large for a single block (which may not happen in your case) then it will continue in another block.
If your BLOB data is really small, you can get Oracle to store it inline with other data in your row (like a varchar2).
Internally, Oracle is doing something similar to what PAX suggested. The chunks are as big as a DB block minus some overhead. If you try and re-invent Oracle features on top of Oracle it's only going to be slower than the native feature.
You will also have to re-implement a whole heap of functionality that is already provided in DBMS_LOB (length, comparisons, etc).
为什么不将二进制数据分段并存储在 4K 块中？ 您可以为这些块设置四个不同的列（以及一个用于将它们重建为大结构的长度列），或者使用另一个表的更标准化的方式，其中的块与原始表记录相关联。
如果您将来需要的话，这将提供扩展。
例如：
当然，您可能会发现您的 DBMS 在 BLOB 的幕后确实执行了这种行为，并且让 Oracle 处理它可能会更有效，从而使您无需从各个块手动重建数据。 我会衡量每个方法的性能以确定最佳方法。
Why don't you segment the binary data and store it in 4K chunks? You could either have four different columns for these chunks (and a length column for rebuilding them into your big structure) or the more normalized way of another table with the chunks in it tied back to the original table record.
This would provide for expansion should you need it in future.
For example:
Primary table: -- normal columns -- chunk_id integer chunk_last_len integer Chunk table: chunk_id integer chunk_sequence integer chunk varchar2(whatever) primary key (chunk_id,chunk_sequence)Of course, you may find that your DBMS does exactly that sort of behavior under the covers for BLOBs and it may be more efficient to let Oracle handle it, relieving you of the need to manually reconstruct your data from individual chunks. I'd measure the performance of each to figure out the best approach.
不要将二进制数据存储在 varchar2 列中，除非您愿意对它们进行编码（base64 或类似的）。 否则字符集问题可能会损坏您的数据！
尝试下面的语句看看效果：
select * from (select rownum-1 original, ascii(chr(rownum-1)) data from user_tab_columns where rownum<=256) where origin<>data;
Don't store binary data in varchar2 columns, unless you are willing to encode them (base64 or similar). Character set issues might corrupt your data otherwise!
Try the following statement to see the effect:
select * from (select rownum-1 original, ascii(chr(rownum-1)) data from user_tab_columns where rownum<=256) where original<>data;
Varchar2 也是可变长度的。 如果您需要在数据库中存储任何大于小尺寸的二进制数据，则必须查看 blob 的方向。 当然，另一个解决方案是将二进制文件存储在文件系统上的某个位置，并将文件的路径作为 varchar 存储在数据库中。
Varchar2 is of variable length just as well. If you need to store binary data of any bigger than small size in your database, you'll have to look in blob's direction. Another solutiuon is of course storing the binary somewhere on the file system, and storing the path to the file as a varchar in the db.