Replace multiple index references in PLANs from SQL with IN() or multiple ORs with abbreviated syntax [CORE2116] #974
Comments
|
Commented by: @dyemanov What is going to happen if you provide such a plan for your SQL statement? Is it going to be accepted? What if the number of IN elements does not match the "x{n}" clause? |
|
Commented by: Sean Leyne (seanleyne) "What is going to happen if you provide such a plan for your SQL statement?" Good question! My initial thought is that this short-form PLAN would really only be used when the server returns the PLAN. Personally, I have never (15+ years using IB/FB) actually specified a PLAN for a query, so I can't image why someone would ever need to specify a plan like the one in the example. But, to the question, I don't see why the PLAN wouldn't be accepted. "What if the number of IN elements does not match the "x{n}" clause?" (You are refering to the case where the short-form PLAN is specified for a query, right?) In general, I would expect the same logic which applies to 'long-form' PLAN would apply to the short-form PLAN. BTW, What happens now if a PLAN is specified which doesn't match to the number of elements in the IN()? |
|
Commented by: @asfernandes I think we don't even need the {n}. Just one time, for IN and ORs. It seems that when specify it one time (or more than required) it's just accepted. |
|
Commented by: Sean Leyne (seanleyne) Adriano, While I realize that the {n} is not required to be provided, I think it is informative for the server to report, as it tell the user how many times the index was used -- since a large number of uses can itself be something that a user could want to 'optimize'/factor into their analysis of the query performance. |
Submitted by: Sean Leyne (seanleyne)
Relate to CORE2115
Votes: 1
When the engine processes a simple SQL statement like:
SELECT ... FROM TestTable WHERE ColumnA IN (1,2,3,4,5,6,7,8,9,10...)
The resulting plan can look like:
PLAN (TESTTABLE INDEX (TESTTABLE_INDEX_LONG_NAME, TESTTABLE_INDEX_LONG_NAME, TESTTABLE_INDEX_LONG_NAME, TESTTABLE_INDEX_LONG_NAME, TESTTABLE_INDEX_LONG_NAME, TESTTABLE_INDEX_LONG_NAME, TESTTABLE_INDEX_LONG_NAME, TESTTABLE_INDEX_LONG_NAME, TESTTABLE_INDEX_LONG_NAME, TESTTABLE_INDEX_LONG_NAME, TESTTABLE_INDEX_LONG_NAME, TESTTABLE_INDEX_LONG_NAME, TESTTABLE_INDEX_LONG_NAME, TESTTABLE_INDEX_LONG_NAME, TESTTABLE_INDEX_LONG_NAME, TESTTABLE_INDEX_LONG_NAME, TESTTABLE_INDEX_LONG_NAME, TESTTABLE_INDEX_LONG_NAME, TESTTABLE_INDEX_LONG_NAME, TESTTABLE_INDEX_LONG_NAME, TESTTABLE_INDEX_LONG_NAME, TESTTABLE_INDEX_LONG_NAME, TESTTABLE_INDEX_LONG_NAME, TESTTABLE_INDEX_LONG_NAME, TESTTABLE_INDEX_LONG_NAME, TESTTABLE_INDEX_LONG_NAME, TESTTABLE_INDEX_LONG_NAME, TESTTABLE_INDEX_LONG_NAME, TESTTABLE_INDEX_LONG_NAME, TESTTABLE_INDEX_LONG_NAME, TESTTABLE_INDEX_LONG_NAME, TESTTABLE_INDEX_LONG_NAME, TESTTABLE_INDEX_LONG_NAME, TESTTABLE_INDEX_LONG_NAME, TESTTABLE_INDEX_LONG_NAME, TESTTABLE_INDEX_LONG_NAME, TESTTABLE_INDEX_LONG_NAME, TESTTABLE_INDEX_LONG_NAME, TESTTABLE_INDEX_LONG_NAME, TESTTABLE_INDEX_LONG_NAME, TESTTABLE_INDEX_LONG_NAME, TESTTABLE_INDEX_LONG_NAME, TESTTABLE_INDEX_LONG_NAME, TESTTABLE_INDEX_LONG_NAME, TESTTABLE_INDEX_LONG_NAME))
while the PLAN is technically correct; at best, the string is 'useless', at worst, the current approach will cause the PLAN string to exceed it 32KB limit.
Therefore, instead multiple references should be replace with a "{IndexName} x{n}", such that in the above example the PLAN generated would be:
PLAN (TESTTABLE INDEX (TESTTABLE_INDEX_LONG_NAME x45))
The text was updated successfully, but these errors were encountered: