+ Start a Discussion
learnSFlearnSF 

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

trigger FindMarketTerritoryFromMarket on Territory_Market__c (before insert, before update) { Map<Id,Territory_Market__c> territoryMarket= new Map<Id,Territory_Market__c>(); for(Territory_Market__c t : Trigger.new) { if(t.Parent_Market__c!=Trigger.oldMap.get(t.Id).Parent_Market__c){ territoryMarket.put(t.Id,t); System.debug(t.Parent_Market__c); } } List<Account> updateAccount = new List<Account>(); Map<Id,String> setAccount= new Map<Id,String>(); for(Id ids : territoryMarket.KeySet()){ List<Account> account = new List<Account>(); account = [Select a.Name From Account a limit 1]; } }

 

this is my code. I removed update account code from this.

 

This is simple code not doing anything yet,still it fails at account = [Select a.Name From Account a limit 1]; line.

 

It giveme error :

 

Review all error messages below to correct your data.
Apex trigger FindMarketTerritoryFromMarket caused an unexpected exception, contact your administrator: FindMarketTerritoryFromMarket: execution of BeforeUpdate 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.FindMarketTerritoryFromMarket: line 31, column 8

 

I tried to pass only not null value still same problem. Then I rmoved all where condition and trying very simple query,still didn't help.

 

Can someone let me know workaroud for this problem?

 

Thanks

paul-lmipaul-lmi
this error is thrown when you try to query a field in an object that isn't indexed, and that object has a lot of rows, or your query returns way too many rows.  take a look at all the fields you're referencing in all objects in this code, and determine if they are indexed (per documenation).  if you need a custom index, you need to contact support.
learnSFlearnSF

Hi,

 

Thanks for reply.

 

I have query on index field as well and as you see it is just limit by 1,somehow its not aallowing to do query on account from this Market Territory object.

 

Any other guess?

ahab1372ahab1372

no-selective means that you do not have a a WHERE statement in the query that would limit the results. I think the limit 1 only returns one row, the query still runs against all records.

Why is your querynot more specific? With this code you just get a more or less random result. Don't you want a particular Account?

 

Furthermore, the query is inside a for-loop which will cause problems with the governor limits. All queries should be outside loops.

Message Edited by ahab1372 on 08-27-2009 11:17 AM
learnSFlearnSF

Hi,

 

thanks for reply.

 

My actual query is below

 

List<Account> account = new List<Account>();

account = [Select a.Market_Territory__c, a.Id From Account a where

a.ShippingState!=null and a.Shipping_County__c !=null and

a.ShippingState =:territoryMarket.get(ids).Territory_State__c and

a.Shipping_County__c =:territoryMarket.get(ids).Territory_County__c and

a.RecordTypeId!=null and

(a.RecordTypeId =: recType1 or a.RecordTypeId=:recType3)];

 

But this is not working and giving me same error.

 

If I give specific account Id then it works but here I need to retrive account Id from query.

 

This query if I write inside foor loop or outside for loop ,giving me same error,

 

this query give me 150 recrod of account.

 

It would be great if you cna help me to query this account and get the result.

 

Thanks.

ahab1372ahab1372

look at tyhis thread, it explains why:

http://community.salesforce.com/sforce/board/message?board.id=apex&thread.id=2882&view=by_date_ascending&page=1

 

If your query does not filter by a field that is indexed, or if the filter uses Null, you might run in this error. One possible solution is adding an index to one of the fields by marking as "External ID" in the UI

Since your query uses a lot of null I am not sure if that helps. I don't know if the Record Type is indexed by default.

 

Btw the  a.RecordTypeId!=null and

is redundant because you are specifying two particular record types in the query so it should not retrun any accounts with record type null

 

HTH

 

Arnt

paul-lmipaul-lmi
per salesforce support, email fields do not create an index when you set the "external id" checkbox.  support has to get that set up.
Prince_sfdcPrince_sfdc
Please take a look at the below for resolved answer: 
https://sfdcspectrum.blogspot.in/2018/04/systemqueryexception-non-selective.html