Oracle-where子句中的断字列

Question

考虑一个表：

ID  COUNTRY_CODE
1   ab-cd-ef
2   gh-ef
3   cd-ab-pq-xy

我需要一个sql查询来选择包含特定国家代码的记录。当然，传统的LIKE方法是有效的：

select ID from TableName where COUNTRY_CODE like '%cd%';

这里的问题是，此查询将运行数百万条记录，从而增加总操作成本。由于成本问题，此处不能选择嵌套表。

注意：如果需要，可以使用Java对查询进行参数化。

有没有任何经济高效的方法来处理这些可搜索的列？

Answer 1

不要将列表存储为分隔字符串。创建一个单独的表：

CREATE TABLE TableName ( id NUMBER PRIMARY KEY );
CREATE TABLE TableName__Country_Codes(
id           NUMBER REFERENCES table_name ( id ),
country_code CHAR(2),
PRIMARY KEY ( id, country_code )
);

然后你可以使用

SELECT ID
FROM   TableName__Country_codes
WHERE  'cd' = COUNTRY_CODE;

或者使用嵌套表：

CREATE TYPE Char2List IS TABLE OF CHAR(2)
/
CREATE TABLE TableName(
ID NUMBER PRIMARY KEY,
Country_Code Char2List
) NESTED TABLE Country_code STORE AS Table_Name__Country_Codes;

然后你可以使用

SELECT ID
FROM   TableName
WHERE  'cd' MEMBER OF COUNTRY_CODE;

如果必须使用分隔字符串，则查询应在搜索表达式中包含分隔符：

SELECT ID
FROM   TableName
WHERE  '-' || COUNTRY_CODE || '-' like '%-cd-%';

否则，如果列表元素较长，则%cd%可以与cd、cda或bbcdbb匹配。