Open Source RDBMS - Seamless, Scalable, Stable and Free

한국어 | Login |Register


2
(click on this box to dismiss)

What's the difference between "USING INDEX" and "USING INDEX (+)"?

In the documentation at http://www.cubrid.org/manual/840/en/USING%20INDEX%20Clause it says that the (+) actually forces the index to be used. What about when writing USING INDEX without the (+)? Is the index still 100% used?

link comment (0) accepted answer
asked 3 years ago
sqlcopter
284
1 Answer
4

The USING_INDEX clause is used by the query planner when deciding the best plan for a query in the following way:

  1. USING_INDEX index_name (+) : Query planner considers the plan which uses this index to have the best cost (0) without computing the actual cost of the plan.
  2. USING_INDEX index_name (-) : Query planner considers the plan which uses this index to have the worst cost (INFINITE) without computing the actual cost of the plan.
  3. USING_INDEX index_name : Query planner computes the cost for this plan like it does with any other index.
So, to answer your question, neither of this hints actually guarantees that your query will have a specific plan.

The force hint (+) will almost certainly be used since that plan is considered to have zero cost. However, there might be other plans that have zero cost and the planner might chooses them instead (highly unlikely).

The not use hint (-) will almost certainly cause the index not to be used since it has the worst cost. However, theoretically, there can be situations in which all plans have the worst cost so your index might still get used. 

The normal hint (the one without + or -) will just cause a plan to be generated with your index and it will be used or not depending on its cost.

link comment (0)
answered 3 years ago
ginarrbrik
516
tagged




You are either using a very old browser or a browser that is not supported.
In order to browse cubrid.org you need to have one of the following browsers:



Internet Explorer: Mozilla Firefox: Google Chrome: