Help Center>
GaussDB(DWS)>
FAQs>
Database Usage>
How Do I Query the Information About GaussDB(DWS) Column-Store Tables?
Updated on 2024-04-26 GMT+08:00
How Do I Query the Information About GaussDB(DWS) Column-Store Tables?
The following SQL statements are used to query common information about column-store tables:
Create a column-store table named my_table, and insert data into the table.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 |
CREATE TABLE my_table ( product_id INT, product_name VARCHAR2(40), product_quantity INT ) WITH (ORIENTATION = COLUMN) PARTITION BY range(product_quantity) ( partition my_table_p1 values less than(600), partition my_table_p2 values less than(800), partition my_table_p3 values less than(950), partition my_table_p4 values less than(1000)); INSERT INTO my_table VALUES(1011, 'tents', 720); INSERT INTO my_table VALUES(1012, 'hammock', 890); INSERT INTO my_table VALUES(1013, 'compass', 210); INSERT INTO my_table VALUES(1014, 'telescope', 490); INSERT INTO my_table VALUES(1015, 'flashlight', 990); INSERT INTO my_table VALUES(1016, 'ropes', 890); |
Run the following command to view the created column-store partitioned table:
1 2 3 4 5 6 7 8 9 10 |
SELECT * FROM my_table; product_id | product_name | product_quantity ------------+--------------+------------------ 1013 | compass | 210 1014 | telescope | 490 1011 | tents | 720 1015 | flashlight | 990 1012 | hammock | 890 1016 | ropes | 890 (6 rows) |
Querying the Boundary of a Partition
1 2 3 4 5 6 7 8 9 |
SELECT relname, partstrategy, boundaries FROM pg_partition where parentid=(select parentid from pg_partition where relname='my_table'); relname | partstrategy | boundaries -------------+--------------+------------ my_table | r | my_table_p1 | r | {600} my_table_p2 | r | {800} my_table_p3 | r | {950} my_table_p4 | r | {1000} (5 rows) |
Querying the Number of Columns in a Column-Store Table
1 2 3 4 5 |
SELECT count(*) FROM ALL_TAB_COLUMNS where table_name='my_table'; count ------- 3 (1 row) |
Querying Data Distribution on DNs
1 2 3 4 5 6 7 8 9 10 |
SELECT table_skewness('my_table'); table_skewness ------------------------------------ ("dn_6007_6008 ",3,50.000%) ("dn_6009_6010 ",2,33.333%) ("dn_6003_6004 ",1,16.667%) ("dn_6001_6002 ",0,0.000%) ("dn_6005_6006 ",0,0.000%) ("dn_6011_6012 ",0,0.000%) (6 rows) |
Querying the Names of the Cudesc and Delta Tables in Partition P1 on a DN
1 2 3 4 5 6 |
EXECUTE DIRECT ON (dn_6003_6004) 'select a.relname from pg_class a, pg_partition b where (a.oid=b.reldeltarelid or a.oid=b.relcudescrelid) and b.relname=''my_table_p1'''; relname ---------------------- pg_delta_part_60317 pg_cudesc_part_60317 (2 rows) |
Parent topic: Database Usage
Database Usage FAQs
- How Do I Change Distribution Columns?
- How Do I View and Set the Database Character Encoding?
- What Do I Do If Date Type Is Automatically Converted to the Timestamp Type During Table Creation?
- Do I Need to Run VACUUM FULL and ANALYZE on Common Tables Periodically?
- Do I Need to Set a Distribution Key After Setting a Primary Key?
- Is GaussDB(DWS) Compatible with PostgreSQL Stored Procedures?
- What Are Partitioned Tables, Partitions, and Partition Keys?
- How Can I Export the Table Structure?
- How Do I Delete Table Data Efficiently?
- How Do I View Foreign Table Information?
- If No Distribution Column Is Specified, How Will Data Be Stored?
- How Do I Replace the Null Result with 0?
- How Do I Check Whether a Table Is Row-Stored or Column-Stored?
- How Do I Query the Information About GaussDB(DWS) Column-Store Tables?
- Why Sometimes the GaussDB(DWS) Query Indexes Become Invalid?
- How Do I Use a User-Defined Function to Rewrite the CRC32() Function?
- What Are the Schemas Starting with pg_toast_temp* or pg_temp*?
- Solutions to Inconsistent GaussDB(DWS) Query Results
- Which System Catalogs That the VACUUM FULL Operation Cannot Be Performed on?
- In Which Scenarios Would a Statement Be "idle in transaction"?
- How Does GaussDB(DWS) Implement Row-to-Column and Column-to-Row Conversion?
- What Are the Differences Between Unique Constraints and Unique Indexes?
- What Are the Differences Between Functions and Stored Procedures?
- How Do I Delete Duplicate Table Data?
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.
The system is busy. Please try again later.
For any further questions, feel free to contact us through the chatbot.
Chatbotmore