Is it possible to increase the max columns of an Oracle database to store huge arrays?

Question

For reasons that are beyond my control I need to store huge arrays (10000+ entries) in database rows, and it has to be easy to access each index of the array individually, which means I'd rather avoid serialization/blobs, if at all possible.

So my first idea, and the actual question here, is can I increase max columns for Oracle in anyway?

Or is there an easy way to say store the array over a few rows and hide the implementation below the surface (I'm thinking stored procedure here).

Any other suggestions are also welcome!

Answer 1

Not sure about increasing the max columns. However, Why not split it across multiple tables which each table sharing the same unique ID. This implementation will be more easier than storing it over multiple rows.

Answer 2

The maximum number of columns is a constraint of the database engine. So no luck there.

I don't know how many rows your database will contain, but it could be a solution to use a table that stores three fields: ArrayId, FieldIndex, Contents. Each array field is a single row in the database. If you put a combined index on ArrayId and FieldIndex, access should be pretty fast.

This solution would also allow for variable-length arrays.

Answer 3

Why not store the array as a Blob?

It is very unlikely that anyone would ever code "Where Acol999 = 42 or Acol1000 = 42 ..." in SQL so if you just want to store and retreive than store it as a Big Clob or Blob depending on your data.

This will be much quicker qnd involve much less code.

If you eally want to query the contents than you really need a separate table with 1 - ONE - row per array cell. You will then be able to query this sensibly.

CREATE TABLE ARRAY_CELLS (
     PARENT_ID INT NOT NULL,
     INDEXNUM  INT NOT NULL,
     VALUEOF   LONG NOT NULL
    PRIMARY KEY(PARENT_ID,INDEXNUM)
);

You can then run queries like:-

 SELECT PARENT_ID from TABLE_ARRAY_CELLS WHERE VALUEOF = 42 GROUP BY PARENT_ID;

Which will give you a list of all the Arrays that contain value 42.