You are viewing a plain text version of this content. The canonical link for it is here.
Posted to dev@calcite.apache.org by Vitalii Diravka <vi...@apache.org> on 2018/09/20 14:18:55 UTC
ANALYZE TABLE
ANALYZE TABLE statement is commonly used by different SQL engines for
collecting table statistics: PostgeSQL [1], MySQL [2], ORACLE [3],
Microsoft Transact-SQL - UPDATE STATISTICS - [4], SPARK SQL [5], Hive [6].
But I didn't find ANALYZE TABLE statement in SQL specification.
Is there any sense to add it to Calcite (for instance for query
validation)?
Or maybe it can be part of the "babel" parser? If so what dialect should be
selected?
[1] https://www.postgresql.org/docs/8.1/static/sql-analyze.html
[2] https://dev.mysql.com/doc/refman/8.0/en/analyze-table.html
[3]
https://docs.oracle.com/cd/B28359_01/server.111/b28310/general002.htm#ADMIN11524
[4]
https://docs.microsoft.com/en-us/sql/t-sql/statements/update-statistics-transact-sql?view=sql-server-2017
[5]
https://docs.microsoft.com/en-us/sql/t-sql/statements/update-statistics-transact-sql?view=sql-server-2017
[6]
https://cwiki.apache.org/confluence/display/Hive/StatsDev#StatsDev-ANALYZETABLE%3Ctable1%3ECACHEMETADATA
Re: ANALYZE TABLE
Posted by Vitalii Diravka <vi...@apache.org>.
It looks that it can be useful for Calcite based projects just to follow
the common style for similar purposes, in our case for gathering statistics.
But looks like Phoenix has other style [1]: UPDATE STATISTICS my_table ALL
But Hive has [2]: ANALYZE TABLE Table1 PARTITION(ds='2008-04-09', hr=11)
COMPUTE STATISTICS;
So it could be difficult to find the common style here.
[1] https://phoenix.apache.org/update_statistics.html
[2]
https://cwiki-test.apache.org/confluence/display/Hive/StatsDev#StatsDev-ExistingTables%E2%80%93ANALYZE
On Thu, Sep 20, 2018 at 9:29 PM Julian Hyde <jh...@apache.org> wrote:
> Yeah, we should probably go with the de facto standard.
>
> But a bigger question is: what would this command do? Calcite has nowhere
> to store statistics currently. Are people asking for this just so they have
> a template that they can copy-paste into their own Calcite-based project?
>
> Julian
>
>
> > On Sep 20, 2018, at 11:25 AM, Gautam Parai <gp...@mapr.com> wrote:
> >
> > Oracle no longer recommends using ANALYZE TABLE except for certain cases
> > and for preserving backwards compatibility. Instead, they now have a
> > DBMS_STATS package with several methods for collecting statistics. [1]
> >
> > ALTER TABLE is usually associated with DDLs. If several projects/vendors
> > already use ANALYZE for gathering statistics it may still be worthwhile
> > since a majority of folks would be familiar with it?
> >
> > [1]
> >
> https://docs.oracle.com/cd/B28359_01/server.111/b28310/general002.htm#ADMIN11524
> <
> https://docs.oracle.com/cd/B28359_01/server.111/b28310/general002.htm#ADMIN11524
> >
> >
> > Gautam
> >
> > On Thu, Sep 20, 2018 at 11:14 AM Julian Hyde <jhyde@apache.org <mailto:
> jhyde@apache.org>> wrote:
> >
> >> I can’t believe that Microsoft’s command is “UPDATE STATISTICS”.
> >> Especially considering STATISTICS is not an ISO reserved word, so some
> >> folks might actually have a table called STATISTICS.
> >>
> >> In every other database, UPDATE STATISTICS would be a DML command.
> >>
> >>> On Sep 20, 2018, at 11:07 AM, Julian Hyde <jh...@apache.org> wrote:
> >>>
> >>> The Babel parser doesn’t really do DDL (because there is too much
> >> variation among dialects).
> >>>
> >>> The “server” parser might be a better place for this. It has a few,
> >> Calcite-specific DDL statements. It could have ANALYZE too.
> >>>
> >>> In my opinion, Oracle made a mistake when they introduced ANALYE TABLE.
> >> A "ALTER TABLE … COMPUTE STATISTICS” command makes just as much sense.
> >>>
> >>> Julian
> >>>
> >>>
> >>>> On Sep 20, 2018, at 7:18 AM, Vitalii Diravka <vi...@apache.org>
> >> wrote:
> >>>>
> >>>> ANALYZE TABLE statement is commonly used by different SQL engines for
> >>>> collecting table statistics: PostgeSQL [1], MySQL [2], ORACLE [3],
> >>>> Microsoft Transact-SQL - UPDATE STATISTICS - [4], SPARK SQL [5], Hive
> >> [6].
> >>>> But I didn't find ANALYZE TABLE statement in SQL specification.
> >>>>
> >>>> Is there any sense to add it to Calcite (for instance for query
> >>>> validation)?
> >>>> Or maybe it can be part of the "babel" parser? If so what dialect
> >> should be
> >>>> selected?
> >>>>
> >>>> [1]
> >>
> https://urldefense.proofpoint.com/v2/url?u=https-3A__www.postgresql.org_docs_8.1_static_sql-2Danalyze.html&d=DwIFaQ&c=cskdkSMqhcnjZxdQVpwTXg&r=sNneUV1qeI9SCmBsUtXeUfboyubppHxYtOoUl6KCGdI&m=PkqrIAegua3OOSuznxIeNaCL0IDLXgeaKRGxKADXRoE&s=LJ-hvGyGPSMjPWwJqlJTMhosJXRswUIChATBjl_7o8o&e=
> <
> https://urldefense.proofpoint.com/v2/url?u=https-3A__www.postgresql.org_docs_8.1_static_sql-2Danalyze.html&d=DwIFaQ&c=cskdkSMqhcnjZxdQVpwTXg&r=sNneUV1qeI9SCmBsUtXeUfboyubppHxYtOoUl6KCGdI&m=PkqrIAegua3OOSuznxIeNaCL0IDLXgeaKRGxKADXRoE&s=LJ-hvGyGPSMjPWwJqlJTMhosJXRswUIChATBjl_7o8o&e=
> >
> >>>> [2]
> >>
> https://urldefense.proofpoint.com/v2/url?u=https-3A__dev.mysql.com_doc_refman_8.0_en_analyze-2Dtable.html&d=DwIFaQ&c=cskdkSMqhcnjZxdQVpwTXg&r=sNneUV1qeI9SCmBsUtXeUfboyubppHxYtOoUl6KCGdI&m=PkqrIAegua3OOSuznxIeNaCL0IDLXgeaKRGxKADXRoE&s=_40GURZMv45K-ZjtBniCfnQbaEUViMyxSA-yCiLYcNg&e=
> <
> https://urldefense.proofpoint.com/v2/url?u=https-3A__dev.mysql.com_doc_refman_8.0_en_analyze-2Dtable.html&d=DwIFaQ&c=cskdkSMqhcnjZxdQVpwTXg&r=sNneUV1qeI9SCmBsUtXeUfboyubppHxYtOoUl6KCGdI&m=PkqrIAegua3OOSuznxIeNaCL0IDLXgeaKRGxKADXRoE&s=_40GURZMv45K-ZjtBniCfnQbaEUViMyxSA-yCiLYcNg&e=
> >
> >>>> [3]
> >>>>
> >>
> https://urldefense.proofpoint.com/v2/url?u=https-3A__docs.oracle.com_cd_B28359-5F01_server.111_b28310_general002.htm-23ADMIN11524&d=DwIFaQ&c=cskdkSMqhcnjZxdQVpwTXg&r=sNneUV1qeI9SCmBsUtXeUfboyubppHxYtOoUl6KCGdI&m=PkqrIAegua3OOSuznxIeNaCL0IDLXgeaKRGxKADXRoE&s=3AIm_d-iuwx6hmdr0gw0Q_PgmgRA3cB5dsHcPof_aCU&e=
> <
> https://urldefense.proofpoint.com/v2/url?u=https-3A__docs.oracle.com_cd_B28359-5F01_server.111_b28310_general002.htm-23ADMIN11524&d=DwIFaQ&c=cskdkSMqhcnjZxdQVpwTXg&r=sNneUV1qeI9SCmBsUtXeUfboyubppHxYtOoUl6KCGdI&m=PkqrIAegua3OOSuznxIeNaCL0IDLXgeaKRGxKADXRoE&s=3AIm_d-iuwx6hmdr0gw0Q_PgmgRA3cB5dsHcPof_aCU&e=
> >
> >>>> [4]
> >>>>
> >>
> https://urldefense.proofpoint.com/v2/url?u=https-3A__docs.microsoft.com_en-2Dus_sql_t-2Dsql_statements_update-2Dstatistics-2Dtransact-2Dsql-3Fview-3Dsql-2Dserver-2D2017&d=DwIFaQ&c=cskdkSMqhcnjZxdQVpwTXg&r=sNneUV1qeI9SCmBsUtXeUfboyubppHxYtOoUl6KCGdI&m=PkqrIAegua3OOSuznxIeNaCL0IDLXgeaKRGxKADXRoE&s=AqyCkawUMy5vdNCs9WTVh5djUMK9ZwDMT36BjVRaATw&e=
> <
> https://urldefense.proofpoint.com/v2/url?u=https-3A__docs.microsoft.com_en-2Dus_sql_t-2Dsql_statements_update-2Dstatistics-2Dtransact-2Dsql-3Fview-3Dsql-2Dserver-2D2017&d=DwIFaQ&c=cskdkSMqhcnjZxdQVpwTXg&r=sNneUV1qeI9SCmBsUtXeUfboyubppHxYtOoUl6KCGdI&m=PkqrIAegua3OOSuznxIeNaCL0IDLXgeaKRGxKADXRoE&s=AqyCkawUMy5vdNCs9WTVh5djUMK9ZwDMT36BjVRaATw&e=
> >
> >>>> [5]
> >>>>
> >>
> https://urldefense.proofpoint.com/v2/url?u=https-3A__docs.microsoft.com_en-2Dus_sql_t-2Dsql_statements_update-2Dstatistics-2Dtransact-2Dsql-3Fview-3Dsql-2Dserver-2D2017&d=DwIFaQ&c=cskdkSMqhcnjZxdQVpwTXg&r=sNneUV1qeI9SCmBsUtXeUfboyubppHxYtOoUl6KCGdI&m=PkqrIAegua3OOSuznxIeNaCL0IDLXgeaKRGxKADXRoE&s=AqyCkawUMy5vdNCs9WTVh5djUMK9ZwDMT36BjVRaATw&e=
> <
> https://urldefense.proofpoint.com/v2/url?u=https-3A__docs.microsoft.com_en-2Dus_sql_t-2Dsql_statements_update-2Dstatistics-2Dtransact-2Dsql-3Fview-3Dsql-2Dserver-2D2017&d=DwIFaQ&c=cskdkSMqhcnjZxdQVpwTXg&r=sNneUV1qeI9SCmBsUtXeUfboyubppHxYtOoUl6KCGdI&m=PkqrIAegua3OOSuznxIeNaCL0IDLXgeaKRGxKADXRoE&s=AqyCkawUMy5vdNCs9WTVh5djUMK9ZwDMT36BjVRaATw&e=
> >
> >>>> [6]
> >>>>
> >>
> https://urldefense.proofpoint.com/v2/url?u=https-3A__cwiki.apache.org_confluence_display_Hive_StatsDev-23StatsDev-2DANALYZETABLE-253Ctable1-253ECACHEMETADATA&d=DwIFaQ&c=cskdkSMqhcnjZxdQVpwTXg&r=sNneUV1qeI9SCmBsUtXeUfboyubppHxYtOoUl6KCGdI&m=PkqrIAegua3OOSuznxIeNaCL0IDLXgeaKRGxKADXRoE&s=pirNl3Hrz27jJFDJQQO6p3O7PAOOvLe0HPw-EPuPEDQ&e=
> <
> https://urldefense.proofpoint.com/v2/url?u=https-3A__cwiki.apache.org_confluence_display_Hive_StatsDev-23StatsDev-2DANALYZETABLE-253Ctable1-253ECACHEMETADATA&d=DwIFaQ&c=cskdkSMqhcnjZxdQVpwTXg&r=sNneUV1qeI9SCmBsUtXeUfboyubppHxYtOoUl6KCGdI&m=PkqrIAegua3OOSuznxIeNaCL0IDLXgeaKRGxKADXRoE&s=pirNl3Hrz27jJFDJQQO6p3O7PAOOvLe0HPw-EPuPEDQ&e=
> >
>
Re: ANALYZE TABLE
Posted by Julian Hyde <jh...@apache.org>.
Yeah, we should probably go with the de facto standard.
But a bigger question is: what would this command do? Calcite has nowhere to store statistics currently. Are people asking for this just so they have a template that they can copy-paste into their own Calcite-based project?
Julian
> On Sep 20, 2018, at 11:25 AM, Gautam Parai <gp...@mapr.com> wrote:
>
> Oracle no longer recommends using ANALYZE TABLE except for certain cases
> and for preserving backwards compatibility. Instead, they now have a
> DBMS_STATS package with several methods for collecting statistics. [1]
>
> ALTER TABLE is usually associated with DDLs. If several projects/vendors
> already use ANALYZE for gathering statistics it may still be worthwhile
> since a majority of folks would be familiar with it?
>
> [1]
> https://docs.oracle.com/cd/B28359_01/server.111/b28310/general002.htm#ADMIN11524 <https://docs.oracle.com/cd/B28359_01/server.111/b28310/general002.htm#ADMIN11524>
>
> Gautam
>
> On Thu, Sep 20, 2018 at 11:14 AM Julian Hyde <jhyde@apache.org <ma...@apache.org>> wrote:
>
>> I can’t believe that Microsoft’s command is “UPDATE STATISTICS”.
>> Especially considering STATISTICS is not an ISO reserved word, so some
>> folks might actually have a table called STATISTICS.
>>
>> In every other database, UPDATE STATISTICS would be a DML command.
>>
>>> On Sep 20, 2018, at 11:07 AM, Julian Hyde <jh...@apache.org> wrote:
>>>
>>> The Babel parser doesn’t really do DDL (because there is too much
>> variation among dialects).
>>>
>>> The “server” parser might be a better place for this. It has a few,
>> Calcite-specific DDL statements. It could have ANALYZE too.
>>>
>>> In my opinion, Oracle made a mistake when they introduced ANALYE TABLE.
>> A "ALTER TABLE … COMPUTE STATISTICS” command makes just as much sense.
>>>
>>> Julian
>>>
>>>
>>>> On Sep 20, 2018, at 7:18 AM, Vitalii Diravka <vi...@apache.org>
>> wrote:
>>>>
>>>> ANALYZE TABLE statement is commonly used by different SQL engines for
>>>> collecting table statistics: PostgeSQL [1], MySQL [2], ORACLE [3],
>>>> Microsoft Transact-SQL - UPDATE STATISTICS - [4], SPARK SQL [5], Hive
>> [6].
>>>> But I didn't find ANALYZE TABLE statement in SQL specification.
>>>>
>>>> Is there any sense to add it to Calcite (for instance for query
>>>> validation)?
>>>> Or maybe it can be part of the "babel" parser? If so what dialect
>> should be
>>>> selected?
>>>>
>>>> [1]
>> https://urldefense.proofpoint.com/v2/url?u=https-3A__www.postgresql.org_docs_8.1_static_sql-2Danalyze.html&d=DwIFaQ&c=cskdkSMqhcnjZxdQVpwTXg&r=sNneUV1qeI9SCmBsUtXeUfboyubppHxYtOoUl6KCGdI&m=PkqrIAegua3OOSuznxIeNaCL0IDLXgeaKRGxKADXRoE&s=LJ-hvGyGPSMjPWwJqlJTMhosJXRswUIChATBjl_7o8o&e= <https://urldefense.proofpoint.com/v2/url?u=https-3A__www.postgresql.org_docs_8.1_static_sql-2Danalyze.html&d=DwIFaQ&c=cskdkSMqhcnjZxdQVpwTXg&r=sNneUV1qeI9SCmBsUtXeUfboyubppHxYtOoUl6KCGdI&m=PkqrIAegua3OOSuznxIeNaCL0IDLXgeaKRGxKADXRoE&s=LJ-hvGyGPSMjPWwJqlJTMhosJXRswUIChATBjl_7o8o&e=>
>>>> [2]
>> https://urldefense.proofpoint.com/v2/url?u=https-3A__dev.mysql.com_doc_refman_8.0_en_analyze-2Dtable.html&d=DwIFaQ&c=cskdkSMqhcnjZxdQVpwTXg&r=sNneUV1qeI9SCmBsUtXeUfboyubppHxYtOoUl6KCGdI&m=PkqrIAegua3OOSuznxIeNaCL0IDLXgeaKRGxKADXRoE&s=_40GURZMv45K-ZjtBniCfnQbaEUViMyxSA-yCiLYcNg&e= <https://urldefense.proofpoint.com/v2/url?u=https-3A__dev.mysql.com_doc_refman_8.0_en_analyze-2Dtable.html&d=DwIFaQ&c=cskdkSMqhcnjZxdQVpwTXg&r=sNneUV1qeI9SCmBsUtXeUfboyubppHxYtOoUl6KCGdI&m=PkqrIAegua3OOSuznxIeNaCL0IDLXgeaKRGxKADXRoE&s=_40GURZMv45K-ZjtBniCfnQbaEUViMyxSA-yCiLYcNg&e=>
>>>> [3]
>>>>
>> https://urldefense.proofpoint.com/v2/url?u=https-3A__docs.oracle.com_cd_B28359-5F01_server.111_b28310_general002.htm-23ADMIN11524&d=DwIFaQ&c=cskdkSMqhcnjZxdQVpwTXg&r=sNneUV1qeI9SCmBsUtXeUfboyubppHxYtOoUl6KCGdI&m=PkqrIAegua3OOSuznxIeNaCL0IDLXgeaKRGxKADXRoE&s=3AIm_d-iuwx6hmdr0gw0Q_PgmgRA3cB5dsHcPof_aCU&e= <https://urldefense.proofpoint.com/v2/url?u=https-3A__docs.oracle.com_cd_B28359-5F01_server.111_b28310_general002.htm-23ADMIN11524&d=DwIFaQ&c=cskdkSMqhcnjZxdQVpwTXg&r=sNneUV1qeI9SCmBsUtXeUfboyubppHxYtOoUl6KCGdI&m=PkqrIAegua3OOSuznxIeNaCL0IDLXgeaKRGxKADXRoE&s=3AIm_d-iuwx6hmdr0gw0Q_PgmgRA3cB5dsHcPof_aCU&e=>
>>>> [4]
>>>>
>> https://urldefense.proofpoint.com/v2/url?u=https-3A__docs.microsoft.com_en-2Dus_sql_t-2Dsql_statements_update-2Dstatistics-2Dtransact-2Dsql-3Fview-3Dsql-2Dserver-2D2017&d=DwIFaQ&c=cskdkSMqhcnjZxdQVpwTXg&r=sNneUV1qeI9SCmBsUtXeUfboyubppHxYtOoUl6KCGdI&m=PkqrIAegua3OOSuznxIeNaCL0IDLXgeaKRGxKADXRoE&s=AqyCkawUMy5vdNCs9WTVh5djUMK9ZwDMT36BjVRaATw&e= <https://urldefense.proofpoint.com/v2/url?u=https-3A__docs.microsoft.com_en-2Dus_sql_t-2Dsql_statements_update-2Dstatistics-2Dtransact-2Dsql-3Fview-3Dsql-2Dserver-2D2017&d=DwIFaQ&c=cskdkSMqhcnjZxdQVpwTXg&r=sNneUV1qeI9SCmBsUtXeUfboyubppHxYtOoUl6KCGdI&m=PkqrIAegua3OOSuznxIeNaCL0IDLXgeaKRGxKADXRoE&s=AqyCkawUMy5vdNCs9WTVh5djUMK9ZwDMT36BjVRaATw&e=>
>>>> [5]
>>>>
>> https://urldefense.proofpoint.com/v2/url?u=https-3A__docs.microsoft.com_en-2Dus_sql_t-2Dsql_statements_update-2Dstatistics-2Dtransact-2Dsql-3Fview-3Dsql-2Dserver-2D2017&d=DwIFaQ&c=cskdkSMqhcnjZxdQVpwTXg&r=sNneUV1qeI9SCmBsUtXeUfboyubppHxYtOoUl6KCGdI&m=PkqrIAegua3OOSuznxIeNaCL0IDLXgeaKRGxKADXRoE&s=AqyCkawUMy5vdNCs9WTVh5djUMK9ZwDMT36BjVRaATw&e= <https://urldefense.proofpoint.com/v2/url?u=https-3A__docs.microsoft.com_en-2Dus_sql_t-2Dsql_statements_update-2Dstatistics-2Dtransact-2Dsql-3Fview-3Dsql-2Dserver-2D2017&d=DwIFaQ&c=cskdkSMqhcnjZxdQVpwTXg&r=sNneUV1qeI9SCmBsUtXeUfboyubppHxYtOoUl6KCGdI&m=PkqrIAegua3OOSuznxIeNaCL0IDLXgeaKRGxKADXRoE&s=AqyCkawUMy5vdNCs9WTVh5djUMK9ZwDMT36BjVRaATw&e=>
>>>> [6]
>>>>
>> https://urldefense.proofpoint.com/v2/url?u=https-3A__cwiki.apache.org_confluence_display_Hive_StatsDev-23StatsDev-2DANALYZETABLE-253Ctable1-253ECACHEMETADATA&d=DwIFaQ&c=cskdkSMqhcnjZxdQVpwTXg&r=sNneUV1qeI9SCmBsUtXeUfboyubppHxYtOoUl6KCGdI&m=PkqrIAegua3OOSuznxIeNaCL0IDLXgeaKRGxKADXRoE&s=pirNl3Hrz27jJFDJQQO6p3O7PAOOvLe0HPw-EPuPEDQ&e= <https://urldefense.proofpoint.com/v2/url?u=https-3A__cwiki.apache.org_confluence_display_Hive_StatsDev-23StatsDev-2DANALYZETABLE-253Ctable1-253ECACHEMETADATA&d=DwIFaQ&c=cskdkSMqhcnjZxdQVpwTXg&r=sNneUV1qeI9SCmBsUtXeUfboyubppHxYtOoUl6KCGdI&m=PkqrIAegua3OOSuznxIeNaCL0IDLXgeaKRGxKADXRoE&s=pirNl3Hrz27jJFDJQQO6p3O7PAOOvLe0HPw-EPuPEDQ&e=>
Re: ANALYZE TABLE
Posted by Gautam Parai <gp...@mapr.com>.
Oracle no longer recommends using ANALYZE TABLE except for certain cases
and for preserving backwards compatibility. Instead, they now have a
DBMS_STATS package with several methods for collecting statistics. [1]
ALTER TABLE is usually associated with DDLs. If several projects/vendors
already use ANALYZE for gathering statistics it may still be worthwhile
since a majority of folks would be familiar with it?
[1]
https://docs.oracle.com/cd/B28359_01/server.111/b28310/general002.htm#ADMIN11524
Gautam
On Thu, Sep 20, 2018 at 11:14 AM Julian Hyde <jh...@apache.org> wrote:
> I can’t believe that Microsoft’s command is “UPDATE STATISTICS”.
> Especially considering STATISTICS is not an ISO reserved word, so some
> folks might actually have a table called STATISTICS.
>
> In every other database, UPDATE STATISTICS would be a DML command.
>
> > On Sep 20, 2018, at 11:07 AM, Julian Hyde <jh...@apache.org> wrote:
> >
> > The Babel parser doesn’t really do DDL (because there is too much
> variation among dialects).
> >
> > The “server” parser might be a better place for this. It has a few,
> Calcite-specific DDL statements. It could have ANALYZE too.
> >
> > In my opinion, Oracle made a mistake when they introduced ANALYE TABLE.
> A "ALTER TABLE … COMPUTE STATISTICS” command makes just as much sense.
> >
> > Julian
> >
> >
> >> On Sep 20, 2018, at 7:18 AM, Vitalii Diravka <vi...@apache.org>
> wrote:
> >>
> >> ANALYZE TABLE statement is commonly used by different SQL engines for
> >> collecting table statistics: PostgeSQL [1], MySQL [2], ORACLE [3],
> >> Microsoft Transact-SQL - UPDATE STATISTICS - [4], SPARK SQL [5], Hive
> [6].
> >> But I didn't find ANALYZE TABLE statement in SQL specification.
> >>
> >> Is there any sense to add it to Calcite (for instance for query
> >> validation)?
> >> Or maybe it can be part of the "babel" parser? If so what dialect
> should be
> >> selected?
> >>
> >> [1]
> https://urldefense.proofpoint.com/v2/url?u=https-3A__www.postgresql.org_docs_8.1_static_sql-2Danalyze.html&d=DwIFaQ&c=cskdkSMqhcnjZxdQVpwTXg&r=sNneUV1qeI9SCmBsUtXeUfboyubppHxYtOoUl6KCGdI&m=PkqrIAegua3OOSuznxIeNaCL0IDLXgeaKRGxKADXRoE&s=LJ-hvGyGPSMjPWwJqlJTMhosJXRswUIChATBjl_7o8o&e=
> >> [2]
> https://urldefense.proofpoint.com/v2/url?u=https-3A__dev.mysql.com_doc_refman_8.0_en_analyze-2Dtable.html&d=DwIFaQ&c=cskdkSMqhcnjZxdQVpwTXg&r=sNneUV1qeI9SCmBsUtXeUfboyubppHxYtOoUl6KCGdI&m=PkqrIAegua3OOSuznxIeNaCL0IDLXgeaKRGxKADXRoE&s=_40GURZMv45K-ZjtBniCfnQbaEUViMyxSA-yCiLYcNg&e=
> >> [3]
> >>
> https://urldefense.proofpoint.com/v2/url?u=https-3A__docs.oracle.com_cd_B28359-5F01_server.111_b28310_general002.htm-23ADMIN11524&d=DwIFaQ&c=cskdkSMqhcnjZxdQVpwTXg&r=sNneUV1qeI9SCmBsUtXeUfboyubppHxYtOoUl6KCGdI&m=PkqrIAegua3OOSuznxIeNaCL0IDLXgeaKRGxKADXRoE&s=3AIm_d-iuwx6hmdr0gw0Q_PgmgRA3cB5dsHcPof_aCU&e=
> >> [4]
> >>
> https://urldefense.proofpoint.com/v2/url?u=https-3A__docs.microsoft.com_en-2Dus_sql_t-2Dsql_statements_update-2Dstatistics-2Dtransact-2Dsql-3Fview-3Dsql-2Dserver-2D2017&d=DwIFaQ&c=cskdkSMqhcnjZxdQVpwTXg&r=sNneUV1qeI9SCmBsUtXeUfboyubppHxYtOoUl6KCGdI&m=PkqrIAegua3OOSuznxIeNaCL0IDLXgeaKRGxKADXRoE&s=AqyCkawUMy5vdNCs9WTVh5djUMK9ZwDMT36BjVRaATw&e=
> >> [5]
> >>
> https://urldefense.proofpoint.com/v2/url?u=https-3A__docs.microsoft.com_en-2Dus_sql_t-2Dsql_statements_update-2Dstatistics-2Dtransact-2Dsql-3Fview-3Dsql-2Dserver-2D2017&d=DwIFaQ&c=cskdkSMqhcnjZxdQVpwTXg&r=sNneUV1qeI9SCmBsUtXeUfboyubppHxYtOoUl6KCGdI&m=PkqrIAegua3OOSuznxIeNaCL0IDLXgeaKRGxKADXRoE&s=AqyCkawUMy5vdNCs9WTVh5djUMK9ZwDMT36BjVRaATw&e=
> >> [6]
> >>
> https://urldefense.proofpoint.com/v2/url?u=https-3A__cwiki.apache.org_confluence_display_Hive_StatsDev-23StatsDev-2DANALYZETABLE-253Ctable1-253ECACHEMETADATA&d=DwIFaQ&c=cskdkSMqhcnjZxdQVpwTXg&r=sNneUV1qeI9SCmBsUtXeUfboyubppHxYtOoUl6KCGdI&m=PkqrIAegua3OOSuznxIeNaCL0IDLXgeaKRGxKADXRoE&s=pirNl3Hrz27jJFDJQQO6p3O7PAOOvLe0HPw-EPuPEDQ&e=
> >
>
>
Re: ANALYZE TABLE
Posted by Julian Hyde <jh...@apache.org>.
I can’t believe that Microsoft’s command is “UPDATE STATISTICS”. Especially considering STATISTICS is not an ISO reserved word, so some folks might actually have a table called STATISTICS.
In every other database, UPDATE STATISTICS would be a DML command.
> On Sep 20, 2018, at 11:07 AM, Julian Hyde <jh...@apache.org> wrote:
>
> The Babel parser doesn’t really do DDL (because there is too much variation among dialects).
>
> The “server” parser might be a better place for this. It has a few, Calcite-specific DDL statements. It could have ANALYZE too.
>
> In my opinion, Oracle made a mistake when they introduced ANALYE TABLE. A "ALTER TABLE … COMPUTE STATISTICS” command makes just as much sense.
>
> Julian
>
>
>> On Sep 20, 2018, at 7:18 AM, Vitalii Diravka <vi...@apache.org> wrote:
>>
>> ANALYZE TABLE statement is commonly used by different SQL engines for
>> collecting table statistics: PostgeSQL [1], MySQL [2], ORACLE [3],
>> Microsoft Transact-SQL - UPDATE STATISTICS - [4], SPARK SQL [5], Hive [6].
>> But I didn't find ANALYZE TABLE statement in SQL specification.
>>
>> Is there any sense to add it to Calcite (for instance for query
>> validation)?
>> Or maybe it can be part of the "babel" parser? If so what dialect should be
>> selected?
>>
>> [1] https://www.postgresql.org/docs/8.1/static/sql-analyze.html
>> [2] https://dev.mysql.com/doc/refman/8.0/en/analyze-table.html
>> [3]
>> https://docs.oracle.com/cd/B28359_01/server.111/b28310/general002.htm#ADMIN11524
>> [4]
>> https://docs.microsoft.com/en-us/sql/t-sql/statements/update-statistics-transact-sql?view=sql-server-2017
>> [5]
>> https://docs.microsoft.com/en-us/sql/t-sql/statements/update-statistics-transact-sql?view=sql-server-2017
>> [6]
>> https://cwiki.apache.org/confluence/display/Hive/StatsDev#StatsDev-ANALYZETABLE%3Ctable1%3ECACHEMETADATA
>
Re: ANALYZE TABLE
Posted by Julian Hyde <jh...@apache.org>.
The Babel parser doesn’t really do DDL (because there is too much variation among dialects).
The “server” parser might be a better place for this. It has a few, Calcite-specific DDL statements. It could have ANALYZE too.
In my opinion, Oracle made a mistake when they introduced ANALYE TABLE. A "ALTER TABLE … COMPUTE STATISTICS” command makes just as much sense.
Julian
> On Sep 20, 2018, at 7:18 AM, Vitalii Diravka <vi...@apache.org> wrote:
>
> ANALYZE TABLE statement is commonly used by different SQL engines for
> collecting table statistics: PostgeSQL [1], MySQL [2], ORACLE [3],
> Microsoft Transact-SQL - UPDATE STATISTICS - [4], SPARK SQL [5], Hive [6].
> But I didn't find ANALYZE TABLE statement in SQL specification.
>
> Is there any sense to add it to Calcite (for instance for query
> validation)?
> Or maybe it can be part of the "babel" parser? If so what dialect should be
> selected?
>
> [1] https://www.postgresql.org/docs/8.1/static/sql-analyze.html
> [2] https://dev.mysql.com/doc/refman/8.0/en/analyze-table.html
> [3]
> https://docs.oracle.com/cd/B28359_01/server.111/b28310/general002.htm#ADMIN11524
> [4]
> https://docs.microsoft.com/en-us/sql/t-sql/statements/update-statistics-transact-sql?view=sql-server-2017
> [5]
> https://docs.microsoft.com/en-us/sql/t-sql/statements/update-statistics-transact-sql?view=sql-server-2017
> [6]
> https://cwiki.apache.org/confluence/display/Hive/StatsDev#StatsDev-ANALYZETABLE%3Ctable1%3ECACHEMETADATA