Data skew exists whereby the number of matching rows is very large

Question

I am getting the following Error. works fine in DEV org but throwing error in UAT. Please suggest me as I was unable to move forward.

The Actual Error is

The first validation error encountered was "Apex trigger OpportunityObject caused an unexpected exception, contact your administrator: OpportunityObject: execution of BeforeUpdate caused by: System.QueryException: Non-selective query against large object type (more than 200000 rows). Consider an indexed filter or contact salesforce.com about custom indexing. Even if a field is indexed a filter might still not be selective when: 1. The filter value includes null (for instance binding with a list that contains null) 2. Data skew exists whereby the number of matching rows is very large (for instance, filtering for a particular foreign key value that occurs many times): Class.OpportunityValidator.ValidateOCR_OnOppStageChange: line 14, column 1".

public class OpportunityValidator {
     public static void ValidateOCR_OnOppStageChange(List < Opportunity > newOppsForUpdate, Map < Id, Opportunity > mapOldOpps) {

      if (Validator_cls.isOCRfirstRun()) {
       Map < String, OCRValidationRequiredRecordTypes__c > mapOCRRequiredRecordTypes = OCRValidationRequiredRecordTypes__c.getAll();
       Map < String, OCRValidationRequiredStages__c > mapOCRRequiredStages = OCRValidationRequiredStages__c.getAll();
       //Map < Id, String > mapRecordtypeIdName = new Map< Id, String> ();
       Map < Id, Opportunity > mapIdOppRecords = new Map < Id, Opportunity > ();
       for (Opportunity Op: [SELECT Id,OppLineItemsCount__c,  (SELECT Id FROM Opportunitylineitems) FROM Opportunity WHERE
         StageName IN: mapOCRRequiredStages.keySet() AND Status__c != 'Closed'
         AND
         Opportunity.recordtype.Name IN: mapOCRRequiredRecordTypes.keyset() AND
         Id NOT IN(SELECT Opportunityid FROM OpportunityContactRole) AND Id IN: newOppsForUpdate
        ]) {
        if ((mapOldOpps.get(Op.id).OppLineItemsCount__c == op.Opportunitylineitems.size() && op.opportunitylineitems.size() > 0)) {
         system.debug('----Inside If------OCR');
         mapIdOppRecords.put(op.id, op);
        }
       }
       for (opportunity Op: newOppsForUpdate) {
        system.debug('Op.recordtype: ' + Op);
        if (mapIdOppRecords.get(Op.Id) != NULL) {
         Op.addError(' Contact Role is Mandatory ');
        }
       }

      } // End of isOCRfirstRun If condition
     } // End of ValidateOCR_OnOppStageChange
    } // End of OpportunityValidator

Answer 1

You only have one query, so the culprit is pretty clear:

SELECT Id,OppLineItemsCount__c,  (SELECT Id FROM Opportunitylineitems) 
FROM Opportunity 
WHERE StageName IN: mapOCRRequiredStages.keySet() 
     AND Status__c != 'Closed'
     AND Opportunity.recordtype.Name IN: mapOCRRequiredRecordTypes.keyset() 
     AND Id NOT IN (SELECT Opportunityid FROM OpportunityContactRole) 
     AND Id IN: newOppsForUpdate

The error message is telling you that this query is not being treated as selective; the platform will always terminate non-selective queries executed against objects with data volume > 200,000 records when run in trigger context (directly or indirectly). This helps preserve performance of the trigger and shorten transaction time.

You should take some time to read Make SOQL query selective from the knowledge base, as well as the articles linked from there. Selectivity can be complex to figure out and can depend as well on the content of the variables being bound in the query and the configuration of the database, such as whether specific fields are indexed or External Ids.

My theory is that since your Id IN :newOppsForUpdate should be selective (assuming that collection contains no nulls and is of reasonable size), it's actually the unfiltered semi-join subquery getting you in trouble:

AND Id NOT IN (SELECT Opportunityid FROM OpportunityContactRole) 

Presumably there's more than 200,000 records in OpportunityContactRole, making this subquery non-selective.

I may be wrong; there's a lot of complexity to LDV SOQL optimization and I'm not a great expert in it.

However, I think there is a quite easy fix if my surmise is correct or close. Assuming, again, that newOppsForUpdate is of reasonable size (I'm presuming it comes from a trigger), run some of these filters in Apex rather than SOQL.

In particular, I would suggest you use a subquery to get the OpportunityContactRoles list for each queried Opportunity, in the same way that you already do with OpportunityLineItems. You would delete your ID NOT IN() clause.

You can then write Apex logic within the loop to exclude those Opportunity records having them, rather than using a semi-join. For example, inside the loop you might check if (Op.OpportunityContactRoles.size() > 0) continue; to skip those records.

I suspect that will cure the issue. I can't promise it, but it's a reasonable guess.

Answer 2

Just to add few points to David's answer, I would mention some extra things

SELECT Id,OppLineItemsCount__c,  (SELECT Id FROM Opportunitylineitems) 
FROM Opportunity 
WHERE StageName IN: mapOCRRequiredStages.keySet() 
     AND Status__c != 'Closed'
     AND Opportunity.recordtype.Name IN: mapOCRRequiredRecordTypes.keyset() 
     AND Id NOT IN (SELECT Opportunityid FROM OpportunityContactRole) 
     AND Id IN: newOppsForUpdate

!= SOQL is very expensive, thus Status__c != 'Closed' is slowing things down.

Instead, create a Custom Formula Field isStatusClosed__c as Boolean and ask Salesforce Support to Index it for you. As its deterministic formula, they will be able to index it.

Also Opportunity.recordtype.Name IN: mapOCRRequiredRecordTypes.keyset() is again a join, instead in SOQL use recordTypeId

Opportunity.recordtypeId IN: mapOCRRequiredRecordTypesId.keyset()