如何提高Oracle中connect-by查询的性能

Question

我有以下connect by查询，对于my_table_name中500的记录计数，它大约需要50到60秒。

你们中的任何一个人可以建议一种更好的方式来编写这个查询，以提高性能。

SELECT DISTINCT REGEXP_SUBSTR(STD_DEP,'[^,]+', 1, LEVEL) AS DEP_STD_ID
FROM my_table_name
WHERE std_id = 242
  CONNECT BY REGEXP_SUBSTR(STD_DEP,'[^,]+', 1, LEVEL) IS NOT NULL;

在上面的查询中: STD_DEP是一个逗号分隔的字段，它将包含所有相关的std_id。

Answer 1

在Oracle中有用于分割分隔字符串的many, many ways。

不使用正则表达式的是：

WITH bounds ( id, list, start_pos, end_pos, lvl ) AS (
  SELECT id, list, 1, INSTR( list, ',' ), 1
  FROM   my_table_name
  WHERE  std_id = 242
UNION ALL
  SELECT id,
         list,
         end_pos + 1,
         INSTR( list, ',', end_pos + 1 ),
         lvl + 1
  FROM   bounds
  WHERE  end_pos > 0
)
SELECT id,
       SUBSTR(
         list,
         start_pos,
         DECODE( end_pos, 0, LENGTH( list ) + 1, end_pos ) - start_pos
       ) AS item,
      lvl
FROM   bounds
ORDER BY id, lvl;