Arrays.

    Arrays are commonly a feature of  programming languages and not of database proprietary languages or SQL itself. The reason is that relational databases rely on normalization (a set of rules that, among other things, partitions a complex object into a "container" relation and an "elements" relation) and it mandates that array elements must be put on another table.

Normalization gives you the general case, where you can insert as many "child" entities as you want without affecting the structure of the data. However, to reassemble the original object, you need at least to access and join two tables. This is the process that has received the effort of the database vendors' developer teams, because almost any retrieval operation involves a join or merge of one relation with itself, two relations or multiple relations, so it needs as much improvement as possible.

Given these facts, when the size of the "inner" or "nested" or "child" data is fixed and known in advance and probably all the elements are filled so you won't lose too much space in each record, then arrays offer good retrieval performance, because they involve reading one table (no join necessary) plus the region of memory where the arrays are stored.

IB supports arrays of all data types except BLOB. An array is a collection of values, or elements, each of the same data type. Individual array elements, block of contiguous elements, or the entire array can be accessed using standard SQL statements and API calls.

An array in IB can be up to 16 dimensions. Because IB arrays are multidimensional, you can store arrays as a whole in a single field, making accessing and retrieval fast and simple. An element of array data is referenced through the use of coordinates (offsets) into the array.

The operator to define and access arrays is the subscript operator [], common to several programming languages. Arrays in IB are implemented on top of stream BLOBs (an esoteric type that nobody has used directly because it's undocumented, as opposed to segmented BLOBs that are the ones declared by default) so they don't make the main part of each row to grow, because BLOBs are kept in their own pages in the database. Given that BLOBs were created by the IB's creator in the Pleistocene of the relational engines (and then other engines began implementing them), it's no great surprise they are the foundation for arrays. The implication is: if you don't access any array or array element, you don't have to pay any penalty in performance.

Currently, neither the BDE nor IBX are able to deal with arrays directly. They can deal with select statements that use each element as a separate column, however. If you want full support for arrays, you must go to the IB-API or use IBO.

This page was last updated on 2000-05-26 04:28:46