EXPLAIN for DML statements

[dmagda]

Alex P., Sergi,

All we know that EXPLAIN is not supported for DML statements at the moment.
http://apacheignite.gridgain.org/docs/dml#section-explain-support-for-dml-statements <http://apacheignite.gridgain.org/docs/dml#section-explain-support-for-dml-statements>

However, I can’t get how I can get over this limitation for UPDATE and DELETE statements following this recommendation

"One possible approach is to execute EXPLAIN for the SELECT automatically generated (UPDATE, DELETE) or used (INSERT, MERGE) by DML statements. This will give an insight on the indexes that are used while a DML operation is executed.”

How can I get SELECT queries that are generated by these DML operations automatically?

—
Denis

[dsetrakyan]

Denis, the link does not work again.

On Thu, Dec 22, 2016 at 4:09 PM, Denis Magda <[hidden email]> wrote:

> Alex P., Sergi,
>
> All we know that EXPLAIN is not supported for DML statements at the moment.
> http://apacheignite.gridgain.org/docs/dml#section-explain-
> support-for-dml-statements <http://apacheignite.gridgain.
> org/docs/dml#section-explain-support-for-dml-statements>
>
> However, I can’t get how I can get over this limitation for UPDATE and
> DELETE statements following this recommendation
>
> "One possible approach is to execute EXPLAIN for the SELECT automatically
> generated (UPDATE, DELETE) or used (INSERT, MERGE) by DML statements. This
> will give an insight on the indexes that are used while a DML operation is
> executed.”
>
> How can I get SELECT queries that are generated by these DML operations
> automatically?
>
> —
> Denis

[dmagda]

I got annoyed with readme.io that periodically switches to the legacy apacheignite.gridgain.org domain. Hope that with the latest upgrade to readme.io 2.0 this is no longer an issue.

https://apacheignite.readme.io/docs/dml#section-explain-support-for-dml-statements <https://apacheignite.readme.io/docs/dml#section-explain-support-for-dml-statements>

—
Denis

> On Dec 23, 2016, at 9:44 AM, Dmitriy Setrakyan <[hidden email]> wrote:
>
> Denis, the link does not work again.
>
> On Thu, Dec 22, 2016 at 4:09 PM, Denis Magda <[hidden email]> wrote:
>
>> Alex P., Sergi,
>>
>> All we know that EXPLAIN is not supported for DML statements at the moment.
>> http://apacheignite.gridgain.org/docs/dml#section-explain-
>> support-for-dml-statements <http://apacheignite.gridgain.
>> org/docs/dml#section-explain-support-for-dml-statements>
>>
>> However, I can’t get how I can get over this limitation for UPDATE and
>> DELETE statements following this recommendation
>>
>> "One possible approach is to execute EXPLAIN for the SELECT automatically
>> generated (UPDATE, DELETE) or used (INSERT, MERGE) by DML statements. This
>> will give an insight on the indexes that are used while a DML operation is
>> executed.”
>>
>> How can I get SELECT queries that are generated by these DML operations
>> automatically?
>>
>> —
>> Denis

[dmagda]

This might be a tricky part for the users because they need to recreate the query manually

> UPDATE Person SET name = 'John' WHERE surname = 'Smith',
>
> then we could show
>
> EXPLAIN SELECT _key from Person WHERE surname = 'Smith’;

How difficult is to support EXPLAIN for DML in general? Let’s discuss the challenges and implementation details and create a ticket after that.

—
Denis

> On Dec 23, 2016, at 7:46 AM, Alexander Paschenko <[hidden email]> wrote:
>
> Denis,
>
> Currently there's no API to get those queries. However, they are
> always generated under the hood - except for 'fast' UPDATE and DELETE
> that don't need any queries.
> So what is suggested in docs is to EXPLAIN those SELECTs - say, if we
> have DML statement
>
> UPDATE Person SET name = 'John' WHERE surname = 'Smith',
>
> then we could show
>
> EXPLAIN SELECT _key from Person WHERE surname = 'Smith';
>
> - Alex
>
> 2016-12-23 3:09 GMT+03:00 Denis Magda <[hidden email]>:
>> Alex P., Sergi,
>>
>> All we know that EXPLAIN is not supported for DML statements at the moment.
>> http://apacheignite.gridgain.org/docs/dml#section-explain-support-for-dml-statements
>>
>> However, I can’t get how I can get over this limitation for UPDATE and
>> DELETE statements following this recommendation
>>
>> "One possible approach is to execute EXPLAIN for the SELECT automatically
>> generated (UPDATE, DELETE) or used (INSERT, MERGE) by DML statements. This
>> will give an insight on the indexes that are used while a DML operation is
>> executed.”
>>
>> How can I get SELECT queries that are generated by these DML operations
>> automatically?
>>
>> —
>> Denis

[dsetrakyan]

I agree. I also would very much like to see the support of "Explain" for
DML statements. Is there some limitation in H2 that prevents us from
supporting it?

On Fri, Dec 23, 2016 at 10:36 AM, Denis Magda <[hidden email]> wrote:

> This might be a tricky part for the users because they need to recreate
> the query manually
>
> > UPDATE Person SET name = 'John' WHERE surname = 'Smith',
> >
> > then we could show
> >
> > EXPLAIN SELECT _key from Person WHERE surname = 'Smith’;
>
> How difficult is to support EXPLAIN for DML in general? Let’s discuss the
> challenges and implementation details and create a ticket after that.
>
> —
> Denis
>
>
> > On Dec 23, 2016, at 7:46 AM, Alexander Paschenko <
> [hidden email]> wrote:
> >
> > Denis,
> >
> > Currently there's no API to get those queries. However, they are
> > always generated under the hood - except for 'fast' UPDATE and DELETE
> > that don't need any queries.
> > So what is suggested in docs is to EXPLAIN those SELECTs - say, if we
> > have DML statement
> >
> > UPDATE Person SET name = 'John' WHERE surname = 'Smith',
> >
> > then we could show
> >
> > EXPLAIN SELECT _key from Person WHERE surname = 'Smith';
> >
> > - Alex
> >
> > 2016-12-23 3:09 GMT+03:00 Denis Magda <[hidden email]>:
> >> Alex P., Sergi,
> >>
> >> All we know that EXPLAIN is not supported for DML statements at the
> moment.
> >> http://apacheignite.gridgain.org/docs/dml#section-explain-
> support-for-dml-statements
> >>
> >> However, I can’t get how I can get over this limitation for UPDATE and
> >> DELETE statements following this recommendation
> >>
> >> "One possible approach is to execute EXPLAIN for the SELECT
> automatically
> >> generated (UPDATE, DELETE) or used (INSERT, MERGE) by DML statements.
> This
> >> will give an insight on the indexes that are used while a DML operation
> is
> >> executed.”
> >>
> >> How can I get SELECT queries that are generated by these DML operations
> >> automatically?
> >>
> >> —
> >> Denis
>
>

[dmagda]

Alexander,

If they do this at the moment

>  If they issue "explain
> update...", there's no problem to return output for EXPLAIN SELECT
> without requiring anything else from the user.


then they will get an exception.

My question was what are the efforts to support this?

—
Denis

> On Dec 26, 2016, at 2:05 AM, Alexander Paschenko <[hidden email]> wrote:
>
> Users won't need to create that query. If they issue "explain
> update...", there's no problem to return output for EXPLAIN SELECT
> without requiring anything else from the user.
>
> From EXPLAIN, the user usually expects to find out which indexes will
> be used by their query. And that's what EXPLAIN for SELECT gives.
>
> - Alex
>
> 2016-12-23 21:36 GMT+03:00 Denis Magda <[hidden email]>:
>> This might be a tricky part for the users because they need to recreate the query manually
>>
>>> UPDATE Person SET name = 'John' WHERE surname = 'Smith',
>>>
>>> then we could show
>>>
>>> EXPLAIN SELECT _key from Person WHERE surname = 'Smith’;
>>
>> How difficult is to support EXPLAIN for DML in general? Let’s discuss the challenges and implementation details and create a ticket after that.
>>
>> —
>> Denis
>>
>>
>>> On Dec 23, 2016, at 7:46 AM, Alexander Paschenko <[hidden email]> wrote:
>>>
>>> Denis,
>>>
>>> Currently there's no API to get those queries. However, they are
>>> always generated under the hood - except for 'fast' UPDATE and DELETE
>>> that don't need any queries.
>>> So what is suggested in docs is to EXPLAIN those SELECTs - say, if we
>>> have DML statement
>>>
>>> UPDATE Person SET name = 'John' WHERE surname = 'Smith',
>>>
>>> then we could show
>>>
>>> EXPLAIN SELECT _key from Person WHERE surname = 'Smith';
>>>
>>> - Alex
>>>
>>> 2016-12-23 3:09 GMT+03:00 Denis Magda <[hidden email]>:
>>>> Alex P., Sergi,
>>>>
>>>> All we know that EXPLAIN is not supported for DML statements at the moment.
>>>> http://apacheignite.gridgain.org/docs/dml#section-explain-support-for-dml-statements
>>>>
>>>> However, I can’t get how I can get over this limitation for UPDATE and
>>>> DELETE statements following this recommendation
>>>>
>>>> "One possible approach is to execute EXPLAIN for the SELECT automatically
>>>> generated (UPDATE, DELETE) or used (INSERT, MERGE) by DML statements. This
>>>> will give an insight on the indexes that are used while a DML operation is
>>>> executed.”
>>>>
>>>> How can I get SELECT queries that are generated by these DML operations
>>>> automatically?
>>>>
>>>> —
>>>> Denis
>>