osdir.com

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

Re: ANALYZE TABLE


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