function readOnly(count){ }
Sign Up ›
Starting November 20, the site will be set to read-only. On December 4, 2023,
forum discussions will move to the Trailblazer Community.
Learn More

Browse by Topic
ShowAll Questionssorted byDate Posted
+ Start a Discussion
developer03developer03 

Non-selective query problem

Hi,

I have created a trigger that will fire on Opportunity after update, insert and delete events. It will determine how many Opportunities are outstanding and update the associated account with the outstanding count.

This is the WHERE clause of the query:

((Opportunity.StageName = '5 - Offer by Client (Conditional)' OR Opportunity.StageName = '6 - Offer by Client (Unconditional)' OR Opportunity.StageName = '7 - Preliminary Accept' OR Opportunity.StageName = 'Deferred by Applicant') AND Opportunity.Course_Start_Date__c <= TODAY AND Opportunity.PSR__c = false AND Opportunity.InstituitionAccount__c = :accountId)

The problem is the following exception gets thrown:
System.QueryException: Non-selective query against large object type (more than 100000 rows). Consider an indexed filter or contact salesforce.com about custom indexing.

What does this mean? How can I avoid this problem?
developer03developer03
Amazing 34 views and not a single reply?
aezellaezell
Could you post the entire query? That might help.

The error seems to indicate that your query is going to select a lot of rows. Too many, in fact. Perhaps, we can track down why with a little more detail.
developer03developer03
[SELECT count() from Opportunity Where ((Opportunity.StageName = '5 - Offer by Client (Conditional)' OR Opportunity.StageName = '6 - Offer by Client (Unconditional)' OR Opportunity.StageName = '7 - Preliminary Accept' OR Opportunity.StageName = 'Deferred by Applicant') AND Opportunity.Course_Start_Date__c <= TODAY AND Opportunity.PSR__c = false AND Opportunity.InstituitionAccount__c = :accountId)]

That is the complete query.

When I run this query in Apex Explorer I get at most 4 rows. Maybe the error is complaining Opportunity table has too many rows (it has 180,000 rows)?
aezellaezell
Instead of doing SELECT count(), try doing a SELECT count(Opportunity.id) or some other unique column. That may allow the query to run on an indexed column instead of having to look at every bit of data in that table.
developer03developer03
I have tried having unique column in count() but it does not work. The API makes no mentioned of the method you have recommended.
developer03developer03
Got this problem solved through Salesforce Support/Product Development.

Essentially trigger recieves (Trigger.new or Trigger.old) will be fully populated with data, but they won't include data from child/related objects. So if you try using child/relational value it will be null and hence non-selective query issue will be encountered.

I have been told with this upcoming 08 release attempting to read a data value that has not been queried from the DB will result in a runtime error. This will make this kind of issue much easier to diagnose.

Thanks.