[PHOENIX-291] Use hint to overide skip scan/range scan determination

• Type: Task
• Status: Resolved
• Resolution: Fixed
• Labels: enhancement
• old issue number: 160
• Assignee: tonyhuang
• Reporter: James R. Taylor
• Created: 10/Mar/14 23:46
• Updated: 16/Mar/14 07:18
• Resolved: 16/Mar/14 07:18

Description

We currently always use a skip scan filter when we can. There are cases in which performance is better if a range scan would have been used. We should allow the user to specify a hint in the query to override this decision.

The same is true of a range scan. There are time when we don't have a filter for the leading PK column, but do have one for the second PK column. In this case currently, a skip scan won't be done. However, depending on the cardinality of the first column, it would be better to do a skip scan.

The SelectStatement already has a hintNode that is passed through the grammar. Let's introduce a couple of simple hints:

SELECT /*+ range_scan */
and
SELECT /*+ skip_scan */

These could be used to override the default behavior. These hints will not be as necessary once we have stats, but aren't a bad idea to have even then, since the table stats may get out of date too.