Search code examples
arraysoracleperformanceoracle11gregexp-substr

Oracle 11g how to store multi values to one column but get ok performance

Table definition

Hi，

My database is Oracle11g. For some reason, I need to store one or several values in column1. In this case I use '||' as a delimiter. So the sql query is trying to answer, e.g. if value 310-G01-000-000-000 in column1?

I want a solution, for Oracle 11g, to enhance the query performance? I know that PostgreSQL column can store an array, and there is index for array. But for Oracle, I don't know.

On of my thought is using REGEXP_SUBSTR to generate a list, and use function index on REGEXP_SUBSTR may work?

Please let me know the best practice to store multi values to one column and the way to query it quickly?

Thanks a lot J

Solution

  • If you want performance then don't store delimited values in a column.

    If you cannot avoid it then just use simple string functions (which are an order of magnitude faster than regular expressions) to check if there is a sub-string match:

    SELECT *
FROM   test_table
WHERE  '||' || column1 || '||' LIKE '%||' || :search_value || '||%';

    or:

    SELECT *
FROM   test_table
WHERE  INSTR('||' || column1 || '||', '||' || :search_value || '||') > 0;

    However

    You could use a nested table:

    CREATE TYPE string_list IS TABLE OF VARCHAR2(19);

CREATE TABLE test_table (
  id      VARCHAR2(10),
  column1 string_list
) NESTED TABLE column1 STORE AS test_table__column1;

    Then:

    INSERT INTO test_table (
  id,
  column1
) VALUES (
  'abc',
  string_list('310-G01-000-000-000', '310-G04-000-000-000','310-G04-000-000-001')
);

    and to find the value use the MEMBER OF collection operator:

    SELECT id
FROM   test_table
WHERE  '310-G01-000-000-000' MEMBER OF column1;

    db<>fiddle here