New issue
Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.
By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.
Already on GitHub? Sign in to your account
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: