Use getBestRowIdentifier() to determine the optimal set of columns to use in the Where clause for updating data. Pseudo-columns often provide the fastest access to the data, and these columns can only be determined by using getBestRowIdentifier().
Some applications cannot be designed to take advantage of positional updates and deletes. Some applications might formulate the Where clause by using all searchable result columns by calling getPrimaryKeys(), or by calling getIndexInfo() to find columns that might be part of a unique index. These methods usually work, but might result in fairly complex queries.
Consider the following example:
ResultSet WSrs = WSs.executeQuery
("SELECT first_name, last_name, ssn, address, city, state, zip
FROM emp");
// fetchdata
...
WSs.executeUpdate ("UPDATE EMP SET ADDRESS = ?
WHERE first_name = ? AND last_name = ? AND ssn = ?
AND address = ? AND city = ? AND state = ?
AND zip = ?");
// fairly complex query
Applications should call getBestRowIdentifier() to retrieve the optimal set of columns (possibly a pseudo-column) that identifies a specific record. Many databases support special columns that are not explicitly defined by the user in the table definition but are hidden columns of every table (for example, ROWID and TID). These pseudo-columns generally provide the fastest access to the data because they typically are pointers to the exact location of the record. Because pseudo-columns are not part of the explicit table definition, they are not returned from getColumns. To determine if pseudo-columns exist, call getBestRowIdentifier().
// fetch data and probably "hide" ROWID from the user
...
WSs.executeUpdate ("UPDATE emp SET address = ? WHERE ROWID = ?");
// fastest access to the data!
If your data source does not contain special pseudo-columns, then the result set of getBestRowIdentifier() consists of the columns of the most optimal unique index on the specified table (if a unique index exists). Therefore, your application does not need to call getIndexInfo to find the smallest unique index.
