ignite-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Юрий <jury.gerzhedow...@gmail.com>
Subject SQL View with list of existing indexes
Date Thu, 24 Jan 2019 12:51:38 GMT
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

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