3

I'm facing trouble in understanding salesforce documentation, about selectivity threshold if SOQL uses two fields in where clause with 'OR' Condition. For e.g.

Select id from account where FieldA = 'SomeValue' or FieldB = 'SomeotherValue' ;

Consider both are index fields.

Now I've two versions from 2 Salesforce documentation- if query optimizer consider above fields for indexing or not.

This docuement says:

For OR, the query optimizer uses the indexes unless they all return more than 10% of the object’s records or 333,333 total records.

Key word is 'all'. From above I'm getting, it is fine, if one of them return more than 10% records BUT if 2nd field return less than 10% of records, then index would be used.

but here is another documentation, which says :

When using an OR comparison, all filters must be indexed and under the 10% threshold. . If you have a non-indexed field or one is above 10%, the plan will not be displayed.

This I think, mean to say, both fields should be indexed and both should be returning less than 10% of records.

Which one is correct or If I'm not understanding it properly ?

0

I suspect you're not understanding the documentation and the concepts properly. With indexed fields, there shouldn't be any special issues to consider unless you have extremely large volumes of data. The issues then would not be related to what you're asking about, but instead the query method. If one of the fields were not indexed, the question would generally be one of whether the query plan would indicate you need to have Salesforce create a skinny table for you to perform the query in a performant manner.

I recognize that doesn't answer your specific question, but I couldn't seem to easily locate the page where you quoted from in the PDF referenced. I believe you're asking under what circumstances you'll be able to get the query optimizer to generate a query plan and display it for you. It doesn't apply to the scenario you've described since both fields are indexed making the query with either one selective. Regardless of whether there's an OR or and AND, you'll automatically have a selective query. When both fields are indexed and they're not boolean fields or tests for null values, you've already got optimal query "where" conditions.

  • Hi crmprogdev.. thanks for responding back.. Yes, please consider LDV scenario. You mentioned, query plan will be displayed with either one selective, but that is not the 2nd help link says- it mentions, both of index(es) should be selective, only then plan will be displayed. – curious_techy Jun 24 at 13:19
  • @curious_techy In your scenario, with both fields being indexed, both indexes ARE selective. In the OR condition, with both being selective, either field would cause the query to be selective. A plan is not displayed because nothing can be done to make the query more selective. If one of the fields were not indexed, then you could expect to see the query plan displayed in LDV scenario. – crmprogdev Jun 24 at 17:34

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.