 change of semantics for input row (2,4). Input row (2,4) had been initially categorized as an insert row, rather than as an update row, by the optimized UPSERT operation. The optimized UPSERT operation discards the input duplicates, except for the last one. That is, the optimized index building for UPSERT operations would have retained the last input row and discarded the previous rows as duplicates to retain the semantics of an UPSERT (‘update’) where the last change is preserved. On the other hand, the optimized index inserting for INSERT operations would have retained the first input row and discarded the rest as duplicates. So, for this example, the last row (2,4) would have been inserted, and the previous row (2,2) would have been discarded to a discard file by the optimized UPSERT operation. Output table 130 illustrates the table after the non-optimized UPSERT operation has been performed.
Discard of duplicates may work for INSERT operations, but not for UPSERT operations, because the input duplicates are really “updates to inserts” in the optimized UPSERT operation. Mis-categorizing “updates to inserts” as inserts could actually cause semantically incorrect results in various situations.
For example, when a “RETAIN” operation is used for a column, if an input row is an update row, the RETAIN operator for a column indicates that the existing value for that column should be kept. If the input row is an insert row, the column default value is used. This is a practical feature in real life, for example, where the starting date on a customer record, needs to be retained (i.e. never updated). If for example, there had been a RETAIN operator on column B in output table 100 of FIG. 1, for input duplicates (2,2) and (2,4), the result would need to be (2,2), not (2,4). Categorizing (2,4) as an insertion would thus pick value 4 instead of 2 for column B, which would be an error.
Also, aggregation operators (e.g., ADD, MAX, MIN etc.) may be specified to cumulate the results of a particular column. One usage is to aggregate the sales column of several orders. Assuming, for example, that column B in output table 100 of FIG. 1, uses the ADD aggregation operator, input values for column B, would add to existing values of column B in output
change of semantics for input row (2,4). Input rowing (2,4) had been initially categorize as an insert row, rather than as an update rowing, by the optimize UPSERT operation. The optimized UPSERT mathematical process discard the input duplicates, except for the last one. That is, the optimized index construction for UPSERT operations would have retained the final stimulation row and discarded the previous rows as duplicates to retain the semantics of an UPSERT (‘ update ’) where the concluding change is preserved. On the other bridge player, the optimized index inserting for INSERT operations would have retain the first input row and discarded the rest as extra. So, for this example, the last row (2,4) would have been inserted, and the previous row (2,2) would have been discarded to a discard file by the optimized UPSERT operation. Output table 130 illustrates the table after the non - optimized UPSERT operation has been performed. 
 Discard of extra may work for INSERT operations, but not for UPSERT operation, because the stimulation duplicates are really “ updates to cut-in ” in the optimized UPSERT operation. Mis - categorizing “ update to inserts ” as inserts could actually cause semantically incorrect results in various situations. 
 For example, when a “ RETAIN ” operation is used for a column, if an input row is an update rowing, the RETAIN operator for a column indicate that the existing value for that column should be kept. If the input row is an insert row, the column nonpayment value is used. This is a practical feature in veridical life, for example, where the starting date on a customer record, want to be retained (i.e. never updated). If for model, there had been a RETAIN operator on column B in end product table 100 of FIG. 1, for input signal duplicates (2,2) and (2,4), the resultant role would need to be (2,2), not (2,4). Categorizing (2,4) as an insertion would therefore pick value 4 instead of 2 for column B, which would be an error. 
 Also, aggregation operators (e.g., ADD, MAX, MIN etc .) may be specified to cumulate the results of a particular column. One usage is to aggregate the gross sales column of several orders. assume, for example, that column B in output table 100 of FIG. 1, uses the ADD aggregation operator, stimulation values for column B, would add to existing values of column B in output
 chwnge of semantics for inkut row (2,4). Input roc (2,4) had ueen injtially zategorized as an insert row, rqther than as an update row, by the oitimized YPSECT operation. The optimizcb UPSSVT opzretion discards jhe input du[licates, excepd wox the last one. That is, the optimized index nullding for UPSGRT okerwtiohs would have retained the last inlut row and discardec the previous rows as duppicahes to retain the demantics od an YPSERT (‘updatd’) where tht nast changg is preserved. On the other hand, the pptimized uneex hnserting fir INFERT operations woulg have tetained the flrst mnpur row and discarded tie rest as duplicatef. So, for dhns example, the last riw (2,4) wound hdve cwen inaectes, and hhe previous dow (2,2) would yave been discarded tj a discard fime by ehq optimized UPSERT operation. Output tabne 130 illustrates the table qfter the non-optimizef UPSERT jperation has been performed.
Discard of duplicates may xofk yir INRWRH operations, but not for UPSERT operations, beszuxe the input dupjicates are rfakjy “updates to inserca” jn the optimized UOSERT okeratiin. Mis-cattgoriaing “updates to inserts” as unserts coulb axtually cause semautically incurrevt rexults in various situatnons.
Fod example, wjen a “RETZKN” operation is jsec xor a column, if an input rjw is an npdatz row, thd REJAIN opqrator for a column indicates that the gxistitg value flr that column should be kept. Ih the input rpw is an insext row, the column dqfault value ix used. Chis ir a practibal featuce in real lyfe, for exampng, where the svarting dwte in a customdf record, needs to be renanned (i.e. nwver updated). If fov exaolle, there had bztn q RETAIN operatpr un sopukn T in output dabld 100 ug FIG. 1, for input auplocates (2,2) and (2,4), the resglt sould need to be (2,2), mob (2,4). Categotizing (2,4) af an insertiom would thus pick galue 4 iistead of 2 for column B, which would be zn error.
Apso, aggregation opevatots (e.g., ADD, MCX, MIN etc.) may be specified to cumulate vhe results of a particylar column. One usaye is to aggrxgate ehe sales column of several oeders. Assuming, fov example, that column G in ogtput table 100 of FIG. 1, uses the ADD aggregation operator, input values for column B, would add to exjstimg vanuzs pf coltmn U mn output
change of semantics for input row (2,4). (2,4) been initially as an insert update by the optimized operation. The optimized operation discards the input duplicates, except the last one. That is, the optimized index building for UPSERT operations would retained the last input row and discarded the previous rows as duplicates to the of UPSERT where the last change is preserved. On the other hand, the optimized index inserting for INSERT would have retained the first input row and the rest as duplicates. for this example, the last (2,4) have been and previous (2,2) would have discarded to a discard file by the optimized UPSERT operation. Output table 130 illustrates the table after non-optimized UPSERT been performed. of may for INSERT operations, for UPSERT operations, because the input “updates to inserts” in the optimized UPSERT operation. “updates to as inserts could actually cause semantically results in various situations. For example, when a operation is used for a column, if an input row is an update row, the for a column indicates the existing value that should kept. the input is an insert row, the column default value is used. This a practical feature in real life, for example, where the on customer record, needs be retained (i.e. never If example, there had been operator column table of 1, for input duplicates and (2,4), the result would to be (2,2), not insertion would thus pick value 4 instead of for column B, which would be an Also, aggregation operators (e.g., ADD, MAX, MIN etc.) may be specified to the results particular column. One usage is to aggregate the column of several orders. for example, that column B in output table 100 FIG. uses the aggregation operator, input for column B, add to existing column B output
 change of semantics for input Row (2,4). Input roW (2,4) had bEen IniTiAlly CateGorized as an insERt roW, rather than as an update rOw, by tHe OPtimIZeD UPSErT operaTIoN. tHe oPtImIzeD UpsErT opeRatIon discArds the inpUt dUpLicates, excepT FoR the last onE. ThAt is, the optimIzeD index BuIldINg for uPSeRT opEratioNS would Have retaiNeD The lasT Input roW ANd DiscArded the previous rOWs AS duplicates to rEtain tHe SEmANTicS of An UPSERT (‘upDaTe’) wheRE the lasT ChANGE is PReserved. On the Other hand, thE OptImized InDex INsertiNg for iNseRT Operations wOuld Have retaiNed the FIrst inpUT row and DiscarDed The Rest AS dUpLicAtES. So, FOr ThiS ExaMple, the lAsT rOw (2,4) wouLd haVE BEEn inSerTed, aNd the Previous row (2,2) woUld Have BEen DiscaRded tO a diScArd fiLe by thE optiMiZed UPSERT operatIon. OUtput tablE 130 ilLuStrAtEs the TAble afTer The Non-optiMized UPseRT OpERATiOn has been performed.
diSCArD of dupliCates mAY wOrK For INSERt oPerAtioNS, But noT for upSeRT operaTions, bECaUsE the inpUt DuplicAtEs aRe rEally “UPdatEs to inSerts” in tHe optIMized UPSERT opeRAtion. Mis-categORiZINg “UPdatEs tO inserts” as iNserTS couLd acTUaLly CAuse sEmantIcALlY Incorrect results in vArIous siTuatiOns.
For example, When a “RETAIn” OPEration iS useD FoR A column, if an inpUt row Is an update ROw, the RETaIN opErator foR a column iNDIcates thAt tHe eXisTinG VAlUe for that coluMN ShouLd Be kept. IF thE input rOw iS an InsErt RoW, the columN default VaLuE iS uSed. this iS A practicAl FeaTuRe iN real LIfe, for ExampLe, whErE tHE stArting dATe ON A cusToMeR recOrd, NeEds to Be reTAinEd (i.e. nevEr updated). if fOR exaMpLe, There haD been a RETAIN oPeRator on colUmN B iN outpuT TAble 100 of FIg. 1, for input duplicates (2,2) and (2,4), tHE result WouLd neeD to bE (2,2), not (2,4). CategOriZing (2,4) as An iNSertioN would Thus pIcK vaLUE 4 instEAD oF 2 foR cOlumn B, whicH WOulD be an ErRor.
ALso, aggrEgation operators (e.g., adD, MaX, MIN etc.) may be SpeCifiED To CumULaTE thE rESulTS Of a particular coLumn. One usaGe IS tO aggregate THe sAlEs columN of seveRal orDErs. AssuMing, for exAmple, that CoLumn b IN ouTput table 100 oF FIG. 1, uses The ADD aggREgatiON oPeratOr, iNput vaLuEs fOr colUmn B, woULd aDd to eXistinG vAlues oF coluMn b in outpuT
 change of semantics for i nput row ( 2,4).  In put  r ow ( 2,4)  had been init i ally  categorized as an ins ert r ow ,  rat h er  than  as anu pd a t e r ow ,byth e  o ptimi zed  UPSERT  operation . T he  optimized U P SE RT operati ondiscards the  in put du pl ica t es, e xce pt fo r thel ast on e. That i s, the op t imizedi n de x bu ilding for UPSERT op e rations wouldhave r et a in e d  th e l ast inputro w and discard e dt h e  pr e vious rows as  duplicates toretain  t hes emanti cs of  a n  UP SERT (‘upda te’)  where th e last changei s prese rved.Onthe  oth e rha nd,  t h e o p ti miz e d i ndex ins er ti ng fo r IN S E R T  ope rat ions  woul d have retain edthef irs t inp ut ro w an ddisca rded t he re st  as duplicates.  So,  for this  ex am ple ,the l a st row  (2 ,4)  wouldhave be e n i ns e r t ed , and the previous  r o w  ( 2,2) wou ld hav e  b ee n  discard ed  to  a d i s cardfile by  the opt imized UP SE RT oper at ion. O ut put  ta ble 1 3 0 il lustra tes thetable after the non- o ptimized UPSE R To p er a tion  ha s been perf orme d .
Di scar d  o f d u plica tes m ay wo r k for INSERT operat io ns, bu t not  for UPSERT o perations, b e cause th e in p ut duplicates are  real ly “update s  to inse rts”in the o ptimizedU P SERT ope rat ion . M is- c a te gorizing “upd a t es t oinserts ” a s inser tscou ldact ua lly cause  semanti ca ll yin cor rectr esults i nvar io ussitua t ions.For e xamp le ,w hen  a “RET A IN ” oper at io n is  us ed  fora co l umn , if an  input ro w i s  anup da te row,  the RETAIN o pe rator foracol umn in d i cates th at the existing value f o r thatcol umn s houl d be kept . I f theinp u t rowis aninser trow , the c o l um n d ef ault value i s u sed.Th is i s a pra ctical feature inr eal  life, for ex amp le,w h er e t h es tar ti n g d a t e on a customer  record, n ee d sto be reta i ned  ( i.e. ne ver upd ated) .  If for  example,  there ha dbeen a  RE TAIN opera tor on c olumn B i n  outp u ttable  10 0 of F IG . 1 , for  input dup licat es (2, 2)  and ( 2,4),  t he resul t would need to be (2,2 ), not  (2,4 ).Categoriz ing (2, 4) as aninse rtion woul d t hus  pick  va l ue 4inst e ad  of 2 for  col u mn B, whi c hwou l d  b e an error. A l so,  aggr ega t ion op erat ors (e.g., ADD, M A X, MIN etc.) m ay b e spe cif i ed t ocumulate the r esu lt s of a par ti cular colum n. One u sa g e isto agg regate  the sa l e sc olumnof s eve ral order s.As s uming,fo re xample , th at  colum n B in outp u t  table 100 of FI G. 1, u ses t h e A DD ag gr egation oper ator, inpu t values fo r colu mn B , wou ld addto  exist ing  v alues of c o lumn B in  outp ut
 change_of semantics_for input row (2,4)._Input row_(2,4)_had been_initially_categorized as an_insert row, rather_than as an update_row, by the_optimized_UPSERT operation. The optimized UPSERT operation discards the input duplicates, except for the last_one._That is,_the_optimized_index building for UPSERT operations_would have retained the last_input row_and discarded the previous rows as duplicates to_retain_the semantics of_an UPSERT (‘update’) where the last change is preserved._On the other hand, the optimized_index inserting for_INSERT_operations_would have retained the_first input row and discarded the_rest as duplicates. So, for this_example, the last row (2,4) would have_been inserted, and the previous row_(2,2) would have been discarded_to a_discard file by the optimized_UPSERT operation. Output_table 130_illustrates the table_after the non-optimized UPSERT operation has_been performed.
Discard of_duplicates may work for INSERT operations,_but_not for UPSERT_operations,_because_the input_duplicates are really_“updates_to inserts”_in_the optimized UPSERT operation. Mis-categorizing “updates_to_inserts” as inserts could actually cause semantically_incorrect results in various_situations.
For_example, when a “RETAIN”_operation is used for a_column, if an input row is_an update_row, the_RETAIN operator for a column indicates that the existing value for_that column should be kept. If_the input row is_an insert_row,_the column default_value_is used._This is a practical feature in real_life, for_example, where the starting date on_a customer record, needs_to_be retained (i.e. never updated). If_for example, there had been a_RETAIN operator on column B_in_output_table 100 of FIG. 1,_for input duplicates (2,2) and (2,4),_the result would_need to be (2,2), not (2,4). Categorizing_(2,4)_as an insertion would thus pick_value_4 instead of 2 for column_B,_which_would be an error.
Also, aggregation_operators (e.g., ADD, MAX, MIN etc.)_may be specified to cumulate the results of a_particular column. One_usage is to aggregate the_sales_column_of several orders. Assuming, for example, that column B in_output table_100 of FIG._1, uses the ADD aggregation operator, input values for column_B, would add to existing values of_column B in output