Non selective query

Question

I have query in batch apex to delete records from Salesforce. The query is:

global date ext_date=System.today().addDays(-7); 

string query='select id,name,Campaign__c,Contact__c,Date_Bounced__c,Date_Time_Opened__c,Date_Time_Sent__c,Date_Unsubscribed__c,From_Address__c,From_Name__c,Lead__c,Number_of_Total_Clicks__c,Number_of_Unique_Clicks__c,Opened__c,Report_Name__c,Subject_Line__c from xtma_Individual_Email_Result__c where createddate<=:ext_date AND Date_Time_Opened__c=null limit 200000 ';

but its failing to delete the records giving this error:-

First error: SQLException [java.sql.SQLException: ORA-01013: user requested cancel of current operation : select /*ApexBatch.Class.Del_Ind_Email_Results.start: line 21*/ * from (select "Id", "Name", "Campaign_c", "Contact_c", "Date_Bounced__c", "Date...

I raised a case to Salesforce but they are saying it is non-selective and to make it selective. How do I make it selective? Date_Time_Opened__c (Date data type)

Answer 1

It's hard to say something without knowing how your data is structured...

1. Can you use smaller LIMIT clause? I doubt it's actually helping in anything. Is this query within start() or execute()?
2. Can you think of any other fields you could filter on (you have only 2 components in the query and they're not related to ID fields).
3. If you have huge backlog of unopened tickets (or whatever it is) - can you consider an one-time cleanup action to delete them all manually (ExecuteAnonymous some apex, DataLoader etc)?

4. You could try restructuring the query to something like this:

   SELECT Id, 
       (SELECT Id, Name 
       FROM xtma_Individual_Email_Result__r 
       WHERE createddate<=LAST_N_DAYS:7 AND Date_Time_Opened__c=null)
   FROM Campaign
   

   Loop through campaigns in your batch (even one at a time if you have to), examine the results of subquery and delete them for each as needed?

5. Do you have to query for all these fields (are they used later in some complex logic)? Shouldn't matter much but in all such performance-related scenarios... Try selecting only Id column.
6. Common trick to make queries selective is to apply "external id" or "unique" flag on field(s) from WHERE but I doubt it'll work nicely on Date fields.

Answer 2

Try to filter the query by fields that are indexed.

Take a look at this:

http://www.salesforce.com/us/developer/docs/apexcode/Content/langCon_apex_SOQL_VLSQ.htm