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 <[email protected]> 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 <[email protected]>:
>
>> 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
>>
>
>
