osdir.com

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

Re: ANALYZE TABLE


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 <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=
> >> [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=
> >
>
>