All Products
Search
Document Center

PolarDB:Manage tables

Last Updated:Sep 11, 2023

This topic describes how to change the sharding keys of tables, disable the full table scan feature, and view the schemas of tables in the PolarDB-X 1.0 console.

Prerequisites

Your client is connected to your PolarDB-X database and a table is created in the database by using an SQL statement. For more information, see Connect to PolarDB-X 1.0 database and Basic SQL operations.

Overview

Change the shard key

A shard key is a field for sharding and is used to generate sharding rules. PolarDB-X 1.0 allows you to modify the shard key in the console. In PolarDB-X 1.0, the sharding strategy of a logical table is defined by the specified sharding function and the shard key. The sharding function specifies the number of shards to be created and the data routing algorithm. Only if the same sharding function and shard key are specified for a PolarDB-X 1.0 database and a table in the database, the database and the table are sharded by using the same sharding strategy.

If a database and a table in the database are sharded by using the same sharding strategy, PolarDB-X 1.0 can locate each physical database shard and physical table shard based on the values of the shard key. If the sharding strategy that is used for database sharding and the sharding strategy that is used for table sharding are different and no conditions are specified to filter database shards and table shards in SQL statements, PolarDB-X 1.0 scans all database shards and table shards to query data.

Disable the full table scan feature

By default, PolarDB-X 1.0 enables the full table scan feature when a table is created. Full table scan operations require database resources. If full table scan operations are frequently performed, query efficiency is reduced. You can disable the full table scan feature for tables in the PolarDB-X 1.0 console.

View the schema of a table

You can view the schema of a table in the PolarDB-X 1.0 console.

Delete tables

Tables cannot be deleted in the PolarDB-X 1.0 console. You can log on to the database and execute SQL statements to delete tables. For more information, see Basic SQL operations.

Go to the Data table configuration page

  1. Log on to the PolarDB for Xscale console.

  2. In the top navigation bar, select the region where the target instance is located.

  3. In the left-side navigation pane, click Instances.

  4. On the page that appears, click the ID of the instance that contains the table you want to manage.

  5. In the left-side navigation pane of the page that appears, choose Configuration and Management > Databases.

  6. Find the database that contains the table you want to manage and click the name of the database.

    Find the database
  7. In the left-side navigation pane of the page that is displayed, choose Table Management > Tables.

Change the shard key

This feature creates a destination table based on information of the specified table name and shard key. Data is synchronized to the destination table in the background. You can use the new destination table directly or execute RENAME statement to replace the original table.

Limits

  • If the sharding function uses YYYYMM, YYYYWEEK, or YYYYDD, the same function must be used for sharding.

  • STR_HASH sharding is not supported.

  • MM, DD, WEEK, and MMDD can server as table sharding functions.

Procedure

  1. On the Data table configuration page, find the table and click Adjust Shard Key in the Actions column.

  2. In the dialog box that appears, modify the sharding strategy, and click Precheck.

    Important
    • You cannot modify the temporary table that is generated or the original table before the precheck task is complete.

    • You cannot change the shard key of a table that has a global secondary index. For information about how to specify a shard key and how to specify a sharding strategy, see CREATE TABLE and Overview.

    • You cannot perform DDL operations on the original and destination tables during data migration.

    • If you want to execute RENAME statement to replace the original table, we recommend that you execute the statement when you stop writing data to the table.

    • If the new table uses the auto-increment primary key, you must modify the start value of the sequence after you execute the RENAME statement. This prevents primary key conflicts. For more information, see Implicit sequences.

  3. After Success is displayed as the precheck result in the dialog box that appears, click Adjust.

  4. Click the 456789 icon in the upper-right corner to view the progress of data migration in the Split tool-data migration message. After the status of the data migration task changes to Success, data migration is complete.

  5. On the page that appears, click Stop Sync. In the message box that appears, click OK.

  6. Refresh the Data table information page to view information about the table for which you changed the shard key. If the table is in the Normal state, the shard key of the table is changed.

Disable the full table scan feature

Important
  • A sharding key is specified for the table for which you want to disable the full table scan feature. For information about how to specify a sharding key, see Change the shard key.

  • If the full table scan feature is disabled for a sharded table in PolarDB-X 1.0, you must include the sharding key in the WHERE clause in SQL statements that are executed to query data in the table. Otherwise, PolarDB-X 1.0 returns the TDDL-4510 ERR_CONTAINS_NO_SHARDING_KEY error code.

  1. On the Data table configuration page, find the table and click Advanced Settings in the Actions column.

    Scan full table
    Note

    The full table scan feature is supported only for tables for which the Table Attribute parameter is set to Database Shard. You can specify a sharding strategy to shard a table. For more information, see Change the shard key.

  2. In the dialog box that appears, select No and click OK.

View the schema of a table

Find the table whose schema you want to view and click View Table Schema in the Actions column. In the dialog box that appears, view the details of the schema, such as the names of columns, data types of columns, and whether a column is a primary key column.

View the schema of a table