I have the following sql
SELECT distinct q.broker_ID, q.user_ID , q.MGC_BREACH_ALERT, REGEXP_SUBSTR (nvl(q.CGC_ALERT_THRESHOLDS,0),'[^|]+',1,LEVEL) value
FROM
(select u.broker_ID ,t.user_ID , t.CGC_ALERT_THRESHOLDS ,t.MGC_BREACH_ALERT from ATSD_USE_TRADERS t ,ATS_USERS u where u.user_ID =t.user_ID and t.sponsored_user = 1) q
CONNECT BY (REGEXP_SUBSTR (nvl(q.CGC_ALERT_THRESHOLDS,0),'[^|]+',1,LEVEL) IS NOT NULL)
order by 1
when all the values in the table has CGC_ALERT_THRESHOLDS as two pipe separated values (e.g 34|44) the query is running in less than 1 second.
When the CGC_ALERT_THRESHOLDS are three pipe separated values (e.g 34|44|54) then the query is taking more 30 seconds to complete.
The table has only about 150 rows .
Can anyone please explain where I have gone wrong / or any other suggested improvements to this query?