We have a trigger that gets the highest value of a custom Field "UIN__c" on various Objects and increments the value by 1 on each new Record. This Field functions as an ID and is unique.
We use following Query to get the highest value:
Integer.valueOf( database.query('select UIN__c from '+ this.name + ' order by UIN__c DESC NULLS LAST LIMIT 1 ALL ROWS ' ).get('UIN__c') );
At some point, the number of Records of some Objects reaches 200 000. As soon as this happens the logic breaks, as the query being used has to be "selective" to query a large Object. "Selective" in the sense of Salesforce. If we query without being "selective" then we get following error:
Non-selective query against large object type (more than 200000 rows)
To make the Query selective, we must add a WHERE clause and filter by an indexed Field and query less than 30% of total records. But this reduction is conflicting with our goal to get the highest UIN__c of ALL records. Any thinkable filter could exclude the highest UIN__c. We thought querying only the newest records created in the last 365 days, but that would not be bullet proof:
a) if NO record is created in the last 365 days, we would not have any result at all.
b) if more than 30% new records have been created within the last 365 days, we would get the very same error again.
Our conclusion is that ANY possible where clause would not be bullet proof. Hence the issue would not be avoidable.
Now we are looking for alternatives instead of adding a WHERE to the query. Is there another query we can use to get the highest value of a custom field on a large object? In the database could be any number of records (even millions of records are possible).
Is it worth to try aggregate queries using MAX(UID__c)?
Any other approaches?
Even ideas or conceptual fragments are welcome! We do not expect a complete solution. Also feedbacks of approaches which are obviously NOT feasible will help to rule stuff out.