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)
Attribution to: anil bathula
Possible Suggestion/Solution #1
It's hard to say something without knowing how your data is structured...
- Can you use smaller LIMIT clause? I doubt it's actually helping in anything. Is this query within
start()
orexecute()
? - 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).
- 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)?
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?
- 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. - 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.
Attribution to: eyescream
Possible Suggestion/Solution #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
Attribution to: PepeFloyd
This content is remixed from stackoverflow or stackexchange. Please visit https://salesforce.stackexchange.com/questions/5472