Home » RDBMS Server » Performance Tuning » Split Data separated by comma ; then create a collection with data mapped from other columns
Split Data separated by comma ; then create a collection with data mapped from other columns [message #596582] Wed, 25 September 2013 01:20 Go to next message
abhenav123
Messages: 6
Registered: July 2013
Junior Member
DB Used : Oracle 10g.

A table X : NUM, INST are column names

NUM ----- INST

1234 ----- 23,22,21,78
2235 ----- 20,7,2,1
1298 ----- 23,22,21,65,98
9087 ----- 20,7,2,1

-- PROBLEM 1 :

-- Based upon requirement :

1) Split values from "INST" Column : suppose 23
2) Find all values from "NUM" column for above splitted value i.e 23 ,
Eg:

For Inst : 23 ,
It's corresponding "NUM" values are : 1234,1298

3) Save these values into

A table Y : INST, NUM are column names.

INST NUM
23 1234,1298


-- PROBLEM 2 :

1) I have a thousand records in Table X ,
and for all of those records i need to split and save data into Table Y.

Hence, I need to do this task with best possible performance.

2) After this whenever a new data comes in Table X,
above 'split & save' operation should automatically be called and append corresponding data wherever possible ...

My sincere thanks in advance,
Every suggestion accepted, Please provide a solution to this.
icon13.gif  Re: Split Data separated by comma ; then create a collection with data mapped from other columns [message #596583 is a reply to message #596582] Wed, 25 September 2013 01:29 Go to previous messageGo to next message
Michel Cadot
Messages: 58856
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Any way you will do it it will be slow.
This is one the reason of Normalization.
The best way should be to have data in 3NF and only one table.

Re: Split Data separated by comma ; then create a collection with data mapped from other columns [message #596586 is a reply to message #596583] Wed, 25 September 2013 01:40 Go to previous messageGo to next message
abhenav123
Messages: 6
Registered: July 2013
Junior Member
The Actual Scenario is that the data in "table X" comes from a warehouse file.
[So, it cannot be changed].
But I have to create a Table on my own : table Y where the corresponding output will be stored as shown in the question.

-- And thanks for your reply.

[Updated on: Wed, 25 September 2013 01:42]

Report message to a moderator

Re: Split Data separated by comma ; then create a collection with data mapped from other columns [message #596596 is a reply to message #596586] Wed, 25 September 2013 02:26 Go to previous messageGo to next message
pablolee
Messages: 2615
Registered: May 2007
Location: Scotland
Senior Member
Not entirely true. You are propagating the issue, just moving it to a different table/column. Based on your requirements, you are looking to split out the comma separated list in 'INST', That's great, exactly what you should be doing, but you then want to create a comma separated list of values for each instance of 'INST'. This is a terrible idea and, as Michel already said, goes against normalization. Whilst the structure of the data coming into you is not in your control, what you do with that data afterwards IS within your control.
Anyway, a google search will turn up many techniques for doing the first part:
Here's the search I performed.
Re: Split Data separated by comma ; then create a collection with data mapped from other columns [message #596651 is a reply to message #596596] Wed, 25 September 2013 07:45 Go to previous message
abhenav123
Messages: 6
Registered: July 2013
Junior Member
Thanks for reply ...

I am using this approach to solve this issue :

1) Made a splitter function (using instr, substr functions) : This function will split all the values from "INST" column and store into a collection : "collection_1" which is being returned to calling procedure.

2) Then a procedure is made which loops like :
For Eg :

p_rowid : Collection containing rowid's of all the rows from the table X
collection_1 : Collection containing splitted values from one row (INST) at a time.
collection_2 : Collection containing splitted values from next row (INST) at a time.
save_inst : Collection containing each "inst" value;
save_num : Collection containing respective "rowid" value for each "inst" in save_inst.
--------------------------------------------------------------------------------------

Select rowid into p_rowid from X;

collection_1 = split_function(...); -- returns splitted value for a 1st row

for i in 1..collection_1.count -- iterates through values of collection_1
loop
collection_2 = split_function(...); -- returns splitted value for next row.

for j in (i+1)..collection_2.count --iterates through values of collection_2

loop

if ( collection_1(i) = collection_2(j)) then

save_inst(i) := collection_1(i);
save_num (i) := p_rowid(i);
exit;

end if;

end loop;

end loop;

-------------------------------------------------------------------------------

Pardon any mistakes in above code ... (just an overview)

Actually i am able to get Unique "INST" values in one collection : [save_inst]
and Their ONE-TO-ONE "NUM" value in another collection : [save_num].

-- But I have to append each num value using ',' by getting it through rowid value.

-- Secondly, after doing all this operation once, I want a trigger to append any new num value that falls into same inst value using ','

[ For doing so, Should I use UPDATE to put new value of num in table Y or is there any other way out ? ]
Previous Topic: Select query taking time even after using PARALLEL hint
Next Topic: pagging
Goto Forum:
  


Current Time: Thu Aug 21 15:21:36 CDT 2014

Total time taken to generate the page: 0.08289 seconds