In addition to the set of general rules provided in General Rules For CREATE TABLE AS … WITH NO DATA, the following list of specific rules applies only to an AS … WITH NO DATA clause that does specify a subquery to define the source table for the copy operation.
- Vantage copies zeroed statistics if a set of the columns specified in the subquery do not have uniqueness constraints, and the target table is a SET table.
- Vantage copies zeroed statistics if any of the following clauses, operators, conditions, or function types are specified in the definition of the target table.
- WHERE clause
- GROUP BY clause
- HAVING clause
- QUALIFY clause
- DISTINCT operator
- TOP n operator
- Inner join
- Outer join
- Aggregate function
- OLAP function
- Vantage copies zeroed multicolumn statistics and zeroed index statistics even if the relative order of the index columns specified in the select list is different from that of the index column order in the source table.
- Vantage does not copy zeroed statistics if the subquery references more than 1 table.
- Vantage does not copy zeroed statistics for a column or index if that column or index is modified in the target table definition because this violates the rule against copying statistics when a column or index set is modified in any way in the target table definition.
- Vantage does not copy zeroed statistics for a composite index if any member of the index column set is modified in the target table definition because this violates the rule against copying statistics when a column or index set is modified in any way in the target table definition.
An example case of a modification to a column or index is specifying a CASE expression, arithmetic expression, or any conversion attribute.
- Vantage does not copy zeroed statistics, whether on a single column or on a composite column set, for the system-derived PARTITION column.
- Vantage does not copy zeroed statistics for target table columns that are formulated from complex views or complex derived tables in a subquery.
- Vantage does not copy multicolumn zeroed statistics or composite index zeroed statistics if any of the component columns of the multicolumn statistics or composite index statistics are not copied to the target table.
- Vantage does not copy single-column zeroed statistics or single-column index zeroed statistics if the column or single-column index is not copied to the target table.