osdir.com

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

Re: ANALYZE TABLE


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 <jhyde@xxxxxxxxxx> 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 <gparai@xxxxxxxx> 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@xxxxxxxxxx <mailto:
> jhyde@xxxxxxxxxx>> 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 <jhyde@xxxxxxxxxx> 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 <vitalii@xxxxxxxxxx>
> >> 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=
> >
>