hive-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Mich Talebzadeh <mich.talebza...@gmail.com>
Subject Re: last stats time on table columns
Date Fri, 17 Jun 2016 10:39:43 GMT
In general to see if columns stats have been updated you need to look at
the metadata tables tbl, tab_col_stats, table_params and so forth.

For example all table parameters are stored in table_params table, the
column stats are stored in epoch date in tab_col_stats

My metadata in on Oracle but table structure will be the same across all
databases I assume.

For example the following will get the last stats time for columns

set echo off
set linesize 180
set pagesize 40
set heading on
break on Database skip 1 on report
break on Table skip 1 on report
column time format a25 heading "LAST_ANALYSED Time (GMT)"
SELECT
          SUBSTR(DB_NAME,1,12) AS "Database"
        , SUBSTR(TABLE_NAME,1,15) AS "Table"
        , SUBSTR(COLUMN_NAME,1,15) AS "Column"
        , SUBSTR((timestamp '1970-01-01 00:00:00' +
NUMTODSINTERVAL(LAST_ANALYZED,'second')) AT TIME ZONE
tz_offset('GMT'),1,18) AS time
FROM tab_col_stats
ORDER by DB_NAME, TABLE_NAME, COLUMN_NAME;

And the output will be something like

Database     Table           Column          LAST_ANALYSED Time (GMT)
------------ --------------- --------------- -------------------------
oraclehadoop dummy           clustered       16-JUN-16 16.23.32
oraclehadoop                 id              16-JUN-16 16.23.32
oraclehadoop                 padding         16-JUN-16 16.23.32
oraclehadoop                 random_string   16-JUN-16 16.23.32
oraclehadoop                 randomised      16-JUN-16 16.23.32
oraclehadoop                 scattered       16-JUN-16 16.23.32
oraclehadoop                 small_vc        16-JUN-16 16.23.32

oraclehadoop sales_staging   amount_sold     17-JUN-16 09.25.08
oraclehadoop                 channel_id      17-JUN-16 09.25.08
oraclehadoop                 cust_id         17-JUN-16 09.25.08
oraclehadoop                 prod_id         17-JUN-16 09.25.08
oraclehadoop                 promo_id        17-JUN-16 09.25.08
oraclehadoop                 quantity_sold   17-JUN-16 09.25.08
oraclehadoop                 time_id         17-JUN-16 09.25.08
That is the only way I could find the stats time for columns.

HTH

Dr Mich Talebzadeh



LinkedIn * https://www.linkedin.com/profile/view?id=AAEAAAAWh2gBxianrbJd6zP6AcPCCdOABUrV8Pw
<https://www.linkedin.com/profile/view?id=AAEAAAAWh2gBxianrbJd6zP6AcPCCdOABUrV8Pw>*



http://talebzadehmich.wordpress.com



On 17 June 2016 at 06:55, Damien Carol <damien.carol@gmail.com> wrote:

> ANALYZE TABLE <table_name> COMPUTE STATISTICS => change stats for the
> table and should should it
> ANALYZE TABLE <table_name> COMPUTE STATISTICS for COLUMNS => change stats
> for columns and should change it for columns but NOT for the table
>
> That's it.
>
> 2016-06-16 21:10 GMT+02:00 Ashok Kumar <ashok34668@yahoo.com>:
>
>> Greeting gurus,
>>
>> When I use
>>
>> ANALYZE TABLE <table_name> COMPUTE STATISTICS for COLUMNS,
>>
>> Where can I get the last stats time.
>>
>> DESC FORMATTED <table_name> does not show it
>>
>> thanking you
>>
>
>

Mime
View raw message