ࡱ> hji~(  \      / 00DTimes New Roman0̱̱~0DArialNew Roman0̱̱~0" DLucida Console0̱̱~010DCourier Newle0̱̱~01@  @@``  @n?" dd@  @@`` P)U        S ~1?`[QDf3f@8> g4MdMdر~0б ppp@ <4!d!dLg4:d:dر~0б p@ pp? hUnicode Oddity     "from a Unicode PeopleSoft Database # SELECT emplid, name, LENGTH(name), BLENGTH(name) FROM ps_personal_data WHERE emplid = '007 ; EMPLID NAME LENGTH(NAME) BLENGTH(NAME) ------------- ---------- ------------ ------------- 007 Conaov,d 10 12(x  *          Insert 10 characters in 11 bytes ! CREATE TABLE fred (fred10 VARCHAR2(11)); Table created. INSERT INTO fred SELECT name FROM ps_personal_data WHERE emplid = '007'; ERROR at line 1: ORA-01401: inserted value too large for column &b   3      L  Insert 10 characters in 12 bytes ! CREATE TABLE fred (fred10 VARCHAR2(12)); Table created. INSERT INTO fred SELECT name FROM ps_personal_data WHERE emplid = '007'; 1 row created.b   3        "How does PeopleSoft create tables? # VCREATE TABLE fred (fred10 VARCHAR2(30) CHECK (LENGTH(fred10)<=10) ); Table created. WW   F  So...  Length checking constraint on EVERY character column in the database! >500,000 user constraints What effect does this have on performance    Experiment 1  create table test_nocons (id number ,field_01 varchar2(30) & ,field_20 varchar2(30) ); &[X   C  create table test_cons (id number ,field_01 varchar2(30) CHECK(LENGTH(field_01)<=30) & ,field_20 varchar2(30) CHECK(LENGTH(field_01)<=30) );   Populate tables, trace enabled  alter session set sql_trace = true; BEGIN FOR i IN 1..10000 LOOP INSERT INTO test_nocons VALUES (i ,RPAD(TO_CHAR(i),11,'.') & ,RPAD(TO_CHAR(i),30,'.') ); COMMIT; END LOOP; END; / &  I               Results of Experiment 1  rInsert 10000 rows CPU time for recursive SQL on my 500Mhz Laptop No constraints: 11.08s With constraints 13.23s6/0/0 s  Experiment 2  Now deliberately generate different SQL statements, forcing parse every time. BEGIN FOR i IN 1..1000 LOOP EXECUTE IMMEDIATE 'INSERT INTO test_nocons VALUES ('||i||',RPAD(TO_CHAR('||i||'),11,''.''))'; END LOOP; COMMIT; END; /,NN   U Results of Experiment 2  ^>99% parse time Duration of parse CPU Without Constraints: 41.05s With Constraints: 156.93s$&9&9 _  Conclusion   Execution of constraints adds overhead. On my PC 15%-20% increase in CPU consumption. If you have much SQL parsing this will aggravate the problem. In my case 4 times worse.6*.Z*.Z   Unicode Oddity  VDavid Kurtz Go-Faster Consultancy Ltd. david.kurtz@go-faster.co.uk www.go-faster.co.ukD K(-B$!1 / u  ` ___f3f` ___3f` 999MMM>?" dd@,|?" dd@   " @ `"  n?" dd@   @@``PR    @ ` ` p>> S K   (  b8  q0a q 0a2  C BUCENG HZI8Qf3? جU,جU,8`T,8`T q0a2  C BUCENG HZIDQD? U8U8D`T8D`T 2  C BUCENG HZIEQ[? U9U9E`T9E`T dn"   G/*d6?@33@@@sf 4  Z$"gֳgֳ ?  T Click to edit Master title style! !:  T"gֳgֳ ?0  RClick to edit Master text styles Second level Third level Fourth level Fifth level!     S    `"gֳgֳ ?`  ?*    `D#gֳgֳ ?   A*    `#gֳgֳ ?   A*H  0޽h? ??0 ___f3f Fireball.pot\     P d (   b8  0    0 2   C BUCENG HZI8Qf3? جU,جU,8`T,8`T 0 2   C BUCENG HZIDQD? U8U8D`T8D`T 9Y 2   C BUCENG HZIEQ[? U9U9E`T9E`T "    G/*d6?@33@@@sf   Z_gֳgֳ ?`  T Click to edit Master title style! !  T`gֳgֳ ?0 `    W#Click to edit Master subtitle style$ $   ``gֳgֳ ?`  ?*   `Dagֳgֳ ?   A*   `agֳgֳ ?   A*H  0޽h? ??0    ___f3f 0 00(  V H  0޽h ? ̙33 @$(  r  S b `  r  S db 0 `    H  0޽h ? ___f3f  `L( )pM@ Ll L C $c   l L C c0  H L 0޽h ? ___f3f  pT$( %P Tr T S c   r T S Dd0  H T 0޽h ? ___f3f  X$( ftl  Xr X S e   r X S de0  H X 0޽h ? ___f3f  \$(  j@ \r \ S $f   r \ S f0  H \ 0޽h ? ___f3f  `( ?, `l ` C    l ` C 40  H ` 0޽h ? ___f3f^  h(  hr h S    x h c $T 0  l h C p 0  H h 0޽h ? ___f3f  t*(  tr t S   < x t c $t0 < H t 0޽h ? ___f3f   |(  |l | C   < l | C 40 < H | 0޽h ? ___f3f  0( K@ l  C   < l  C 0 < H  0޽h ? ___f3f   ( \@d@ l  C D^   l  C ^0  H  0޽h ? ___f3f  @( p\ l  C t  < l  C 0 < H  0޽h ? ___f3fH P4v(  4 4 3 rTgֳgֳ ? `   4 # lgֳgֳ ?   d2 4 c gֳgֳvd @?UQl$ 000 < 2  d2 4 c tgֳgֳvd @?@UQ< l$ 000 < 2  d2 4 c gֳgֳvd @? UQ l$ 000 < 2  d2 4 c 4gֳgֳvd @?@ TP< l$ 000 < 2  B 4 s *޽h ? f3f3f" 0 `8r(  8^ 8S (8   < 8# ldIdI 9  <   H 8 0޽h ? ̙33p 0 0((  ^  S  ?   "  Tf1 ? @   `This is perfectly reasonable and exactly the behaviour you would expect fromVARCHAR2 in Oracle 8 a H  0޽h ? ̙33  0 Z( @@L@ ^  S  ?     Tg1 ? @     H  0޽h ? ̙33  0 Z( @@L@ ^  S  ?     Th1 ? @     H  0޽h ? ̙33  0 Z( p@v@ ^  S  ?     T1 ? @     H  0޽h ? ̙33   0 Z(  ^  S  ?     T1 ? @     H  0޽h ? ̙33rhPg4*O5 0LT;79;<>@%CEJFHWY[]_aOh+'0\D hp   @ L Xdldb_writer_processes on NT.  David KurtzNC:\Program Files\Microsoft Office\Templates\Presentation Designs\FIREBALL.POT\ David Kurtz8viMicrosoft PowerPointoso@ @ّ0y@P68btGBoM  k!P& &&#TNPP0D & TNPP &&TNPP    --- !---&0&W&--&&- $WjjW- $j}}j - $}}"- $*- $3- $;- $C!- $K%- $R)- $X,- $**\.- $*==*`0- $=PP=b1- $PddPd2- $dwwde2- $ww&&&- &P$&[[\{ 0CUd$p,z3;DLU]zepmdtU{C0{\ZW\L&&-&& &&-&&P$&[[\{ 0CUd$p,z3;DLU]zepmdtU{C0{\ZW\L&&- $WjjW- $j}}j - $}}"- $*- $3- $;- $C!- $K%- $R)- $X,- $**\.- $*==*`0- $=PP=b1- $PddPd2- $dwwde2- $ww&- --&&&O &--&& - $O b bO - $b u ub"- $u  u.- $  9- $  D"- $  P'- $  [-- $  e2- $  o7- $  v:- $ " "|=- $" 5 5"@- $5 H H5A- $H \ \HC- $\ o o\C- $o  o&&&- &N$%T T s   (<!M'\,i2s8z?~EL~SzZs`if\lMq<w({sTROTL&&-&& &&-&&N$%T T s   (<!M'\,i2s8z?~EL~SzZs`if\lMq<w({sTROTL&& - $O b bO - $b u ub"- $u  u.- $  9- $  D"- $  P'- $  [-- $  e2- $  o7- $  v:- $ " "|=- $" 5 5"@- $5 H H5A- $H \ \HC- $\ o o\C- $o  o&- --&&&K!~z&--&&- $K!^!^zKz- $^!q!qz^z.- $q!!zqz=- $!!zzM&- $!!zz[-- $!!zzk5- $!!zzy<- $!!zzC- $!!zzI- $! ! zzN- $ !!z zR- $!1!1zzU- $1!D!Dz1zW- $D!X!XzDzY- $X!k!kzXzZ- $k!~!~zkz&&&- &N$%O!O!n!"#$&(+$.71I4X7d;n?vCzH|LzPvUnYd]XaIe7h$knpsuvwnxOxMxKxOL&&-&& &&-&&N$%O!O!n!"#$&(+$.71I4X7d;n?vCzH|LzPvUnYd]XaIe7h$knpsuvwnxOxMxKxOL&&- $K!^!^zKz- $^!q!qz^z.- $q!!zqz=- $!!zzM&- $!!zz[-- $!!zzk5- $!!zzy<- $!!zzC- $!!zzI- $! ! zzN- $ !!z zR- $!1!1zzU- $1!D!Dz1zW- $D!X!XzDzY- $X!k!kzXzZ- $k!~!~zkz&- --&&&0CqW&--&&- $0C:C:W0W- $:CECEW:W - $ECOCOWEW- $OCZCZWOW- $ZCdCdWZW- $dCoCoWdW- $oCyCyWoW- $yCCWyW"- $CCWW&- $CCWW*- $CCWW.- $CCWW2- $CCWW6- $CCWW:- $CCWW>- $CCWW@- $CCWW@- $CCWW @- $CCWW@- $CCWW@- $C%C%WW@- $%C4C4W%W@- $4CDCDW4W@- $DCTCTWDW"@- $TCcCcWTW&@- $cCsCsWcW)@- $sCCWsW-@- $CCWW2@- $CCWW6@- $CCWW9@- $CCWW=@- $CCWWB@- $CCWWH@- $CCWWM@- $CCWWR@- $CCWWW@- $CCWW\@- $C!C!WWa@- $!C.C.W!We@- $.C;C;W.Wj@- $;CHCHW;Wo@- $HCVCVWHWu@- $VCcCcWVWz@- $cCpCpWcW~@- $pC}C}WpW@- $}CCW}W@- $CCWW@- $CCWW;- $CCWW5- $CCWW1- $CCWW -- $CCWW')- $CCWW/%- $CCWW6!- $CCWW=- $CCWWD- $CCWWK- $CCWWS- $CCWWZ - $CCWWa - $CCWWh- $CCWWo- $CCWWv- $C&C&WW~- $&C.C.W&W- $.C6C6W.W- $6C;C;W6W- $;C@C@W;W- $@CECEW@W- $ECJCJWEW- $JCOCOWJW- $OCUCUWOW- $UCZCZWUW- $ZC_C_WZW- $_CdCdW_W- $dCiCiWdW- $iCoCoWiW- $oCqCqWoW&&&- &,$9C6D3F1I0L0L1P3R6T9UgUjTmRnPoLoLnImFjDgC&&-&& &&-&&,$9C6D3F1I0L0L1P3R6T9UgUjTmRnPoLoLnImFjDgC&&- $0C:C:W0W- $:CECEW:W - $ECOCOWEW- $OCZCZWOW- $ZCdCdWZW- $dCoCoWdW- $oCyCyWoW- $yCCWyW"- $CCWW&- $CCWW*- $CCWW.- $CCWW2- $CCWW6- $CCWW:- $CCWW>- $CCWW@- $CCWW@- $CCWW @- $CCWW@- $CCWW@- $C%C%WW@- $%C4C4W%W@- $4CDCDW4W@- $DCTCTWDW"@- $TCcCcWTW&@- $cCsCsWcW)@- $sCCWsW-@- $CCWW2@- $CCWW6@- $CCWW9@- $CCWW=@- $CCWWB@- $CCWWH@- $CCWWM@- $CCWWR@- $CCWWW@- $CCWW\@- $C!C!WWa@- $!C.C.W!We@- $.C;C;W.Wj@- $;CHCHW;Wo@- $HCVCVWHWu@- $VCcCcWVWz@- $cCpCpWcW~@- $pC}C}WpW@- $}CCW}W@- $CCWW@- $CCWW;- $CCWW5- $CCWW1- $CCWW -- $CCWW')- $CCWW/%- $CCWW6!- $CCWW=- $CCWWD- $CCWWK- $CCWWS- $CCWWZ - $CCWWa - $CCWWh- $CCWWo- $CCWWv- $C&C&WW~- $&C.C.W&W- $.C6C6W.W- $6C;C;W6W- $;C@C@W;W- $@CECEW@W- $ECJCJWEW- $JCOCOWJW- $OCUCUWOW- $UCZCZWUW- $ZC_C_WZW- $_CdCdW_W- $dCiCiWdW- $iCoCoWiW- $oCqCqWoW&- --&&&--yH-- LVw_wgw L - Times New Roman_wgw ] - f.2 Unicode Oddity*+.--"Systemwf  -&TNPP &՜.+,D՜.+,x4    $ On-screen ShowGo-Faster Consultancy Ltd.ebe 1 Times New RomanArialLucida Console Courier New Fireball.potUnicode Oddity#from a Unicode PeopleSoft Database!Insert 10 characters in 11 bytes!Insert 10 characters in 12 bytes#How does PeopleSoft create tables?So... Experiment 1Populate tables, trace enabledResults of Experiment 1 Experiment 2Results of Experiment 2 Conclusion Unicode Oddity  Fonts UsedDesign Template Slide Titles T(RZ _PID_GUID _PID_HLINKSAN{19222E0C-F767-4A8C-B01C-0AE8AC64E83E}A257,2,Custom Topic A258,3,Custom Topic B259,4,Vital Statistics260,5,Favorite Links to Visit#_Ab David Kurtz  !"#$%&'()*+,-./013456789:;<=>?@ABCDEFGHIJKLMNOPQRSTVWXYZ[\^_`abcdgRoot EntrydO)Current User]SummaryInformation(2DPowerPoint Document(ebDocumentSummaryInformation8URoot EntrydO)cbk@Current User+SummaryInformation(2DPowerPoint Document(eb  !"#$%&'()*+,-./013456789:;<=>?@ABCDEFGHIJKLMNOPQRSTVWXYZ[\g#_Ab David Kurtz