Table Design Specifications
- [Rule] View nesting is not recommended.
If wildcards are used during view compilation, an error occurs in the view when columns are added to or deleted from the called view. At the same time, view nesting may cause low execution efficiency because indexes cannot be used. Therefore, you are advised to use base tables with indexes instead of performing join operations on views.
- [Recommendation] Try to avoid collation operations in a view definition.
ORDER BY is invalid in the top-level view. If you must collate the output data, use ORDER BY in a called view.
- [Recommendation] Minimize random I/Os. Through clustering, you can sequentially store hot data, converting random I/O to sequential I/O to reduce the cost of I/O scanning.
Selecting a Partitioning Mode
Partitioning Mode |
Description |
---|---|
Range |
Table data is partitioned by range. |
Interval |
Table data is partitioned by range. If the data exceeds the range, a new partition is automatically created based on the interval. |
List |
Table data is partitioned by a specified column based on a specific value. |
Hash |
Table data is partitioned by hash. |
Comply with the following rules when partitioning a table with a large amount of data:
- [Rule] Select a proper partition solution for large data tables.
Currently, GaussDB provides the RANGE PARTITION, LIST PARTITION, and HASH PARTITION partitioning methods. Note the following principles during partitioning:
- You are advised to use interval columns, such as date and area, for partitioning.
- The upper boundary value of a range partition must be set to MAXVALUE to prevent data overflow.
- The partition name must reflect the data features of the partition. For example, the partition name can be in the format of keyword+interval feature.
- [Recommendation] It is recommended that the number of partitions be less than or equal to 100, the data volume of a single partition be less than or equal to 50 million, and the data capacity of a single partition be less than or equal to 50 GB.
- [Recommendation] Create partitions on columns that indicate certain ranges, such as dates and regions.
- [Recommendation] A partition name should show the data characteristics of a partition. For example, its format can be Keyword+Range characteristics.
- [Recommendation] Set the upper limit of a partition to MAXVALUE to prevent data overflow.
- [Description] Reduce the amount of data to be scanned by using partition pruning.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 |
-- Create a range partitioned table. CREATE TABLE staffS_p1 ( staff_ID NUMBER(6) not null, FIRST_NAME VARCHAR2(20), LAST_NAME VARCHAR2(25), EMAIL VARCHAR2(25), PHONE_NUMBER VARCHAR2(20), HIRE_DATE DATE, employment_ID VARCHAR2(10), SALARY NUMBER(8,2), COMMISSION_PCT NUMBER(4,2), MANAGER_ID NUMBER(6), section_ID NUMBER(4) ) PARTITION BY RANGE (HIRE_DATE) ( PARTITION HIRE_19950501 VALUES LESS THAN ('1995-05-01 00:00:00'), PARTITION HIRE_19950502 VALUES LESS THAN ('1995-05-02 00:00:00'), PARTITION HIRE_maxvalue VALUES LESS THAN (MAXVALUE) ); -- Create an interval partitioned table. The table has two initial partitions. When data beyond the partition range is inserted, another partition is automatically added. CREATE TABLE sales (prod_id NUMBER(6), cust_id NUMBER, time_id DATE, channel_id CHAR(1), promo_id NUMBER(6), quantity_sold NUMBER(3), amount_sold NUMBER(10,2) ) PARTITION BY RANGE (time_id) INTERVAL('1 day') ( PARTITION p1 VALUES LESS THAN ('2019-02-01 00:00:00'), PARTITION p2 VALUES LESS THAN ('2019-02-02 00:00:00') ); -- Create a list partitioned table. CREATE TABLE test_list (col1 int, col2 int) partition by list(col1) ( partition p1 values (2000), partition p2 values (3000), partition p3 values (4000), partition p4 values (5000) ); -- Create a hash partitioned table. CREATE TABLE test_hash (col1 int, col2 int) partition by hash(col1) ( partition p1, partition p2 ); |
For details about the table partition syntax, see CREATE TABLE PARTITION.
Feedback
Was this page helpful?
Provide feedbackThank you very much for your feedback. We will continue working to improve the documentation.See the reply and handling status in My Cloud VOC.
For any further questions, feel free to contact us through the chatbot.
Chatbot