Oracle table connection modes (SQL optimization)

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 ))

(Merge) 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.

Iii. Table join details:

1. NESTED LOOP

For a small subset of connected data, nested loop connection is a good choice. The nested loop is used to scan a table. Every time a record is read, it is searched in another table based on the index. Without an index, it is generally not nested loops. Generally, in the nested loop, the result set of the driver table meeting the condition is not large, and the connected fields of the driver table must be indexed, so that nstedloop is used. If the driver table returns too many records, it is not suitable for nested loops. If the join field does not have an index, hash join is applicable because no index is required.

You can use the ordered prompt to change the default drive table of CBO. You can use the USE_NL (table_name1 table_name2) prompt to force the nested loop.

Key points are as follows:
1) nested loop join is a good choice for a small subset of connected data.
2) USE_NL (table_name1 table_name2) is used, But CBO is forced to execute nested loop connections.
3) The Nested loop is generally used when the connected table has an index and the index is more selective.
4) The OIN sequence is very important. The record set of the driver table must be small, and the response time of the returned result set is the fastest.
5) The Nested loops method is to read data from a table and access another table (usually an index) for matching. nested loops is applicable when a joined table is small, higher efficiency.

Example:

SQL> create table t as select * from user_tables;

The table has been created.

SQL> create index index_t on t (table_name );

The index has been created.

SQL> create table t1 as select * from user_tables where table_name like '% ACCESS % ';

The table has been created.

SQL> create index index_t1 on t1 (table_name );

The index has been created.

SQL> begin
2 dbms_stats.gather_table_stats (ownname => 'test', tabname => 'T ');
3 end;
4/

The PL/SQL process is successfully completed.

SQL> begin
2 dbms_stats.gather_table_stats (ownname => 'test', tabname => 'T ');
3 end;
4/

 

Because table t1 records are very small as a driving table and table t has indexes, it is suitable for NL. The execution plan is as follows:

SQL> set wrap off;
SQL> set autotrace traceonly;

SQL> select a. table_name, B. table_name from t a, t1 B
2 where a. table_name = B. table_name;

Eight rows have been selected.

Execution Plan
----------------------------------------------------------
Plan hash value: 3579965632

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

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

| 0 | select statement | 8 | 280 | 4 (0) | 00:00:01

| 1 | nested loops | 8 | 280 | 4 (0) | 00:00:01

| 2 | index fast full scan | INDEX_T | 1921 | 34578 | 4 (0) | 00:00:01

| * 3 | index range scan | INDEX_T1 | 1 | 17 | 0 (0) | 00:00:01

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

Predicate Information (identified by operation id ):
---------------------------------------------------

3-access ("A". "TABLE_NAME" = "B". "TABLE_NAME ")

Note
-----
-Dynamic sampling used for this statement (level = 2)

Statistics
----------------------------------------------------------
0 recursive cballs
0 db block gets
18 consistent gets
0 physical reads
0 redo size
807 bytes sent via SQL * Net to client
415 bytes encoded ed via SQL * Net from client
2 SQL * Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
8 rows processed

SQL> select a. table_name, B. table_name from t1 a, t B
2 where a. table_name = B. table_name;

Eight rows have been selected.

Execution Plan
----------------------------------------------------------
Plan hash value: 3579965632

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

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

| 0 | select statement | 8 | 280 | 4 (0) | 00:00:01

| 1 | nested loops | 8 | 280 | 4 (0) | 00:00:01

| 2 | index fast full scan | INDEX_T | 1921 | 34578 | 4 (0) | 00:00:01

| * 3 | index range scan | INDEX_T1 | 1 | 17 | 0 (0) | 00:00:01

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

Predicate Information (identified by operation id ):
---------------------------------------------------

3-access ("A". "TABLE_NAME" = "B". "TABLE_NAME ")

Note
-----
-Dynamic sampling used for this statement (level = 2)

Statistics
----------------------------------------------------------
4 recursive cballs
0 db block gets
23 consistent gets
0 physical reads
0 redo size
807 bytes sent via SQL * Net to client
415 bytes encoded ed via SQL * Net from client
2 SQL * Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
8 rows processed

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

Assume that we use the join sequence of the table to be changed.

The ORACLE parser processes the table names in the FROM clause in the order FROM right to left. The table written in the FROM clause (basic table driving table) will be processed first, when the FROM clause contains multiple tables, You must select the table with the least number of records as the base table. If more than three tables are connected for query, You need to select an intersection table as the base table, which is the table referenced by other tables.

1 2 3 Next Page