Yes, you can see whether all AMP random AMP sampling or single AMP random AMP sampling is going to be close to the same by comparing them both. All AMP sampling can only be turned on in DBS Control, so you could get the estimates from the explain text that scans a table both before and after changing that parameter.
Also, since turning on all AMP sampling is a DBS Control setting, all tables in the database will be impacted. You cannot decide to use all AMP sampling on a table by table basis.
Random sampling, whether single AMP or all AMP, has nothing to do with column-level statistics. Here is when random AMP sampling takes place:
If no statistics have been collected, the optimizer will make a rough estimate of a table’s demographics by using dynamic samples from one or more AMPs (one being the default). These samples are collected automatically each time the table header is accessed from disk and are embedded in the table header when it is placed in the dictionary cache.
Random AMP sampling produces simple, limited demographic information. First, it comes up with an estimate of the total rows in the table. Secondly, it samples rows from each non-unique secondary index (NUSI) subtable and extrapolates total distinct values (and from that, an estimated number of rows per value) for each NUSI that has been defined on the table. It ignores non-indexed columns completely and does not try to draw sophisticated conclusions.
I have a table which has a column say X whose cardinality is very low, for example consider a table with billions of records having a column named src_syst_c.
The column src_syst_c is having 50 unique values.
However collecting random sampling stats on this column can give me any number higher or lower, what are the pros and cons of collecting random amp sampling stats for these columns with low cardinality on big tables ?