IBM Netezza or Puredata Systems uses the cost based optimizer to determine best methods determine redistribution, scan, join, join orders. Optimizer uses statistics to generate optimal execution plan. You need collect statistics on tables or databases regularly. In my other post, Netezza Generate Statistics: A Guide and Best Practices, we have discussed some of best practices to generate statistics on Netezza tables. In this post, we will discuss different methods to display Netezza table statistics with some examples.
What Are Table Statistics?
Tables statistics are nothing but information about each column proportion of the
- Duplicate values,
- Unique values,
- NULL values
- Maximum and minimum values.
Netezza cost based optimized uses these information to generate optimal plan for query execution.
Display Netezza Table Statistics
As a part of maintenance, Netezza administrator usually collects stats regularly using automated scripts. There are various methods that you can use to display statistics that are collected on the Netezza tables. Below are some of methods that you can use to view Netezza tables statistics:
Netezza System Table/Vies – _v_statistic and _v_relation_column
You can use Netezza system tables or views to check latest statistics on the tables. You can find more infromation on queries and system views in one or nzcommunity discussion topic: how to check whether table stats is out-dated ?
As discussed in that post, you can use _v_statistic and _v_relation_column system view to check statistics on tables.
Netezza Provided Script – nz_get
IBM provides set of utility scripts such as formatting Netezza view, count number of records in Netezza database tables, migrating Netezza database and tables to another host.
Those Netezza utility scripts are usually present in /nz/support/bin directory on host machine or edge node.
Another useful script that you can use is nz_get script to get or display the statistics of the Netezza table.
nz_get Script usage and Example
As mentioned in previous section, you can use ne_get script to display Netezza table statistics. Below is the usage and example of nz_get script:
[nz@netezza bin]$ /nz/support/bin/nz_get TESTDB INVENTORYQuery to View Netezza Table Statistics
Modified nzsql query code from nz_get script to provide Netezza table statistics for all tables in given Netezza databases. Here is full SQL query that might help you:
https://gist.github.com/45693f7a7040df2d91307db76859bfb4
Provide database and table name to get tables statistics.
Hope this helps 🙂