Using the DROP DISTRIBUTIONS ONLY Option

Use the DROP DISTRIBUTIONS ONLY option to remove distribution information from the sysdistrib table and update the systables.version column in the system catalog for those tables whose distributions were dropped, without gathering any LOW mode table and index statistics.

If you specify both the DROP DISTRIBUTIONS ONLY option and the FOR TABLE clause, HCL OneDB™ removes the existing distribution data for the set of columns of the table that the FOR TABLE clause specifies (or for all columns, if you provide no column specification), but does not gather any LOW mode table and index statistics.

You must have the DBA privilege or be owner of the table to use this option.

The following example removes distributions for the customer_num column in the customer table:

UPDATE STATISTICS LOW 
   FOR TABLE customer (customer_num) DROP DISTRIBUTIONS ONLY;

This drops the customer.customer_num distribution data without updating the statistical information that the LOW mode option generates when the ONLY keyword does not follow the DROP DISTRIBUTIONS keywords. This example deletes from the system catalog any row describing customer.customer_num from the sysdistrib table, and updates the version number for customer in the systables table. None of the other LOW mode updates are performed on systables, so the nrow and npused column values are unchanged by this example, and the syscolumns, sysfragments and sysindexes tables of the system catalog are not updated. The LOW keyword has no effect in this example, but the DROP DISTRIBUTIONS ONLY option is not available in MEDIUM or HIGH mode.

Because it specifies no FOR TABLE clause, the next example drops all rows from the sysdistrib table and updates the systables.version column in the system catalog for all tables in the database.

UPDATE STATISTICS DROP DISTRIBUTIONS ONLY;