You are viewing a plain text version of this content. The canonical link for it is here.
Posted to dev@ignite.apache.org by Zhenya <ar...@mail.ru.INVALID> on 2018/11/28 17:57:16 UTC
Case sensitive indexes question.
Igniters, i found that ignite allow to create multiple case sensitive
indexes with equal fields collection, i.e. no exception and warn here:
CREATE INDEX \"title_idx\" ON books (title);
CREATE INDEX \"tiTLE_IDX\" ON books (title);
1. in this case will be created two different index structures.
2. documentation [1] not clarify that quotation usage will create
different ones and quotation absence will create index name in upper
registry.
3. what index, query planner would be use?
4. and main question: why do we need this functional?
i found that other vendors not allow such indexes [2]
if we can`t change this in 2.x version due to some backward compatibility
reasons, plz show them, i have no clue why we can`t change it in near
release versions.
[1] https://apacheignite-sql.readme.io/docs/create-index
[2]
https://oracle-base.com/articles/12c/multiple-indexes-on-same-set-of-columns-12cr1
thanks!
Re: Case sensitive indexes question.
Posted by Vladimir Ozerov <vo...@gridgain.com>.
Hi Zhenya,
Yes, will do in the nearest time.
On Sat, Dec 15, 2018 at 8:25 PM Zhenya Stanilovsky
<ar...@mail.ru.invalid> wrote:
> i fill the ticket https://issues.apache.org/jira/browse/IGNITE-10654 ,
> Vladimir can u check it?
>
> thanks.
>
>
> >Суббота, 15 декабря 2018, 20:11 +03:00 от stanilovsky evgeny <
> estanilovskiy@gridgain.com>:
> >
> >
> >
> >------- Forwarded message -------
> >From: "Vladimir Ozerov" < vozerov@gridgain.com >
> >To: dev < dev@ignite.apache.org >
> >Cc: arzamas123@mail.ru
> >Subject: Re: Case sensitive indexes question.
> >Date: Tue, 04 Dec 2018 15:27:02 +0300
> >
> >I think that this is not an exceptional case, as nothing is broken.
> >Throwing exception may make migration from other systems harder. Warning
> >should be enough.
> >Also remember that all SQL caches already have 1-2 automatic indexes out
> of
> >the box, and we haven't seen much performance complaints about that.
> >Additional duplicate index will not lead to severe performance degradation
> >or system stall.
> >
> >On Fri, Nov 30, 2018 at 3:52 PM Yakov Zhdanov < yzhdanov@apache.org >
> wrote:
> >
> >> Zhenya,
> >>
> >> Vladimir suggested not to restrict anything. However, my opinion is to
> >> throw exception on duplicating indexes. We should better add ability to
> >> rename index if it can be useful for anyone. Having same field set
> >> indexed
> >> with same index type is pretty strange and adds a lot of risk for
> >> performance of the system. If this is hard to support in 2.x then let's
> >> do
> >> it in 3.0. Vladimir, what do you think?
> >>
> >> -- Yakov
>
>
> --
> Zhenya Stanilovsky
>
Re: Case sensitive indexes question.
Posted by Zhenya Stanilovsky <ar...@mail.ru.INVALID>.
i fill the ticket https://issues.apache.org/jira/browse/IGNITE-10654 , Vladimir can u check it?
thanks.
>Суббота, 15 декабря 2018, 20:11 +03:00 от stanilovsky evgeny <es...@gridgain.com>:
>
>
>
>------- Forwarded message -------
>From: "Vladimir Ozerov" < vozerov@gridgain.com >
>To: dev < dev@ignite.apache.org >
>Cc: arzamas123@mail.ru
>Subject: Re: Case sensitive indexes question.
>Date: Tue, 04 Dec 2018 15:27:02 +0300
>
>I think that this is not an exceptional case, as nothing is broken.
>Throwing exception may make migration from other systems harder. Warning
>should be enough.
>Also remember that all SQL caches already have 1-2 automatic indexes out of
>the box, and we haven't seen much performance complaints about that.
>Additional duplicate index will not lead to severe performance degradation
>or system stall.
>
>On Fri, Nov 30, 2018 at 3:52 PM Yakov Zhdanov < yzhdanov@apache.org > wrote:
>
>> Zhenya,
>>
>> Vladimir suggested not to restrict anything. However, my opinion is to
>> throw exception on duplicating indexes. We should better add ability to
>> rename index if it can be useful for anyone. Having same field set
>> indexed
>> with same index type is pretty strange and adds a lot of risk for
>> performance of the system. If this is hard to support in 2.x then let's
>> do
>> it in 3.0. Vladimir, what do you think?
>>
>> -- Yakov
--
Zhenya Stanilovsky
Re: Case sensitive indexes question.
Posted by Vladimir Ozerov <vo...@gridgain.com>.
I think that this is not an exceptional case, as nothing is broken.
Throwing exception may make migration from other systems harder. Warning
should be enough.
Also remember that all SQL caches already have 1-2 automatic indexes out of
the box, and we haven't seen much performance complaints about that.
Additional duplicate index will not lead to severe performance degradation
or system stall.
On Fri, Nov 30, 2018 at 3:52 PM Yakov Zhdanov <yz...@apache.org> wrote:
> Zhenya,
>
> Vladimir suggested not to restrict anything. However, my opinion is to
> throw exception on duplicating indexes. We should better add ability to
> rename index if it can be useful for anyone. Having same field set indexed
> with same index type is pretty strange and adds a lot of risk for
> performance of the system. If this is hard to support in 2.x then let's do
> it in 3.0. Vladimir, what do you think?
>
> -- Yakov
>
Re: Case sensitive indexes question.
Posted by Yakov Zhdanov <yz...@apache.org>.
Zhenya,
Vladimir suggested not to restrict anything. However, my opinion is to
throw exception on duplicating indexes. We should better add ability to
rename index if it can be useful for anyone. Having same field set indexed
with same index type is pretty strange and adds a lot of risk for
performance of the system. If this is hard to support in 2.x then let's do
it in 3.0. Vladimir, what do you think?
-- Yakov
Re: Case sensitive indexes question.
Posted by Zhenya Stanilovsky <ar...@mail.ru.INVALID>.
Vladimir, thank you for clarification.
i.e. you suggest creating two tickets:
1. for performance log message.
2. restrict such indexes creation.
is it correct ?
what about flag disabled by default, thus nothing would be broken?
>Пятница, 30 ноября 2018, 15:15 +03:00 от Evgeniy Stanilovskiy <es...@gridgain.com>:
>
>
>
>------- Forwarded message -------
>From: "Vladimir Ozerov" < vozerov@gridgain.com >
>To: dev < dev@ignite.apache.org >
>Cc:
>Subject: Re: Case sensitive indexes question.
>Date: Wed, 28 Nov 2018 23:11:50 +0300
>
>Hi Zhenya,
>
>Answering your questions:
>1) Yes, this is expected
>2) This is standard rule applicable for almost all vendors and all SQL
>commands - object name without quotes is normalized to some case (upper or
>lower), object name in qoutes is left as is
>3) Hard to say, need to experiment with it
>4) We don't
>
>Some vendors allow it (MySQL, Postgres), may be some other's don't. We
>cannot restrict their usage in AI 2.x as it may break existing
>applications. Neither I think we should restrict it - nothing is broken.
>IMO what we do need is to inform user that he is doing something strange
>and let him decide what to do. This could be a warning in the log, special
>"performance suggestions" SQL view, whatever.
>
>Vladimir.
>
>On Wed, Nov 28, 2018 at 8:57 PM Zhenya < arzamas123@mail.ru.invalid > wrote:
>
>> Igniters, i found that ignite allow to create multiple case sensitive
>> indexes with equal fields collection, i.e. no exception and warn here:
>>
>> CREATE INDEX \"title_idx\" ON books (title);
>> CREATE INDEX \"tiTLE_IDX\" ON books (title);
>>
>> 1. in this case will be created two different index structures.
>> 2. documentation [1] not clarify that quotation usage will create
>> different ones and quotation absence will create index name in upper
>> registry.
>> 3. what index, query planner would be use?
>> 4. and main question: why do we need this functional?
>>
>> i found that other vendors not allow such indexes [2]
>>
>> if we can`t change this in 2.x version due to some backward compatibility
>> reasons, plz show them, i have no clue why we can`t change it in near
>> release versions.
>>
>> [1] https://apacheignite-sql.readme.io/docs/create-index
>> [2]
>>
>> https://oracle-base.com/articles/12c/multiple-indexes-on-same-set-of-columns-12cr1
>>
>> thanks!
--
Zhenya Stanilovsky
Re: Case sensitive indexes question.
Posted by Vladimir Ozerov <vo...@gridgain.com>.
Hi Zhenya,
Answering your questions:
1) Yes, this is expected
2) This is standard rule applicable for almost all vendors and all SQL
commands - object name without quotes is normalized to some case (upper or
lower), object name in qoutes is left as is
3) Hard to say, need to experiment with it
4) We don't
Some vendors allow it (MySQL, Postgres), may be some other's don't. We
cannot restrict their usage in AI 2.x as it may break existing
applications. Neither I think we should restrict it - nothing is broken.
IMO what we do need is to inform user that he is doing something strange
and let him decide what to do. This could be a warning in the log, special
"performance suggestions" SQL view, whatever.
Vladimir.
On Wed, Nov 28, 2018 at 8:57 PM Zhenya <ar...@mail.ru.invalid> wrote:
> Igniters, i found that ignite allow to create multiple case sensitive
> indexes with equal fields collection, i.e. no exception and warn here:
>
> CREATE INDEX \"title_idx\" ON books (title);
> CREATE INDEX \"tiTLE_IDX\" ON books (title);
>
> 1. in this case will be created two different index structures.
> 2. documentation [1] not clarify that quotation usage will create
> different ones and quotation absence will create index name in upper
> registry.
> 3. what index, query planner would be use?
> 4. and main question: why do we need this functional?
>
> i found that other vendors not allow such indexes [2]
>
> if we can`t change this in 2.x version due to some backward compatibility
> reasons, plz show them, i have no clue why we can`t change it in near
> release versions.
>
> [1] https://apacheignite-sql.readme.io/docs/create-index
> [2]
>
> https://oracle-base.com/articles/12c/multiple-indexes-on-same-set-of-columns-12cr1
>
> thanks!
>