From dev-return-44288-archive-asf-public=cust-asf.ponee.io@ignite.apache.org Thu Jan 24 16:09:11 2019 Return-Path: X-Original-To: archive-asf-public@cust-asf.ponee.io Delivered-To: archive-asf-public@cust-asf.ponee.io Received: from mail.apache.org (hermes.apache.org [140.211.11.3]) by mx-eu-01.ponee.io (Postfix) with SMTP id C422818062C for ; Thu, 24 Jan 2019 16:09:10 +0100 (CET) Received: (qmail 87582 invoked by uid 500); 24 Jan 2019 15:09:09 -0000 Mailing-List: contact dev-help@ignite.apache.org; run by ezmlm Precedence: bulk List-Help: List-Unsubscribe: List-Post: List-Id: Reply-To: dev@ignite.apache.org Delivered-To: mailing list dev@ignite.apache.org Received: (qmail 87568 invoked by uid 99); 24 Jan 2019 15:09:09 -0000 Received: from pnap-us-west-generic-nat.apache.org (HELO spamd3-us-west.apache.org) (209.188.14.142) by apache.org (qpsmtpd/0.29) with ESMTP; Thu, 24 Jan 2019 15:09:09 +0000 Received: from localhost (localhost [127.0.0.1]) by spamd3-us-west.apache.org (ASF Mail Server at spamd3-us-west.apache.org) with ESMTP id 98F0C1805FB for ; Thu, 24 Jan 2019 15:09:08 +0000 (UTC) X-Virus-Scanned: Debian amavisd-new at spamd3-us-west.apache.org X-Spam-Flag: NO X-Spam-Score: 1.903 X-Spam-Level: * X-Spam-Status: No, score=1.903 tagged_above=-999 required=6.31 tests=[DKIMWL_WL_MED=-0.001, DKIM_SIGNED=0.1, DKIM_VALID=-0.1, DKIM_VALID_AU=-0.1, DKIM_VALID_EF=-0.1, FROM_EXCESS_BASE64=0.105, HTML_MESSAGE=2, RCVD_IN_DNSWL_NONE=-0.0001, SPF_PASS=-0.001] autolearn=disabled Authentication-Results: spamd3-us-west.apache.org (amavisd-new); dkim=pass (2048-bit key) header.d=gmail.com Received: from mx1-lw-eu.apache.org ([10.40.0.8]) by localhost (spamd3-us-west.apache.org [10.40.0.10]) (amavisd-new, port 10024) with ESMTP id mCvVqihQpB41 for ; Thu, 24 Jan 2019 15:09:06 +0000 (UTC) Received: from mail-ua1-f48.google.com (mail-ua1-f48.google.com [209.85.222.48]) by mx1-lw-eu.apache.org (ASF Mail Server at mx1-lw-eu.apache.org) with ESMTPS id 9F1F3621D4 for ; Thu, 24 Jan 2019 14:58:30 +0000 (UTC) Received: by mail-ua1-f48.google.com with SMTP id j3so2079115uap.3 for ; Thu, 24 Jan 2019 06:58:30 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20161025; h=mime-version:references:in-reply-to:from:date:message-id:subject:to; bh=zH+c9k/CZ3TiZDKWIslnIazR059WloYBzuQOuja8lnQ=; b=bqyAvrc9P2z8kiovTXctX02JSZhwMn+Uvm8LllW3id/YVUFgo5CjNREQPVTmd5qi3l 5783jC3on14MeZoN7fwCT4C9npkQHKX84Z/GNIKqGEONwpFrmf5gaFcgQOHS42l/CSiy 5ppNi78peNex5omK8l6S7k0azaNRy/aZJr4Qt+l6HwUuSaB0CS48GPIciN0owYOjBDir NYyb4gxafYMUjhZJf9e9Y//hMwv43Y2538zG+qm1u9xp3ED9zv4TXApIuuqju9rXUwVW +U/stzQ2TqZKOXN+TwyQKTXNGBRCDNmEKa45Z6rW0r+eghNtfiq8q5AmzG+vGbEUJO75 k3Zg== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20161025; h=x-gm-message-state:mime-version:references:in-reply-to:from:date :message-id:subject:to; bh=zH+c9k/CZ3TiZDKWIslnIazR059WloYBzuQOuja8lnQ=; b=D76+nxvhdMetqfosC7yKY8grtMdsgWDrXIr35I5EOd3/c/m/UfM83bH2p7hE6DQfxG QNT516QR3NWyKIozPkDkE/gTSzfQAisHibhZbNbRnvLDv/jKsNUw8jocE77DbpZxXfL+ PDFv5HCUQ4v+VApgeOcNXxnqd89stziNFPDgc5QLtf6CrEVeV0qY+RY1Y7/2juq49QDu 1QEVb58fRKrJFq5Njtx8TtfSKidO6SHDtc7OjskgTAYLhprFmDt2GKEIzvM1mpEpZ8FE Hbn+pdJVH+6I1NDrzdNo9oJdjUztBz1NuvSXajYLU4GWjGnOhOOcl+Sj702gdHU7YWl8 QQ7Q== X-Gm-Message-State: AJcUukf/noSIjZSdi43NiJ/is/uckN+Z86IuK2QMAnNZpCREJ8bpGfby 3HfzwGCFfyt4MRPz3k64CC+Ng2FrSri6JY1tQz0+xA== X-Google-Smtp-Source: ALg8bN5ClqhnFQQd2sdHmyzNiXXlq7VOg1b5K56VHCArCRbxG5zYqAuMx44nvZWgp3z4teZEguYwKC8JkQ8RlYH4B2U= X-Received: by 2002:a9f:3f41:: with SMTP id i1mr2626780uaj.42.1548341902332; Thu, 24 Jan 2019 06:58:22 -0800 (PST) MIME-Version: 1.0 References: In-Reply-To: From: =?UTF-8?B?0K7RgNC40Lk=?= Date: Thu, 24 Jan 2019 17:58:11 +0300 Message-ID: Subject: Re: SQL View with list of existing indexes To: dev@ignite.apache.org Content-Type: multipart/alternative; boundary="00000000000014577b0580357192" --00000000000014577b0580357192 Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable 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. =D1=87=D1=82, 24 =D1=8F=D0=BD=D0=B2. 2019 =D0=B3. =D0=B2 15:51, =D0=AE=D1= =80=D0=B8=D0=B9 : > Hi Igniters, > > As part of IEP-29: 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_I= NDEXES.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 qu= ery. > 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 w= hole > 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, tab= le > 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 include= d > 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? > > -- > =D0=96=D0=B8=D0=B2=D0=B8 =D1=81 =D1=83=D0=BB=D1=8B=D0=B1=D0=BA=D0=BE=D0= =B9! :D > --=20 =D0=96=D0=B8=D0=B2=D0=B8 =D1=81 =D1=83=D0=BB=D1=8B=D0=B1=D0=BA=D0=BE=D0=B9!= :D --00000000000014577b0580357192--