0

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

TonyRen
  • 11
  • 4
  • This is a denormalized database design, that could be beneficial for a few read-only queries, but quite detrimental for the rest of queries (when applying changes). Are you sure you want to do this? – The Impaler Jun 24 '22 at 15:45
  • Yes，only for query beneficial – TonyRen Jun 24 '22 at 16:20
  • I have made some experiments. create table testtable(id integer primary key, column1 varchar2(200)); insert into testtable （id,column1) values(123,'310-G01-000-000-000,310-G04-000-000-000,310-G04-000-000-001'); insert into testtable （id,column1) values(456,'310-G01-000-000-000'); select id from (SELECT id,REGEXP_SUBSTR(column1,'[^,]+',1,level) region FROM testtable connect by level <= (REGEXP_COUNT(column1,'\,')+1)) where region = '310-G01-000-000-000' ;then I create a function index: CREATE INDEX index1 ON testtable(REGEXP_SUBSTR(column1,'[^,]+',1) ); The index seems doesnt help – TonyRen Jun 24 '22 at 16:28
  • I was thinking about this problem. With a normalizes the design (one value per row) the search would be really fast. Without it it's not easy for the engine to index it. – The Impaler Jun 24 '22 at 16:45
  • Use [Nested Tables](https://docs.oracle.com/database/121/LNPLS/composites.htm#LNPLS99929). Oracle provides [various functions](https://docs.oracle.com/database/121/SQLRF/operators006.htm#SQLRF0032) for them, they are much easier and have better performance than regular expressions. – Wernfried Domscheit Jun 24 '22 at 20:49
  • no, we don't want normalize it.There are N:N relationships, but I don't want to create relationship tables because there are so many other tables like testtable. – TonyRen Jun 25 '22 at 01:34

1 Answers1

1

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

MT0
  • 143,790
  • 11
  • 59
  • 117
  • Hi MTO, Thank you! I test your code , all good. So the nested table must have better performance than delimited values ，correct? In real world, the test_table already exists and it has 6 millions records. Do I have to recreate it using the ’create table‘ statement you provided , or can I use 'Alter table add column column1' sort of statement? I don't know the specific syntax, can you please tell me？ Also, do I need to create any index for the column1? Thanks – TonyRen Jun 25 '22 at 02:20
  • I create index for the nested table, I will try to test if the index help. create or replace type obj as object ( column1_t varchar2(100)); CREATE OR REPLACE TYPE string_list AS TABLE OF obj; CREATE TABLE test_table ( id VARCHAR2(10), column1 string_list ) NESTED TABLE column1 STORE AS test_table__column1; INSERT INTO test_table ( id, column1) VALUES ('abc', string_list(obj('310-G01-000-000-000'),obj('310-G04-000-000-000'),obj('310-G04-000-000-001')) ); create index index1 on test_table__column1 (column1_t); – TonyRen Jun 25 '22 at 05:36
  • @TonyRen It is in [the documentation](https://docs.oracle.com/cd/E11882_01/server.112/e41084/statements_3001.htm#i2192749) `ALTER TABLE test_table ADD ( column1 string_list ) NESTED TABLE column1 STORE AS test_table__column1;` [db<>fiddle](https://dbfiddle.uk/?rdbms=oracle_11.2&fiddle=2f64974a4f5c20a7450da5a1159cdf61) – MT0 Jun 25 '22 at 09:59