Oracle table Connection Method Analysis

I. Introduction

The data warehouse technology is a well-known and widely used solution. It is used for all scattered raw business data within the entire Telecom Operating Company and through convenient and effective data access means, users of different departments and different levels can obtain the information they need at any time. The data warehouse system must be able to track and analyze a large amount of historical data in a timely manner and make analysis and prediction in a timely manner. Therefore, real-time performance is an important indicator. Due to its reliability and high performance, Oracle plays the role of a backend database in most data warehouse systems in the telecom industry. Due to the characteristics of the telecom industry, the data volume processed is very large and the processing time is long. In particular, for association operations between large tables, some large tables have hundreds of millions of records, and the processing time is even longer. This has become the main factor affecting the database operation efficiency. Therefore, it is important to optimize the database performance. Performance optimization is a big topic and needs to be considered comprehensively from the server, disk, network, Oracle instance, Oracle SQL and other aspects. This article focuses on analyzing the table connection methods, features, and applicability that greatly affect the system performance in Oracle SQL optimization, and discusses in detail how to use and optimize it.

Ii. Table join

Table join refers to the association between tables in an SQL statement to retrieve relevant data from one or more tables. The join operation is performed by multiple table names of the from clause in the SQL statement and the join conditions between the tables defined in the WHERE clause. If an SQL statement has more than two joined tables, what is the order of connection? Oracle first connects two tables to generate a result set, and then associates the generated result set with the next table. continue until all tables are connected; finally, generate the required data. The following uses the join of two tables as an example:

Create Table user_info (user_name char (10), user_id char (10 ));

Create Table dev_info (dev_no char (10), user_id char (10), dev_type char (10 ));

Description and analysis table connection methods.

Since Oracle 6, the optimizer uses four different table Connection Methods:

• Nested loop join)
• Cluster join)
• Sort merge join)
• Cartesian join)
• Hash join is added in Oracle 7.3)
• In Oracle 8, index join is added)

These six connection methods have their unique technical characteristics. Under certain conditions, they can make full use of the efficient performance.

However, they also have their own limitations. Improper use will not improve the efficiency, but will seriously affect the system performance. Therefore, it is necessary to thoroughly explore the internal operation mechanism of the Connection Mode for performance optimization.

1. nested loop connection

Internal processing process of nested loop connections:

1) Oracle optimizer selects one of the two tables as the driving table based on the Rule RBO or cost-based CBO and specifies it as the External table.

2) The Oracle optimizer then specifies another table as an internal table.

3) Oracle reads the first row from the External table and compares it with the data in the internal table one by one. All matching records are placed in the result set.

4) Oracle reads the second row from the External table and compares it with the data in the internal table one by one. All matching records are added to the result set.

5) Repeat the preceding steps until all records in the External table are processed.

6) Finally, a result set meeting the requirements is generated.

By querying the execution plan of SQL statements, you can see which table is an external table and which is an internal table.

For example, select a. user_name, B. dev_no from user_info A, dev_info B where a. user_id = B. user_id;

The above table is an external table, that is, the driver table. The following table shows the execution plan of the internal table:

Select statement optimizer = choose Nested loops Table Access (full) of 'User _ info' Table Access (full) of 'dev _ info'

Nested loop join is the fastest way to extract the first batch of records from the result set. When the driving row source table (that is, the record being searched) is small, or the connected columns of the internal row source table have a unique index or a highly Optional non-unique index, nested loop connections are ideal. Nested loop connections have advantages over other connection methods. They can quickly extract the first batch of records from the result set without waiting for the entire result set to be completely determined. In this way, end users can view the first batch of records on the query screen and read other records at the same time. No matter how conditions or modes of connection are defined, any two-row record source can use nested loop connections, so nested loop connections are flexible.

However, if the connected columns of the internal row source table (the second table to be read) do not contain indexes, or the indexes are not highly optional, the nested loop connection efficiency is very low. Other connection methods may be more effective if the number of records in the driver table is very large.

You can add hints to an SQL statement to force the Oracle optimizer to generate an execution plan for nested loop connections.

Select/* + use_nl (a B) */a. user_name, B. dev_no from user_info A, dev_info B where a. user_id = B. user_id;

2. Cluster join)

Cluster connection is actually a special case of nested loop connection. If the two connected source tables are in the cluster, that is, the two tables belong to the same segment (segment), Oracle can use the cluster connection. The process is as follows: Oracle reads the first row from the first row source table, and then uses the cluster index in the second row source table to find the matching records; continue the above steps to process the second row in the row source table until all the records have been processed.

The cluster connection efficiency is extremely high, because the two row source tables that participate in the connection are actually in the same physical block. However, there are limits on cluster connection. Two tables without a cluster cannot be connected to a cluster. Therefore, cluster connections are rarely used.

3. sort and merge join (sort merge join)

Internal process of sorting and merging connections:

1) The optimizer checks whether the first source table has been sorted. If it has been sorted, it goes to Step 1; otherwise, it goes to step 2.

2) sorting of the first source table

3) The optimizer checks whether the second source table has been sorted. If the table has been sorted, it goes to Step 1; otherwise, it goes to step 2.

4) sort the second source table

5) merge two source tables that have already been sorted and generate the final result set.

When there is a lack of data selectivity or available indexes, or both source tables are too large (the selected data exceeds 5% of the table records, sort and merge connections are more efficient than nested loop connections.

Sort and merge connections require a relatively large temporary memory block for sorting, which will occupy more memory and disk I/O in the temporary tablespace.

Select a. user_name, B. dev_no from user_info A, dev_info B where a. user_id> B. user_id; Plan -------------------------------------------------- Select statement optimizer = choose (cost = 7 card = 336 bytes = 16128) Merge join (cost = 7 card = 336 bytes = 16128) Sort (join) (cost = 4 card = 82 bytes = 1968) Table Access (full) of 'User _ info' (cost = 2 card = 82 bytes = 1968) Sort (join) (cost = 4 card = 82 bytes = 1968) Table Access (full) of 'dev _ info' (cost = 2 card = 82 bytes = 1968)

You can add hints to an SQL statement to force the Oracle optimizer to generate an execution plan for sorting and merging connections.

Select/* + use_merge (a B) */a. user_name, B. dev_no from user_info A, dev_info B where a. user_id> B. user_id;

Sort and merge connections are based on RBO.

　　4. Cartesian join)

Cartesian join is a condition in which the SQL statement does not write table connections. The optimizer connects each record of the first table to all records of the second table. If the number of records in the first table is m and the number of records in the second table is m, M * n records will be generated.

In the following query, a Cartesian connection is generated if the join condition is not specified.

Select a. user_name, B. dev_no from user_info A, dev_info B;

Due to Cartesian connections, SQL statements with poor performance are rarely used.

5. Hash connection

When the memory can provide sufficient space, the hash connection is the common choice of the Oracle optimizer. In the hash connection, the optimizer first selects the small tables in the two tables based on the statistical information and creates the hash table based on the connection key in the memory. The optimizer then scans the large tables in the table connection, compare the data in a large table with a hash table. If there is associated data, add the data to the result set.

When a small table in a table connection can be completely cached to available memory, the hash connection works best. The cost of hash connection is only the cost of reading two tables from the hard disk to the memory.

However, if the hash table is too large to be fully cached in the available memory, the optimizer will split the hash table into multiple partitions and then cache the partitions one by one into the memory. When the table partition exceeds the available memory, part of the partition data will be temporarily written to the temporary tablespace on the disk. Therefore, when the partition data is written to the disk, the large range (extent) will improve the I/O performance. The recommended range of temporary tablespace in Oracle is 1 MB. The interval size of the temporary tablespace is specified by the uniform size.

After the hash table is created, perform the following operations:

1) scan the second large table

2) if a large table cannot be completely cached to available memory, the large table will also be divided into many partitions.

3) cache the first partition of a large table to the memory.

4) scan the data in the first partition of a large table and compare it with the hash table. If there is a matching record, add it to the result set. 5) Like the first partition, other partitions are also processed.

6) After all partitions are processed, Oracle merges and summarizes the generated result sets to generate the final result.

When the hash table is too large or the available memory is limited, the hash table cannot be completely cached to the memory. As the number of result sets meeting the connection conditions increases, the available memory will decrease. data cached in the memory may be written back to the hard disk. In this case, the system performance will decrease.

When the two connected tables use equivalent join and the data volume of the tables is large, the Optimizer may use Hash join. Hash connections are based on CBO. Oracle uses the hash EDGE connection only when the hash_join_enabled parameter of the database Initialization is set to true and a sufficiently large value is set for the pga_aggregate_target parameter. Hash_area_size is a backward compatible parameter, but hash_area_size should be used in versions earlier than Oracle9i. When ordered is used, the first table in the from clause is used to create a hash table.

Select a. user_name, B. dev_no from user_info A, dev_info B where a. user_id = B. user_id; Plan ---------------------------------------------------------- 0 SELECT statement optimizer = choose (cost = 5 card = 82 bytes = 3936) 1 0 hash join (cost = 5 card = 82 bytes = 3936) 2 1 Table Access (full) of 'User _ info' (cost = 2 card = 82 bytes = 1968) 3 1 Table Access (full) of 'dev _ info' (cost = 2 card = 82 bytes = 1968)

You can add hints to an SQL statement to force the Oracle optimizer to generate an execution plan for hash connections.

Select/* + use_hash (a B) */a. user_name, B. dev_no from user_info A, dev_info B where a. user_id = B. user_id;

When a useful index is missing, hash connections are more effective than nested loop connections. Hash connections may also be faster than nested loop connections, because processing hash tables in memory is faster than retrieving B _ tree indexes.

6. Index connection

If a group of existing indexes contains all the information required for the query, the optimizer will generate a group of hash tables in the index. You can access each index through a range or quick global scan. The scan method you choose depends on the conditions available in the WHERE clause. This method is very effective when a table has a large number of columns and you only want to access a limited number of columns. The more constraints the WHERE clause has, the faster the execution speed. This is because the optimizer treats the constraints as an option when evaluating the Optimization Path for query execution. You must create an index on the appropriate columns (those that meet the entire query) to ensure that the optimizer uses the index connection as one of the options. This task usually involves adding an index to a column that does not have an index or that has not previously created a joint index. Compared with quick global scan, the index connection has the following advantages: Quick global scan only has one index for the entire query; index connections can have multiple indexes for the entire query.

Assume that the table dev_info has two cables (one on dev_no and the other on dev_type ).

Perform the following query:

Select dev_no, dev_type From user_info Where user_id = 'u101010' And dev_type = '000000 ';

Iii. Comparison of several major table connections

Category	Nested loop connection	Sort and merge connections	Hash connection
Optimizer prompt	Use_nl	Use_merge	Use_hash
Conditions for use	Any connection	Mainly used for non-equivalent connections, such as <, <=,>,> =; But not including <>	Used only for equivalent connections
Related Resources	CPU, disk I/O	Memory and temporary space	Memory and temporary space
Features	When there is a highly selective index or the Restricted Search validity rate is relatively high, the first search result can be quickly returned.	When there is no index or the index condition is fuzzy, the sorting merge link is more effective than the nested loop.	In the absence of indexes or fuzzy index conditions, the hash connection is more effective than nested loops. It is usually faster than sorting and merging connections. In a data warehouse environment, if the number of records in a table is large, the efficiency is high.
Disadvantages	When the index is lost or the query conditions are insufficient, the efficiency is very low. When the number of records in a table is large, the efficiency is low.	All tables must be sorted. It is designed for optimal throughput and does not return data until all results are found.	A large amount of memory is required to create a hash table. The first response is slow.

Iv. Conclusion

An in-depth understanding and understanding of Oracle table connections is critical to optimizing database performance. Due to different optimizer selection methods, missing statistical information, or inaccurate statistical information, the table connection method automatically selected by Oracle is not necessarily optimal. When the execution efficiency of SQL statements is low, you can track and analyze the execution plan using auto trace. When a multi-Table connection occurs, you need to carefully analyze whether there are better connection conditions. According to the characteristics of the system, you can add hints to SQL when necessary to change the SQL Execution Plan, so as to achieve performance optimization.