git.net

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

Re: ANALYZE TABLE


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://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
>