Shulou(Shulou.com)05/31 Report--

This article mainly explains "what are the methods and types of oracle table connection". The explanation in this article is simple and clear, easy to learn and understand. Please follow the ideas of Xiaobian and go deep into it slowly to study and learn "what are the methods and types of oracle table connection" together!

1. Table join is to join multiple tables together by join conditions. When the optimizer parses sql with table join, it will determine the type of table join according to sql writing, and determine the following three conditions before specifying the execution plan.

1. The order of table connection. No matter how many tables are connected, SQL can only connect two tables when executing, and then connect the next two tables according to the connection result until all tables are connected. The order of connection has two meanings. One is to decide who is the outer table (driving table) and who is the inner table(driven table) when connecting two tables. The other is to decide which two tables to connect first in the case of multi-table connection.

2, table join methods, there are four kinds, sort merge join, nested loop join, hash join, Cartesian join, optimizer in parsing sql to decide which connection to use

3, access to a single table method optimizer in the table table join, but also decide how to read the data in a single table, such as with the full table scan or index, index, then how to index, and so on

II. Types of table connections

Type is divided into inner link and outer link, type determines the result of table connection, sql writing directly determines the type

1. Inner link: The connection result only contains records that fully meet the connection conditions. As long as the keywords for outer connection are not written in sql, there are three ways to write inner link and inner link, one of which is oracle specific.

SELECT T1.COL1,T1.COL2,T2.COL3 FROM T1,T2 WHERE T1.COL2=T2.COL2;

SELECT T1.COL1,T1.COL2,T2.COL3 FROM T1 JOIN T2 ON(T1.COL2=T2.COL2);

SELECT T1.COL1, COL2,T2.COL3 FROM T1 JOIN T2 USING(COL2);

There is also a natural join in standard sql, which means that only all columns of the same name in two tables are used together as a join column. It is not recommended to use it. Although it will omit writing a join column, it will increase the risk of sql reporting errors.

2. External join, the connection result includes not only records that fully meet the connection conditions, but also records that do not meet the connection conditions in all drive tables. External join is divided into three types: left outer join, right outer join and full outer join.

Left link, left drive table

SELECT T1.COL1,t1.COL2,T2.COL3 FROM T1,t2 where t1.col2=t2.col2(+);

SELECT T1.COL1,t1.COL2,T2.COL3 FROM T1 left outer join T2 on (t1.col2=t2.col2);

SELECT T1.COL1, COL2,T2.COL3 FROM T1 left outer join T2 USING(COL2);

Right link, right drive table

SELECT T1.COL1,t2.COL2,T2.COL3 FROM T1,t2 where t1.col2(+)=t2.col2;

SELECT T1.COL1, COL2,T2.COL3 FROM T1 right outer join T2 USING(COL2);

SELECT T1.COL1,t2.COL2,T2.COL3 from t1 right join t2 on(t1.col2=t2.col2);

Full connection, equivalent to left connection union right connection

SELECT T1.COL1,t1.COL2,T2.COL3 from t1 full join t2 on(t1.col2=t2.col2);

3. When the connection has other conditions besides the connection condition

The following two statements have the same result.

SELECT T1.COL1,t1.COL2,T2.COL3 from t1 join t2 on(t1.col2=t2.col2 and t1.col1=1);

SELECT T1.COL1,t1.COL2,T2.COL3 from t1 join t2 on(t1.col2=t2.col2) where t1.col1=1;

The outer join will be different, so in addition to the outer join constraint, the text position of other constraints will affect the final result.

SELECT T1.COL1,t1.COL2,T2.COL3 from t1 left outer join t2 on(t1.col2=t2.col2 and t1.col1=1);

SELECT T1.COL1,t1.COL2,T2.COL3 from t1 left join t2 on(t1.col2=t2.col2) where t1.col1=1;

The above two standard sql statements can be written as (+) in oracle

SELECT T1.COL1,t1.COL2,T2.COL3 FROM T1,t2 where t1.col2=t2.col2(+) and t1.col1(+)=1;

SELECT T1.COL1,t1.COL2,T2.COL3 FROM T1,t2 where t1.col2=t2.col2(+) and t1.col1=1;

t1.col2(+)=1 means that this condition is also within the connection constraints.

The execution plan for the first statement above uses a hash join outer, while the second statement uses a hash join, i.e. it actually executes with an equivalent inner join.

Natural joins that apply to inner links can also be used for outer links

III. Method of table connection

1. Sort merge join

The two tables are sorted separately and merged to obtain the result set

Not as efficient as hash join, but applicable to a wider range than hash join, because hash join is usually only used for equivalent join conditions, sort merge join can use different join conditions, such as

Wait a minute.

It is not suitable for OLAP, of course, if you can avoid sorting, you can also use it in OLAP, for example, there is an index on the join column of each of the two tables

Strictly speaking, sort merge joins do not have the concept of driving tables

2. nested loops join

When two tables are joined, they rely on two nested loops (outer loop and inner loop) to get the result set

step

a. The optimizer determines the driving table and the driven table according to the rules. The driving table is used for the outer layer and the driven table is used for the inner layer.

b. Access the driving table according to the predicate condition to obtain the result set 1.

c. Traversing the result set 1 and traversing the driven table at the same time, that is, first taking out a record in the result set 1, then traversing the driven table according to this record, finding out the matching record, then taking out the second record in the result set 1, and continuing to traverse the driven table until all records in the result set 1 are taken out, that is, how many times the driven table is traversed according to the number of records in the result set 1, and how many times the inner loop is traversed.

The key point of nested loop join is the amount of data in result set 1. Less data will be more efficient, and this kind of join has an advantage that other joins do not have: it can achieve fast response. That is, records that have been connected and meet the connection conditions can be quickly returned without waiting for all connections to be completed.

Vector I/O is added to oracle 11g to improve the performance of nested loops.

3. hash join

When two tables are joined, the result set is obtained by hashing

The_hash_join_enabled parameter defaults to true, enabling hash_join

alter session set "_hash_join_enabled"=true;

alter session set "_hash_join_enabled"=false;

use_hash hash takes precedence over this parameter

Si & Bj Sn&Bn(representing hash partition in disk)

Advantages and disadvantages of hash and applicable scenarios

A, hash is not necessarily sorted, or most of them will not be sorted.

b. The selectivity of the connection column of the drive table is as good as possible, because this will affect the number of records in the hash bucket.

c. Only applicable to CBO and only applicable to equivalent connection

d. Suitable for small table and large table connection and large result set, small table connection column selectivity is very good, the execution time of hash connection is equivalent to the time of large table full table scan

e. When two tables are connected, after hash is done, hash table can be completely in memory (PGA), then hash connection execution efficiency will be very high.

Descartes (cross join)

A table join method without any join condition when two tables are joined. In fact, it is a special merge join without sorting (MERGE JOIN CARTESIAN). The result set of T1 is m, the result set of T2 is n, and the number of records after Cartesian join is M*N.

select t1.col1,t2.col3 from t1,t2;

standard sql : select t1.col1,t2.col3 from t1 cross join t2;

Descartes join is generally bad, often because the connection condition is omitted or ordered hint is used, and there is no direct association condition between the two adjacent tables in sql text, and it may be caused by inaccurate statistical information. This connection is used only if it is intentional, such as to avoid multiple scans of large tables.

4. anti join

Result Set 1, Result Set 2, t1.col2=t2.col2 will be removed, only return values that do not meet the join condition

select * from t1 where col2 not in( select col2 from t2);

select * from t1 where col2 all(select col2 from t2);

select * from t1 where not exists(select 1 from t2 where col2=t1.col2);

When t1,t2 have no null value, the above three statements have the same result.

When there is null, the result will be different.

not in and all are null sensitive. When the subquery or constant collection behind them has null, the execution result of the whole sql is null.

not exists is insensitive to null and does not affect execution results

5. Semi join

t1,t2 When connected, the drive table t1, driven table t2, even if t2 meets the connection condition t1.col2=t2.col2, there are multiple records, only the first record will be returned, that is, the special inner connection when semi-connected, actually has the effect of removing duplication. When a subquery is expanded, oracle usually converts the condition after where =any,exist,in, etc. into the corresponding semi-join.

select * from t1 where col2 in(select col2 from t2);

select * from t1 where col2= any(select col2 from t2);

select * from t1 where exists(select col2 from t2 where col2=t1.col2);

6. star join

Usually used in data warehouse, neither join type nor join method, it is a join between a fact table and multiple dimension tables. Basically, the foreign key column of the fact table corresponds to the primary key column of each dimension table. The fact table is a large table. This connection will be described in detail in the following chapters.

Thank you for reading, the above is "oracle table connection methods and types of what" content, after learning this article, I believe that we have a deeper understanding of oracle table connection methods and types of this problem, the specific use of the situation also needs to be verified by practice. Here is, Xiaobian will push more articles related to knowledge points for everyone, welcome to pay attention!

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.