Re: SQL: Index hints

[Sergi Vladykin]

Exactly, this syntax was taken from MySQL.

Sergi

2017-04-21 9:58 GMT+03:00 Denis Magda :

> If multiple indexes are listed then H2 will pick only one of them like
> MySql does, right?
>
> Denis
>
> On Thursday, April 20, 2017, Sergi Vladykin 
> wrote:
>
> > No, it must be USE INDEX without underscore. Also mention that multiple
> > indexes can be listed.
> >
> > http://h2database.com/html/grammar.html#table_expression
> >
> > Sergi
> >
> >
> > 2017-04-21 4:25 GMT+03:00 Denis Magda  >:
> >
> > > Sergi, I’ve documented this feature for 2.0. Please confirm that the
> text
> > > below is technically correct:
> > >
> > > Index hints are useful in scenarios when it's known that one index is
> > more
> > > selective for certain queries than another and it's needed to instruct
> > the
> > > query optimizer to choose a more efficient execution plan. To do this
> > trick
> > > in Apache Ignite use USE_INDEX(index_list) statement that tells Ignite
> to
> > > take only one of the named indexes for query execution.
> > >
> > > Below is an example that leverages from this capability:
> > >
> > > SELECT * FROM table1 USE_INDEX(index_age)
> > >   WHERE salary > 15 AND age < 35;
> > >
> > > —
> > > Denis
> > >
> > > > On Jan 23, 2017, at 12:19 PM, Denis Magda  > > wrote:
> > > >
> > > > Created a ticket so that we don’t forget about this new H2
> capability.
> > > > https://issues.apache.org/jira/browse/IGNITE-4594 <
> > > https://issues.apache.org/jira/browse/IGNITE-4594>
> > > >
> > > > Alexander P. feel free to assign it on yourself.
> > > >
> > > > —
> > > > Denis
> > > >
> > > >> On Jan 23, 2017, at 10:05 AM, Dmitriy Setrakyan <
> > dsetrak...@apache.org >
> > > wrote:
> > > >>
> > > >> Very cool! Would be nice to add it to Ignite.
> > > >>
> > > >> On Mon, Jan 23, 2017 at 3:17 AM, Sergi Vladykin <
> > > sergi.vlady...@gmail.com >
> > > >> wrote:
> > > >>
> > > >>> Guys,
> > > >>>
> > > >>> Recently in H2 we've merged a very important feature: index hints.
> It
> > > is an
> > > >>> additional MySQL-like syntax:
> > > >>>
> > > >>> SELECT * FROM  my_table USE INDEX (index_a) WHERE A = 1
> > > >>>
> > > >>> It will be very easy to support this in Ignite.
> > > >>>
> > > >>> Alex,
> > > >>>
> > > >>> Since you are working on better SQL Enum support and it will
> require
> > H2
> > > >>> upgrade anyways, you can add this stuff to Ignite as well.
> > > >>>
> > > >>> Sergi
> > > >>>
> > > >
> > >
> > >
> >
>

Re: SQL: Index hints

[Sergi Vladykin]

No, it must be USE INDEX without underscore. Also mention that multiple
indexes can be listed.

http://h2database.com/html/grammar.html#table_expression

Sergi


2017-04-21 4:25 GMT+03:00 Denis Magda :

> Sergi, I’ve documented this feature for 2.0. Please confirm that the text
> below is technically correct:
>
> Index hints are useful in scenarios when it's known that one index is more
> selective for certain queries than another and it's needed to instruct the
> query optimizer to choose a more efficient execution plan. To do this trick
> in Apache Ignite use USE_INDEX(index_list) statement that tells Ignite to
> take only one of the named indexes for query execution.
>
> Below is an example that leverages from this capability:
>
> SELECT * FROM table1 USE_INDEX(index_age)
>   WHERE salary > 15 AND age < 35;
>
> —
> Denis
>
> > On Jan 23, 2017, at 12:19 PM, Denis Magda  wrote:
> >
> > Created a ticket so that we don’t forget about this new H2 capability.
> > https://issues.apache.org/jira/browse/IGNITE-4594 <
> https://issues.apache.org/jira/browse/IGNITE-4594>
> >
> > Alexander P. feel free to assign it on yourself.
> >
> > —
> > Denis
> >
> >> On Jan 23, 2017, at 10:05 AM, Dmitriy Setrakyan 
> wrote:
> >>
> >> Very cool! Would be nice to add it to Ignite.
> >>
> >> On Mon, Jan 23, 2017 at 3:17 AM, Sergi Vladykin <
> sergi.vlady...@gmail.com>
> >> wrote:
> >>
> >>> Guys,
> >>>
> >>> Recently in H2 we've merged a very important feature: index hints. It
> is an
> >>> additional MySQL-like syntax:
> >>>
> >>> SELECT * FROM  my_table USE INDEX (index_a) WHERE A = 1
> >>>
> >>> It will be very easy to support this in Ignite.
> >>>
> >>> Alex,
> >>>
> >>> Since you are working on better SQL Enum support and it will require H2
> >>> upgrade anyways, you can add this stuff to Ignite as well.
> >>>
> >>> Sergi
> >>>
> >
>
>

Re: SQL: Index hints

[Denis Magda]

Sergi, I’ve documented this feature for 2.0. Please confirm that the text below 
is technically correct:

Index hints are useful in scenarios when it's known that one index is more 
selective for certain queries than another and it's needed to instruct the 
query optimizer to choose a more efficient execution plan. To do this trick in 
Apache Ignite use USE_INDEX(index_list) statement that tells Ignite to take 
only one of the named indexes for query execution.

Below is an example that leverages from this capability:

SELECT * FROM table1 USE_INDEX(index_age)
  WHERE salary > 15 AND age < 35;

—
Denis

> On Jan 23, 2017, at 12:19 PM, Denis Magda  wrote:
> 
> Created a ticket so that we don’t forget about this new H2 capability.
> https://issues.apache.org/jira/browse/IGNITE-4594 
> 
> 
> Alexander P. feel free to assign it on yourself.
> 
> —
> Denis
> 
>> On Jan 23, 2017, at 10:05 AM, Dmitriy Setrakyan  
>> wrote:
>> 
>> Very cool! Would be nice to add it to Ignite.
>> 
>> On Mon, Jan 23, 2017 at 3:17 AM, Sergi Vladykin 
>> wrote:
>> 
>>> Guys,
>>> 
>>> Recently in H2 we've merged a very important feature: index hints. It is an
>>> additional MySQL-like syntax:
>>> 
>>> SELECT * FROM  my_table USE INDEX (index_a) WHERE A = 1
>>> 
>>> It will be very easy to support this in Ignite.
>>> 
>>> Alex,
>>> 
>>> Since you are working on better SQL Enum support and it will require H2
>>> upgrade anyways, you can add this stuff to Ignite as well.
>>> 
>>> Sergi
>>> 
> 

[jira] [Created] (IGNITE-4966) Document SQL index hints and merge sort capabilities

[Denis Magda (JIRA)]

Denis Magda created IGNITE-4966:
---

 Summary: Document SQL index hints and merge sort capabilities
 Key: IGNITE-4966
 URL: https://issues.apache.org/jira/browse/IGNITE-4966
 Project: Ignite
  Issue Type: Sub-task
Reporter: Denis Magda
Assignee: Denis Magda


Document the following:
* SQL hints usage (IGNITE-4594)
* Merge sort (IGNITE-3013)



--
This message was sent by Atlassian JIRA
(v6.3.15#6346)

Re: SQL: Index hints

[Denis Magda]

Created a ticket so that we don’t forget about this new H2 capability.
https://issues.apache.org/jira/browse/IGNITE-4594 


Alexander P. feel free to assign it on yourself.

—
Denis

> On Jan 23, 2017, at 10:05 AM, Dmitriy Setrakyan  wrote:
> 
> Very cool! Would be nice to add it to Ignite.
> 
> On Mon, Jan 23, 2017 at 3:17 AM, Sergi Vladykin 
> wrote:
> 
>> Guys,
>> 
>> Recently in H2 we've merged a very important feature: index hints. It is an
>> additional MySQL-like syntax:
>> 
>> SELECT * FROM  my_table USE INDEX (index_a) WHERE A = 1
>> 
>> It will be very easy to support this in Ignite.
>> 
>> Alex,
>> 
>> Since you are working on better SQL Enum support and it will require H2
>> upgrade anyways, you can add this stuff to Ignite as well.
>> 
>> Sergi
>> 

Re: SQL: Index hints

[Dmitriy Setrakyan]

Very cool! Would be nice to add it to Ignite.

On Mon, Jan 23, 2017 at 3:17 AM, Sergi Vladykin 
wrote:

> Guys,
>
> Recently in H2 we've merged a very important feature: index hints. It is an
> additional MySQL-like syntax:
>
> SELECT * FROM  my_table USE INDEX (index_a) WHERE A = 1
>
> It will be very easy to support this in Ignite.
>
> Alex,
>
> Since you are working on better SQL Enum support and it will require H2
> upgrade anyways, you can add this stuff to Ignite as well.
>
> Sergi
>

SQL: Index hints

[Sergi Vladykin]

Guys,

Recently in H2 we've merged a very important feature: index hints. It is an
additional MySQL-like syntax:

SELECT * FROM  my_table USE INDEX (index_a) WHERE A = 1

It will be very easy to support this in Ignite.

Alex,

Since you are working on better SQL Enum support and it will require H2
upgrade anyways, you can add this stuff to Ignite as well.

Sergi