Non-selective query exception when attempting to query the standard Name field with case-insensitive encryption and over 200,000 records
Last updated 2021-10-12 ·Reference W-6981742 ·Reported By 4 users
Querying the standard Name field on an object with over 200,000 records returns the following error when encrypted using case-insensitive encryption and included within a custom index:
System.QueryException: Non-selective query against large object type (more than 200000 rows). Consider an indexed filter or contact salesforce.com about custom indexing.
1) Load 200,000+ records into a standard or custom object, which includes data for the standard Name field of that object.
2) Ensure the standard Name field is then put into a custom index to allow for querying of the large data set.
3) Encrypt the Name field with case-insensitive encryption.
4) Attempt to query the Name field and receive System.QueryException error.
- Use the case-sensitive encryption scheme for the Name field.
- Copy the data within the Name field to a custom field which can then be put into a custom index.
- Use another (non-Name) query term that is backed by an index and provides good selectivity. This can be used in conjunction with the Name fields (e.g. "email@example.com AND name='Alice') as long as the email column is supported by an index.
Is it Fixed?
Any unreleased services, features, statuses, or dates referenced in this or other public statements are not currently available and may not be delivered on time or at all. Customers who purchase our services should make their purchase decisions based upon features that are currently available.