Mixing
Query becoming non-selective with null values
Clash Royale CLAN TAG#URR8PPP
.everyoneloves__top-leaderboard:empty,.everyoneloves__mid-leaderboard:empty margin-bottom:0;
up vote
2
down vote
favorite
In the query I asked here - Confirm if sub-query is selective? , is there a chance of the top level query becoming non-selective if conAccMap.values() has null values?
Below is the query:
[SELECT Id, (SELECT Id, Active_opp__c
FROM Opportunities
ORDER BY Days_from_Last_Stage__c ASC)
FROM Account
WHERE Id IN: conAccMap.values()]
soql before-trigger
edited Aug 7 at 22:15
Derek F
17.4k31344
asked Aug 7 at 21:37
Krishna
878
add a comment |Â
up vote
2
down vote
favorite
In the query I asked here - Confirm if sub-query is selective? , is there a chance of the top level query becoming non-selective if conAccMap.values() has null values?
Below is the query:
[SELECT Id, (SELECT Id, Active_opp__c
FROM Opportunities
ORDER BY Days_from_Last_Stage__c ASC)
FROM Account
WHERE Id IN: conAccMap.values()]
soql before-trigger
edited Aug 7 at 22:15
Derek F
17.4k31344
asked Aug 7 at 21:37
Krishna
878
It'd be nice if you would copy/paste the query you're asking about into this question. There is little that is more frustrating than clicking an endless series of links (looking at you, Microsoft knowledge base)
– Derek F
Aug 7 at 22:09
add a comment |Â
up vote
2
down vote
favorite
up vote
2
down vote
favorite
In the query I asked here - Confirm if sub-query is selective? , is there a chance of the top level query becoming non-selective if conAccMap.values() has null values?
Below is the query:
[SELECT Id, (SELECT Id, Active_opp__c
FROM Opportunities
ORDER BY Days_from_Last_Stage__c ASC)
FROM Account
WHERE Id IN: conAccMap.values()]
soql before-trigger
edited Aug 7 at 22:15
Derek F
17.4k31344
asked Aug 7 at 21:37
Krishna
878
In the query I asked here - Confirm if sub-query is selective? , is there a chance of the top level query becoming non-selective if conAccMap.values() has null values?
Below is the query:
[SELECT Id, (SELECT Id, Active_opp__c
FROM Opportunities
ORDER BY Days_from_Last_Stage__c ASC)
FROM Account
WHERE Id IN: conAccMap.values()]
soql before-trigger
edited Aug 7 at 22:15
Derek F
17.4k31344
asked Aug 7 at 21:37
Krishna
878
edited Aug 7 at 22:15
Derek F
17.4k31344
edited Aug 7 at 22:15
Derek F
17.4k31344
edited Aug 7 at 22:15
Derek F
17.4k31344
17.4k31344
asked Aug 7 at 21:37
Krishna
878
asked Aug 7 at 21:37
Krishna
878
asked Aug 7 at 21:37
Krishna
878
878
It'd be nice if you would copy/paste the query you're asking about into this question. There is little that is more frustrating than clicking an endless series of links (looking at you, Microsoft knowledge base)
– Derek F
Aug 7 at 22:09
add a comment |Â
It'd be nice if you would copy/paste the query you're asking about into this question. There is little that is more frustrating than clicking an endless series of links (looking at you, Microsoft knowledge base)
– Derek F
Aug 7 at 22:09
It'd be nice if you would copy/paste the query you're asking about into this question. There is little that is more frustrating than clicking an endless series of links (looking at you, Microsoft knowledge base)
– Derek F
Aug 7 at 22:09
It'd be nice if you would copy/paste the query you're asking about into this question. There is little that is more frustrating than clicking an endless series of links (looking at you, Microsoft knowledge base)
– Derek F
Aug 7 at 22:09
add a comment |Â
2 Answers
2
active
oldest
votes
up vote
4
down vote
accepted
Id fields are always indexed. It's not possible to get a non-selective query except in some esoteric cases where you have really huge data skew or lots of null values, but you'd almost certainly run into other limits (e.g. the 50,000 row limit) first.
In addition, trying to filter for a field with null values when the field cannot possibly be null (Master-Detail fields, Id fields, Universally Required fields), null values are silently ignored in the query. Trying to generate a query plan for your query when using a null value returns the following query plan:
There is no query to execute, as the query planner has determined that the query is impossible and will return 0 rows. You don't need to worry about null values in that case.
There are situations where it makes a difference; if NULL is legal value in the field, then the query plan must assume a TableScan:
If you're ever in doubt, check the query plan tool. It will tell you if a query is potentially non-selective. You'll find this in the Developer Console, on the Query tab. Just put a query in the box, and click on the Query Plan button.
If the top entry in the Query Plan is TableScan, you are at risk for a non-selective query. If the top-level plan is Index or Other, you should be okay in all normal situations.
answered Aug 7 at 22:33
sfdcfox
224k10170384
when i run the query plan with Id = null, , I see following message - " Not considering filter for optimization because unindexed. Table: Account Fields: ["IsDeleted"]". Does this mean an issue?
– Krishna
Aug 7 at 23:27
1
@Krishna Not usually. You just need to pay attention to the first row of the query plan, because that's what will execute.
– sfdcfox
Aug 7 at 23:48
I do not see any rows in the plan. Does this mean the query will not execute? I see only the message in the bottom section. I am unable to paste the image somehow please excuse.
– Krishna
Aug 8 at 0:04
1
@Krishna Yes, that's correct. No plan rows means no results would be returned.
– sfdcfox
Aug 8 at 0:25
add a comment |Â
up vote
0
down vote
I don't think that would be the case. If conAcMap is null you'd get attempt to de-reference a null object. If it simply has a "null" as a value, that would look for a record who's Id = null and no records would match this criteria. It'd be more like making it the most selective query imaginable (SELECT Id FROM Account WHERE TRUE = FALSE).
Whether or not Salesforce adhere's to this same logic I can't say. I have done several queries like this where the list has a "null" value in it without any issues.
Also, you seem to be asking a lot of hypotheticals which isn't a terribly bad thing; however, I'd always recommend trying it out first to see what happens. Very often you can answer these questions for yourself.
answered Aug 7 at 21:53
gNerb
4,679532
@sfdcnerd Any inputs for me here? Thanks.
– Krishna
Aug 7 at 22:09
add a comment |Â
2 Answers
2
active
oldest
votes
2 Answers
2
active
oldest
votes
active
oldest
votes
active
oldest
votes
up vote
4
down vote
accepted
Id fields are always indexed. It's not possible to get a non-selective query except in some esoteric cases where you have really huge data skew or lots of null values, but you'd almost certainly run into other limits (e.g. the 50,000 row limit) first.
In addition, trying to filter for a field with null values when the field cannot possibly be null (Master-Detail fields, Id fields, Universally Required fields), null values are silently ignored in the query. Trying to generate a query plan for your query when using a null value returns the following query plan:
There is no query to execute, as the query planner has determined that the query is impossible and will return 0 rows. You don't need to worry about null values in that case.
There are situations where it makes a difference; if NULL is legal value in the field, then the query plan must assume a TableScan:
If you're ever in doubt, check the query plan tool. It will tell you if a query is potentially non-selective. You'll find this in the Developer Console, on the Query tab. Just put a query in the box, and click on the Query Plan button.
If the top entry in the Query Plan is TableScan, you are at risk for a non-selective query. If the top-level plan is Index or Other, you should be okay in all normal situations.
answered Aug 7 at 22:33
sfdcfox
224k10170384
when i run the query plan with Id = null, , I see following message - " Not considering filter for optimization because unindexed. Table: Account Fields: ["IsDeleted"]". Does this mean an issue?
– Krishna
Aug 7 at 23:27
1
@Krishna Not usually. You just need to pay attention to the first row of the query plan, because that's what will execute.
– sfdcfox
Aug 7 at 23:48
I do not see any rows in the plan. Does this mean the query will not execute? I see only the message in the bottom section. I am unable to paste the image somehow please excuse.
– Krishna
Aug 8 at 0:04
1
@Krishna Yes, that's correct. No plan rows means no results would be returned.
– sfdcfox
Aug 8 at 0:25
add a comment |Â
up vote
4
down vote
accepted
Id fields are always indexed. It's not possible to get a non-selective query except in some esoteric cases where you have really huge data skew or lots of null values, but you'd almost certainly run into other limits (e.g. the 50,000 row limit) first.
In addition, trying to filter for a field with null values when the field cannot possibly be null (Master-Detail fields, Id fields, Universally Required fields), null values are silently ignored in the query. Trying to generate a query plan for your query when using a null value returns the following query plan:
There is no query to execute, as the query planner has determined that the query is impossible and will return 0 rows. You don't need to worry about null values in that case.
There are situations where it makes a difference; if NULL is legal value in the field, then the query plan must assume a TableScan:
If you're ever in doubt, check the query plan tool. It will tell you if a query is potentially non-selective. You'll find this in the Developer Console, on the Query tab. Just put a query in the box, and click on the Query Plan button.
If the top entry in the Query Plan is TableScan, you are at risk for a non-selective query. If the top-level plan is Index or Other, you should be okay in all normal situations.
answered Aug 7 at 22:33
sfdcfox
224k10170384
when i run the query plan with Id = null, , I see following message - " Not considering filter for optimization because unindexed. Table: Account Fields: ["IsDeleted"]". Does this mean an issue?
– Krishna
Aug 7 at 23:27
1
@Krishna Not usually. You just need to pay attention to the first row of the query plan, because that's what will execute.
– sfdcfox
Aug 7 at 23:48
I do not see any rows in the plan. Does this mean the query will not execute? I see only the message in the bottom section. I am unable to paste the image somehow please excuse.
– Krishna
Aug 8 at 0:04
1
@Krishna Yes, that's correct. No plan rows means no results would be returned.
– sfdcfox
Aug 8 at 0:25
add a comment |Â
up vote
4
down vote
accepted
up vote
4
down vote
accepted
Id fields are always indexed. It's not possible to get a non-selective query except in some esoteric cases where you have really huge data skew or lots of null values, but you'd almost certainly run into other limits (e.g. the 50,000 row limit) first.
In addition, trying to filter for a field with null values when the field cannot possibly be null (Master-Detail fields, Id fields, Universally Required fields), null values are silently ignored in the query. Trying to generate a query plan for your query when using a null value returns the following query plan:
There is no query to execute, as the query planner has determined that the query is impossible and will return 0 rows. You don't need to worry about null values in that case.
There are situations where it makes a difference; if NULL is legal value in the field, then the query plan must assume a TableScan:
If you're ever in doubt, check the query plan tool. It will tell you if a query is potentially non-selective. You'll find this in the Developer Console, on the Query tab. Just put a query in the box, and click on the Query Plan button.
If the top entry in the Query Plan is TableScan, you are at risk for a non-selective query. If the top-level plan is Index or Other, you should be okay in all normal situations.
answered Aug 7 at 22:33
sfdcfox
224k10170384
Id fields are always indexed. It's not possible to get a non-selective query except in some esoteric cases where you have really huge data skew or lots of null values, but you'd almost certainly run into other limits (e.g. the 50,000 row limit) first.
In addition, trying to filter for a field with null values when the field cannot possibly be null (Master-Detail fields, Id fields, Universally Required fields), null values are silently ignored in the query. Trying to generate a query plan for your query when using a null value returns the following query plan:
There is no query to execute, as the query planner has determined that the query is impossible and will return 0 rows. You don't need to worry about null values in that case.
There are situations where it makes a difference; if NULL is legal value in the field, then the query plan must assume a TableScan:
If you're ever in doubt, check the query plan tool. It will tell you if a query is potentially non-selective. You'll find this in the Developer Console, on the Query tab. Just put a query in the box, and click on the Query Plan button.
If the top entry in the Query Plan is TableScan, you are at risk for a non-selective query. If the top-level plan is Index or Other, you should be okay in all normal situations.
answered Aug 7 at 22:33
sfdcfox
224k10170384
edited Aug 7 at 22:39
answered Aug 7 at 22:33
sfdcfox
224k10170384
answered Aug 7 at 22:33
sfdcfox
224k10170384
answered Aug 7 at 22:33
sfdcfox
224k10170384
224k10170384
when i run the query plan with Id = null, , I see following message - " Not considering filter for optimization because unindexed. Table: Account Fields: ["IsDeleted"]". Does this mean an issue?
– Krishna
Aug 7 at 23:27
1
@Krishna Not usually. You just need to pay attention to the first row of the query plan, because that's what will execute.
– sfdcfox
Aug 7 at 23:48
I do not see any rows in the plan. Does this mean the query will not execute? I see only the message in the bottom section. I am unable to paste the image somehow please excuse.
– Krishna
Aug 8 at 0:04
1
@Krishna Yes, that's correct. No plan rows means no results would be returned.
– sfdcfox
Aug 8 at 0:25
add a comment |Â
when i run the query plan with Id = null, , I see following message - " Not considering filter for optimization because unindexed. Table: Account Fields: ["IsDeleted"]". Does this mean an issue?
– Krishna
Aug 7 at 23:27
1
@Krishna Not usually. You just need to pay attention to the first row of the query plan, because that's what will execute.
– sfdcfox
Aug 7 at 23:48
I do not see any rows in the plan. Does this mean the query will not execute? I see only the message in the bottom section. I am unable to paste the image somehow please excuse.
– Krishna
Aug 8 at 0:04
1
@Krishna Yes, that's correct. No plan rows means no results would be returned.
– sfdcfox
Aug 8 at 0:25
when i run the query plan with Id = null, , I see following message - " Not considering filter for optimization because unindexed. Table: Account Fields: ["IsDeleted"]". Does this mean an issue?
– Krishna
Aug 7 at 23:27
when i run the query plan with Id = null, , I see following message - " Not considering filter for optimization because unindexed. Table: Account Fields: ["IsDeleted"]". Does this mean an issue?
– Krishna
Aug 7 at 23:27
1
1
@Krishna Not usually. You just need to pay attention to the first row of the query plan, because that's what will execute.
– sfdcfox
Aug 7 at 23:48
@Krishna Not usually. You just need to pay attention to the first row of the query plan, because that's what will execute.
– sfdcfox
Aug 7 at 23:48
I do not see any rows in the plan. Does this mean the query will not execute? I see only the message in the bottom section. I am unable to paste the image somehow please excuse.
– Krishna
Aug 8 at 0:04
I do not see any rows in the plan. Does this mean the query will not execute? I see only the message in the bottom section. I am unable to paste the image somehow please excuse.
– Krishna
Aug 8 at 0:04
1
1
@Krishna Yes, that's correct. No plan rows means no results would be returned.
– sfdcfox
Aug 8 at 0:25
@Krishna Yes, that's correct. No plan rows means no results would be returned.
– sfdcfox
Aug 8 at 0:25
add a comment |Â
up vote
0
down vote
I don't think that would be the case. If conAcMap is null you'd get attempt to de-reference a null object. If it simply has a "null" as a value, that would look for a record who's Id = null and no records would match this criteria. It'd be more like making it the most selective query imaginable (SELECT Id FROM Account WHERE TRUE = FALSE).
Whether or not Salesforce adhere's to this same logic I can't say. I have done several queries like this where the list has a "null" value in it without any issues.
Also, you seem to be asking a lot of hypotheticals which isn't a terribly bad thing; however, I'd always recommend trying it out first to see what happens. Very often you can answer these questions for yourself.
answered Aug 7 at 21:53
gNerb
4,679532
@sfdcnerd Any inputs for me here? Thanks.
– Krishna
Aug 7 at 22:09
add a comment |Â
up vote
0
down vote
I don't think that would be the case. If conAcMap is null you'd get attempt to de-reference a null object. If it simply has a "null" as a value, that would look for a record who's Id = null and no records would match this criteria. It'd be more like making it the most selective query imaginable (SELECT Id FROM Account WHERE TRUE = FALSE).
Whether or not Salesforce adhere's to this same logic I can't say. I have done several queries like this where the list has a "null" value in it without any issues.
Also, you seem to be asking a lot of hypotheticals which isn't a terribly bad thing; however, I'd always recommend trying it out first to see what happens. Very often you can answer these questions for yourself.
answered Aug 7 at 21:53
gNerb
4,679532
@sfdcnerd Any inputs for me here? Thanks.
– Krishna
Aug 7 at 22:09
add a comment |Â
up vote
0
down vote
up vote
0
down vote
I don't think that would be the case. If conAcMap is null you'd get attempt to de-reference a null object. If it simply has a "null" as a value, that would look for a record who's Id = null and no records would match this criteria. It'd be more like making it the most selective query imaginable (SELECT Id FROM Account WHERE TRUE = FALSE).
Whether or not Salesforce adhere's to this same logic I can't say. I have done several queries like this where the list has a "null" value in it without any issues.
Also, you seem to be asking a lot of hypotheticals which isn't a terribly bad thing; however, I'd always recommend trying it out first to see what happens. Very often you can answer these questions for yourself.
answered Aug 7 at 21:53
gNerb
4,679532
I don't think that would be the case. If conAcMap is null you'd get attempt to de-reference a null object. If it simply has a "null" as a value, that would look for a record who's Id = null and no records would match this criteria. It'd be more like making it the most selective query imaginable (SELECT Id FROM Account WHERE TRUE = FALSE).
Whether or not Salesforce adhere's to this same logic I can't say. I have done several queries like this where the list has a "null" value in it without any issues.
Also, you seem to be asking a lot of hypotheticals which isn't a terribly bad thing; however, I'd always recommend trying it out first to see what happens. Very often you can answer these questions for yourself.
answered Aug 7 at 21:53
gNerb
4,679532
answered Aug 7 at 21:53
gNerb
4,679532
answered Aug 7 at 21:53
gNerb
4,679532
answered Aug 7 at 21:53
gNerb
4,679532
4,679532
@sfdcnerd Any inputs for me here? Thanks.
– Krishna
Aug 7 at 22:09
add a comment |Â
@sfdcnerd Any inputs for me here? Thanks.
– Krishna
Aug 7 at 22:09
@sfdcnerd Any inputs for me here? Thanks.
– Krishna
Aug 7 at 22:09
@sfdcnerd Any inputs for me here? Thanks.
– Krishna
Aug 7 at 22:09
add a comment |Â
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
StackExchange.ready(
function ()
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fsalesforce.stackexchange.com%2fquestions%2f228075%2fquery-becoming-non-selective-with-null-values%23new-answer', 'question_page');
);
Post as a guest
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
It'd be nice if you would copy/paste the query you're asking about into this question. There is little that is more frustrating than clicking an endless series of links (looking at you, Microsoft knowledge base)
– Derek F
Aug 7 at 22:09