Oracle tree query performance optimization documentary (startwith, connectby)

For permission control in A project, you need to use the Organization class to control the data that can be accessed. For example, the person in organization A can view the data of the personnel in its subordinate organization, or only organization A is organization B.

For permission control in A project, you need to use the Organization class to control the data that can be accessed. For example, the person in organization A can view the data of the personnel in its subordinate organization, or only organization A is organization B.

To control permissions in a project, you must use the Organization class to control the data that can be accessed,
For example, A person in organization A can view the personnel data of its subordinate organization, or has the right to view the personnel data of organization B only when organization A is the superior of organization B.
Construct the database table structure as needed, as shown in the following figure (ORG_RANK)

Organizational ID (PK) Upper organizational ID
ORG_ID HIGH_ORG_ID

Based on the above structure, use the Oracle tree query statement (start with and connect by) to create an SQL statement, as shown below:

Query the subordinate organizations of a specified organization:

SQL code
Select ORANK. ORG_ID
From ORG_RANK ORANK
Where (level-1) = 1
Start with ORANK. ORG_ID = # orgId #
Connect by prior ORANK. ORG_ID = ORANK. HIGH_ORG_ID
Select ORANK. ORG_ID
From ORG_RANK ORANK
Where (level-1) = 1
Start with ORANK. ORG_ID = # orgId #
Connect by prior ORANK. ORG_ID = ORANK. when HIGH_ORG_ID evaluates the performance of the preceding SQL statements, it finds serious performance problems. (when there are 10 layers of organizations and 3000 pieces of data, the query time is more than one minute. The following optimization is performed.

1. If the execution plan is analyzed and a Full Table is found, the index usage fails. The optimization method is to add an index to HIGH_ORG_ID.

2. Although only the subordinate organizations are queried, when the preceding SQL statement is executed, all the lower-level organizations of the specified organization are first queried,

Then, filter out the lower-level organizations (where (level-1) = 1) from the results ).

The above analysis proves that the execution time of the second-to-last organizational unit of the input is much less than that of the upper-level organizational unit of the input.

The optimization method is to add the conditions (and (level-1) <= 1) for the connect by statement. Subtrees that do not meet the conditions are not queried, and many useless recursive queries are saved.

SQL code
Select ORANK. ORG_ID
From ORG_RANK ORANK
Where (level-1) = 1
Start with ORANK. ORG_ID = # orgId #
Connect by prior ORANK. ORG_ID = ORANK. HIGH_ORG_ID
And (level-1) <= 1
Select ORANK. ORG_ID
From ORG_RANK ORANK
Where (level-1) = 1
Start with ORANK. ORG_ID = # orgId #
Connect by prior ORANK. ORG_ID = ORANK. HIGH_ORG_ID
And (level-1) <= 1 determines that organization A is the upper-level organization of organization B:

Method 1: Query all lower-level organizations of A to see if B exists;

Method 2: Query all upper-layer organizations of B to see if A exists.

As long as you draw a tree structure in your mind, you will naturally think that the execution speed of method 2 will be much faster than that of method 2,

The second method is retrograde query, with a small amount of data found.