sql - 有效地检查文本列中是否存在文本

Question

我有一个包含大约 2,000,000 行的表。我需要查询其中一列以检索字符串作为值的一部分存在的行。

当我运行查询时，我会知道字符串的位置，但事先不知道。因此，采用子字符串的 View 不是一个选项。

据我所知，我有三个选择

1. 像‘% %’一样使用
2. 使用指令
3. 使用子字符串

如果我对 dba 友善，我确实可以选择创建基于函数的索引。

目前所有查询都需要大约两秒钟。有没有人知道这些选项中哪一个最有效，或者是否还有其他选择？选择将每隔几秒用于删除一次，它通常会选择 10 行。

编辑更多信息

当我们使用一个表来存储具有任意键和值的对象时，问题就出现了。这些对象来 self 们系统之外，所以我们控制它们的范围有限，所以文本列类似于 'key1=abc,key2=def,keyn=ghi' 我知道这是非常反规范化的，但因为我们不知道键将(在某种程度上)是存储和检索值的可靠方式。检索行的速度相当快，因为​​我们正在搜索已编入索引的整个列。但是如果我们想检索带有 key2=def 的行，性能就不好。

我们也许能够创建一个包含最常用键列的表，但我想知道是否有一种方法可以通过现有设置提高性能。

Answer 1

在甲骨文 10 中:

CREATE TABLE test (tst_test VARCHAR2(200));

CREATE INDEX ix_re_1 ON test(REGEXP_REPLACE(REGEXP_SUBSTR(tst_test, 'KEY1=[^,]*'), 'KEY1=([^,]*)', '\1'))

SELECT  *
FROM    TEST
WHERE   REGEXP_REPLACE(REGEXP_SUBSTR(TST_TEST, 'KEY1=[^,]*'), 'KEY1=([^,]*)', '\1') = 'TEST'

这将使用新选择的索引。

您需要的索引数量与数据中的 KEY 数量相同。

INDEX 的存在当然会影响性能，但这与 REGEXP 的存在关系不大:

SQL> CREATE INDEX ix_test ON test (tst_test)
  2  /
Index created
Executed in 0,016 seconds

SQL> INSERT
  2  INTO   test (tst_test)
  3  SELECT 'KEY1=' || level || ';KEY2=' || (level + 10000)
  4  FROM   dual
  5  CONNECT BY
  6     LEVEL <= 1000000
  7  /
1000000 rows inserted
Executed in 47,781 seconds

SQL> TRUNCATE TABLE test
  2  /
Table truncated
Executed in 2,546 seconds

SQL> DROP INDEX ix_test
  2  /
Index dropped
Executed in 0 seconds

SQL> CREATE INDEX ix_re_1 ON test(REGEXP_REPLACE(REGEXP_SUBSTR(tst_test, 'KEY1=[^,]*'), 'KEY1=([^,]*)', '\1'))
  2  /
Index created
Executed in 0,015 seconds

 SQL> INSERT
      2  INTO   test (tst_test)
      3  SELECT 'KEY1=' || level || ';KEY2=' || (level + 10000)
      4  FROM   dual
      5  CONNECT BY
      6     LEVEL <= 1000000
      7  /
1000000 rows inserted
Executed in 53,375 seconds

如您所见，在我的机器(Core2 4300，1 Gb RAM)上，您可以每秒插入 20000 条记录到索引字段，这个比率几乎不依赖于正在使用的 INDEX 类型:普通的或基于函数的。