PL/SQL වැනුම

සෑහෙන කලකට පස්සෙ තාක්ෂණික සටහනක් ලියන්න හිතුණා. මගේ රැකියාවේ හැටියට බොහොමයක් අළුත් දේවල් අත්හදා බලන්න ඉඩ ලැබුණත්, මගේ රුචිකත්වය අනුව අත්හදාබැලීම් වල යෙදෙන්න තියෙන නිදහස කැම්පස් යන කාලෙ තිබුණ නිදහසට සාපේක්ෂව ගොඩක් අඩුයි. ඒ නිසා තාක්ෂණික සටහන් ලිවිල්ල සෑහෙන අඩු වුණා (නැත්තටම නැති වුණා). බයික් ගැන බ්ලොග් සටහනක් ලිව්වෙ ඒ ගැන යමක් කමක් අත්හදා බලන්න නිදහසක් ලැබුණු නිසා. 2013 මට තරමක් කරදර සිද්ධ වෙච්ච වගේම ගොඩක් වැඩ අධික අවුරුද්දක් වුණු නිසා 2013 අවුරුද්දම මගේ බ්ලොග් එක නිහඬයි. 2014 අවුරුද්දත් එහෙම වෙන්න එපා කියල හිතාගෙනම මගේ ඩේටාබේස් අත්දැකීම මෙසේ සටහන් වන වගයි. :-)

මේක තාක්ෂණික සටහනක්. Database ගැන අත්දැකීම් නැති අයට, IT නොකරන අයට මේක නොතේරෙන්න හොඳටම ඉඩකඩ තියෙනවා. මේක අණ්ඩර දෙමළ වගේ කියල හිතුණොත් මගේ මෝටර්සයිකල් ටුවර් එක ගැන කියවන්න. :)

මගේ හොඳ වෙලාවට මගේ රැකියාව ආශ්‍රිත වැඩ ගොඩක්ම යෙදිල තියෙන්නෙ JIRA කියන විවෘත කේත (open source) මෘදුකාංගයත් එක්ක. ඒ නිසා යම් යම් දේවල් මට බ්ලොග් එකේ සකල ලෝකවාසීන්ට පේන්න ලියන්න පුළුවන්කම ලැබිලා තියෙනවා. මෑතකදි මගේ වැඩකට අවශ්‍ය වුණා JIRA database එකේ තියෙන එක table එකක දත්ත වල යම් යම් වෙනස්කම් ටිකක් කරන්න. සරලව කිව්වොත් SQL updates. හැබැයි වැඩේ හිතන තරම් සරල නැහැ කියල තේරුණේ මෙන්න මේ කාරණා ටිකත් එක්කයි.

1. එකම table එකේ CLOB (Character Large Object) ආකෘතියෙන් ඇති columns හතරක් update කළ යුතුයි. සාමාන්‍යයෙන් Oracle database එකක පාඨ (text) ආකාරයෙන් දත්ත ආචයනය කරන්න පාවිච්චි කරන්නෙ VARCHAR2 කියන ආකෘතිය. නමුත් ඉතා දීර්ඝ පාඨයන් ආචයනය කිරීමේ අවශ්‍යතාවක් මතු වෙන්න ඉඩ තියෙනවනම් database එක පිරිසැලසුම් කරන අය VARCHAR2 වෙනුවට CLOB යොදා ගන්නවා. මේ එවැනි අවස්ථාවක්. නමුත් CLOB සඳහා වැඩිපුර ආචයන ඉඩ වගේම වැඩිපුර processing power අවශ්‍ය වෙන්නත් පුළුවන්.
• කොහොම වුණත් මේ ප්‍රශ්නයෙදි CLOB ලෙස ගබඩා කර තිබුණෙ කෙටි පාඨ කොටස් (< 255 bytes).
2. මේ table එකේ rows මිලියන ගණනක් තියෙනවා. වැඩේ ඉවර වෙනකම් දවස් ගණන් බලාගෙන ඉන්න බැහැ.

 අවශ්‍ය අවසාන ප්‍රතිපලය වුණේ සරල දෙයක්. CLOB එකේ X කියන පාඨය තිබුණොත් ඒක Y විධියට වෙනස් කරන්න. A තිබුණොත් B විධියට වෙනස් කරන්න. Database ගැන නොදන්න කෙනෙකුට මේක පේන්නෙ හරියට 'word document' එකක 'Find & Replace' වගේ.

විස්තර කිරීමේ පහසුව තකා  මම ටේබල් රාජයා මෙන්න මේ විදියට SQL වලින්ම විස්තර කරන්නම්.

-- Table in DDL (මේ JIRA වල CHANGEITEM table එක. CLOB හතර තමයි මෙතනින් වැදගත්)
CREATE TABLE CHANGEITEM (
  ID NUMBER (18,0) NOT NULL, 
  GROUPID NUMBER (18,0), 
  FIELDTYPE VARCHAR2 (255 CHAR), 
  FIELD VARCHAR2 (255 CHAR), 
  OLDVALUE CLOB, 
  OLDSTRING CLOB, 
  NEWVALUE CLOB, 
  NEWSTRING CLOB, 
  FIELDID VARCHAR2 (255 CHAR)
);

මගේ 'Find & Replace' ටිකත් මෙන්න මෙහෙම table එකකට දා ගත්තා.

CREATE TABLE REPLACESTR (
  OLDSTR VARCHAR2 (255 BYTE),
  NEWSTR VARCHAR2 (255 BYTE)
);

INSERT INTO REPLACESTR VALUES ('A', 'B');
INSERT INTO REPLACESTR VALUES ('X', 'Y');
...
...
...
COMMIT;

(ඇත්තම scenario එක හංගන්න පාවිච්චි කරපු බොරු දත්ත ටිකක් A, B, X, Y කියල මම ගත්තෙ)

මගේ පළමු උත්සාහය වුණේ මෙන්න මෙහෙම දෙයක්. මෙතන subquery (nested query) වගේම පොඩි join කෑල්ලකුත් තියෙනවා.

UPDATE CHANGEITEM CI
SET
  CI.OLDVALUE = (SELECT RS.NEWSTR FROM REPLACESTR RS 
        WHERE DBMS_LOB.SUBSTR(CI.OLDVALUE, 255, 1) = RS.OLDSTR),
  CI.OLDSTRING = (SELECT RS.NEWSTR FROM REPLACESTR RS 
        WHERE DBMS_LOB.SUBSTR(CI.OLDSTRING, 255, 1) = RS.OLDSTR),
  CI.NEWVALUE = (SELECT RS.NEWSTR FROM REPLACESTR RS 
        WHERE DBMS_LOB.SUBSTR(CI.NEWVALUE, 255, 1) = RS.OLDSTR),
  CI.NEWSTRING = (SELECT RS.NEWSTR FROM REPLACESTR RS 
        WHERE DBMS_LOB.SUBSTR(CI.NEWSTRING, 255, 1) = RS.OLDSTR)
WHERE
  CI.FIELD = 'Customer';

මේක සෑහෙන්න බරයි database එකට. Records මිලියන ගණනක්. ඉතින් වැඩ නොකරන එක අහන්නත් දෙයක් නෙමෙයිනේ. පැයක් දෙකක් බලාගෙන ඉන්නකොට මේක නං හරියන වැඩක් නෙමෙයි කියල තේරුණා. ඒකට තියෙන resources මදි වගේ. ඒ නිසා Ctrl + C එකක් ගහලා වෙන විධියක් කල්පනා කළා. මගේ ඊළඟ උත්සාහය වුණේ ගෙඩි පිටින් ඔක්කොම columns වෙනස් නොකර එකින් එක වෙනස් කරන්න. ඒ කිව්වෙ උඩින් තියෙන query එක පොඩ query පැටව් හතරකට වෙන් කරන්න. මෙන්න මේ වගේ...

UPDATE CHANGEITEM CI
SET
  CI.OLDVALUE = (SELECT RS.NEWSTR FROM REPLACESTR RS 
        WHERE DBMS_LOB.SUBSTR(CI.OLDVALUE, 255, 1) = RS.OLDSTR)
WHERE
  CI.FIELD = 'Customer';

UPDATE CHANGEITEM CI
SET
  CI.OLDSTRING = (SELECT RS.NEWSTR FROM REPLACESTR RS 
        WHERE DBMS_LOB.SUBSTR(CI.OLDSTRING, 255, 1) = RS.OLDSTR)
WHERE
  CI.FIELD = 'Customer';

UPDATE CHANGEITEM CI
SET
  CI.NEWVALUE = (SELECT RS.NEWSTR FROM REPLACESTR RS 
        WHERE DBMS_LOB.SUBSTR(CI.NEWVALUE, 255, 1) = RS.OLDSTR)
WHERE
  CI.FIELD = 'Customer';

UPDATE CHANGEITEM CI
SET
  CI.NEWSTRING = (SELECT RS.NEWSTR FROM REPLACESTR RS 
        WHERE DBMS_LOB.SUBSTR(CI.NEWSTRING, 255, 1) = RS.OLDSTR)
WHERE
  CI.FIELD = 'Customer';

මේකත් හරි ගියේ නැහැ. නමුත් දැන් බර අඩු query හතරක්නෙ කියල හිත සැහැල්ලු කරගෙන පැයක් දෙකක් නෙමේ දවස් තුනක් තිස්සෙ මේ ටික run වෙන්න දාල තිබ්බ. ම්හූ.... නෑ ප්‍රතිපලයක්. දැන් ඉතින් ඉස්සරහට යන්න පාරවල් දෙකයි. එකක් Oracle performance tuning. අනිත් එක තමයි query optimization.

Performance tuning ගැන මගේ අත්දැකීම් ඉතාම අඩු නිසා මම කල්පනා කළා query optimization ගැන. ඒත් මේ ඇබිත්තං query වල තව මොනව optimize කරන්නද? ඊළඟට නිකං උත්සාහයක් කරල බලන්න තිබුණු එකම එක දේ තමයි වරකට එක row එක බැගින් rows මිලියන ගණනම update කරන එක. විහිළුවක් වගේ නේද? අතින් නෙමේ කරන්නෙ පොඩි පහේ script කෑල්ලකින්. ඔන්න ඔතනදි තමයි අපේ උදව්වට PL/SQL එන්නෙ.

මුලින්ම ලියා ගත්තා මේ වගේ පුංචි පහේ PL/SQL block එකක්. මේක තමා සැකිල්ල.

DECLARE
BEGIN
EXCEPTION
END;
/

ඊළඟට මිරිස්, තුනපහ එකතු කරලා ගත්තා මේ වගේ,
බ්ලොග් එකේ මෙහෙම එක ෂොට් එකෙන් ලිව්වට මට මේක ලියාගන්නත් ටිකක් මහන්සියක් වෙන්න වුණා PL/SQL දැනගෙන හිටියෙ නැති නිසා. Oracle documentation, Tech on the Net, StackOverflow වගේ වෙබ් අඩවි තමයි මගේ පිහිටට හිටියේ. සෑහෙන වාර ගණනක් වරද්දලා තමයි අන්තිමේ හරියට ලියා ගත්තේ. :)

DECLARE
  LOB_OLD VARCHAR2 (255 BYTE);
  LOB_NEW VARCHAR2 (255 BYTE);
BEGIN
  FOR I IN (SELECT ID FROM CHANGEITEM WHERE FIELD = 'Customer')
  LOOP
    SELECT DBMS_LOB.SUBSTR (OLDVALUE, 255, 1) INTO LOB_OLD 
        FROM CHANGEITEM WHERE ID = I.ID;
    BEGIN
      SELECT NEWSTR INTO LOB_NEW FROM REPLACESTR WHERE OLDSTR = LOB_OLD;
      UPDATE CHANGEITEM SET OLDVALUE = LOB_NEW WHERE ID = I.ID;
    EXCEPTION
      WHEN NO_DATA_FOUND THEN NULL;
    END;
  END LOOP;
END;
/

COMMIT;

CLOB හතරට මේ වගේ ඒවා හතරක් හදා ගත්තා. ඕනෙ නං එක PL/SQL block එකෙන්ම ගොඩ දාන්නත් පුළුවන්. එක CLOB column එකක් (rows මිලියන ගණනක්) upadte වෙන්න මේ ක්‍රමයෙදි කොයි තරම් කාලයක් ගත වුණාද කියල හිතන්න පුළුවන්ද? පැය භාගයයි!! දවස් ගණන් ගිහිල්ලත් කරගන්න බැරි වෙච්ච වැඩේට දැන් පැය භාගයයි!!!

ඔන්න ඔහොමයි මම PL/SQL වලට අත ගැහුවේ. මේක හරියට ගොඩක් බර පොත් ගොඩක් එක පාරටම ඔක්කොම උස්සන් යන්නෙ නැතුව වරකට එක බැගින් උස්සන් යනව වගේ වැඩක්. සම්පූර්ණ බර දරාගන්න බැරි නිසා ඔක්කොම එක පාරින් ගෙනියන්න හැදුවොත් අඩුම තරමින් බර උස්සගන්නවත් බෑ.

එහෙනම්,  2014 අවුරුද්දට තවත් මොනවහරි ලියන්න ලැබෙයි කියල හිතාගෙනම, ආයුබෝවන්, වණක්කම්! :)

(මම අවුරුදු ගාණක් FOSS ගැන තවුසන්ඩ් ටෝක්ස් දීලා Oracle ගේ අහවල් එකක් වර්ණනා කරනව නෙමෙයි. කොයි දේත් ඉගෙනගත්තොත් වටිනාකම තියෙන්නෙ එතැනයි)