Take the 2-minute tour ×
Salesforce Stack Exchange is a question and answer site for Salesforce administrators, implementation experts, developers and anybody in-between. It's 100% free, no registration required.

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)

share|improve this question
1  
I figure a reference to the docs could be usefull: salesforce.com/us/developer/docs/apexcode/Content/… –  Samuel De Rycke Dec 17 '12 at 8:17

2 Answers 2

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.
share|improve this answer
    
: my arcticheture was this was a batch class and this will be executed from a schedule class.and iam querying all this fields because before i am deleteing the records it should send a pdf. i will share my code if u want .I am executing this query in start() method. –  anil bathula Dec 17 '12 at 8:51
    
Point #4 is one that I've had to revert to. You can actually just select all Ids from the object and let the execute method do the non-selective query since it will have less records and be able to finish. –  Peter Knolle Feb 15 '13 at 14:51

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

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.