Clash Royale CLAN TAG#URR8PPP

Storing searchable Arrays in a SQL-database field

Currently I try to solve the following problem:
The document management software I´m working with can define masks for there documents and each mask has one or more indexfields (type:String). The system supports the following databases:

Now I should do a research if it is possible to store the array of strings in just one field of the sql database so that each mask only takes up one line in the sql-table.
One more requirement is that the array is searchable via sql.

The result should be that the sql-database isn´t as fragmented as it is now, cause every indexfield is one column in the mask definition and also in regard of performance the result should be equal (or if possible faster).

The current state is that I have working examples for postgres ( but only with Double so far ) and ORACLE ( via the ArrayDouble type ) with good results (no fragmentation and with performance improvements (>200%)). The examples are written in java ( standard jdbc connection ).

For DB2 I found some documentation about ordinary arrays (and two other types) but was not able to build a working example and for Microsoft SQL I was not able to find any hint that it has an array feature built in.

So my main question is if you guys have experience with this problem and can give me advice esspecially regarding DB2 and Microsoft SQL in how to tackle this problem. Also if you have an awesome solution for postgres and/or Oracle please share your knowledge with me. :)

Thanks in advance :) wish you all a enjoyable monday morning :)
Greetings Pascal

1 Answer
1

With Oracle, you can store arrays in a column using NESTED TABLEs (or VARRAYs):

NESTED TABLE

VARRAY

SQL Fiddle

Oracle 11g R2 Schema Setup:

CREATE TYPE String_Table IS TABLE OF VARCHAR2(100)
/

CREATE TABLE test (
id NUMBER(10,0),
col1 VARCHAR2(10),
array1 String_Table
) NESTED TABLE array1 STORE AS test__array1
/

INSERT INTO test ( id, col1, array1 )
SELECT 1, 'Row1', String_Table( 'A', 'B', 'C' ) FROM DUAL UNION ALL
SELECT 2, 'Row2', String_Table( 'C', 'D', 'E' ) FROM DUAL
/


Query 1: Then you can use collection operations such as: MEMBER OF to find items in a collection; and MULTISET operators like SUBMULTISET OF to find collections containing all items of another collection.

MEMBER OF

MULTISET

SUBMULTISET OF

SELECT *
FROM test
WHERE 'B' MEMBER OF array1
OR String_Table( 'E', 'C' ) SUBMULTISET OF array1


Results:

| ID | COL1 | ARRAY1 |
|----|------|--------|
| 1 | Row1 | A,B,C |
| 2 | Row2 | C,D,E |


If you are using Java then you can pass Java arrays as bind parameters of a PreparedStatement or CallableStatement. Some examples of this are here and here.

PreparedStatement

CallableStatement

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.