ignite-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Vladimir Ozerov <voze...@gridgain.com>
Subject Re: SQL View with list of existing indexes
Date Mon, 28 Jan 2019 12:57:43 GMT
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 Юрий <jury.gerzhedowich@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 <vozerov@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 Юрий <jury.gerzhedowich@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
>

Mime
  • Unnamed multipart/alternative (inline, None, 0 bytes)
View raw message