Optimization method of Oracle table connection method (with examples)

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 (>,6580843315dd7804e35fd3743df832ea=,<=)

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 Bucket

Total 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

Example

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>

[Related recommendations:

SQL tutorial

】

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!