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

As implied, a table join means that multiple tables are joined together by join conditions, and the purpose of using the join target sql is to obtain data from multiple tables in different dimensions stored in these tables. Reflected in the sql statement, multiple tables appear in the from portion of the target sql with table joins, while the where condition parts of these sql define specific table join conditions.

When the optimizer parses a target sql with a table join, in addition to determining the type of table join based on the writing of the sql text of the target sql, it must decide on three things to get the final execution plan.

(1) Table join order

No matter how many tables are joined in the target sql, oracle can only join two tables first, and then perform such a two-table join process in turn until all the tables in the target sql have been joined. So strictly speaking, the table join order here contains two meanings: first, when two tables are joined, the optimizer needs to decide which of the two tables is the driven table and which is the driven table. Another meaning is that when multiple tables (more than two tables) do table join, the optimizer needs to decide who and who do the table join first, and then decide which table join is left in the result set of the table join result. This pairwise table join process will continue until all the tables in the target sql have been joined.

(2) the method of table join

In oracle database, there are four methods of table join between two tables: merge join, nested loop join, hash join and Cartesian join, so when parsing the target sql with table join, the optimizer needs to choose one of the above four methods as the method that needs to be adopted when each pair of tables are joined by two tables.

(3) the method of accessing single table

It is not enough for the optimizer to determine the table join order and join method, which is not enough to get the final execution plan of the target sql, because when the optimizer joins each table in pairs in the target sql, it must also decide how to obtain the different dimensions of data stored in those tables, that is, the optimizer also decides how to access the single table. For example, when accessing a single table, whether to use full table scan or walk index, if it is an index, what kind of index access method should be used and so on.

Type of table join

In general, we can think that the table join in oralce database can be divided into two types: inner join and outer join. The type of table join directly determines the result of table join, and the writing of the sql text of the target sql directly determines the type of table join.

(1) Internal connection

An inner join means that the join result of a table join contains only those records that fully meet the criteria. For a target sql that contains table joins, as long as the keywords representing outer joins defined in standard sql or customized in oracle are not written in its where condition (such as left outer join,right outer join,full outer join in standard sql or the key (+) customized in oracle to represent outer joins), the join type of the sql is inner join.

SQL > create table T1 (col1 number,col2 varchar2 (1))

Table created.

SQL > SQL > create table T2 (col2 varchar2 (1), col3 varchar2 (2))

Table created.

SQL > insert into T1 values (1)

1 row created.

SQL > insert into T1 values (2MIT B')

1 row created.

SQL > insert into T1 values (3mcc')

1 row created.

SQL > insert into T2 values ('axiomagery')

1 row created.

SQL > insert into T2 values ('Bamboo dagger')

1 row created.

SQL > insert into T2 values ('Dumbledore D2')

1 row created.

SQL > commit

Commit complete.

SQL > select * from T1

COL1 C

--

1 A

2 B

3 C

SQL > select * from T2

C CO

An A2

B B2

D D2

SQL > select t1.col1 where t1.col2=t2.col2 t1.col2 from t2.col3

COL1 C CO

1 An A2

2 B B2

From the above execution results, we can see that the connection result of the inner connection contains only those records that play to meet the conditions.

The standard sql is written as follows:

SQL > select t1.col1 on 1.col2 from t2.col3 join T2 on (t1.col2=t2.col2)

COL1 C CO

1 An A2

2 B B2

SQL > select t1.col1 using 1.col2 from t2.col3 join T2 using (col2)

Select t1.col1 from T1 join T2 using (col2)

*

ERROR at line 1:

ORA-25154: column part of USING clause cannot have qualifier

It should be noted that for standard sql using join using, if the join column appears in the query column at the same time, the join column cannot be preceded by a table name or alias of the table name, otherwise oralce will throw an ORA-25154. The following is the correct way to write it:

SQL > select t1.col1 join col2jue 2.col3 from T1 join T2 using (col2)

COL1 C CO

1 An A2

2 B B2

Using standard sql to represent table joins, then there is a special jion using, which we call NATURAL JOIN,NATURAL JOIN, is a special JOIN USING, which means that the join columns of the table join using NATURAL JOIN are all the same name columns of the two tables joined by the table.

SQL > select t1.col1 natural join 2.col3 from T1 natural join T2

COL1 C CO

1 An A2

2 B B2

The advantage of using NATURAL JOIN is that there is no need to write join sets in JOIN USING, but the downside is that it increases the risk of errors in the execution results of table joins, because columns with the same name between two tables are not necessarily the same in meaning (maybe they just happen to have the same name, and their meanings are completely different, so they should not be used as join columns) and even if they have the same meaning, they are not necessarily required to join.

(2) external connection

The outer join is an extension of the inner join, which means that the join result of the table join not only contains the records that fully meet the join condition, but also contains the records of all the joins in the drive table that do not meet the condition.

The outer connections of standard sql can be divided into three types: left connection, right connection and full connection. Their corresponding keywords in standard sql are left outer join,right outer join and full outer join, which can be used with join on or join using.

Left join syntax:

Target Table 1 left outer join Target Table 2 on (connection conditions) or

Target Table 1 left outer join Target Table 2 using (connection set)

The meaning of "target table 1 left outer join target table 2 on (connection condition)" means that target table 1 and target table 2 are joined according to the connection conditions in parentheses, and the target table 1 located on the left side of the key left outer join will be used as the driver table of the table connection (the keyword "left outer" indicates that the position is in left means outer table,outer table refers to the driver table). At this time, the connection not only contains all the records in the target table 1 and 2 that meet the condition, but also contains the records in the drive table (target table 1) whose indexes do not meet the join condition. At the same time, the query columns in the driven table (that is, the target table 2) corresponding to all the records in the drive table that do not meet the connection condition are filled with NULL values.

Right join syntax:

Target Table 1 right outer join Target Table 2 on (connection conditions) or

Target Table 1 right outer join Target Table 2 using (connection set)

Full join syntax:

Target Table 1 full outer join Target Table 2 on (connection conditions) or

Target Table 1 full outer join Target Table 2 using (connection set)

Full connection can be understood as first making a left connection, then a right connection, and finally doing a union operation on the connection result of the left and right connection.

Connect the instance on the left:

SQL > select t1.col1 on 1.col2 from t2.col3 left outer join T2 on (t1.col2=t2.col2)

COL1 C CO

1 An A2

2 B B2

3 C

Oracle custom writing method:

SQL > select t1.col1 where t1.col2=t2.col2 (+) 1.col2 from t2.col3 where t1.col2=t2.col2 T1

COL1 C CO

1 An A2

2 B B2

3 C

The keyword (+) appears after the join column col2 in table T2, which means that T2 populates the query column (col3) in T2 with NULL values that do not meet the join criteria for t1.col2=t2.col2.

Connect the instance on the right:

SQL > select t1.col1 on 1.col2 from t2.col3 right outer join T2 on (t1.col2=t2.col2)

COL1 C CO

1 An A2

2 B B2

D2

Oracle custom writing method:

SQL > select t1.col1 where t1.col2 1.col2 from t2.col3 where t1.col2 (+) = t2.col2

COL1 C CO

1 An A2

2 B B2

D2

Fully connected instance:

SQL > select t1.col1 on 1.col2 from t2.col3 full outer join T2 on (t1.col2=t2.col2)

COL1 C CO

1 An A2

2 B B2

D2

3 C

In the above example sql, there are no other additional connection conditions except for the join conditions. If there are other additional restrictions in the target sql in addition to the table join conditions, the type of table join in the target sql and the location where the extra condition appears in the sql text of the target sql may affect the final execution plan.

SQL > select t1.col1 on 1.col2 from t2.col3 join T2 on (t1.col2=t2.col2 and t1.col1=1)

COL1 C CO

1 An A2

SQL > select t1.col1 on 1.col2 where t1.col1=1 2.col3 from T1 join T2 on (t1.col2=t2.col2)

COL1 C CO

1 An A2

The above results show that for inner joins, the location of additional constraints other than table join conditions in the sql text of the target sql does not affect the actual execution result of the sql.

SQL > select t1.col1 on 1.col2 from t2.col3 right outer join T2 on (t1.col2=t2.col2 and t1.col1=1)

COL1 C CO

1 An A2

D2

B2

SQL > select t1.col1 on 1.col2 where t1.col1=1 2.col3 from T1 right outer join T2 on (t1.col2=t2.col2)

COL1 C CO

1 An A2

For example 1, its constraint is in the parentheses corresponding to right outer join in the sql text, which means that the constraint will be applied to table T1 before tables T1 and T2 are right joined. The data of T1 participating in the right join is those records that meet the condition t1.col1=1, while the constraint of example 2 is outside the right outer join parentheses, which means that the constraint is after T1 and T2 are right joined. Will be applied to the join result set of tables T1 and T2, and all the data in table T1 will participate in the right join.

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.