I have a table with some 50 column count. Every time there is change in row I do not have a knowledge that which columns will change. I don't want to deal with each and every permutation and combination when updating the table.
So when I have to do so I am updating all 50 columns and which, I know, takes much more time than my expectation when dealing with huge number of updates.
- To address this I have one solution. Create different set of frequently and together updated fields and design my application that way. Which I know will require change whenever new field is added to my table.
UPDATE TBLX SET NAME = ? WHERE ID = ?;
Result of Explain Update...
UPDATE
INDEX SCAN of "TBLX" using "TBLX_ID"
scan matches for (U_ID = ?5), filter by (column#0 = ?6)
- Another approach is that I write a query with when and then(as shown below). This way my code will need update but not as much as it might require in first approach.
UPDATE TBLX SET NAME = CASE WHEN (? != '####') THEN ? ELSE NAME END WHERE ID = ?;
Result of Explain Update...
UPDATE
INDEX SCAN of "TBLX" using "TBLX_ID"
scan matches for (U_ID = ?3), filter by (column#0 = ?4)
So my question is about the internal of the query execution. How both type of the query will be treated and which one will work faster.
Thing I want to understand is whether executor will ignore the part of the query where I am not changing value in column. i.e. assign same value to the column.