Replace multiple index references in PLANs from SQL with IN() or multiple ORs with abbreviated syntax [CORE2116]

[firebird-automations]

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))

[firebird-automations]

Modified by: Sean Leyne (seanleyne)

Link: This issue relate to [CORE2115](https://github.com/FirebirdSQL/firebird/issues?q=CORE2115+in%3Atitle) \[ [CORE2115](https://github.com/FirebirdSQL/firebird/issues?q=CORE2115+in%3Atitle) \]

[firebird-automations]

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?

[firebird-automations]

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()?

[firebird-automations]

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.

[firebird-automations]

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.

[firebird-automations]

Modified by: @hvlad

Link: This issue relate to [CORE2698](https://github.com/FirebirdSQL/firebird/issues?q=CORE2698+in%3Atitle) \[ [CORE2698](https://github.com/FirebirdSQL/firebird/issues?q=CORE2698+in%3Atitle) \]

[firebird-automations]

Modified by: @hvlad

Link: This issue relate to [CORE2698](https://github.com/FirebirdSQL/firebird/issues?q=CORE2698+in%3Atitle) \[ [CORE2698](https://github.com/FirebirdSQL/firebird/issues?q=CORE2698+in%3Atitle) \] =\>