Scheduled analyzed_statistics on a table

Answers

• baron42bba Vertica Customer ✭

  April 2020

  In the code makes most sense if a lot of the table get's modified. Like re-ingesting the data of a complete day.
  a query like

  SELECT 'analyze_statistics(''' || table_schema || '.' || table_name || '.' || table_column_name || ''')' AS suggested_action
  FROM projection_columns
  INNER JOIN
  (SELECT
         DISTINCT
         qe.suggested_action AS suggested_action
  FROM
              v_monitor.query_events   qe
         JOIN v_monitor.query_requests qr
           ON qr.transaction_id        = qe.transaction_id
          AND qr.statement_id          = qe.statement_id
  WHERE
         qr.request_type                        = 'QUERY'
    AND  qe.event_type                          = 'NO HISTOGRAM'
    AND  qe.suggested_action NOT LIKE '%v_temp_schema.%'
  ORDER  BY
         1
  ) sugg ON suggested_action = sugg.suggested_action
  WHERE statistics_type != 'FULL' AND table_schema != 'dba'
  GROUP BY 1
  

  run once a day might give you a list of queries for tables to optimize.

  0