Issue Details (XML | Word | Printable)

Key: CORE-2116
Type: New Feature New Feature
Status: Open Open
Priority: Major Major
Assignee: Unassigned
Reporter: Sean Leyne
Votes: 1
Watchers: 1
Operations

If you were logged in you would be able to see more operations.
Firebird Core

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

Created: 10/Oct/08 12:24 PM   Updated: 19/Oct/09 05:29 PM
Component/s: Engine
Affects Version/s: None
Fix Version/s: None

Issue Links:
Relate
 


 Description  « Hide
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))

 All   Comments   Change History   Subversion Commits      Sort Order: Ascending order - Click to sort in descending order
Dmitry Yemanov added a comment - 10/Oct/08 01:51 PM
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?

Sean Leyne added a comment - 10/Oct/08 02:15 PM
"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()?

Adriano dos Santos Fernandes added a comment - 10/Oct/08 02:33 PM
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.

Sean Leyne added a comment - 10/Oct/08 02:39 PM
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.