Home » RDBMS Server » Performance Tuning » Performace issue
Performace issue [message #665302] Tue, 29 August 2017 08:27 Go to next message
Genesys
Messages: 45
Registered: August 2010
Member
Hi All,

have loaded nearly 4 millions of date into external table called ABC with below structure

create table abc (pkey varchar2(30), aliases varchar2(3000));

sample data;

KEY aliases
001 ABDILLAHI;Hohamed Barkat;ABDILLAHI;Hohammad Barkat

like this have nearly 4 million of data.

now im trying to convert above data like this

PKEY alias seq
001 ABDILLAHI 1
001 Hohamed Barkat 2
001 ABDILLAHI 3
001 Hohammad Barkat 4

used the below and working fine for few records

select Alias, PKEY, row_number() over (partition by pkey order by pkey) seq from
(select distinct regexp_substr(trim (aliases),'[^;]+', 1,level) as Alias,PKEY
from (select pkey,aliases
from abc
)
connect by regexp_substr(aliases, '[^;]+', 1, level) is not null);

but while converting 4 million records this query taking long hours pls suggest on this.

Thank
Genesys
Performace issue [message #665303 is a reply to message #665302] Tue, 29 August 2017 08:30 Go to previous messageGo to next message
Genesys
Messages: 45
Registered: August 2010
Member
Hi All,

have loaded nearly 4 millions of date into external table called ABC with below structure

create table abc (pkey varchar2(30), aliases varchar2(3000));

sample data;

KEY aliases
001 ABDILLAHI;Hohamed Barkat;ABDILLAHI;Hohammad Barkat

like this have nearly 4 million of data.

now im trying to convert above data like this

PKEY alias seq
001 ABDILLAHI 1
001 Hohamed Barkat 2
001 ABDILLAHI 3
001 Hohammad Barkat 4

used the below and working fine for few records

select Alias, PKEY, row_number() over (partition by pkey order by pkey) seq from
(select distinct regexp_substr(trim (aliases),'[^;]+', 1,level) as Alias,PKEY
from (select pkey,aliases
from abc
)
connect by regexp_substr(aliases, '[^;]+', 1, level) is not null);

but while converting 4 million records this query taking long hours pls suggest on this.

Thank
Genesys
Data Conversion [message #665304 is a reply to message #665302] Tue, 29 August 2017 08:34 Go to previous messageGo to next message
Genesys
Messages: 45
Registered: August 2010
Member
Hi All,

have loaded nearly 4 millions of date into external table called ABC with below structure

create table abc (pkey varchar2(30), aliases varchar2(3000));

sample data;

KEY aliases
001 ABDILLAHI;Hohamed Barkat;ABDILLAHI;Hohammad Barkat

like this have nearly 4 million of data.

now im trying to convert above data like this

PKEY alias seq
001 ABDILLAHI 1
001 Hohamed Barkat 2
001 ABDILLAHI 3
001 Hohammad Barkat 4

used the below and working fine for few records

select Alias, PKEY, row_number() over (partition by pkey order by pkey) seq from
(select distinct regexp_substr(trim (aliases),'[^;]+', 1,level) as Alias,PKEY
from (select pkey,aliases
from abc
)
connect by regexp_substr(aliases, '[^;]+', 1, level) is not null);

but while converting 4 million records this query taking long hours pls suggest on this.

Thank
Genesys
Re: Data Conversion [message #665330 is a reply to message #665304] Wed, 30 August 2017 07:07 Go to previous messageGo to next message
Flyby
Messages: 188
Registered: March 2011
Location: Belgium
Senior Member
Maybe a simple program or PL/SQL can do it faster then regular expressions.
Your example saves key(pkey) value(alias) positioninrow(seq)
Re: Data Conversion [message #665464 is a reply to message #665330] Wed, 06 September 2017 09:33 Go to previous messageGo to next message
JPBoileau
Messages: 88
Registered: September 2017
Member
This should steer you in the right direction. Ran in less than a minute for 1M rows.

DECLARE
   str VARCHAR2(3000);
   part VARCHAR2(3000);
   pos NUMBER;
BEGIN
   FOR c1 IN (SELECT pkey, aliases FROM abc) LOOP
      str := c1.aliases;
      pos := INSTR(str, ';');
      WHILE (pos >= 1 OR str IS NOT NULL) LOOP
         part := SUBSTR(str, 1, pos-1);
         DBMS_OUTPUT.PUT_LINE(part); -- replace with INSERT here
         str := SUBSTR(str, pos+1);
         pos := INSTR(str, ';');
         IF pos = 0 THEN 
            pos := LENGTH(str);
         END IF;
      END LOOP;
      -- COMMIT; -- COMMIT if inserting.
   END LOOP;
END;
/


JP
Re: Data Conversion [message #665465 is a reply to message #665464] Wed, 06 September 2017 09:39 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
OP has NOT visited this forum since starting this thread.
Some folks are Write Once Return Never.
Re: Performace issue [message #665517 is a reply to message #665302] Fri, 08 September 2017 04:12 Go to previous message
Clanner
Messages: 1
Registered: September 2017
Junior Member
Genesys wrote on Tue, 29 August 2017 08:27
Hi All,

have loaded nearly 4 millions of date into external table called ABC with below structure

create table abc (pkey varchar2(30), aliases varchar2(3000));

sample data;

KEY aliases
001 ABDILLAHI;Hohamed Barkat;Mantra Management Client;ABDILLAHI;Hohammad Barkat

like this have nearly 4 million of data.

now im trying to convert above data like this

PKEY alias seq
001 ABDILLAHI 1
001 Hohamed Barkat 2
001 ABDILLAHI 3
001 Hohammad Barkat 4

used the below and working fine for few records

select Alias, PKEY, row_number() over (partition by pkey order by pkey) seq from
(select distinct regexp_substr(trim (aliases),'[^;]+', 1,level) as Alias,PKEY
from (select pkey,aliases
from abc
)
connect by regexp_substr(aliases, '[^;]+', 1, level) is not null);

but while converting 4 million records this query taking long hours pls suggest on this.

Thank
Genesys
Can you explain more clearly? I don't really understand
Previous Topic: sql need tuning - high cpu_time and buffer gets
Next Topic: How to partition unpartitioned existing table
Goto Forum:
  


Current Time: Thu Mar 28 19:01:12 CDT 2024