[R-sig-DB] Re: Oracle: SELECT CLOB

[David James]

Michael Mader wrote:
> Hi all,
> 
> is there anybody working on a stable/fast way to select entire CLOBs via
> ROracle?
> 
> Sounds nasty? 

Yes, it does.  I'm not aware of any work on this.  Large objects
(both character and binary) pose some interesting problems to R
and Splus -- from buffer overruns in the various libraries and
interface layers all the way to the lack of suitable containers
in R and Splus.  R has the concept of foreign references (external
pointers), and Splus has "raw" objects that could perhaps be used
to implement proxy objects (objects that defer their operations
to other systems/languages, as in the various inter-system
packages RPgSQL, RS-Perl, RS-Python, RS-Java, RS-Corba, ...)
Another potential hurdle is the S language "whole-object" view
(as compared to "one-record-at-a-time") that makes it difficult to
working with data larger than physical memory.  Some attempts have
been made in the past to handle large external objects (e.g., John
Chambers and Neil Crellin's work on images and external objects,
http://cm.bell-labs.com/stat/doc/93.1.ps) but I believe that a
general framework is yet to be defined.

> What I am thinking about is to store vectors (5k to 30k elements) in
> CLOBs which would speed up retrieval.

The biggest risk I see is that you may run into some buffer limit
somewhere (probably sooner rather than later).  At first glance
it appears to me that packing vectors into CLOBs for the sake
of retrieval speed may carry hidden costs for other calculations
(not to mention that it may violate good relational database design
principles).

Perhaps you may want to consult with an experienced RDBMS programmer 
as to how to solve this type of database design problem? 

Regards,

> 
> Cheers
> 
> Michael
> -- 
> Michael T. Mader
> Institute for Bioinformatics/MIPS, GSF
> 0049-89-3187-3576

-- 
David A. James
Statistics Research, Room 2C-253            Phone:  (908) 582-3082       
Bell Labs, Lucent Technologies              Fax:    (908) 582-3340
Murray Hill, NJ 09794-0636