Oracle Table Connection Method Analysis

Oracle Table Connection Method Analysis

Basic table join knowledge:

1. Which table will drive the query (that is, the first table accessed )? When will no table be accessed during query based on the specified path? What are the optional driver paths?

2. What Oracle connections may occur? Remember: in Oracle, different connections, optional indexes, available memory used for sorting, and hash creation results in different results.

3. Which indexes are available? Which indexes are optional? The selection of indexes will not only cause the optimizer to use or limit an index, but also change the method of driving the query, and may decide to use or limit other indexes in the query.

4. Which prompts provide the optional path? Which tips limit or force use of indexes? These prompts not only change the driving sequence of the table, but also change the connection performance in Oracle and determine which indexes can be restricted or forcibly used.

5. Which version of Oracle are you using? Your choice depends on the version of Oracle you are using. Different version optimizers work in different ways.

--------------------------------------------------------------------------------

Installing Oracle 12C in Linux-6-64

Install Oracle 11gR2 (x64) in CentOS 6.4)

Steps for installing Oracle 11gR2 in vmwarevm

Install Oracle 11g XE R2 In Debian

--------------------------------------------------------------------------------

Table connection method:

When viewing the SQL Execution Plan, we will find that there are many table connection methods. This article introduces the table connection methods to better understand the execution plan and understand the SQL Execution principles.

I. Connection method:

Nested loop (Nested Loops (NL ))

(Hash) Hash Join (HJ ))

Sort Merge Join (SMJ ))

Ii. Connection description:

1. Oracle can only connect two tables at a time. No matter how many tables are in the query, Oracle can only operate on two tables at a time during a connection.

2. when multiple tables are connected, the optimizer starts from one table and connects it to another table. Then, the intermediate result is connected to the next table, and so on, until all tables are processed.

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

Nested loop join)

Cluster join)

SORTMERGEJOIN)

Cartesian join)

HASHJOIN)

INDEXJOIN)

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) The Oracle optimizer selects one of the two tables as the driving table based on the Rule RBO (rulebasedoptimizer) or cost-based CBO (costbasedoptimizer) 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.

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 (AB) */a. user_name, B. dev_nofromuser_infoa, dev_infobwherea.user_id = B. user_id;

Case:

At 12:00:44 SCOTT @ prod> select e. ename, e. sal, d. deptno, d. dname

12:01:50 2 from emp e, dept d

12:01:50 3 where d. deptno = e. deptno and d. deptno = 10;

ENAME SAL DEPTNO DNAME

--------------------------------------------

CLARK 2450 10 ACCOUNTING

KING 5000 10 ACCOUNTING

MILLER 1300 10 ACCOUNTING

Elapsed: 00:00:00. 03

Execution Plan

----------------------------------------------------------

Plan hash value: 568005898

Bytes ----------------------------------------------------------------------------------------

| Id | Operation | Name | Rows | Bytes | Cost (% CPU) | Time |

Bytes ----------------------------------------------------------------------------------------

| 0 | select statement | 5 | 105 | 4 (0) | 00:00:01 |

| 1 | nested loops | 5 | 105 | 4 (0) | 00:00:01 |

| 2 | table access by index rowid | DEPT | 1 | 11 | 1 (0) | 00:00:01 |

| * 3 | index unique scan | PK_DEPT | 1 | 0 (0) | 00:00:01 |

| * 4 | table access full | EMP | 5 | 50 | 3 (0) | 00:00:01 |

Bytes ----------------------------------------------------------------------------------------

Predicate Information (identified by operation id ):

---------------------------------------------------

3-access ("D". "DEPTNO" = 10)

4-filter ("E". "DEPTNO" = 10)

Statistics

----------------------------------------------------------

1 recursive cballs

0 db block gets

10 consistent gets

0 physical reads

0 redo size

835 bytes sent via SQL * Net to client

523 bytes encoded ed via SQL * Net from client

2 SQL * Net roundtrips to/from client

0 sorts (memory)

0 sorts (disk)

3 rows processed

12:01:51 SCOTT @ prod & gt;

At 11:59:48 SCOTT @ prod> select/* + use_nl (d e) */e. ename, e. sal, d. deptno, d. dname

11:59:49 2 from dept d, emp e

11:59:49 3 where d. deptno = e. deptno;

14 rows selected.

Elapsed: 00:00:00. 03

Execution Plan

----------------------------------------------------------

Plan hash value: 4192419542

---------------------------------------------------------------------------

| Id | Operation | Name | Rows | Bytes | Cost (% CPU) | Time |

---------------------------------------------------------------------------

| 0 | select statement | 14 | 294 | 10 (0) | 00:00:01 |

| 1 | nested loops | 14 | 294 | 10 (0) | 00:00:01 |

| 2 | table access full | DEPT | 4 | 44 | 3 (0) | 00:00:01 |

| * 3 | table access full | EMP | 4 | 40 | 2 (0) | 00:00:01 |

---------------------------------------------------------------------------

Predicate Information (identified by operation id ):

---------------------------------------------------

3-filter ("D". "DEPTNO" = "E". "DEPTNO ")

Statistics

----------------------------------------------------------

1 recursive cballs

0 db block gets

37 consistent gets

0 physical reads

0 redo size

1038 bytes sent via SQL * Net to client

523 bytes encoded ed via SQL * Net from client

2 SQL * Net roundtrips to/from client

0 sorts (memory)

0 sorts (disk)

14 rows processed

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

1 2 3 4 Next Page