Oracle 11g Release 2

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

CREATE TABLE HSERS_INCIDENT_PSA ( incidentid, casualfactors, active_flag, isdeleted ) AS
  SELECT 1, 'a,b,c,d,e,f', '1', 'F' FROM DUAL UNION ALL
  SELECT 2, 'g,h,i,j,k',   '1', 'F' FROM DUAL UNION ALL
  SELECT 3, 'l',           '1', 'F' FROM DUAL;
3 rows affected

select  INCIDENTID,
        trim(regexp_substr(CASUALFACTORS,'[^,]+', 1, level) ) value  
FROM    HSERS_INCIDENT_PSA
WHERE   ACTIVE_FLAG='1'
AND     ISDELETED = 'F'
connect by regexp_substr(CASUALFACTORS, '[^,]+', 1, level) is not null
order by INCIDENTID


INCIDENTID
VALUE


1
a

1
b

1
c

1
d

1
e

1
f

1
f

1
e

1
f

1
f

1
d

1
e

1
f

1
f

1
e

1
f

1
f

1
c

1
d

1
e

1
f

1
f

1
e

1
f

1
f

1
d

1
e

1
f

1
f

1
e

1
f

1
f

1
b

1
c

1
d

1
e

1
f

1
f

1
e

1
f

1
f

1
d

1
e

1
f

1
f

1
e

1
f

1
f

1
c

1
d

1
e

1
f

1
f

1
e

1
f

1
f

1
d

1
e

1
f

1
f

1
e

1
f

1
f

1
b

1
c

1
d

1
e

1
f

1
f

1
e

1
f

1
f

1
d

1
e

1
f

1
f

1
e

1
f

1
f

1
c

1
d

1
e

1
f

1
f

1
e

1
f

1
f

1
d

1
e

1
f

1
f

1
e

1
f

1
f

2
k

2
k

2
k

2
i

2
k

2
j

2
k

2
h

2
k

2
j

2
k

2
i

2
k

2
j

2
k

2
g

2
k

2
j

2
k

2
i

2
k

2
j

2
k

2
h

2
k

2
j

2
k

2
i

2
k

2
j

2
k

2
j

2
k

2
j

2
k

2
i

2
k

2
j

2
k

2
h

2
k

2
j

2
k

2
i

2
k

2
j

3
l


EXPLAIN PLAN FOR
select  INCIDENTID,
        trim(regexp_substr(CASUALFACTORS,'[^,]+', 1, level) ) value  
FROM    HSERS_INCIDENT_PSA
WHERE   ACTIVE_FLAG='1'
AND     ISDELETED = 'F'
connect by regexp_substr(CASUALFACTORS, '[^,]+', 1, level) is not null
order by INCIDENTID
SELECT PLAN_TABLE_OUTPUT FROM TABLE(DBMS_XPLAN.DISPLAY());


PLAN_TABLE_OUTPUT


Plan hash value: 1864632740

 

-----------------------------------------------------------------------------------------------------

| Id  | Operation                      | Name               | Rows  | Bytes | Cost (%CPU)| Time     |

-----------------------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT               |                    |     3 |    78 |     4  (25)| 00:00:01 |

|   1 |  SORT ORDER BY                 |                    |     3 |    78 |     4  (25)| 00:00:01 |

|*  2 |   FILTER                       |                    |       |       |            |          |

|*  3 |    CONNECT BY WITHOUT FILTERING|                    |       |       |            |          |

|   4 |     TABLE ACCESS FULL          | HSERS_INCIDENT_PSA |     3 |    78 |     3   (0)| 00:00:01 |

-----------------------------------------------------------------------------------------------------

 

Predicate Information (identified by operation id):

---------------------------------------------------

 

   2 - filter("ACTIVE_FLAG"='1' AND "ISDELETED"='F')

   3 - filter( REGEXP_SUBSTR ("CASUALFACTORS",'[^,]+',1,LEVEL) IS NOT NULL)

 

Note

-----

   - dynamic sampling used for this statement (level=2)


WITH casualfactors_bounds ( incidentid, casualfactors, startidx, endidx ) AS (
  SELECT incidentid,
         casualfactors,
         1,
         INSTR( casualfactors, ',', 1 )
  FROM   HSERS_INCIDENT_PSA
  WHERE  ACTIVE_FLAG = '1'
  AND    ISDELETED   = 'F'
UNION ALL
  SELECT incidentid,
         casualfactors,
         endidx + 1,
         INSTR( casualfactors, ',', endidx + 1 )
  FROM   casualfactors_bounds
  WHERE  endidx > 0
)
SELECT incidentid,
       CASE
       WHEN endidx = 0
       THEN SUBSTR( casualfactors, startidx )
       ELSE SUBSTR( casualfactors, startidx, endidx - startidx )
       END AS casualfactor
FROM   casualfactors_bounds
ORDER BY incidentid, startidx


INCIDENTID
CASUALFACTOR


1
a

1
b

1
c

1
d

1
e

1
f

2
g

2
h

2
i

2
j

2
k

3
l


EXPLAIN PLAN FOR
WITH casualfactors_bounds ( incidentid, casualfactors, startidx, endidx ) AS (
  SELECT incidentid,
         casualfactors,
         1,
         INSTR( casualfactors, ',', 1 )
  FROM   HSERS_INCIDENT_PSA
  WHERE  ACTIVE_FLAG = '1'
  AND    ISDELETED   = 'F'
UNION ALL
  SELECT incidentid,
         casualfactors,
         endidx + 1,
         INSTR( casualfactors, ',', endidx + 1 )
  FROM   casualfactors_bounds
  WHERE  endidx > 0
)
SELECT incidentid,
       CASE
       WHEN endidx = 0
       THEN SUBSTR( casualfactors, startidx )
       ELSE SUBSTR( casualfactors, startidx, endidx - startidx )
       END AS casualfactor
FROM   casualfactors_bounds
ORDER BY incidentid, startidx
SELECT PLAN_TABLE_OUTPUT FROM TABLE(DBMS_XPLAN.DISPLAY());


PLAN_TABLE_OUTPUT


Plan hash value: 2740663158

 

-----------------------------------------------------------------------------------------------------------------

| Id  | Operation                                  | Name               | Rows  | Bytes | Cost (%CPU)| Time     |

-----------------------------------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT                           |                    |     6 |   276 |     7  (15)| 00:00:01 |

|   1 |  SORT ORDER BY                             |                    |     6 |   276 |     7  (15)| 00:00:01 |

|   2 |   VIEW                                     |                    |     6 |   276 |     6   (0)| 00:00:01 |

|   3 |    UNION ALL (RECURSIVE WITH) BREADTH FIRST|                    |       |       |            |          |

|*  4 |     TABLE ACCESS FULL                      | HSERS_INCIDENT_PSA |     3 |    78 |     3   (0)| 00:00:01 |

|*  5 |     RECURSIVE WITH PUMP                    |                    |       |       |            |          |

-----------------------------------------------------------------------------------------------------------------

 

Predicate Information (identified by operation id):

---------------------------------------------------

 

   4 - filter("ACTIVE_FLAG"='1' AND "ISDELETED"='F')

   5 - filter("ENDIDX">0)

 

Note

-----

   - dynamic sampling used for this statement (level=2)

INCIDENTID	VALUE
1	a
1	b
1	c
1	d
1	e
1	f
1	f
1	e
1	f
1	f
1	d
1	e
1	f
1	f
1	e
1	f
1	f
1	c
1	d
1	e
1	f
1	f
1	e
1	f
1	f
1	d
1	e
1	f
1	f
1	e
1	f
1	f
1	b
1	c
1	d
1	e
1	f
1	f
1	e
1	f
1	f
1	d
1	e
1	f
1	f
1	e
1	f
1	f
1	c
1	d
1	e
1	f
1	f
1	e
1	f
1	f
1	d
1	e
1	f
1	f
1	e
1	f
1	f
1	b
1	c
1	d
1	e
1	f
1	f
1	e
1	f
1	f
1	d
1	e
1	f
1	f
1	e
1	f
1	f
1	c
1	d
1	e
1	f
1	f
1	e
1	f
1	f
1	d
1	e
1	f
1	f
1	e
1	f
1	f
2	k
2	k
2	k
2	i
2	k
2	j
2	k
2	h
2	k
2	j
2	k
2	i
2	k
2	j
2	k
2	g
2	k
2	j
2	k
2	i
2	k
2	j
2	k
2	h
2	k
2	j
2	k
2	i
2	k
2	j
2	k
2	j
2	k
2	j
2	k
2	i
2	k
2	j
2	k
2	h
2	k
2	j
2	k
2	i
2	k
2	j
3	l

INCIDENTID	VALUE
1	a
1	b
1	c
1	d
1	e
1	f
1	f
1	e
1	f
1	f
1	d
1	e
1	f
1	f
1	e
1	f
1	f
1	c
1	d
1	e
1	f
1	f
1	e
1	f
1	f
1	d
1	e
1	f
1	f
1	e
1	f
1	f
1	b
1	c
1	d
1	e
1	f
1	f
1	e
1	f
1	f
1	d
1	e
1	f
1	f
1	e
1	f
1	f
1	c
1	d
1	e
1	f
1	f
1	e
1	f
1	f
1	d
1	e
1	f
1	f
1	e
1	f
1	f
1	b
1	c
1	d
1	e
1	f
1	f
1	e
1	f
1	f
1	d
1	e
1	f
1	f
1	e
1	f
1	f
1	c
1	d
1	e
1	f
1	f
1	e
1	f
1	f
1	d
1	e
1	f
1	f
1	e
1	f
1	f
2	k
2	k
2	k
2	i
2	k
2	j
2	k
2	h
2	k
2	j
2	k
2	i
2	k
2	j
2	k
2	g
2	k
2	j
2	k
2	i
2	k
2	j
2	k
2	h
2	k
2	j
2	k
2	i
2	k
2	j
2	k
2	j
2	k
2	j
2	k
2	i
2	k
2	j
2	k
2	h
2	k
2	j
2	k
2	i
2	k
2	j
3	l

INCIDENTID	VALUE
1	a
1	b
1	c
1	d
1	e
1	f
1	f
1	e
1	f
1	f
1	d
1	e
1	f
1	f
1	e
1	f
1	f
1	c
1	d
1	e
1	f
1	f
1	e
1	f
1	f
1	d
1	e
1	f
1	f
1	e
1	f
1	f
1	b
1	c
1	d
1	e
1	f
1	f
1	e
1	f
1	f
1	d
1	e
1	f
1	f
1	e
1	f
1	f
1	c
1	d
1	e
1	f
1	f
1	e
1	f
1	f
1	d
1	e
1	f
1	f
1	e
1	f
1	f
1	b
1	c
1	d
1	e
1	f
1	f
1	e
1	f
1	f
1	d
1	e
1	f
1	f
1	e
1	f
1	f
1	c
1	d
1	e
1	f
1	f
1	e
1	f
1	f
1	d
1	e
1	f
1	f
1	e
1	f
1	f
2	k
2	k
2	k
2	i
2	k
2	j
2	k
2	h
2	k
2	j
2	k
2	i
2	k
2	j
2	k
2	g
2	k
2	j
2	k
2	i
2	k
2	j
2	k
2	h
2	k
2	j
2	k
2	i
2	k
2	j
2	k
2	j
2	k
2	j
2	k
2	i
2	k
2	j
2	k
2	h
2	k
2	j
2	k
2	i
2	k
2	j
3	l