db-torque-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Bill Schneider" <bschnei...@vecna.com>
Subject Re: clobs using torque and oracle
Date Mon, 03 Feb 2003 14:03:27 GMT
I've found that using the LONGVARBINARY types like "bytea" in PostgreSQL and
"long raw" in Oracle is more portable and makes db independence easier.
But, there's a catch.

Internally in Oracle, "LONG RAW" and "BLOB" are very different.  ("LONG" and
"CLOB" are analogous but store character data instead of binary).  When you
have a LONG RAW, all of the object data--which could be megabytes--is stored
in-line with the row.  So, a query like "select id, title from foo" will
take longer when there is a LONG [RAW] column in the table, _even when that
data is not selected_, because the execution plan has to figure out how big
each LONG column is to skip over it.  Physical seeks between rows are much
longer and not fixed-size.

LOB types (CLOB, BLOB) store only an ID pointer physically in-line with the
rest of the row, and the actual LOB data is stored somewhere else.  So,
"select id from foo" is fast, no matter how big the data in the LOB is.
There is a related type BFILE which goes a step futher, storing the physical
content directly in the file system.  (BFILE is not that useful in practice
when Oracle is just a backend for a J2EE app.)

Other advantages to LOBs are that you get the "dbms_lob" package for
manipulating them (LONGs are hard to manipulate), and you can partition your
tables so that LOB data is stored in a different tablespace.  Also, in JDBC,
you can pull LOB data in with an input stream so you don't need enough heap
to store the whole byte array at once.

So, in Oracle, LOBs are a real win over LONGs, and the documentation
strongly discourages use of LONG [RAW].  The drawback is that LOBs tend to
be non-portable.

the "oid" type in  PostgreSQL is analogous to a LOB in Oracle, because it
stores an integer ID in-line with the row and the actual data goes
elsewhere, manipulated with the "lo_xxx" stored functions.  The main
difference in PostgreSQL is that the object ID is much more visible--you see
the integer ID directly.  This doesn't play nice with JDBC, because you have
no way to tell from JDBC metadata whether a Types.INTEGER column is really
an int, or an OID pointer.

-- Bill

> Something more to the subject...
> The oracle field is declared as LONG RAW.
> This schema Oracle-LONG-RAW / Torque-BLOB works for us, but...there is
> a shadow of doubt...
> Is there any Oracle guru able to explain the differences between the
> "LOB" fields in Oracle?

View raw message