The content of this article is about the optimization method of Oracle table connection (with examples). It has certain reference value. Friends in need can refer to it. I hope it will be helpful to you.
In Oracle database, there are four table connection methods between two tables: sort merge connection, nested loop connection, hash connection and Cartesian connection
1. Sort merge connection (sort merge join)
Sort merge join is a table connection method that uses sort (SORT) operation and merge (MERGE) operation to obtain the connection result set when two tables are connected.
If the t1 table and the t2 table use a sort-merge connection when making table connections, Oracle will perform the following steps in sequence:
a. Access the t1 table with the predicate conditions specified in the target SQL, and then access The results are sorted according to the connection column of the t1 table, and the sorted result set is recorded as s1
b. Access the t2 table based on the predicate conditions specified in the target SQL, and then sort the access results according to the connection column of the t2 table. , the sorted result set is recorded as s2
c. Merge s1 and s2, and take out the matching records as the final result set
The advantages, disadvantages and application of sorting and merging connections Scenario:
a. Under normal circumstances, the effect of hash join is better than sort merge join. However, if the row source has been sorted, there is no need to sort again when executing sort merge join. In this case, sort merge The performance of join will be better than hash join
b. Normally, sort merge join will be used only when the following situations occur:
1) RBO mode
2) Unequal value join (>,<,>=,<=)
3) When hash join is disabled (_HASH_JOIN_ENABLED=false)
Example
SQL> select * from scott.emp t1,scott.emp t2 where t1.empno > t2.mgr;
89 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 3950110903
----------------------------------------------------------------------------------------
| 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 processed
SQL>2. Nested loops join
Nested loops join is a kind of two-table connection that relies on two levels of nested loops (outer loop/inner loop) To get the table connection method of the connection result set
If the t1 table and the t2 table use a nested loop connection when making table connections, Oracle will perform the following steps in sequence:
a. First, the optimizer will determine who is the driving table and who is the driven table in t1 and t2 according to certain rules. The driving table is used for the outer loop, and the driven table is used for the memory loop. Assume that t1 is the driving table
b. Access the driving table t1 with the predicate condition specified in the target SQL, and obtain the result set s1
c. Traverse s1, and at the same time traverse the driven table t2, that is, take out The records in s1 are matched with the driven table t2 according to the connection conditions. The result set will eventually be returned
Advantages, disadvantages and applicable scenarios of nested loop connections:
a. Able to achieve fast response, that is, the connection that has been connected and satisfied can be returned as soon as possible Conditional records, without having to wait for all connection operations to be completed before returning the connection result
b. It is suitable for the driving result set corresponding to the driving table to have a small number of records, and at the same time, in the connection of the driven table There is a unique index on the column (or a non-unique index with good selectivity on the connected column of the driven table)
Example
SQL> select /*+ gather_plan_statistics use_nl(t1,t2)*/* from scott.emp t1,scott.dept t2 where t1.deptno = t2.deptno;
SQL> select * from table(dbms_xplan.display_cursor(null,0,'allstats,last'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID dcsf9m1rzzga5, child number 0
-------------------------------------
select /*+ gather_plan_statistics use_nl(t1,t2)*/* from scott.emp
t1,scott.dept t2 where t1.deptno = t2.deptno
Plan 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:00:00.01 | 32 |
| 2 | TABLE ACCESS FULL| DEPT | 1 | 4 | 4 |00:00:00.01 | 7 |
|* 3 | TABLE ACCESS FULL| EMP | 4 | 4 | 14 |00:00:00.01 | 25 |
-------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
- filter("T1"."DEPTNO"="T2"."DEPTNO")
rows selected.
SQL>3. Hash join
Hash join is a table connection method that relies on hash operations to obtain the connection result set when two tables are connected. It was introduced after Oracle 7.3
Hash join works by hashing a table (usually a smaller table) and storing it in a hash list, extracting records from another table, performing a hashing operation, and finding the corresponding value in the hash list. , for matching
Hash joins are only applicable to 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 selectivity of join columns in a small table is very good, the execution time of a hash join can be approximately equal to the time spent on a full table scan of a large table. When doing a hash connection, the Hash Table corresponding to the driver result set can be completely accommodated in the memory (PGA workspace). At this time, the execution efficiency of the hash connection is very high.
The performance issues of the hash connection can be solved by 10104 event to diagnose, the relevant instructions are as follows:
Number of in-memory partitions (may have changed): Hash Partition
Final number of hash buckets: Number of Hash BucketTotal buckets: Empty buckets: Non-empty buckets: The situation of empty records and non-empty records in the Hash Bucket
Total number of rows: The number of records in the driving result set
Maximum number of rows in a bucket: The records contained in the Hash Bucket with the largest number of records Number
Disabled bitmap filtering: Whether to enable bitmap filtering
The above is the detailed content of Optimization method of Oracle table connection method (with examples). For more information, please follow other related articles on the PHP Chinese website!SQL> select /*+ gather_plan_statistics use_hash(t1,t2)*/* from scott.emp t1,scott.dept t2 where t1.deptno = t2.deptno;
SQL> select * from table(dbms_xplan.display_cursor(null,0,'allstats,last'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID 0j83q86ara5u2, child number 0
-------------------------------------
select /*+ gather_plan_statistics use_hash(t1,t2)*/* from scott.emp
t1,scott.dept t2 where t1.deptno = t2.deptno
Plan 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:00.01 | 13 | | | |
|* 1 | HASH JOIN | | 1 | 14 | 14 |00:00:00.01 | 13 | 1321K| 1321K| 1070K (0)|
| 2 | TABLE ACCESS FULL| DEPT | 1 | 4 | 4 |00:00:00.01 | 6 | | | |
| 3 | TABLE ACCESS FULL| EMP | 1 | 14 | 14 |00:00:00.01 | 7 | | | |
----------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("T1"."DEPTNO"="T2"."DEPTNO")
21 rows selected.
SQL>
![[Web front-end] Node.js quick start](https://img.php.cn/upload/course/000/000/067/662b5d34ba7c0227.png)
