3

I plan to create an Oracle database that will contain arrays of numeric data. Each array has from 2 to 4 dimensions, and on the order of 1000 data points. I will want to use the arrays in WHERE clauses, e.g. .

Oracle's array support (VARRAYS, Nested Tables) seems disappointing; it's awfully hard for client software to retrieve the data in a useful way. Possible, but hard.

Any advice is appreciated.

user3006778
  • 41
  • 1
  • 4
  • 5
    What's wrong with a good old, regular table? – OldProgrammer Nov 19 '13 at 00:08
  • > What's wrong with a good old, regular table? You mean like a table with columns X11, X12, X13, X21, X22, etc? These arrays are ~ 10 x 10 x 10. Not very practical to represent each item as a column. – user3006778 Nov 19 '13 at 03:50
  • you might want to have a look at oracle spatial and how they solved similar problems. – steve Nov 19 '13 at 04:12
  • 2
    An array is an organising principle and is not required in a relational database. Instead, think of each data point as a "fact" and then store each fact as a tuple. e.g. table DATAPOINTS with columns DIMENSION1, DIMENSION2, DIMENSION3, DIMENSION4, POINTVALUE. If the ordering of the values within an "array" is important, add an ORDERINDEX column. With this design you'll have the best flexibility, and the full power of SQL analytics will be at your disposal. – Jeffrey Kemp Nov 19 '13 at 05:13
  • Jeffrey, interesting idea. I will give this a try. – user3006778 Nov 19 '13 at 06:46

1 Answers1

0

Simple example of varray

declare
   type array_example is varray(5) of varchar2(1);
   array array_example := array_example('A', 'B', 'C' , 'D', 'E');
begin
   for i in 1..array.count loop
       dbms_output.put_line(array(i));
   end loop;
end;
Pravin Satav
  • 702
  • 5
  • 17
  • 36
  • 1
    Pravin, I have found that VARRAYs are great if you stay within Oracle and PL/SQL. But they make it hard to retrieve the data for manipulation outside of Oracle. – user3006778 Nov 19 '13 at 06:45