NoSQL Database

Hello, everyone!

I have one question about Oracle NoSQL. It can store huge ammount of data and operate it using oracle.kv.lob package.
It can also translate data to a common relational Oracle Database 11gR2 using External Tables mechanism.
But combining these two mechanisms getting me a big trouble.

In ideal way I want to write:

SELECT PK, oracle_BLOB
FROM NoSQL_ExternalTable
WHERE PK in (<list of integer>);

BUT (now difficult part begins)

1)As documentation says, method toOracleLoaderFormat of Formatter interface returns String only. So you can not return unstructured data or especially 1GB of unstructured data in an ordinal way. The workaround is to use temporary files: unload NoSQL BLOB to a stream, write the stream to a temp file and then read this file using oracle BFILE locator. Yes, I encapsulated filewritting mechanism in toOracleLoaderFormat. But this method is slow, on kvlite database it takes 1:41 minutes to select 1,8 GB file and temp file is a really bottleneck.

2) As far as I encapsulated filewritting mechanism in toOracleLoaderFormat function, every time preprocessor invokes this method, a new 1 GB file is created.
And as long as NoSQL does full scan on its database using ETables, every SELECT results in ALL files extracted in temp directory.
As a crutch I used another file with list of PK that I needed. It is taken to be that this file will have been formed BEFORE main SELECT procedes.
So initially formatter function reads this file and then compares Key part of incoming KeyValueVersion variable with contents of this file. If the Key is in list then extracting proceeds.

For example, PK file contains 1000, 1001, 1003 and NoSQL DB contains recors with keys like
exttab/1000/-/document/archive1000.zip
...
exttab/9000/-/document/archive9000.zip

Preprocessor invokes Formatter with parameter kvv (key part) = exttab/1000/-/document/archive1000.zip, Formatter parses key, extracts 1000.
Then it reads PK file, finds record 1000 and puts huge 2GB archive to temp dir. When it doesn't find record 9000 it doesn't put a huge archive to temp dir.

==================================================================================
Is there any idea how to perform seamlesly SELECT of BLOB (BFILE) from NoSQL to "SQL" database in a less complicated way and in acceptable time?
==================================================================================

Edited by: 1004734 on May 8, 2013 3:26 PM