Oracle Table Connection

A plain statement select * from T1, t2 where t1.id = t2.id and t1.name = ' a '; Under what circumstances is this statement most efficient?

Table Connection Categories:

1. Nested loops connection (Nested loop join)

2. Sort Merge Connection (merge sort join): Sort_area_size control in the PGA

3. Hash connection (hash join): Hash_area_size control in the PGA

Examples (basically all performed in a sql*plus environment):

--Prepare the script

Prompt preparation of experimental environment .....

DROP TABLE t1 cascade constraints Purge;

DROP TABLE t2 cascade constraints Purge;

CREATE TABLE T1 (

ID number is not NULL,

N Number,

Contents VARCHAR2 (4000)

);

CREATE TABLE T2 (

ID number is not NULL,

T1_ID number is not NULL,

N Number,

Contents VARCHAR2 (4000)

);

Execute dbms_random.seed (0);

INSERT INTO T1

(

Id

N

Contents

)

Select RowNum,

RowNum

Dbms_random.string (' A ', 50)

From dual

Connect by Level <= 100

Order BY Dbms_random.random;

INSERT INTO T2

(

Id

T1_ID,

N

Contents

)

Select RowNum,

RowNum

RowNum

Dbms_random.string (' B ', 50)

From dual

Connect by Level <= 100000

Order BY Dbms_random.random;

Commit

--Modify statistical parameters to all

alter system set Statistics_level=all;

--ALTER session set Statistics_level=all; Session-level modification, occasional invalidation

Show parameter statistics_level;

Set Linesize 1000

--Experiment started

--NL junction table

--1.1 hint meaning leading (T1) means to force first access to the T1 table, which is the T1 table as the driver table; Use_nl means to force nested loops to join

Select/*+leading (T1) use_nl (T2) */* from T1, t2 where t1.id = t2.t1_id;

SELECT * FROM table (dbms_xplan.display_cursor (null, NULL, ' Allstats last '));

Starts column is the number of table accesses, T1 table accessed 1 times, T2 table accessed 100 times

--1.2

Select/*+leading (T1) use_nl (T2) */* from T1, t2 where t1.id = t2.t1_id and T1.N in (19, 20);

SELECT * FROM table (dbms_xplan.display_cursor (null, NULL, ' Allstats last '));

T1 table visited 1 times, T2 table visited 2 times

--1.3

Select/*+leading (T1) use_nl (T2) */* from T1, t2 where t1.id = t2.t1_id and T1.N = 19;

SELECT * FROM table (dbms_xplan.display_cursor (null, NULL, ' Allstats last '));

T1 table visited 1 times, T2 table visited 1 times

--1.4

Select/*+leading (T1) use_nl (T2) */* from T1, t2 where t1.id = t2.t1_id and T1.N = 9999999;

SELECT * FROM table (dbms_xplan.display_cursor (null, NULL, ' Allstats last '));

T1 table visited 1 times, T2 table visited 0 times

NL table Connection Access times the final conclusion: how many records are returned by the query for the T1 table, and how many times the T2 table is accessed. That is, in a nested loop connection, how many records the driver table returns and how many times the drive table is accessed.

--2. Hash join table

--2.1

Select/*+leading (T1) use_hash (T2) */* from T1, t2 where t1.id = t2.t1_id;

SELECT * FROM table (dbms_xplan.display_cursor (null, NULL, ' Allstats last '));

T1 1 times, T2 1 times

In a hash connection, both the driver table and the driver table will only be accessed 1 or 0 times.

--2.2

Select/*+leading (T1) use_hash (T2) */* from T1, t2 where t1.id = t2.t1_id and T1.N = 9999999;

SELECT * FROM table (dbms_xplan.display_cursor (null, NULL, ' Allstats last '));

T1 1 times, T2 0 times

--2.3

Select/*+leading (T1) use_hash (T2) */* from T1, t2 where t1.id = t2.t1_id and 1 = 2;

SELECT * FROM table (dbms_xplan.display_cursor (null, NULL, ' Allstats last '));

T1 0 times, T2 0 times

--3. Sort Merge Connections

Select/*+ordered use_merge (T2) */* from T1, t2 where t1.id = t2.t1_id;

SELECT * FROM table (dbms_xplan.display_cursor (null, NULL, ' Allstats last '));

T1 1 times, T2 1 times

Suppose that two tables, A and B, if a as a driver table, B as the driver table, then, the nested loop connection, is to scan a table first, a table of each record, scan the B table;

A sort merge connection is a table that is sorted with the records in table B and then connected; Hash joins, like sort merges, establish hash tables to connect. So when a small number of records are returned (OLTP systems are often used, OLTP is more useful for systems such as telecommunications, finance, etc.), nested loops are more efficient, and when a large number of records are returned, a sort merge connection is more efficient than a hash connection.

Different types of connection drive sequence differences

1. Nested loops Join

Select/*+leading (T1) use_nl (T2) */* from T1, t2 where t1.id = t2.t1_id and T1.N = 19;

Select/*+leading (T2) USE_NL (T1) */* from T1, t2 where t1.id = t2.t1_id and T1.N = 19;

SELECT * FROM table (dbms_xplan.display_cursor (null, NULL, ' Allstats last '));

T1 Table First access: BUFFER 1014,t2 access only 1 times

T2 table First Accessed: BUFFER 701k,t1 was accessed 100,000 times

Therefore, nested loops join to pay special attention to the order of the driver tables, the small result set is first accessed, the large result set is accessed after

2. Hash connection

Select/*+leading (T1) use_hash (T2) */* from T1, t2 where t1.id = t2.t1_id and T1.N = 19;

Select/*+leading (T2) Use_hash (T1) */* from T1, t2 where t1.id = t2.t1_id and T1.N = 19;

SELECT * FROM table (dbms_xplan.display_cursor (null, NULL, ' Allstats last '));

T1 Table First access: BUFFER 1013,used_mem 286K, time 0.04 seconds

T2 Table First Access: BUFFER 1013,used_mem 11MB, time 0.01 seconds

In a hash join, it is also important to drive the table order

3. Sort Merge Connections

Select/*+leading (T1) use_merge (T2) */* from T1, t2 where t1.id = t2.t1_id and T1.N = 19;

Select/*+leading (T2) use_merge (T1) */* from T1, t2 where t1.id = t2.t1_id and T1.N = 19;

SELECT * FROM table (dbms_xplan.display_cursor (null, NULL, ' Allstats last '));

The same efficiency, execution time, Buffer,used_mem are the same regardless of whether you visit T1 first or T2 first, which indicates that the sort merge connection does not have a driver table concept.

In summary: Nested loops and Hash joins have a driving order, and the order of the driver tables will affect the performance of the table joins, whereas a sort merge connection has no driver concept, no matter which table is in front of it.

Nested loop joins do not require sorting, hash joins are not sorted, memory consumption is used to suggest hash tables; Sort merge joins need to be sorted. For hash joins and sort merge joins, do not take extra fields into the sort, because the fewer fields you select, the smaller the size of the memory that is consumed.

Select/*+leading (T2) use_merge (T1) */* from T1, t2 where t1.id = t2.t1_id and T1.N = 19;

Select/*+leading (T2) use_merge (t1) */t1.id from T1, t2 where t1.id = t2.t1_id and T1.N = 19;

SELECT * FROM table (dbms_xplan.display_cursor (null, NULL, ' Allstats last '));

It can be seen that USED_MEM represents a significant difference in memory consumption.

Various connection limit Scenarios

Explain plan for

Select/*+leadind (T1) use_hash (T2) * *

from T1, T2

where T1.id <> t2.t1_id and T1.N = 19;

SELECT * FROM table (dbms_xplan.display);

Did not follow the instructions of hint to go hash connection, but walked the NL connection

Similarly, can be tested >, <, Like,hash are not supported.

Explain plan for

Select/*+leadind (T1) use_merge (T2) * *

from T1, T2

where T1.id <> t2.t1_id and T1.N = 19;

SELECT * FROM table (dbms_xplan.display);

Sort merge connections do not support <> and like, but support >, <

Nested Loops Unlimited

Table joins and indexes

Select/*+leading (T1) use_nl (T2) */* from T1, t2 where t1.id = t2.t1_id;

SELECT * FROM table (dbms_xplan.display_cursor (null, NULL, ' Allstats last '));

If you do not use the hint hint

SELECT * from T1, t2 where t1.id = t2.t1_id;

SELECT * FROM table (dbms_xplan.display_cursor (null, NULL, ' Allstats last '));

Will show a hash connection, because Oracle thinks the hash connection is more reasonable, but at this time the logical read, the cost value is similar

CREATE index t1_n on t1 (n);

Then execute the above statement to see the difference in buffer when scanning the T1 table.

Create index t2_t1_id on T2 (t1_id);

Then execute the above statement to see the difference in buffer when scanning the T2 table.

The most suitable scenario for the NL connection:

1. Two table associations return a small number of records, the best case is that the drive result set only returns 1 or a few records, and the driver table only matches to 1 or a few records, in this case, even if the records of the T1 and T2 tables are extremely large, it is very fast.

2. Encountering some non-equivalence queries that result in hash and sort merge connections being restricted, the NL connection has to be used.

3. The best index Establishment location: The driver table of the constraints of the column is indexed, the connection condition of the driver table is indexed. The reason for this is that the constraints on the driver table are indexed to reduce the time to scan the driver table, and if indexing on the connection condition of the driver table is meaningless, all columns are associated to all columns of the other table, equivalent to each record being associated. While the constraints of the driver table index, only quickly return 1 or a few, and then passed to the t1_id column of the T2 table, in general, T2 table corresponding T1 table returned records are not many, so T2 table t1_id column to build the index is meaningful.

Hash joins and indexes

The index of the join condition is not transitive for hash joins and sort merge joins, and for hash joins and sort merge joins, the index's join condition is not fast enough to retrieve, but it can improve performance if there are suitable indexes to quickly retrieve a small number of records.

Two tables associated with the equivalent query, Oracle generally tend to go hash connection, because the hash connection algorithm itself is more efficient, but also more advanced. The optimized hash connection can be optimized by adjusting the hash_area_size in the PGA, which increases the PGA size if the PGA is automatically managed.

Sort merge joins and indexes

The join conditions on a sort merge connection are not retrievable, but have the effect of eliminating sorting, thus improving efficiency.

Sql>select/*+ordered Use_merge (T2) */

*

from T1, T2

where t1.id = t2.t1_id;

Sql>select * FROM table (dbms_xplan.display_cursor (null, NULL, ' Allstats last '));

CREATE index IDX_T1 on T1 (ID);

Create index idx_t1_id on T2 (t1_id); --Built above

Sql>select/*+ordered Use_merge (T2) */

*

from T1, T2

where t1.id = t2.t1_id;

Sql>select * FROM table (dbms_xplan.display_cursor (null, NULL, ' Allstats last '));

Two indexes were built, eventually using only one, which is the Oracle sort merge connection bug, which is acknowledged in the 11G official document.

You can also optimize the sort merge connection by increasing the PGA

Oracle Table Connection