Oracle recursive functions

Oracle recursive functions

Oracle start with connect by usage

Connect by prior recursive algorithm in oracle
 
In Oracle, the start with... connect by prior clause connect by is used in structured queries. Its basic syntax is:
Select... from tablename start with condition 1
Connect by condition 2
Where Condition 3;
Example:
Select * from table
Start with org_id = 'hbh1_wgwpy'
Connect by prior org_id = parent_id;
 
Simply put, a tree structure is stored in a table. For example, a table has two fields:
Org_id and parent_id indicate the parent of each record to form a tree structure.
You can use the preceding syntax to query all records of the tree.
Where:
Condition 1 is a restriction statement for the root node. Of course, you can relax the restriction to obtain multiple root nodes. Actually, it is multiple trees.
Condition 2 is the connection condition, where PRIOR is used to represent the previous record. For example, connect by prior org_id = parent_id indicates that the org_id of the previous record is the parent_id of this record, that is, the father of this record is the previous record.
Condition 3 is a filtering condition used to filter all returned records.
 
A brief introduction is as follows:
When you scan a tree structure table early, you need to access each node in the tree structure. A node can only be accessed once. The steps are as follows:
Step 1: start with the root node;
Step 2: Access the node;
Step 3: Check whether the node has any child nodes that have not been accessed. If yes, switch to the left-side unaccessed subsection of the node and perform Step 2. Otherwise, execute step 4;
Step 4: If the node is the root node, the access is completed; otherwise, step 5 is executed;
Step 5: return to the parent node of the node and perform step 3.
 
In short, the process of scanning the entire tree structure is also the process of traversing the tree in the middle order.

Install Oracle 11gR2 (x64) in CentOS 6.4)

Steps for installing Oracle 11gR2 in vmwarevm

Install Oracle 11g XE R2 In Debian

Oracle recursive query
 
1. Tree Structure Description
Data in the tree structure is stored in the table. The hierarchy between the data is the parent-child relationship. It is described by the relationship between columns in the table, such as EMPNO and MGR In the EMP table. EMPNO indicates the employee's number, MGR indicates the number of the person who leads the employee, that is, the MGR value of the child node is equal to the EMPNO value of the parent node. In each row of the table, there is a MGR representing the parent node (except the root node). The entire tree structure can be determined through the parent node of each node.
You can use the connect by and start with clause in the SELECT command to query the tree structure of the table. The command format is as follows:
SELECT...
Connect by {PRIOR column name 1 = column name 2 | column name 1 = PRIOR split name 2}
[Start with];
The connect by clause indicates that each row of data is retrieved in a hierarchical order and that the data in the table is connected to the tree structure. The PRIORY operator must be placed before one of the two columns of the connection relationship. For the parent-child relationship between nodes, the PRIOR operator represents the parent node on one side and the child node on the other side to determine whether the order of the tree structure is top-down or bottom-up. In addition to column names, you can also use column expressions in the join relationship. The start with clause is optional to identify the node used as the root node for searching the tree structure. If this clause is omitted, all rows that meet the query conditions are used as the root node.
Start with: You can specify not only one root node, but also multiple root nodes.
2. About PRIOR
The PRIOR operator is placed before and after the equal sign, which determines the query order.
When PRIOR is placed before a moderate number in the connect by clause, it is forced to search from the root node to the leaf node in sequence, that is, the parent node is directed to the child node through the tree structure, we call it a top-down approach. For example:
Connect by prior empno = MGR
When the PIROR operator is placed behind a moderate number in the connect by clause, it forces the sequential retrieval from the leaf node to the root node, that is, the child node is directed to the parent node through the tree structure, we call it a bottom-up approach. For example:
Connect by empno = PRIOR MGR
In this way, you should also specify a Start node.
3. Define the Start Node for search
When querying the tree structure from top to bottom, you can not only start from the root node, but also define any node as the Start Node to start searching down. In this way, the search result is a branch of the structure tree starting with this node.
4. Use LEVEL
In a table with a tree structure, each row of data is a node in the tree structure. Because the nodes are in different levels, each row of records can have a layer number. The layer number is determined based on the distance between the node and the root node. No matter which node starts, the layer number of the starting root node is always 1, and the child node of the root node is 2.
5. Crop nodes and branches
When querying the tree structure, you can remove some rows in the table, or cut off a branch in the tree, and use the WHERE clause to limit a single node in the tree structure to remove a single node in the tree, however, it does not affect its child nodes (When retrieving from top to bottom) or its predecessors (When retrieving from bottom to top ).
6. Sort display
Like other queries, the order by clause can also be used in tree structure queries to change the display ORDER of query results without having to traverse the tree structure.
 

Bytes ----------------------------------------------------------------------------------------------------------
 
Oracle provides the start with connect by syntax structure to implement recursive queries.
 
1. A simple example:
SQL> select * from test;
 
BILL_MONTH DAY_NUMBER MSISDN
--------------------------------------------------
200803 1 13800
200803 3 13800
200803 2 13800
200803 2 13801
200803 4 13804
200803 5 13804
200803 7 13804
200803 8 13804
200803 6 13802
200803 6 13801
200803 7 13801
200803 8 13801
 
12 rows selected
 
SQL>
SQL> select * from test
2 start with day_number = 1
3 connect by prior day_number = day_number-1 and prior msisdn = msisdn
4;
 
BILL_MONTH DAY_NUMBER MSISDN
--------------------------------------------------
200803 1 13800
200803 2 13800
200803 3 13800
 
SQL>
 

The preceding statement finds the data starting from 1 and increasing from day_number to 1.
 

2. start with connect by syntax structure
As shown in the preceding example, the syntax structure is start with condition connect by condition (including the prior keyword)
The range of seed data given by start with conditon. The conditions for recursive query are provided after connect by. The prior keyword indicates the parent data, and the prior condition indicates the conditions for the child data to meet the parent data.
 
In the following start with connect by structure, it indicates that the data starting from 1 is found, and the day_number gradually increases by 1, and those data with the same msisdn are found.
 
Start with day_number = 1
Connect by prior day_number = day_number-1 and prior msisdn = msisdn
 
3. Execution Plan
For this special syntax structure, let's take a look at its execution plan.
Through the following execution plan, we can see that for a simple recursive query of accessing an object, oracle actually needs to access the object to be queried three times. Therefore, this one tells us that we must be cautious when using recursive queries, because even if there is not much data in the original table, three times the access is preferred, and the cost will be high.
 
SQL> explain plan
2
2 select * from test
3 -- where bill_month = '20140901'
4 start with day_number = 1
5 connect by prior day_number = day_number-1 and prior msisdn = msisdn
6;
 
Explained
 
SQL> select * from table (dbms_xplan.display );
 
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
-------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost |
-------------------------------------------------------------------------
| 0 | select statement | ||||
| * 1 | connect by with filtering |
| * 2 | FILTER |
| 3 | table access full | TEST |
| 4 | nested loops |
| 5 | buffer sort |
| 6 | connect by pump |
| * 7 | table access full | TEST |
| 8 | table access full | TEST |
-------------------------------------------------------------------------
Predicate Information (identified by operation id ):
---------------------------------------------------
1-filter ("TEST". "DAY_NUMBER" = 1)
2-filter ("TEST". "DAY_NUMBER" = 1)
 
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
7-filter ("TEST". "MSISDN" = null and "TEST". "DAY_NUMBER"-1 = NULL)
Note: rule based optimization
 
23 rows selected
 
SQL>
 

In addition, we found that in recursion with other conditions, we first process all recursive queries and finally use the added conditional filter.
See the following example.
Compared with the execution plan above, we can know that after adding the condition where bill_month = '000000', it is actually the 1-filter ("TEST") that is last executed after recursion ". "BILL_MONTH" = '20140901 ').
 
Therefore, to ensure the statement performance, do not directly add the conditions in the start with connect by structure, but try to control the data of the original table. You can use a subquery method or a temporary table (it is best to use a temporary table to control the data volume from the source; as we can see from the execution plan of the subquery, it also accesses the entire table at a time, and then uses conditional filtering to repeat three times. It is enough if it is not a single filter ).

For more details, please continue to read the highlights on the next page:

1 2 Next Page