Forum » Programiranje » [PL SQL] Mešanje podatkov
[PL SQL] Mešanje podatkov
JanezH ::
Imam tabelo z atributi Person_ID, First_Name, Maiden_Name, Last_Name (150.000 zapisov)
Rad bi zmesal podatke: First_Name, Maiden_Name, Last_Name tako da najprej zmesamo First_Name, nato Maiden_Name in na koncu Last_Name
Se pravi nekako takole:
Zacetna tabela:
ID First_Name Maiden_name Last_Name
1. Miran Grozni Mlakar
2. Peter Kosmati Pogačar
3. Jacman Uhati Raka
Dobimo tabelo:
ID First_Name Maiden_name Last_Name
1. Jacman Grozni Pogacar
2. Miran Uhati Raka
3. Peter Kosmati Mlakar
Delam pa na tak način:
1. Najprej z enim kurzorjem naredim selekcijo katere zapise bom mešal:
CURSOR zenske is
SELECT p.Person_Id
FROM person p, party pa
WHERE p.Person_ID = pa.Party_ID
AND pa.Party_Type_ID = 1
ORDER by p.Person_Id desc;
2. Nato odprem se en kurzor v katerem so atributi, ki jih bom mesal - v temle primeru First_Name:
OPEN z_ime FOR
SELECT p.First_Name
FROM person p, party pa
WHERE p.Person_ID = pa.Party_ID
AND pa.Party_Type_ID = 1
ORDER BY p.First_Name ASC;
sCounter := 0;
3. Sprehajam se cez prvi kurzor in iz drugega kurzorja jemljem First_Name ter ga update-am v tabelo, pri tem naredi COMMIT na vsakih 1000 zapisov:
FOR r IN zenske LOOP
FETCH z_ime
INTO sFirst_Name;
sCounter := sCounter + 1;
UPDATE person p
SET p.First_Name = sFirst_Name
WHERE p.Person_ID = r.Person_ID;
IF ( mod(sCounter, 1000)= 0 ) THEN
COMMIT;
--Dbms_Output.put_line (sCounter || ', ');
END IF;
END LOOP;
4. Na enak način naredim še za Maiden_Name in Last_Name
Ampak to dela zelo zelo počasi. Mi lahko kdo napiše proceduro, ki bi to hitreje opravila?
Rad bi zmesal podatke: First_Name, Maiden_Name, Last_Name tako da najprej zmesamo First_Name, nato Maiden_Name in na koncu Last_Name
Se pravi nekako takole:
Zacetna tabela:
ID First_Name Maiden_name Last_Name
1. Miran Grozni Mlakar
2. Peter Kosmati Pogačar
3. Jacman Uhati Raka
Dobimo tabelo:
ID First_Name Maiden_name Last_Name
1. Jacman Grozni Pogacar
2. Miran Uhati Raka
3. Peter Kosmati Mlakar
Delam pa na tak način:
1. Najprej z enim kurzorjem naredim selekcijo katere zapise bom mešal:
CURSOR zenske is
SELECT p.Person_Id
FROM person p, party pa
WHERE p.Person_ID = pa.Party_ID
AND pa.Party_Type_ID = 1
ORDER by p.Person_Id desc;
2. Nato odprem se en kurzor v katerem so atributi, ki jih bom mesal - v temle primeru First_Name:
OPEN z_ime FOR
SELECT p.First_Name
FROM person p, party pa
WHERE p.Person_ID = pa.Party_ID
AND pa.Party_Type_ID = 1
ORDER BY p.First_Name ASC;
sCounter := 0;
3. Sprehajam se cez prvi kurzor in iz drugega kurzorja jemljem First_Name ter ga update-am v tabelo, pri tem naredi COMMIT na vsakih 1000 zapisov:
FOR r IN zenske LOOP
FETCH z_ime
INTO sFirst_Name;
sCounter := sCounter + 1;
UPDATE person p
SET p.First_Name = sFirst_Name
WHERE p.Person_ID = r.Person_ID;
IF ( mod(sCounter, 1000)= 0 ) THEN
COMMIT;
--Dbms_Output.put_line (sCounter || ', ');
END IF;
END LOOP;
4. Na enak način naredim še za Maiden_Name in Last_Name
Ampak to dela zelo zelo počasi. Mi lahko kdo napiše proceduro, ki bi to hitreje opravila?
WarpedGone ::
1. narediš novo tabelo person_new(Person_ID, First_Name, Maiden_Name, Last_Name), nanjo daš le PK
2. nato narediš vanjo INSERT ... SELECT ...
INSERT INTO person_new
SELECT s1.person_id,
s2.First_Name,
s3.Maiden_Name,
s4.Last_Name
FROM (SELECT ROWNUM AS ID,
person_id
FROM person
ORDER BY person_id ASC ) s1,
(SELECT ROWNUM AS ID,
First_Name
FROM person
ORDER BY First_Name DESC) s2,
(SELECT ROWNUM AS ID,
Maiden_Name
FROM person
ORDER BY Maiden_Name ASC ) s3,
(SELECT ROWNUM AS ID,
Last_Name
FROM person
ORDER BY Last_Name DESC) s4
WHERE s1.ID = s2.ID AND
s2.ID = s3.ID AND
s3.ID = s4.ID;
3. tabelo person zamenjaš z novo:
RENAME person TO person_org;
RENAME person_new TO person;
4. zgradiš morebitne dodatne indekse
Tole seveda nuca velik ROLLBACK, je pa za probat.
2. nato narediš vanjo INSERT ... SELECT ...
INSERT INTO person_new
SELECT s1.person_id,
s2.First_Name,
s3.Maiden_Name,
s4.Last_Name
FROM (SELECT ROWNUM AS ID,
person_id
FROM person
ORDER BY person_id ASC ) s1,
(SELECT ROWNUM AS ID,
First_Name
FROM person
ORDER BY First_Name DESC) s2,
(SELECT ROWNUM AS ID,
Maiden_Name
FROM person
ORDER BY Maiden_Name ASC ) s3,
(SELECT ROWNUM AS ID,
Last_Name
FROM person
ORDER BY Last_Name DESC) s4
WHERE s1.ID = s2.ID AND
s2.ID = s3.ID AND
s3.ID = s4.ID;
3. tabelo person zamenjaš z novo:
RENAME person TO person_org;
RENAME person_new TO person;
4. zgradiš morebitne dodatne indekse
Tole seveda nuca velik ROLLBACK, je pa za probat.
Zbogom in hvala za vse ribe
Vredno ogleda ...
Tema | Ogledi | Zadnje sporočilo | |
---|---|---|---|
Tema | Ogledi | Zadnje sporočilo | |
» | php updateOddelek: Programiranje | 919 (733) | keworkian |
» | php skripta za registracijo uporabnikovOddelek: Izdelava spletišč | 2077 (1658) | skorpio |
» | razložitev nekaterij elementov v visual studio 05Oddelek: Programiranje | 2099 (1904) | darkolord |
» | [SQL] Ali se tale poizvedba da optimizirat?Oddelek: Programiranje | 1073 (961) | krho |
» | Obleci nas!Oddelek: Novice / Grafične kartice | 2400 (2400) | root987 |