How to improve the performance of a connect-by query in Oracle

Question

I have the following connect by query which takes approximately 50 to 60 seconds for a record count of 500 in my_table_name.

Can any one of you please suggest a better way of writing this query to improve the performance.

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;

In the above query: STD_DEP is a comma separated field which will have all the dependant std_id's.

I can't discern what it is you are trying to query. Try including your table DDL, sample data, and sample output. See here -> http://stackoverflow.com/help/how-to-ask — unleashed, Apr 28 '17 at 12:59

MT0 · Answer 1 · 2017-09-14T14:11:21.123

There are many, many ways to split a delimited string in Oracle.

One that does not use regular expressions is:

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;

How to improve the performance of a connect-by query in Oracle

1 Answers1