SQL & PL/SQL

hi @all

I'm using Oracle 11g R2 on Windows 7

Unfortunately i have a column in a table that stored multiple vales delimited with *,* e.g. [+*execute 1B-B-06-34-61-00A-281A-A, 1B-B-06-37-10-00A-281A-A, 1B-B-06-37-20-00A-281A-A, 1B-B-06-37-30-00A-281A-A*+]

I have to query that column to get seperate values after execute

Example:

Serial_No Remarks
---------|----------------------------------------------------------------------------------------------------------------------------------------------------------------------------
AS0007 execute 1B-B-06-34-61-00A-281A-A, 1B-B-06-37-10-00A-281A-A, 1B-B-06-37-20-00A-281A-A, 1B-B-06-37-30-00A-281A-A
---------|----------------------------------------------------------------------------------------------------------------------------------------------------------------------------
AS0007 execute 1B-B-28-52-00-00A-340C-A
---------|----------------------------------------------------------------------------------------------------------------------------------------------------------------------------
AS0007 execute 1B-B-57-11-03-06A-281A-A, 1B-B-57-11-03-07A-281A-A, 1B-B-57-11-03-07B-281A-A,
---------|----------------------------------------------------------------------------------------------------------------------------------------------------------------------------

expected Output:

Serial_No Remarks
---------|----------------------------------------------------------------
AS0007 1B-B-06-34-61-00A-281A-A
---------|----------------------------------------------------------------
AS0007 1B-B-06-37-10-00A-281A-A
---------|----------------------------------------------------------------
AS0007 1B-B-06-37-20-00A-281A-A
---------|----------------------------------------------------------------
AS0007 1B-B-06-37-30-00A-281A-A
---------|----------------------------------------------------------------
AS0007 1B-B-28-52-00-00A-340C-A
---------|----------------------------------------------------------------
AS0007 1B-B-57-11-03-06A-281A-A
---------|----------------------------------------------------------------
AS0007 1B-B-57-11-03-07A-281A-A
---------|----------------------------------------------------------------
AS0007 1B-B-57-11-03-07B-281A-A


I have the following Script to query this:

select distinct SCHED_MAINT.AC_SERIAL_NO, regexp_substr (SUBSTR(SCHED_MAINT.Remarks, 9), '[^,]+', 1, level) as DMC
from SCHED_MAINT
where SCHED_MAINT.NEXT_DUE_MINS = '24000'
and SCHED_MAINT.AC_SERIAL_NO = 'AS0007'
connect by prior SCHED_MAINT.AC_SERIAL_NO = SCHED_MAINT.AC_SERIAL_NO
and regexp_instr (SCHED_MAINT.Remarks, '[^,]+', 1, level) > 0
and prior dbms_random.string ('p', 10) is not null;


The Problem is that, with this few rows it works, but slow, if i have more rows e.g 1000, I killed the runtime after 2h without any output

If I run this script without the DISTINCT it runs fast in one second but i get duplicated rows

any suggestions to solve this problem

unfortunately i cannot use funktions or procedures


thanks in advanced
cu ice