Storing Array of Floats as a BLOB in Oracle

Question

I am designing a new laboratory database. For some tests, I have several waveforms with ~10,000 data points acquired simultaneously. In the application (written in C), the waveforms are stored as an array of floats.

I believe I would like to store each waveform as a BLOB.

Questions:

Can the data in a BLOB be structured in such a way that Oracle can work with the data itself using only SQL or PL/SQL?

• Determine max, min, average, etc
• Retrieve index when value first exceeds 500
• Retrieve 400th number
• Create BLOB which is a derivative of first BLOB

NOTE: This message is a sub-question of Storing Waveforms in Oracle.

Answer 1

I think that you could probably create PL/SQL functions that take the blob as a parameter and return information on it.

If you could use XMLType for the field, then you can definitely parse in PL/SQL and write the functions you want.

http://www.filibeto.org/sun/lib/nonsun/oracle/11.1.0.6.0/B28359_01/appdev.111/b28369/xdb10pls.htm

Of course, XML will be quite a bit slower, but if you can't parse the binary data, it's an alternative.

Answer 2

You may also wish to consider the VARRAY type. You do have to work with the entire array (no retreival of subsets, partial updates, etc.) but you can define a max length and Oracle will store only what you use. You can declare VARRAYs of most any datatype, including BINARY_FLOAT or NUMBER. BINARY_FLOAT will minimize your storage, but suffers from some minor precision issues (although important in financial applications). It is in IEEE 754 format.

Since you're planning to manipulate the data with PL/SQL I might back off from the BLOB design. VARRAYs will be more convenient to use. BLOBs would be very convenient to store an array of raw C floats for later use in another C program.

See PL/SQL Users Guide and Reference for how to use them.

Answer 3

• Determine max, min, average, etc
• Retrieve index when value first exceeds 500
• Retrieve 400th number

The relational data model was designed for this kind of analysis - and Oracle's SQL is more than capable of doing this, if you model your data correctly. I recommend you focus on transforming the array of floats into tables of numbers - I suspect you'll find that the time taken will be more than compensated for by the speed of performing these sorts of queries in SQL.

The alternative is to try to write SQL that will effectively do this transformation at runtime anyway - every time the SQL is run; which will probably be much less efficient.