代码之家 › 专栏 › 技术社区 › Jeremy French

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

oracle database sql

Jeremy French · 技术社区 · 16 年前

我有一张大约有200万行的桌子。我需要查询其中一列来检索字符串作为值一部分存在的行。

当我运行查询时,我将知道字符串的位置,但不是在手之前。所以接受子字符串的视图不是一个选项。

据我所知,我有三个选择

使用like_
使用仪器
使用子串

如果我对DBA很好的话,我可以选择创建一个基于函数的索引。

目前,所有查询大约需要两秒钟。有没有人有过这些选择中哪一个最有效的经验,或者如果有其他选择的话?选择将每隔几秒钟用于删除一次,通常会选择10行。

用更多信息编辑

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

我们可以为最常见的键创建一个带有列的表,但是我想知道是否有一种方法可以提高现有设置的性能。

8 回复 | 直到 16 年前

Quassnoi 16 年前

在Oracle 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 在你的数据中。

AN的存在 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 每秒到索引字段的记录数,此速率几乎不取决于 索引 正在使用:普通的或基于函数的。

Tony Andrews 16 年前

你可以使用 Tom Kyte's runstats package 比较不同实现的性能——每个实现在一个循环中运行1000次。例如,我只是将like与substr进行比较,它说like更快,大约占用了substr时间的80%。

请注意,“col like'%xxx%'”不同于“substr(col,5,3)='xxx'”。类似的情况是:

col LIKE '____xxx%'

对每个要忽略的前导字符使用一个“u”。

我认为无论你用什么方法做,结果都是相似的——它总是涉及到一个完整的表(或者可能是完整的索引)扫描。只有在创建索引时知道子字符串的偏移量,基于函数的索引才会有帮助。

当你说“每隔几秒钟删除一次”时,我相当担心。这确实表明某个地方存在设计缺陷,但不知道需求,很难说。

更新:

如果您的列值类似于“key1=abc,key2=def,keyn=ghi”,那么您可以考虑添加另一个这样的表:

 create table key_values
    ( main_table_id references main_table
    , key_value varchar2(50)
    , primary key (fk_col, key_value)
    );

 create index key_values_idx on key_values (key_value);

向上拆分键值并将其存储在此表中,如下所示:

main_table_id key_value
123           key1=abc
123           key2=def
123           key3=ghi

(例如,这可以在主表的after insert触发器中完成)

那么您的删除可能是:

delete main_table
where id in (select main_table_id from key_values
             where key_value = 'key2=def');

joel.neely 16 年前

你能提供更多的信息吗?

您是在查询字符串列的任意子字符串,还是在列中的字符串存储区中有一些语法允许进行一些预处理以最小化重复工作?

您是否已经对三个选项进行了时间测试,以确定它们在您查询的数据上的相对性能?

John 16 年前

我建议重新考虑你的逻辑。

检查字符串的长度是否为>0而不是字符串,而不是查找字符串所在的位置,可能会更快。

您可以使用Oracle中的translate函数将所有非字符串字符转换为空值,然后检查结果是否为空值。

Dave Costa 16 年前

单独回答对表格设计的意见。

您至少不能有一个键/值结构,因此,与其将其存储在单列中,“key1=abc,key2=def,keyn=ghi”,不如使用类似的子表

KEY     VALUE
key1    abc
key2    def
key3    ghi

然后,您可以对键和值创建一个索引,您的查询就简单多了(因为我认为您实际上是在寻找给定键值的精确匹配)。

有些人可能会评论这是一个可怕的设计,但我认为它比你现在拥有的要好。

Dave Costa 16 年前

如果您总是要查找相同的子字符串,那么使用instr和基于函数的索引对我来说是有意义的。你也可以这样做,如果你有一个小的常数子串集,你将要寻找,创建一个联邦调查局为每一个。

Quassnoi的regexp想法看起来也很有希望。我还没有在Oracle中使用正则表达式。

我认为甲骨文将是另一种方式。关于这方面的信息 here

Anton Gogolev 16 年前

不确定是否要改进现有的安装工具,但Lucene(全文搜索库;移植到许多平台)确实能起到帮助。将索引与数据库同步有额外的负担,但是如果在某些编程语言中有任何类似于服务层的东西,这将成为一项简单的任务。

Gary Myers 16 年前

与安东·戈戈戈列夫的回应类似,甲骨文也加入了一个文本搜索引擎 here

还有可扩展的索引,因此您可以构建自己的索引结构,文档化 here

正如您所同意的,这是一个非常糟糕的数据结构,我认为您将很难实现每隔几秒钟删除内容的目标。根据这些数据的输入方式,我将研究如何在加载时正确地构造数据,至少在具有“parent_i d”、“key_name”、“key_value”行的范围内如此。