Another option, if we want to be more complete when it comes to how things are executed…
Is to use Sybase/SAP syntax select * from titles where title_id = 'On Liberty' plan '(i_scan title_id_ix titles)' The nice thing with the above syntax is that it’s · “detached” from the SQL statement itself (not embedded in the *actual* sql text, but appended after the statement) Or decouples the SQL text from the execution plan · Easier to extend the syntax of the “plan” specification (we can have an alternate parser for parsing the execution plan) Meaning not *just* index hints, but also join order, if we want to use nested loop join or merge join or... etc, etc… · Maybe over the top: later on at “execution time” an “abstract plan” can be associated with a specific SQL Statement/text (this so the application doesn’t have to be changed just because you want to “change” the execution plan) The “Abstract Query Plan” is described in: http://infocenter.sybase.com/help/topic/com.sybase.infocenter.dc00743.1570/html/queryprocessing/X14674.htm In there we might be able to get some ideas that can be reused in H2 /Göran Schwarz From: [email protected] [mailto:[email protected]] On Behalf Of Steve McLeod Sent: den 4 januari 2017 13:13 To: H2 Database <[email protected]> Subject: [h2] Index hints I'm considering attempting to add index hints to H2. Questions: * Is this a good idea? Or is it better to work on making the query optimizer smarter at choosing the correct index? * Is Oracle's syntax preferable? Or MySQL's syntax? Or is there a better syntax altogether? Oracle: SELECT /*+ INDEX (employees emp_department_ix)*/ employee_id, department_id FROM employees WHERE department_id > 50; MySQL: SELECT * FROM table1 USE INDEX (col1_index,col2_index) WHERE col1=1 AND col2=2 AND col3=3; -- You received this message because you are subscribed to the Google Groups "H2 Database" group. To unsubscribe from this group and stop receiving emails from it, send an email to [email protected] <mailto:[email protected]> . To post to this group, send email to [email protected] <mailto:[email protected]> . Visit this group at https://groups.google.com/group/h2-database. For more options, visit https://groups.google.com/d/optout. -- You received this message because you are subscribed to the Google Groups "H2 Database" group. To unsubscribe from this group and stop receiving emails from it, send an email to [email protected]. To post to this group, send email to [email protected]. Visit this group at https://groups.google.com/group/h2-database. For more options, visit https://groups.google.com/d/optout.
