Shulou(Shulou.com)06/01 Report--

What are the ways to optimize the join mode of Oracle tables? In view of this problem, this article introduces the corresponding analysis and answers in detail, hoping to help more partners who want to solve this problem to find a more simple and feasible way.

In Oracle database, there are four table join methods between two tables: sort merge join, nested loop join, hash join and Cartesian join.

1. Sort merge join (sort merge join)

Sort merge join is a table join method in which two tables use sort (SORT) operation and merge (MERGE) operation to get the join result set when making a table join.

If tables T1 and T2 use sort merge joins when making table joins, Oracle performs the following steps in turn:

a. Access the T1 table with the predicate condition specified in the target SQL, and then sort the access results according to the join column of the T1 table, and the sorted result set is recorded as S1.

b. Access the T2 table with the predicate condition specified in the target SQL, and then sort the access results according to the join column of the T2 table, and the sorted result set is recorded as S2.

c. Merge S1 and S2, and take the matching record as the final result set

Advantages and disadvantages of sorting merge connections and applicable scenarios:

a. In general, hash join is better than sort merge join, but if the row source has been sorted and there is no need for sorting when executing sort merge join, then sort merge join will perform better than hash join

b. Typically, sort merge connections are used only if the following occurs:

1) RBO mode

2) non-equivalent connection (>, =, select * from scott.emp T1 where t1.empno > t2.mgr 89 rows selected.Execution Plan---Plan hash value: 3950110903 Murray- -| Id | Operation | Name | Rows | Bytes | Cost (% CPU) | Time |- -| 0 | SELECT STATEMENT | | 62 | 4712 | 6 (17) | 00:00:01 | | 1 | MERGE JOIN | | 62 | 4712 | 6 (17) | 00:00:01 | | 2 | SORT JOIN | | 14 | 532 | 2 (0) | 00:00:01 | 3 | TABLE ACCESS BY INDEX ROWID | EMP | 14 | 532 | 2 (0) | 00:00:01 | 4 | INDEX FULL SCAN | PK_EMP | 14 | 1 (0) | 00:00:01 | * 5 | SORT JOIN | | | | 14 | 532 | 4 (25) | 00:00:01 | 6 | TABLE ACCESS FULL | EMP | 14 | 532 | 3 (0) | 00:00:01 |-- | -- Predicate Information (identified by operation id):-5-access (INTERNAL_FUNCTION ("T1". "EMPNO") > INTERNAL_FUNCTION ("T2". "MGR") filter (INTERNAL_FUNCTION ("T1". "EMPNO") > INTERNAL_FUNCTION ("T2". "MGR") Statistics--- 1 recursive calls 0 db block gets 8 consistent gets 0 physical reads 0 Redo size 6612 bytes sent via SQL*Net to client 575 bytes received via SQL*Net from client 7 SQL*Net roundtrips to/from client 2 sorts (memory) 0 sorts (disk) 89 rows processedSQL >

two。 Nested Loop join (nested loops join)

Nested loop join is a table join method in which two tables rely on two layers of nested loops (outer loop / inner loop) to get the join result set.

If the T1 and T2 tables use nested loop joins when making table joins, Oracle performs the following steps in turn:

a. First of all, the optimizer will follow certain rules to determine who is the driven table and who is the driven table. The driven table is used in the outer loop and the driven table is used in the memory loop. Suppose T1 is the driver table.

b. Access the driver table T1 with the predicate condition specified in the target SQL to get the result set S1

c. Traverse S1 and traverse driven table T2 at the same time, that is, the records in S1 are matched with driven table T2 according to the connection conditions. The resulting result set will be returned

Advantages and disadvantages of nested loop connections and applicable scenarios:

a. Fast response can be achieved, that is, records that have been connected and meet the connection conditions can be returned as soon as possible, without having to wait for all the connection operations to be completed before returning the connection result.

b. It is suitable for the case where the number of records of the driven result set corresponding to the driven table is small, and at the same time, there is a unique index on the join column of the driven table (or a non-unique index with good selectivity on the join column of the driven table).

Example

SQL > select / * + gather_plan_statistics use_nl (T1 null,0,'allstats,last') * / * from scott.emp T1 where t1.deptno = t2.dept from table > select * SQL (dbms_xplan.display_cursor (null,0,'allstats,last')) PLAN_TABLE_OUTPUT- -SQL_ID dcsf9m1rzzga5 Child number 0--select / * + gather_plan_statistics use_nl (T1 dint T2) * / * from scott.empt1 Scott.dept T2 where t1.deptno = t2.deptnoPlan hash value: 4192419542-| Id | Operation | Name | Starts | E-Rows | A- Rows | A-Time | Buffers |-| 0 | SELECT STATEMENT | | 1 | | 14 | 00:00 |: 00.01 | 32 | 1 | NESTED LOOPS | | 1 | 14 | 14 | 00 DEPT 00.01 | 32 | 2 | TABLE ACCESS FULL | DEPT | 1 | 4 | 4 | 00 DEPT | 7 | | * 3 | TABLE ACCESS FULL | EMP | 4 | 4 | 14 | 00 DEPT | 25 |-| -Predicate Information (identified by operation id):- -filter ("T1". "DEPTNO" = "T2". "DEPTNO") rows selected.SQL >

3. Hash join (hash join)

Hash join is a table join method in which two tables rely on hash operation to get the join result set when making a table join. After oracle 7.3, it is introduced.

Hash join works by performing hash operations on a table (usually the smaller table) and storing it in the hash list, extracting records from another table, doing the hash operation, finding the corresponding values in the hash list, and matching them.

Hash joins are only available for CBO and can only be used for equivalent join conditions

Hash joins are very suitable for joining small tables and large tables, especially when the join columns of small tables are very selective. at this time, the execution time of the hash join can be approximately regarded as equivalent to the time it takes to scan the large table with a full table.

When hashing is connected, the Hash Table corresponding to the driving result set can be completely contained in memory (the workspace of PGA), and the execution efficiency of hash connection is very high.

Performance problems with hash connections can be diagnosed by the 10104 event, as described below:

Number of in-memory partitions (may have changed): Hash Partition

Final number of hash buckets: number of Hash Bucket

Total buckets: Empty buckets: Non-empty buckets: Hash Bucket Hollow record and non-empty record

Total number of rows: the number of records driving the result set

Maximum number of rows in a bucket: the number of records contained in the Hash Bucket with the largest number of records

Disabled bitmap filtering: whether bitmap filtering is enabled

Example

SQL > select / * + gather_plan_statistics use_hash (T1 null,0,'allstats,last') * / * from scott.emp T1 where t1.deptno = t2.dept from table > select * SQL (dbms_xplan.display_cursor (null,0,'allstats,last')) PLAN_TABLE_OUTPUT- -SQL_ID 0j83q86ara5u2 Child number 0--select / * + gather_plan_statistics use_hash (T1 dint T2) * / * from scott.empt1 Scott.dept T2 where t1.deptno = t2.deptnoPlan hash value: 615168685- -| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem |- -- | 0 | SELECT STATEMENT | | 1 | | 14 | 00 00.01 | 13 | | * 1 | HASH JOIN | | 1 | 14 | 00.01 | 00RV 00.01 | 13 | | | 1321K | 1321K | 1070K (0) | | 2 | TABLE ACCESS FULL | DEPT | 1 | 4 | 4 | 00 TABLE ACCESS FULL 00.01 | 6 | | 3 | TABLE ACCESS FULL | EMP | 1 | 14 | 14 | 00 TABLE ACCESS FULL 00.01 | 7 |-| -Predicate Information (identified by operation id):- -- 1-access ("T1". "DEPTNO" = "T2". "DEPTNO") 21 rows selected.SQL > this is the answer to the question about how to optimize the connection of Oracle tables. I hope the above content can help you to a certain extent, if you still have a lot of doubts to be solved, you can follow the industry information channel to learn more related knowledge.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.