集合类型列中的快速搜索

Question

创建集合类型：

CREATE TYPE nums_list AS TABLE OF NUMBER;

创建具有集合类型列(嵌套表列)的表：

CREATE TABLE test1 (
        num NUMBER,
        tagged nums_list
)
NESTED TABLE tagged STORE AS mytest_tagged_table;

在表中插入100万行：

DECLARE 
    tagg_value nums_list := nums_list(3,4,5);
BEGIN
    for i in 1..1000000 loop
          if i = 600000 then
              tagg_value := nums_list(7,8);
          end if;

          INSERT INTO test1
          (num, tagged)
          VALUES
          (i, tagg_value);
    end loop;
END;

然后以集合类型运行对搜索元素的查询：

    select count(*) from test1 where 8 member of tagged;

这个查询运行缓慢，大约7-8秒是执行时间。

问:如何加快执行时间？可能是索引？但是，如何为嵌套的表列使用索引，我没有得到。

我尝试使用循环检查PL/SQL块中的每一行，使用游标，然后将结果作为流水线表函数返回，但这比直接查询慢得多。

Answer 1

如果您从示例中对此查询执行EXPLAIN PLAN：

select count(*) from test1 where 8 member of tagged;

..。您将看到，Oracle可能在mytest_tagged_table上使用一个(系统生成的)索引来提高性能。它仍然花费这么长时间的原因是，400,000个索引查找实际上比仅仅读取整个表的效率要低。

因此，问题不在于“如何让Oracle在嵌套表中使用索引”？我怎么才能让甲骨文不这么做？

有一种选择，因为您的tagged列表看起来很小，那就是使用VARRAY。这些可以内联存储，以获得更好的性能，尽管相关的语法不那么清晰。

下面是您的示例，修改为VARRAY

CREATE NONEDITIONABLE TYPE nums_varray AS VARRAY(10) OF NUMBER;

CREATE TABLE test2 (
        num NUMBER,
        tagged nums_varray
);

INSERT INTO test2
SELECT rownum,
       case when rownum < 600000 then new nums_varray(3,4,5) else new nums_varray(7,8) end
FROM   dual
connect by rownum <= 1000000;


select count(*) from test2 
where exists ( 
   SELECT '8 in list' 
   FROM TABLE(tagged) 
   WHERE column_value = 8);

在我的系统上，这只需要3600个缓冲区才能运行--而不是示例查询所需的210万个缓冲区。相应地，它也运行得更快。

VARRAYS并不是与嵌套表直接等效的，它们附带了警告。但是，根据你的例子，他们可能是你想要的。