StartWith Operator
Description
The hierarchical query operator is used to perform recursive query operations. The hierarchical query process is as follows:
- Select the initial dataset based on the condition in the START WITH area and set the initial dataset as the working set.
- If the working set is not empty, use the data in the working set as the input for the next query. Specify the filter criteria in the CONNECT BY clause. The keyword PRIOR indicates the current record.
- Set the dataset filtered in step 2 as the working set and repeat the operation in step 2.
In addition, the database adds the following pseudocolumns to each selected data record so that you can learn about the location of the data in the recursive or tree structure. You can create indexes based on the conditions in the CONNECT BY clause to improve the performance of the START WITH clause.
Typical Scenarios
Perform hierarchical query.
Examples
-- Prepare data. gaussdb=#DROP TABLE IF EXISTS area; gaussdb=#CREATE TABLE area (id INT,name VARCHAR(25),parent_id INT); CREATE TABLE gaussdb=#INSERT INTO area VALUES (1,'China',NULL); INSERT 0 1 gaussdb=#INSERT INTO area VALUES (2,'Beijing',1); INSERT 0 1 gaussdb=#INSERT INTO area VALUES (3,'Chaoyang District',2); INSERT 0 1 gaussdb=#INSERT INTO area VALUES(4,'Shaanxi Province',1); INSERT 0 1 gaussdb=#INSERT INTO area VALUES(5,'Xi'an',4); INSERT 0 1 gaussdb=#INSERT INTO area VALUES (6,'Yanta District',5); INSERT 0 1 gaussdb=#INSERT INTO area VALUES(7,'Weiyang District',5); INSERT 0 1 -- Execution result. gaussdb=#EXPLAIN SELECT level, name FROM area START WITH (id = 1) CONNECT BY PRIOR id = parent_id; QUERY PLAN ------------------------------------------------------------------------------------------ CTE Scan on tmp_reuslt (cost=294.67..295.55 rows=44 width=72) CTE tmp_reuslt -> StartWith Operator (cost=0.00..294.67 rows=44 width=76) Start With pseudo atts: array_key_1 -> Recursive Union (cost=0.00..294.67 rows=44 width=76) -> Seq Scan on area (cost=0.00..19.64 rows=4 width=76) Filter: (id = 1) -> Hash Join (cost=27.35..27.42 rows=4 width=76) Hash Cond: (tmp_reuslt.id = public.area.parent_id) -> WorkTable Scan on tmp_reuslt (cost=0.00..0.02 rows=1 width=4) -> Hash (cost=17.71..17.71 rows=771 width=76) -> Seq Scan on area (cost=0.00..17.71 rows=771 width=76) (12 rows) -- Drop. gaussdb=#DROP TABLE IF EXISTS area;
In the preceding example, the output of the StartWith operator is as follows.
Item |
Description |
---|---|
StartWith |
Operator name. |
Start With pseudo atts |
Recursive key column number. Start With pseudo atts: array_key_1 indicates that the condition marked by prior is the first column of the original table and recursion is performed based on this column. |
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