You are viewing a plain text version of this content. The canonical link for it is here.
Posted to dev@ignite.apache.org by Юрий <ju...@gmail.com> on 2019/01/24 12:51:38 UTC
SQL View with list of existing indexes
Hi Igniters,
As part of IEP-29: SQL management and monitoring
<https://cwiki.apache.org/confluence/display/IGNITE/IEP-29%3A+SQL+management+and+monitoring>
I'm going to implement SQL view with list of existing indexes.
I've investigate how it expose by ORACLE, MySQL and Postgres.
ORACLE -
https://docs.oracle.com/en/database/oracle/oracle-database/18/refrn/ALL_INDEXES.html#GUID-E39825BA-70AC-45D8-AF30-C7FF561373B6
MySQL - https://dev.mysql.com/doc/refman/8.0/en/show-index.html
Postgres - https://www.postgresql.org/docs/11/view-pg-indexes.html ,
https://www.postgresql.org/docs/11/catalog-pg-index.html
All vendors have such views which show at least following information:
schema name - Name of schema related to table and index.
table name - Name of table related to an index.
index name - Name of index.
list of columns - All columns and their order included into an
index.
collation - ASC or DESC sort for each columns.
+ many specific information which different form vendor to vendor.
In our case such specific information could be at least:
1. Owning cache ID - not sure, but may be
useful to join with other our views.
2. number of columns at the index - just to know how many result
should be in columns view
3. query parallelism - It's configuration
parameter show how many thread can be used to execute query.
4. inline size - inline size
used for this index.
5. is affinity - boolean
parameter show that affinity key index
6. is pk - boolean
parameter show that PK index
7. approx recommended inline size - dynamically calculated
recommended inline size for this index to show required size to keep whole
indexed columns as inlined.
All vendors have different ways to present information about index columns:
PG - use array of index table columns and second array for collation each
of columns.
MySQL - each row in index view contains information about one of indexed
columsn with ther position at the index. So for one index there are many
columns.
ORACLE, - use separate view where each of row present column included into
index with all required information and can be joined by schema, table and
index names.
ORACLE indexed columns view -
https://docs.oracle.com/cd/B19306_01/server.102/b14237/statviews_1064.htm#i1577532
MySql -
I propose use ORACLE way and have second view to represent column included
into indexes.
In this case such view can have the following information:
schema name - Name of schema related to table and index.
table name - Name of table related to an index.
index name - Name of index.
column name - Name of column included into index.
column type - Type of the column.
column position - Position of column within the index.
collation - Either the column is sorted descending or
ascending
And can be joined with index view through schema, table and index names.
What do you think about such approach and list of columns which could be
included into the views?
--
Живи с улыбкой! :D
Re: SQL View with list of existing indexes
Posted by Юрий <ju...@gmail.com>.
One additional thought which I figured out just now.
Seems approximately recommended inline size is not good choice to add to
the index view, due to value this parameter has different value for each
node. Even more, for non affinity node it always will be zero. So, seems it
should be excluded from my initial proposal.
чт, 24 янв. 2019 г. в 15:51, Юрий <ju...@gmail.com>:
> Hi Igniters,
>
> As part of IEP-29: SQL management and monitoring
> <https://cwiki.apache.org/confluence/display/IGNITE/IEP-29%3A+SQL+management+and+monitoring>
> I'm going to implement SQL view with list of existing indexes.
> I've investigate how it expose by ORACLE, MySQL and Postgres.
> ORACLE -
> https://docs.oracle.com/en/database/oracle/oracle-database/18/refrn/ALL_INDEXES.html#GUID-E39825BA-70AC-45D8-AF30-C7FF561373B6
>
> MySQL - https://dev.mysql.com/doc/refman/8.0/en/show-index.html
> Postgres - https://www.postgresql.org/docs/11/view-pg-indexes.html ,
> https://www.postgresql.org/docs/11/catalog-pg-index.html
>
> All vendors have such views which show at least following information:
> schema name - Name of schema related to table and index.
> table name - Name of table related to an index.
> index name - Name of index.
> list of columns - All columns and their order included into an
> index.
> collation - ASC or DESC sort for each columns.
>
> + many specific information which different form vendor to vendor.
>
> In our case such specific information could be at least:
>
> 1. Owning cache ID - not sure, but may
> be useful to join with other our views.
> 2. number of columns at the index - just to know how many
> result should be in columns view
> 3. query parallelism - It's
> configuration parameter show how many thread can be used to execute query.
> 4. inline size - inline size
> used for this index.
> 5. is affinity - boolean
> parameter show that affinity key index
> 6. is pk - boolean
> parameter show that PK index
> 7. approx recommended inline size - dynamically calculated
> recommended inline size for this index to show required size to keep whole
> indexed columns as inlined.
>
>
>
> All vendors have different ways to present information about index
> columns:
> PG - use array of index table columns and second array for collation each
> of columns.
> MySQL - each row in index view contains information about one of indexed
> columsn with ther position at the index. So for one index there are many
> columns.
> ORACLE, - use separate view where each of row present column included
> into index with all required information and can be joined by schema, table
> and index names.
> ORACLE indexed columns view -
> https://docs.oracle.com/cd/B19306_01/server.102/b14237/statviews_1064.htm#i1577532
> MySql -
>
> I propose use ORACLE way and have second view to represent column included
> into indexes.
>
> In this case such view can have the following information:
> schema name - Name of schema related to table and index.
> table name - Name of table related to an index.
> index name - Name of index.
> column name - Name of column included into index.
> column type - Type of the column.
> column position - Position of column within the index.
> collation - Either the column is sorted descending or
> ascending
>
> And can be joined with index view through schema, table and index names.
>
>
>
> What do you think about such approach and list of columns which could be
> included into the views?
>
> --
> Живи с улыбкой! :D
>
--
Живи с улыбкой! :D
Re: SQL View with list of existing indexes
Posted by Vladimir Ozerov <vo...@gridgain.com>.
Hi Yuriy,
Yes, I believe we will have columns view(s) at some point in time for sure.
On Thu, Jan 24, 2019 at 7:08 PM Юрий <ju...@gmail.com> wrote:
> Hi Vladimir,
>
> Thanks for your comments,
>
> 1) Agree.
> 2) Ok.
> 3) We create number of index copies depend on query parallelism. But seems
> you are right - it should be exposed on TABLES level.
> 4) Approx. inline size shouldn't be used here, due to the value depend on
> node and not has single value.
> 5) Do we have a plans for some view with table columns? If yes, may be will
> be better have just array with column order from the columns view. For
> example you want to know which columns are indexed already. In case we will
> have plain comma-separated form it can't be achieved.
>
>
>
>
>
> чт, 24 янв. 2019 г. в 18:09, Vladimir Ozerov <vo...@gridgain.com>:
>
> > Hi Yuriy,
> >
> > Please note that MySQL link is about SHOW command, which is a different
> > beast. In general I think that PG approach is better as it allows user to
> > get quick overview of index content without complex JOINs. I would start
> > with plain single view and add columns view later if we found it useful.
> As
> > far as view columns:
> > 1) I would add both cache ID/name and cache group ID/name
> > 2) Number of columns does not look as a useful info to me
> > 3) Query parallelism is related to cache, not index, so it should be in
> > IGNITE.TABLES view instead
> > 4) Inline size is definitely useful metric. Not sure about approximate
> > inline size
> > 5) I would add list of columns in plain comma-separated form with
> ASC/DESC
> > modifiers
> >
> > Thoughts?
> >
> > Vladimir.
> >
> > On Thu, Jan 24, 2019 at 3:52 PM Юрий <ju...@gmail.com>
> wrote:
> >
> > > Hi Igniters,
> > >
> > > As part of IEP-29: SQL management and monitoring
> > > <
> > >
> >
> https://cwiki.apache.org/confluence/display/IGNITE/IEP-29%3A+SQL+management+and+monitoring
> > > >
> > > I'm going to implement SQL view with list of existing indexes.
> > > I've investigate how it expose by ORACLE, MySQL and Postgres.
> > > ORACLE -
> > >
> > >
> >
> https://docs.oracle.com/en/database/oracle/oracle-database/18/refrn/ALL_INDEXES.html#GUID-E39825BA-70AC-45D8-AF30-C7FF561373B6
> > >
> > > MySQL - https://dev.mysql.com/doc/refman/8.0/en/show-index.html
> > > Postgres - https://www.postgresql.org/docs/11/view-pg-indexes.html ,
> > > https://www.postgresql.org/docs/11/catalog-pg-index.html
> > >
> > > All vendors have such views which show at least following information:
> > > schema name - Name of schema related to table and index.
> > > table name - Name of table related to an index.
> > > index name - Name of index.
> > > list of columns - All columns and their order included into
> an
> > > index.
> > > collation - ASC or DESC sort for each columns.
> > >
> > > + many specific information which different form vendor to vendor.
> > >
> > > In our case such specific information could be at least:
> > >
> > > 1. Owning cache ID - not sure, but may
> > be
> > > useful to join with other our views.
> > > 2. number of columns at the index - just to know how many
> > result
> > > should be in columns view
> > > 3. query parallelism - It's
> > configuration
> > > parameter show how many thread can be used to execute query.
> > > 4. inline size - inline
> size
> > > used for this index.
> > > 5. is affinity - boolean
> > > parameter show that affinity key index
> > > 6. is pk -
> boolean
> > > parameter show that PK index
> > > 7. approx recommended inline size - dynamically calculated
> > > recommended inline size for this index to show required size to keep
> > > whole
> > > indexed columns as inlined.
> > >
> > >
> > >
> > > All vendors have different ways to present information about index
> > > columns:
> > > PG - use array of index table columns and second array for collation
> each
> > > of columns.
> > > MySQL - each row in index view contains information about one of
> indexed
> > > columsn with ther position at the index. So for one index there are
> many
> > > columns.
> > > ORACLE, - use separate view where each of row present column included
> > into
> > > index with all required information and can be joined by schema, table
> > and
> > > index names.
> > > ORACLE indexed columns view -
> > >
> > >
> >
> https://docs.oracle.com/cd/B19306_01/server.102/b14237/statviews_1064.htm#i1577532
> > > MySql -
> > >
> > > I propose use ORACLE way and have second view to represent column
> > included
> > > into indexes.
> > >
> > > In this case such view can have the following information:
> > > schema name - Name of schema related to table and index.
> > > table name - Name of table related to an index.
> > > index name - Name of index.
> > > column name - Name of column included into index.
> > > column type - Type of the column.
> > > column position - Position of column within the index.
> > > collation - Either the column is sorted descending
> or
> > > ascending
> > >
> > > And can be joined with index view through schema, table and index
> names.
> > >
> > >
> > >
> > > What do you think about such approach and list of columns which could
> be
> > > included into the views?
> > >
> > > --
> > > Живи с улыбкой! :D
> > >
> >
>
>
> --
> Живи с улыбкой! :D
>
Re: SQL View with list of existing indexes
Posted by Юрий <ju...@gmail.com>.
Hi Vladimir,
Thanks for your comments,
1) Agree.
2) Ok.
3) We create number of index copies depend on query parallelism. But seems
you are right - it should be exposed on TABLES level.
4) Approx. inline size shouldn't be used here, due to the value depend on
node and not has single value.
5) Do we have a plans for some view with table columns? If yes, may be will
be better have just array with column order from the columns view. For
example you want to know which columns are indexed already. In case we will
have plain comma-separated form it can't be achieved.
чт, 24 янв. 2019 г. в 18:09, Vladimir Ozerov <vo...@gridgain.com>:
> Hi Yuriy,
>
> Please note that MySQL link is about SHOW command, which is a different
> beast. In general I think that PG approach is better as it allows user to
> get quick overview of index content without complex JOINs. I would start
> with plain single view and add columns view later if we found it useful. As
> far as view columns:
> 1) I would add both cache ID/name and cache group ID/name
> 2) Number of columns does not look as a useful info to me
> 3) Query parallelism is related to cache, not index, so it should be in
> IGNITE.TABLES view instead
> 4) Inline size is definitely useful metric. Not sure about approximate
> inline size
> 5) I would add list of columns in plain comma-separated form with ASC/DESC
> modifiers
>
> Thoughts?
>
> Vladimir.
>
> On Thu, Jan 24, 2019 at 3:52 PM Юрий <ju...@gmail.com> wrote:
>
> > Hi Igniters,
> >
> > As part of IEP-29: SQL management and monitoring
> > <
> >
> https://cwiki.apache.org/confluence/display/IGNITE/IEP-29%3A+SQL+management+and+monitoring
> > >
> > I'm going to implement SQL view with list of existing indexes.
> > I've investigate how it expose by ORACLE, MySQL and Postgres.
> > ORACLE -
> >
> >
> https://docs.oracle.com/en/database/oracle/oracle-database/18/refrn/ALL_INDEXES.html#GUID-E39825BA-70AC-45D8-AF30-C7FF561373B6
> >
> > MySQL - https://dev.mysql.com/doc/refman/8.0/en/show-index.html
> > Postgres - https://www.postgresql.org/docs/11/view-pg-indexes.html ,
> > https://www.postgresql.org/docs/11/catalog-pg-index.html
> >
> > All vendors have such views which show at least following information:
> > schema name - Name of schema related to table and index.
> > table name - Name of table related to an index.
> > index name - Name of index.
> > list of columns - All columns and their order included into an
> > index.
> > collation - ASC or DESC sort for each columns.
> >
> > + many specific information which different form vendor to vendor.
> >
> > In our case such specific information could be at least:
> >
> > 1. Owning cache ID - not sure, but may
> be
> > useful to join with other our views.
> > 2. number of columns at the index - just to know how many
> result
> > should be in columns view
> > 3. query parallelism - It's
> configuration
> > parameter show how many thread can be used to execute query.
> > 4. inline size - inline size
> > used for this index.
> > 5. is affinity - boolean
> > parameter show that affinity key index
> > 6. is pk - boolean
> > parameter show that PK index
> > 7. approx recommended inline size - dynamically calculated
> > recommended inline size for this index to show required size to keep
> > whole
> > indexed columns as inlined.
> >
> >
> >
> > All vendors have different ways to present information about index
> > columns:
> > PG - use array of index table columns and second array for collation each
> > of columns.
> > MySQL - each row in index view contains information about one of indexed
> > columsn with ther position at the index. So for one index there are many
> > columns.
> > ORACLE, - use separate view where each of row present column included
> into
> > index with all required information and can be joined by schema, table
> and
> > index names.
> > ORACLE indexed columns view -
> >
> >
> https://docs.oracle.com/cd/B19306_01/server.102/b14237/statviews_1064.htm#i1577532
> > MySql -
> >
> > I propose use ORACLE way and have second view to represent column
> included
> > into indexes.
> >
> > In this case such view can have the following information:
> > schema name - Name of schema related to table and index.
> > table name - Name of table related to an index.
> > index name - Name of index.
> > column name - Name of column included into index.
> > column type - Type of the column.
> > column position - Position of column within the index.
> > collation - Either the column is sorted descending or
> > ascending
> >
> > And can be joined with index view through schema, table and index names.
> >
> >
> >
> > What do you think about such approach and list of columns which could be
> > included into the views?
> >
> > --
> > Живи с улыбкой! :D
> >
>
--
Живи с улыбкой! :D
Re: SQL View with list of existing indexes
Posted by Vladimir Ozerov <vo...@gridgain.com>.
Hi Yuriy,
Please note that MySQL link is about SHOW command, which is a different
beast. In general I think that PG approach is better as it allows user to
get quick overview of index content without complex JOINs. I would start
with plain single view and add columns view later if we found it useful. As
far as view columns:
1) I would add both cache ID/name and cache group ID/name
2) Number of columns does not look as a useful info to me
3) Query parallelism is related to cache, not index, so it should be in
IGNITE.TABLES view instead
4) Inline size is definitely useful metric. Not sure about approximate
inline size
5) I would add list of columns in plain comma-separated form with ASC/DESC
modifiers
Thoughts?
Vladimir.
On Thu, Jan 24, 2019 at 3:52 PM Юрий <ju...@gmail.com> wrote:
> Hi Igniters,
>
> As part of IEP-29: SQL management and monitoring
> <
> https://cwiki.apache.org/confluence/display/IGNITE/IEP-29%3A+SQL+management+and+monitoring
> >
> I'm going to implement SQL view with list of existing indexes.
> I've investigate how it expose by ORACLE, MySQL and Postgres.
> ORACLE -
>
> https://docs.oracle.com/en/database/oracle/oracle-database/18/refrn/ALL_INDEXES.html#GUID-E39825BA-70AC-45D8-AF30-C7FF561373B6
>
> MySQL - https://dev.mysql.com/doc/refman/8.0/en/show-index.html
> Postgres - https://www.postgresql.org/docs/11/view-pg-indexes.html ,
> https://www.postgresql.org/docs/11/catalog-pg-index.html
>
> All vendors have such views which show at least following information:
> schema name - Name of schema related to table and index.
> table name - Name of table related to an index.
> index name - Name of index.
> list of columns - All columns and their order included into an
> index.
> collation - ASC or DESC sort for each columns.
>
> + many specific information which different form vendor to vendor.
>
> In our case such specific information could be at least:
>
> 1. Owning cache ID - not sure, but may be
> useful to join with other our views.
> 2. number of columns at the index - just to know how many result
> should be in columns view
> 3. query parallelism - It's configuration
> parameter show how many thread can be used to execute query.
> 4. inline size - inline size
> used for this index.
> 5. is affinity - boolean
> parameter show that affinity key index
> 6. is pk - boolean
> parameter show that PK index
> 7. approx recommended inline size - dynamically calculated
> recommended inline size for this index to show required size to keep
> whole
> indexed columns as inlined.
>
>
>
> All vendors have different ways to present information about index
> columns:
> PG - use array of index table columns and second array for collation each
> of columns.
> MySQL - each row in index view contains information about one of indexed
> columsn with ther position at the index. So for one index there are many
> columns.
> ORACLE, - use separate view where each of row present column included into
> index with all required information and can be joined by schema, table and
> index names.
> ORACLE indexed columns view -
>
> https://docs.oracle.com/cd/B19306_01/server.102/b14237/statviews_1064.htm#i1577532
> MySql -
>
> I propose use ORACLE way and have second view to represent column included
> into indexes.
>
> In this case such view can have the following information:
> schema name - Name of schema related to table and index.
> table name - Name of table related to an index.
> index name - Name of index.
> column name - Name of column included into index.
> column type - Type of the column.
> column position - Position of column within the index.
> collation - Either the column is sorted descending or
> ascending
>
> And can be joined with index view through schema, table and index names.
>
>
>
> What do you think about such approach and list of columns which could be
> included into the views?
>
> --
> Живи с улыбкой! :D
>