- Fix your query so you don't need to use
DISTINCT. The problem with your method is that you are using a hierarchical query with multiple rows of input and no way of correlating each level of the hierarchy to the previous level so the query will correlate it to ALL the items at the previous level of the hierarchy and you will get exponentially more and more duplicate rows generated at each depth. This is incredibly inefficient.
- Change from using regular expressions to simple string functions.
Instead you can use:
WITH bounds ( class_id, class_time, start_pos, end_pos ) AS (
SELECT class_id,
class_time,
1,
INSTR( class_time, ':', 1 )
FROM data
UNION ALL
SELECT class_id,
class_time,
end_pos + 1,
INSTR( class_time, ':', end_pos + 1 )
FROM bounds
WHERE end_pos > 0
)
SELECT class_id,
CASE end_pos
WHEN 0
THEN SUBSTR( class_time, start_pos )
ELSE SUBSTR( class_time, start_pos, end_pos - start_pos )
END AS class_time
FROM bounds;
Which, for the sample data:
CREATE TABLE data ( class_id, class_time ) AS
SELECT 1, '0800AM:0830AM' FROM DUAL UNION ALL
SELECT 1, '0900AM' FROM DUAL UNION ALL
SELECT 2, '0830AM:0900AM:0930AM' FROM DUAL UNION ALL
SELECT 2, '1000AM' FROM DUAL;
Outputs:
CLASS_ID | CLASS_TIME
-------: | :---------
1 | 0800AM
1 | 0900AM
2 | 0830AM
2 | 1000AM
1 | 0830AM
2 | 0900AM
2 | 0930AM
db<>fiddle here
However, an even better method would be to change your model for storing the data and stop storing it as a delimited string and instead store it in a separate table or, maybe, as a collection in a nested table.
An example using a second table is:
CREATE TABLE data (
class_id NUMBER PRIMARY KEY
);
CREATE TABLE class_times (
class_id NUMBER REFERENCES data ( class_id ),
class_time VARCHAR2(6)
);
INSERT ALL
INTO data ( class_id ) VALUES ( 1 )
INTO data ( class_id ) VALUES ( 2 )
INTO class_times ( class_id, class_time ) VALUES ( 1, '0800AM' )
INTO class_times ( class_id, class_time ) VALUES ( 1, '0830AM' )
INTO class_times ( class_id, class_time ) VALUES ( 1, '0900AM' )
INTO class_times ( class_id, class_time ) VALUES ( 2, '0830AM' )
INTO class_times ( class_id, class_time ) VALUES ( 2, '0900AM' )
INTO class_times ( class_id, class_time ) VALUES ( 2, '0930AM' )
INTO class_times ( class_id, class_time ) VALUES ( 2, '1000AM' )
SELECT * FROM DUAL;
Then your query would be (assuming that you need other columns from data alongside the class_id):
SELECT d.class_id,
c.class_time
FROM data d
INNER JOIN class_times c
ON ( d.class_id = c.class_id );
Which outputs:
CLASS_ID | CLASS_TIME
-------: | :---------
1 | 0800AM
1 | 0830AM
1 | 0900AM
2 | 0830AM
2 | 0900AM
2 | 0930AM
2 | 1000AM
An example using a nested table is:
CREATE TYPE stringlist IS TABLE OF VARCHAR2(6);
CREATE TABLE data (
class_id NUMBER,
class_time stringlist
) NESTED TABLE class_time STORE AS data__class_time;
INSERT INTO data ( class_id, class_time )
SELECT 1, stringlist( '0800AM','0830AM' ) FROM DUAL UNION ALL
SELECT 1, stringlist( '0900AM' ) FROM DUAL UNION ALL
SELECT 2, stringlist( '0830AM','0900AM','0930AM' ) FROM DUAL UNION ALL
SELECT 2, stringlist( '1000AM' ) FROM DUAL;
Then your query would become:
SELECT d.class_id,
ct.COLUMN_VALUE AS class_time
FROM data d
CROSS APPLY TABLE ( d.class_time ) ct
Which outputs:
CLASS_ID | CLASS_TIME
-------: | :---------
1 | 0800AM
1 | 0830AM
1 | 0900AM
2 | 0830AM
2 | 0900AM
2 | 0930AM
2 | 1000AM
db<>fiddle here
