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
|
|
|