![]() For information about how to query the state of a BUILD task, see the " Query the state of a BUILD task" section of the BUILD topic.įor information about how to specify a hot and cold data storage policy when you create a table, see CREATE TABLE.Įxample 1: Change the storage policy of a table to COLD ALTER TABLE test_table storage_policy = 'COLD' Įxample 2: Change the storage policy of a table to HOT ALTER TABLE test_table storage_policy = 'HOT' Įxample 3: Change the storage policy of a table to MIXED and set the number of hot partitions to 10 ALTER TABLE test_table storage_policy = 'MIXED' hot_partition_count = 10 īy default, the partition function change feature is disabled for AnalyticDB for MySQL clusters. After the execution is complete, the change takes effect. To trigger an immediate change, you can execute the BUILD TABLE db_name.table_name statement. The storage policy is not immediately changed after the ALTER TABLE statement is executed. You can change the storage policy between COLD, HOT, and MIXED. Syntax ALTER TABLE db_name.table_name MODIFY column_name data_type ALTER TABLE adb_demo.customer MODIFY COLUMN province varchar comment 'The province where the customer is located' Ĭhange the value constraint for a column from NOT NULL to NULL Syntax ALTER TABLE db_name.table_name MODIFY column_name data_type comment 'new_comment' Ĭhange the comment of the province column in the customer table to "The province where the customer is located". ![]() ALTER TABLE adb_demo.customer DROP COLUMN province Remove the province column from the customer table. Syntax ALTER TABLE db_name.table_name DROP column_name data_type ALTER TABLE adb_demo.customer ADD COLUMN province varchar comment 'Province' Syntax ALTER TABLE db_name.table_name ADD column_name data_type Īdd the province column of the VARCHAR type to the customer table. To incorporate new columns, you must recreate the view.You cannot modify the order of columns. +-Īdding new columns to a table that has an associated view does not update the view's result set, even if the view uses a wildcard (*) to represent all table columns. ORDER BY projection_name, projection_column_name => SELECT projection_column_name, projection_name FROM projection_columns WHERE table_name ILIKE 'store_orders' => ALTER TABLE public.store_orders ADD COLUMN delivery_date date PROJECTIONS (store_orders_p) Because the statement omits the PROJECTIONS option, Vertica adds the column only to the table's superprojection: The following ALTER TABLE…ADD COLUMN statement adds column expected_ship_date to the store_orders table. ALL PROJECTIONS adds the column to all projections of this table, excluding projections with pre-aggregated data.įor example, the store_orders table has two projections-superprojection store_orders_super, and user-created projection store_orders_p.The projection list cannot include projections with pre-aggregated data such as live aggregate projections otherwise, Vertica rolls back the ALTER TABLE statement. Vertica adds the column to all buddies of each projection. PROJECTIONS ( projections-list): Adds the new column to one or more projections of this table, specified as a comma-delimted list of projection base names.The ADD…COLUMN clause can also specify to add the column to one or more non-superprojections, with one of these options: When you add a column to a table, Vertica automatically adds the column to superprojections of that table. The lock also blocks SELECT statements issued at SERIALIZABLE isolation level, until the operation completes.Īdding a column to a table does not affect K-safety of the physical schema design. The lock prevents DELETE, UPDATE, INSERT, and COPY statements from accessing the table. When you use ADD COLUMN to alter a table, Vertica takes an O lock on the table until the operation completes. Before you add columns to a table, verify that all its superprojections are up to date.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |