[IGNITE-21416] Add documentation for FORCE_INDEX/NO_INDEX hints

• Type: Improvement
• Status: Open
• Priority: Minor
• Resolution: Unresolved
• Component/s: documentation, sql
• Labels: ignite-3
• Epic Link: Criteria API queries
• Ignite Flags: Docs Required, Release Notes Required
• Assignee: Unassigned
• Reporter: Andrey Novikov
• Created: 01/Feb/24 03:15
• Updated: 01/Feb/24 08:15

Description

Globally it works the same way as in https://ignite.apache.org/docs/latest/SQL/sql-calcite#force_index-no_index, but has some difference when FORCE_INDEX and NO_INDEX used together in the same query.

When both hints reference to the same index - it is invalid case. SQL is declarative language and we shouldn't expect hints collection is ordered:

SELECT /*+ FORCE_INDEX(IDX_ID), NO_INDEX */ * FROM TBL1 WHERE id = ? AND val = ?
OR
SELECT /*+ NO_INDEX(IDX_VAL), FORCE_INDEX(IDX_VAL) */ * FROM TBL1 WHERE val = ?

 

What to do

We need to fix example section and rewrite "Hint Scope" section.

Issue Links

• relates to: IGNITE-21278 Add FORCE_INDEX/NO_INDEX hints for calcite engine