英文:
In Oracle execution time for finding a data inside comma seperated column taking too long
问题
I am trying to find out if the list of data is in a comma-separated column. I have a code select * from my_table where (regexp_substr ( listcolumn, '[^,]+', 1, level ) in ('a','d')) connect by level <= regexp_count(listcolumn, ',') + 1; and a table with a lot of data. The problem is it works for a small data table, but it is taking too much time to execute for a table with a lot of data. I am not an expert in the database, so can you please help with how to resolve this problem? Thank you in advance.
英文:
I am trying to find out if the list of data is in comma separated column. I have a code select * from my_table where (regexp_substr ( and table with a lot data. The problem is it works for small data table but it is taking too much time to execute for a lot a table with a lot of data. I am not expert in database. so can you please help to how to resolve this problem. Thank you in advance.
listcolumn,
'[^,]+',
1,
level
) in ('a','d'))
connect by level <= regexp_count(listcolumn, ',') + 1;
答案1
得分: 1
不要拆分字符串，查找子字符串匹配（带有周围的分隔符，以便匹配整个项）：
SELECT *
FROM my_table
WHERE ',' || listcolumn || ',' LIKE '%,' || 'a' || ',%'
OR ',' || listcolumn || ',' LIKE '%,' || 'd' || ',%';
英文:
Don't split the string, look for a sub-string match (with the surrounding delimiters so that you match entire terms):
SELECT *
FROM my_table
WHERE ',' || listcolumn || ',' LIKE '%,' || 'a' || ',%'
OR ',' || listcolumn || ',' LIKE '%,' || 'd' || ',%';
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论