+ Start a Discussion

Non-selective query against large object type (more than 100000 rows).

Error: System.DmlException: Insert failed. First exception on row 0; first error: CANNOT_INSERT_UPDATE_ACTIVATE_ENTITY, trgCaptureConsultants: execution of BeforeInsert caused by: System.QueryException: Non-selective query against large object type (more than 100000 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) Trigger.trgCaptureConsultants: line 97, column 1: [] (System Code)




can u post the query?




1 . Ask Salesforce to enable custom indexing on the field.

2. if ur using formula field in query make it as external id.

3.add more conditions in where statement.



this may be reduce ur problem....... 


Hi Subra,

My Query:


opportunityLineItem[] OppLineItems = [SELECT PricebookEntry.Product2Id,PricebookEntry.Product2.Family
FROM OpportunityLineItem where Opportunity.Id =: Trigger.new and PricebookEntry.Product2.Family !=: 'Travel' and opportunity.id != null and Opportunity.StageName='Closed' and Opportunity.CreatedDate >2013-10-10T00:00:00Z];


We did with 3rd Statement.

We dont have any formula field in query.

While coming to 1st Statement is this nessary to ask Salesforce to make custom index ?


U need to adjust the query.......use debug log and check how many records it returned.

Opportunity Id is already indexed...

u cant use indexing when u r using != operator.


So pls make product2.family and opp.stagename as external field(if not make it as custom indexing through salesforce)..this will avoid returning the duplicate records and check whether any deleted records in recycle bin(if yes means clean that)




The main problem is the " != 'Travel' " part of the query.  This makes the query non-selective.  The solution is to break the query into two queries.  First, get all of the Product2 records for which "Family != 'Travel'", and then use that list to filter the PricebookEntry.Product2Id in the OpportunityLineItem query:


List<Product2> NonTravelProducts =
    [   SELECT  Id
        FROM    Product2
        WHERE   Family != 'Travel'

List<OpportunityLineItem> OppLineItems =
    [   SELECT  PricebookEntry.Product2Id, PricebookEntry.Product2.Family
        FROM    OpportunityLineItem
        WHERE   (   Opportunity.Id IN :Trigger.new
                AND PricebookEntry.Product2Id IN :NonTravelProducts
                AND Opportunity.StageName = 'Closed'
                AND Opportunity.CreatedDate > 2013-10-10T00:00:00Z

Note that I also removed the check for "Opportunity.Id != null", because you already require Opportunity.Id to be in Trigger.new - there will not be any null values in there.


If this helps, please mark it as a solution, and give kudos (click on the star) if you think I deserve them. Thanks!


-Glyn Anderson
Certified Salesforce Developer | Certified Salesforce Administrator