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 Юрий <[email protected]> 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 <[email protected]>: > > > 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 Юрий <[email protected]> > 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 >
