Home » RDBMS Server » Performance Tuning » Performace issue
Performace issue [message #665302] |
Tue, 29 August 2017 08:27  |
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   |
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   |
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: Performace issue [message #665517 is a reply to message #665302] |
Fri, 08 September 2017 04:12  |
 |
Clanner
Messages: 1 Registered: September 2017
|
Junior Member |
|
|
Genesys wrote on Tue, 29 August 2017 08:27Hi 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
|
|
|
Goto Forum:
Current Time: Thu Feb 20 21:05:29 CST 2025
|