You Asked

For the below script Select statement gives result in 2 sec however when I use this in Insert script it goes on for more than 2 hours and was not finished.
How I can solve this ?

INSERT INTO ODEA_STG_HIST.EKRAN_DENEME_TRY (
SELECT APPLICATIONCODE,
AUTHORITYGROUPCODE,
ROLECODE,
ROLEID,
ROLENAME,
REGEXP_SUBSTR (VALUE,
'[^,]+',
1,
LEVEL)
VALUE
FROM ODEA_STG_HIST.EKRAN_DENEME
CONNECT BY LEVEL <= LENGTH (REGEXP_REPLACE (VALUE, '[^,]+')) + 1);

and Connor said...

I suspect you've taken a 1-row solution and tried to scale to multiple rows. The SQL

select regexp_substr (str, '[^,]+', 1, level) subs
from dual
connect by level <= length (regexp_replace (str, '[^,]+')) + 1

works for a single string (in a single row), but if you want to expand to an entire table, then

select regexp_substr (str, '[^,]+', 1, level) subs
from MY_TABLE
connect by level <= length (regexp_replace (str, '[^,]+')) + 1

does not work because now your connect-by will keep looping back through all of the rows almost endlessly. Hence your two hours

The fix is to alter the code to link for each primary key, eg

select id, regexp_substr (str, '[^,]+', 1, level) subs
from t
connect by level <= length (regexp_replace (str, '[^,]+')) + 1
and pk_col = prior pk_col
and prior sys_guid() is not null;

but you'll find this not scale particular well.

Take a read here for various solutions including benchmarks for larger volumes

https://stewashton.wordpress.com/2016/08/02/splitting-strings-proof/

Rating

(1 rating)

Is this answer out of date? If it is, please let us know via a Comment

Comments

A reader, August 19, 2020 - 7:01 am UTC

Hi,

I'm using exactly same select statement.

This select is exactly same with the one in insert statement. And also I checked the results and saw that it works for all table correctly. it is done 2 sec.

SELECT APPLICATIONCODE,
AUTHORITYGROUPCODE,
ROLECODE,
ROLEID,
ROLENAME,
REGEXP_SUBSTR (VALUE,
'[^,]+',
1,
LEVEL)
VALUE
FROM ODEA_STG_HIST.EKRAN_DENEME
CONNECT BY LEVEL <= LENGTH (REGEXP_REPLACE (VALUE, '[^,]+')) + 1

When I placed it into insert statement ı could not get any results for 2 hours.

INSERT INTO ODEA_STG_HIST.EKRAN_DENEME_TRY (
SELECT APPLICATIONCODE,
AUTHORITYGROUPCODE,
ROLECODE,
ROLEID,
ROLENAME,
REGEXP_SUBSTR (VALUE,
'[^,]+',
1,
LEVEL)
VALUE
FROM ODEA_STG_HIST.EKRAN_DENEME
CONNECT BY LEVEL <= LENGTH (REGEXP_REPLACE (VALUE, '[^,]+')) + 1);

I just wanna make it clear that I'm using same select script for both cases and I expect that if select is fast insert should be fast also for the same select script.

August 19, 2020 - 2:12 pm UTC

Please get the execution plans for the statements so we can see what's going on:

set serveroutput off
alter session set statistics_level = all;

select * from ...

select * 
from   table(dbms_xplan.display_cursor(null, null, 'ALLSTATS LAST'));

insert into ...

select * 
from   table(dbms_xplan.display_cursor(null, null, 'ALLSTATS LAST'));

Also, is it possible there are

- uncommitted transactions in other sessions adding the same primary/unique key values?
- triggers on the table you're inserting to which could slow this down?

Breadcrumb

Question and Answer

You Asked

and Connor said...

Rating

Comments

More to Explore

PL/SQL demos

PL/SQL docs

Insert script Performance when using connect-by

Breadcrumb

Question and Answer

You Asked

and Connor said...

Rating

Comments

More to Explore

PL/SQL demos

PL/SQL docs