Oracle多表连接优化测试

最新推荐文章于 2021-04-27 02:11:25 发布
最新推荐文章于 2021-04-27 02:11:25 发布
阅读量407 收藏 1
点赞数
分类专栏： ORACLE
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/weixin_38623994/article/details/104176231
版权

在SQL语句中，不管有多么复杂都是两个表之间的连接，复杂语句只是这个过程更加复杂。只有对于基本多表连接熟悉掌握，才可以对复杂语句进行抽丝剥茧的逐层破译。

[结果说明]
1.被驱动表的连接字段必须要有索引；
2.被驱动表如果有其他字段，可考虑是否将连接字段与其他字段一起创建复合索引；
3.驱动表通过索引字段优化避免全表扫描；

在这里插入图片描述

[测试数据]

测试环境构筑
create table a (c1 varchar2(30),c2 varchar2(30),c3 varchar2(30));
insert into a (c1,c2,c3) values ('A_C1_01','','C3_01');
insert into a (c1,c2,c3) values ('A_C1_02','','C3_02');
insert into a (c1,c2,c3) values ('A_C1_03','','C3_03');
insert into a (c1,c2,c3) values ('A_C1_04','','C3_04');
insert into a (c1,c2,c3) values ('A_C1_05','','C3_05');
commit;

在这里插入图片描述

create table b (c1 varchar2(30),c2 varchar2(30),c3 varchar2(30));
insert into b (c1,c2,c3) values ('','B_C2_01','C3_01');
insert into b (c1,c2,c3) values ('','B_C2_02','C3_02');
insert into b (c1,c2,c3) values ('','B_C2_03','C3_03');
insert into b (c1,c2,c3) values ('','B_C2_04','C3_04');
insert into b (c1,c2,c3) values ('','B_C2_05','C3_05');
commit;

在这里插入图片描述

在执行计划如果显示是access，就表示这个谓词条件的值将会影响数据的访问路径(表还是索引)，而filter表示谓词条件的值并不会影响数据访问路径，只起到过滤的作用。

内连接测试
 -- 无索引状态
SQL> select a.c1,b.c2 from a join b on a.c3=b.c3;

C1                                                           C2
------------------------------------------------------------ ------------------------------------------------------------
A_C1_01                                                      B_C2_01
A_C1_02                                                      B_C2_02
A_C1_03                                                      B_C2_03
A_C1_04                                                      B_C2_04
A_C1_05                                                      B_C2_05


执行计划
----------------------------------------------------------
Plan hash value: 652036164

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |     5 |   340 |     6   (0)| 00:00:01 |
|*  1 |  HASH JOIN         |      |     5 |   340 |     6   (0)| 00:00:01 |
|   2 |   TABLE ACCESS FULL| A    |     5 |   170 |     3   (0)| 00:00:01 |
|   3 |   TABLE ACCESS FULL| B    |     5 |   170 |     3   (0)| 00:00:01 |
---------------------------------------------------------------------------

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

   1 - access("A"."C3"="B"."C3")

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


统计信息
----------------------------------------------------------
          0  recursive calls
          0  db block gets
         15  consistent gets
          0  physical reads
          0  redo size
        735  bytes sent via SQL*Net to client
        523  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          5  rows processed



-- A表 C3 字段有索引，B表 无索引
SQL> select a.c1,b.c2 from a join b on a.c3=b.c3;

C1                                                           C2
------------------------------------------------------------ ------------------------------------------------------------
A_C1_01                                                      B_C2_01
A_C1_02                                                      B_C2_02
A_C1_03                                                      B_C2_03
A_C1_04                                                      B_C2_04
A_C1_05                                                      B_C2_05


执行计划
----------------------------------------------------------
Plan hash value: 2937931297

-----------------------------------------------------------------------------------------
| Id  | Operation                    | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |          |     5 |   340 |     6  (17)| 00:00:01 |
|   1 |  MERGE JOIN                  |          |     5 |   340 |     6  (17)| 00:00:01 |
|   2 |   TABLE ACCESS BY INDEX ROWID| A        |     5 |   170 |     2   (0)| 00:00:01 |
|   3 |    INDEX FULL SCAN           | IDX_A_C3 |     5 |       |     1   (0)| 00:00:01 |
|*  4 |   SORT JOIN                  |          |     5 |   170 |     4  (25)| 00:00:01 |
|   5 |    TABLE ACCESS FULL         | B        |     5 |   170 |     3   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------

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

   4 - access("A"."C3"="B"."C3")
       filter("A"."C3"="B"."C3")

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


统计信息
----------------------------------------------------------
          8  recursive calls
          0  db block gets
         28  consistent gets
          0  physical reads
          0  redo size
        735  bytes sent via SQL*Net to client
        523  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
          5  rows processed


-- A表无索引，B表 C3字段有索引
SQL> select a.c1,b.c2 from a join b on a.c3=b.c3;

C1                                                           C2
------------------------------------------------------------ ------------------------------------------------------------
A_C1_01                                                      B_C2_01
A_C1_02                                                      B_C2_02
A_C1_03                                                      B_C2_03
A_C1_04                                                      B_C2_04
A_C1_05                                                      B_C2_05


执行计划
----------------------------------------------------------
Plan hash value: 1564157014

-----------------------------------------------------------------------------------------
| Id  | Operation                    | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |          |     5 |   340 |     6  (17)| 00:00:01 |
|   1 |  MERGE JOIN                  |          |     5 |   340 |     6  (17)| 00:00:01 |
|   2 |   TABLE ACCESS BY INDEX ROWID| B        |     5 |   170 |     2   (0)| 00:00:01 |
|   3 |    INDEX FULL SCAN           | IDX_B_C3 |     5 |       |     1   (0)| 00:00:01 |
|*  4 |   SORT JOIN                  |          |     5 |   170 |     4  (25)| 00:00:01 |
|   5 |    TABLE ACCESS FULL         | A        |     5 |   170 |     3   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------

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

   4 - access("A"."C3"="B"."C3")
       filter("A"."C3"="B"."C3")

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


统计信息
----------------------------------------------------------
          0  recursive calls
          0  db block gets
         11  consistent gets
          0  physical reads
          0  redo size
        735  bytes sent via SQL*Net to client
        523  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
          5  rows processed


-- A表与B表均有索引
SQL> select a.c1,b.c2 from a join b on a.c3=b.c3;

C1                                                           C2
------------------------------------------------------------ ------------------------------------------------------------
A_C1_01                                                      B_C2_01
A_C1_02                                                      B_C2_02
A_C1_03                                                      B_C2_03
A_C1_04                                                      B_C2_04
A_C1_05                                                      B_C2_05


执行计划
----------------------------------------------------------
Plan hash value: 2937931297

-----------------------------------------------------------------------------------------
| Id  | Operation                    | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |          |     5 |   340 |     6  (17)| 00:00:01 |
|   1 |  MERGE JOIN                  |          |     5 |   340 |     6  (17)| 00:00:01 |
|   2 |   TABLE ACCESS BY INDEX ROWID| A        |     5 |   170 |     2   (0)| 00:00:01 |
|   3 |    INDEX FULL SCAN           | IDX_A_C3 |     5 |       |     1   (0)| 00:00:01 |
|*  4 |   SORT JOIN                  |          |     5 |   170 |     4  (25)| 00:00:01 |
|   5 |    TABLE ACCESS FULL         | B        |     5 |   170 |     3   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------

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

   4 - access("A"."C3"="B"."C3")
       filter("A"."C3"="B"."C3")

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


统计信息
----------------------------------------------------------
          0  recursive calls
          0  db block gets
         11  consistent gets
          0  physical reads
          0  redo size
        735  bytes sent via SQL*Net to client
        523  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
          5  rows processed
左外连接&右外连接
 -- 无索引状态
SQL> select a.c1,b.c2 from a left join b on a.c3=b.c3;

C1                                                           C2
------------------------------------------------------------ ------------------------------------------------------------
A_C1_01                                                      B_C2_01
A_C1_02                                                      B_C2_02
A_C1_03                                                      B_C2_03
A_C1_04                                                      B_C2_04
A_C1_05                                                      B_C2_05


执行计划
----------------------------------------------------------
Plan hash value: 1365417139

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |     5 |   340 |     6   (0)| 00:00:01 |
|*  1 |  HASH JOIN OUTER   |      |     5 |   340 |     6   (0)| 00:00:01 |
|   2 |   TABLE ACCESS FULL| A    |     5 |   170 |     3   (0)| 00:00:01 |
|   3 |   TABLE ACCESS FULL| B    |     5 |   170 |     3   (0)| 00:00:01 |
---------------------------------------------------------------------------

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

   1 - access("A"."C3"="B"."C3"(+))

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


统计信息
----------------------------------------------------------
          0  recursive calls
          0  db block gets
         15  consistent gets
          0  physical reads
          0  redo size
        735  bytes sent via SQL*Net to client
        523  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          5  rows processed


 -- A表 C3 字段有索引，B表无索引
SQL> select a.c1,b.c2 from a left join b on a.c3=b.c3;

C1                                                           C2
------------------------------------------------------------ ------------------------------------------------------------
A_C1_01                                                      B_C2_01
A_C1_02                                                      B_C2_02
A_C1_03                                                      B_C2_03
A_C1_04                                                      B_C2_04
A_C1_05                                                      B_C2_05


执行计划
----------------------------------------------------------
Plan hash value: 1365417139

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |     5 |   340 |     6   (0)| 00:00:01 |
|*  1 |  HASH JOIN OUTER   |      |     5 |   340 |     6   (0)| 00:00:01 |
|   2 |   TABLE ACCESS FULL| A    |     5 |   170 |     3   (0)| 00:00:01 |
|   3 |   TABLE ACCESS FULL| B    |     5 |   170 |     3   (0)| 00:00:01 |
---------------------------------------------------------------------------

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

   1 - access("A"."C3"="B"."C3"(+))

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


统计信息
----------------------------------------------------------
          0  recursive calls
          0  db block gets
         15  consistent gets
          0  physical reads
          0  redo size
        735  bytes sent via SQL*Net to client
        523  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          5  rows processed


 -- B表 C3 字段有索引，A表无索引
SQL> select a.c1,b.c2 from a left join b on a.c3=b.c3;

C1                                                           C2
------------------------------------------------------------ ------------------------------------------------------------
A_C1_01                                                      B_C2_01
A_C1_02                                                      B_C2_02
A_C1_03                                                      B_C2_03
A_C1_04                                                      B_C2_04
A_C1_05                                                      B_C2_05


执行计划
----------------------------------------------------------
Plan hash value: 1365417139

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |     5 |   340 |     6   (0)| 00:00:01 |
|*  1 |  HASH JOIN OUTER   |      |     5 |   340 |     6   (0)| 00:00:01 |
|   2 |   TABLE ACCESS FULL| A    |     5 |   170 |     3   (0)| 00:00:01 |
|   3 |   TABLE ACCESS FULL| B    |     5 |   170 |     3   (0)| 00:00:01 |
---------------------------------------------------------------------------

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

   1 - access("A"."C3"="B"."C3"(+))

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


统计信息
----------------------------------------------------------
          0  recursive calls
          0  db block gets
         15  consistent gets
          0  physical reads
          0  redo size
        735  bytes sent via SQL*Net to client
        523  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          5  rows processed


-- A表与B表均有索引
SQL> select a.c1,b.c2 from a left join b on a.c3=b.c3;

C1                                                           C2
------------------------------------------------------------ ------------------------------------------------------------
A_C1_01                                                      B_C2_01
A_C1_02                                                      B_C2_02
A_C1_03                                                      B_C2_03
A_C1_04                                                      B_C2_04
A_C1_05                                                      B_C2_05


执行计划
----------------------------------------------------------
Plan hash value: 1365417139

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |     5 |   340 |     6   (0)| 00:00:01 |
|*  1 |  HASH JOIN OUTER   |      |     5 |   340 |     6   (0)| 00:00:01 |
|   2 |   TABLE ACCESS FULL| A    |     5 |   170 |     3   (0)| 00:00:01 |
|   3 |   TABLE ACCESS FULL| B    |     5 |   170 |     3   (0)| 00:00:01 |
---------------------------------------------------------------------------

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

   1 - access("A"."C3"="B"."C3"(+))

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


统计信息
----------------------------------------------------------
          0  recursive calls
          0  db block gets
         15  consistent gets
          0  physical reads
          0  redo size
        735  bytes sent via SQL*Net to client
        523  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          5  rows processed

 -- 无索引状态
SQL> select a.c1,b.c2 from a right join b on a.c3=b.c3;

C1                                                           C2
------------------------------------------------------------ ------------------------------------------------------------
A_C1_01                                                      B_C2_01
A_C1_02                                                      B_C2_02
A_C1_03                                                      B_C2_03
A_C1_04                                                      B_C2_04
A_C1_05                                                      B_C2_05


执行计划
----------------------------------------------------------
Plan hash value: 843196925

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |     5 |   340 |     6   (0)| 00:00:01 |
|*  1 |  HASH JOIN OUTER   |      |     5 |   340 |     6   (0)| 00:00:01 |
|   2 |   TABLE ACCESS FULL| B    |     5 |   170 |     3   (0)| 00:00:01 |
|   3 |   TABLE ACCESS FULL| A    |     5 |   170 |     3   (0)| 00:00:01 |
---------------------------------------------------------------------------

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

   1 - access("A"."C3"(+)="B"."C3")

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


统计信息
----------------------------------------------------------
          0  recursive calls
          0  db block gets
         15  consistent gets
          0  physical reads
          0  redo size
        735  bytes sent via SQL*Net to client
        523  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          5  rows processed


 -- A表 C3 字段有索引，B表无索引
SQL> select a.c1,b.c2 from a right join b on a.c3=b.c3;

C1                                                           C2
------------------------------------------------------------ ------------------------------------------------------------
A_C1_01                                                      B_C2_01
A_C1_02                                                      B_C2_02
A_C1_03                                                      B_C2_03
A_C1_04                                                      B_C2_04
A_C1_05                                                      B_C2_05


执行计划
----------------------------------------------------------
Plan hash value: 843196925

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |     5 |   340 |     6   (0)| 00:00:01 |
|*  1 |  HASH JOIN OUTER   |      |     5 |   340 |     6   (0)| 00:00:01 |
|   2 |   TABLE ACCESS FULL| B    |     5 |   170 |     3   (0)| 00:00:01 |
|   3 |   TABLE ACCESS FULL| A    |     5 |   170 |     3   (0)| 00:00:01 |
---------------------------------------------------------------------------

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

   1 - access("A"."C3"(+)="B"."C3")

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


统计信息
----------------------------------------------------------
          0  recursive calls
          0  db block gets
         15  consistent gets
          0  physical reads
          0  redo size
        735  bytes sent via SQL*Net to client
        523  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          5  rows processed


 -- B表 C3 字段有索引，A表无索引
SQL> select a.c1,b.c2 from a right join b on a.c3=b.c3;

C1                                                           C2
------------------------------------------------------------ ------------------------------------------------------------
A_C1_01                                                      B_C2_01
A_C1_02                                                      B_C2_02
A_C1_03                                                      B_C2_03
A_C1_04                                                      B_C2_04
A_C1_05                                                      B_C2_05


执行计划
----------------------------------------------------------
Plan hash value: 843196925

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |     5 |   340 |     6   (0)| 00:00:01 |
|*  1 |  HASH JOIN OUTER   |      |     5 |   340 |     6   (0)| 00:00:01 |
|   2 |   TABLE ACCESS FULL| B    |     5 |   170 |     3   (0)| 00:00:01 |
|   3 |   TABLE ACCESS FULL| A    |     5 |   170 |     3   (0)| 00:00:01 |
---------------------------------------------------------------------------

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

   1 - access("A"."C3"(+)="B"."C3")

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


统计信息
----------------------------------------------------------
          0  recursive calls
          0  db block gets
         15  consistent gets
          0  physical reads
          0  redo size
        735  bytes sent via SQL*Net to client
        523  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          5  rows processed


-- A表与B表均有索引
SQL> select a.c1,b.c2 from a right join b on a.c3=b.c3;

C1                                                           C2
------------------------------------------------------------ ------------------------------------------------------------
A_C1_01                                                      B_C2_01
A_C1_02                                                      B_C2_02
A_C1_03                                                      B_C2_03
A_C1_04                                                      B_C2_04
A_C1_05                                                      B_C2_05


执行计划
----------------------------------------------------------
Plan hash value: 843196925

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |     5 |   340 |     6   (0)| 00:00:01 |
|*  1 |  HASH JOIN OUTER   |      |     5 |   340 |     6   (0)| 00:00:01 |
|   2 |   TABLE ACCESS FULL| B    |     5 |   170 |     3   (0)| 00:00:01 |
|   3 |   TABLE ACCESS FULL| A    |     5 |   170 |     3   (0)| 00:00:01 |
---------------------------------------------------------------------------

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

   1 - access("A"."C3"(+)="B"."C3")

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


统计信息
----------------------------------------------------------
          0  recursive calls
          0  db block gets
         15  consistent gets
          0  physical reads
          0  redo size
        735  bytes sent via SQL*Net to client
        523  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          5  rows processed
全外连接
 -- 无索引状态
SQL> select a.c1,b.c2 from a full join b on a.c3=b.c3;

C1                                                           C2
------------------------------------------------------------ ------------------------------------------------------------
A_C1_01                                                      B_C2_01
A_C1_02                                                      B_C2_02
A_C1_03                                                      B_C2_03
A_C1_04                                                      B_C2_04
A_C1_05                                                      B_C2_05


执行计划
----------------------------------------------------------
Plan hash value: 3456740935

----------------------------------------------------------------------------------
| Id  | Operation             | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |          |     5 |   170 |     6   (0)| 00:00:01 |
|   1 |  VIEW                 | VW_FOJ_0 |     5 |   170 |     6   (0)| 00:00:01 |
|*  2 |   HASH JOIN FULL OUTER|          |     5 |   340 |     6   (0)| 00:00:01 |
|   3 |    TABLE ACCESS FULL  | A        |     5 |   170 |     3   (0)| 00:00:01 |
|   4 |    TABLE ACCESS FULL  | B        |     5 |   170 |     3   (0)| 00:00:01 |
----------------------------------------------------------------------------------

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

   2 - access("A"."C3"="B"."C3")

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


统计信息
----------------------------------------------------------
          0  recursive calls
          0  db block gets
         15  consistent gets
          0  physical reads
          0  redo size
        735  bytes sent via SQL*Net to client
        523  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          5  rows processed


-- 有索引状态
SQL> select a.c1,b.c2 from a full join b on a.c3=b.c3;

C1                                                           C2
------------------------------------------------------------ ------------------------------------------------------------
A_C1_01                                                      B_C2_01
A_C1_02                                                      B_C2_02
A_C1_03                                                      B_C2_03
A_C1_04                                                      B_C2_04
A_C1_05                                                      B_C2_05


执行计划
----------------------------------------------------------
Plan hash value: 3456740935

----------------------------------------------------------------------------------
| Id  | Operation             | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |          |     5 |   170 |     6   (0)| 00:00:01 |
|   1 |  VIEW                 | VW_FOJ_0 |     5 |   170 |     6   (0)| 00:00:01 |
|*  2 |   HASH JOIN FULL OUTER|          |     5 |   340 |     6   (0)| 00:00:01 |
|   3 |    TABLE ACCESS FULL  | A        |     5 |   170 |     3   (0)| 00:00:01 |
|   4 |    TABLE ACCESS FULL  | B        |     5 |   170 |     3   (0)| 00:00:01 |
----------------------------------------------------------------------------------

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

   2 - access("A"."C3"="B"."C3")

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


统计信息
----------------------------------------------------------
          0  recursive calls
          0  db block gets
         15  consistent gets
          0  physical reads
          0  redo size
        735  bytes sent via SQL*Net to client
        523  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          5  rows processed
theta连接
-- 有索引状态
SQL> select a.c1,b.c2 from a join b on a.c3 != b.c3;

C1                                                           C2
------------------------------------------------------------ ------------------------------------------------------------
A_C1_01                                                      B_C2_02
A_C1_01                                                      B_C2_03
A_C1_01                                                      B_C2_04
A_C1_01                                                      B_C2_05
A_C1_02                                                      B_C2_01
A_C1_02                                                      B_C2_03
A_C1_02                                                      B_C2_04
A_C1_02                                                      B_C2_05
A_C1_03                                                      B_C2_01
A_C1_03                                                      B_C2_02
A_C1_03                                                      B_C2_04

C1                                                           C2
------------------------------------------------------------ ------------------------------------------------------------
A_C1_03                                                      B_C2_05
A_C1_04                                                      B_C2_01
A_C1_04                                                      B_C2_02
A_C1_04                                                      B_C2_03
A_C1_04                                                      B_C2_05
A_C1_05                                                      B_C2_01
A_C1_05                                                      B_C2_02
A_C1_05                                                      B_C2_03
A_C1_05                                                      B_C2_04

已选择20行。


执行计划
----------------------------------------------------------
Plan hash value: 4030965610

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |    20 |  1360 |    12   (0)| 00:00:01 |
|   1 |  NESTED LOOPS      |      |    20 |  1360 |    12   (0)| 00:00:01 |
|   2 |   TABLE ACCESS FULL| A    |     5 |   170 |     3   (0)| 00:00:01 |
|*  3 |   TABLE ACCESS FULL| B    |     4 |   136 |     2   (0)| 00:00:01 |
---------------------------------------------------------------------------

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

   3 - filter("A"."C3"<>"B"."C3")

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


统计信息
----------------------------------------------------------
          0  recursive calls
          0  db block gets
         45  consistent gets
          0  physical reads
          0  redo size
       1121  bytes sent via SQL*Net to client
        534  bytes received via SQL*Net from client
          3  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
         20  rows processed
最佳优化
优化方式：
create index idx_a_c1 on a(c1);
create index idx_b_c3 on b(c3);

-- 内连接优化
SQL> select a.c1,b.c2 from a join b on a.c3=b.c3 where a.c1='A_C1_03';

C1                                                           C2
------------------------------------------------------------ ------------------------------------------------------------
A_C1_03                                                      B_C2_03


执行计划
----------------------------------------------------------
Plan hash value: 21225956

-----------------------------------------------------------------------------------------
| Id  | Operation                    | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |          |     1 |    68 |     4   (0)| 00:00:01 |
|   1 |  NESTED LOOPS                |          |     1 |    68 |     4   (0)| 00:00:01 |
|   2 |   NESTED LOOPS               |          |     1 |    68 |     4   (0)| 00:00:01 |
|*  3 |    TABLE ACCESS FULL         | A        |     1 |    34 |     3   (0)| 00:00:01 |
|*  4 |    INDEX RANGE SCAN          | IDX_B_C3 |     1 |       |     0   (0)| 00:00:01 |
|   5 |   TABLE ACCESS BY INDEX ROWID| B        |     1 |    34 |     1   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------

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

   3 - filter("A"."C1"='A_C1_03')
   4 - access("A"."C3"="B"."C3")

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


统计信息
----------------------------------------------------------
          0  recursive calls
          0  db block gets
         11  consistent gets
          0  physical reads
          0  redo size
        605  bytes sent via SQL*Net to client
        523  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed



-- 左外连接优化
SQL> select a.c1,b.c2 from a left join b on a.c3=b.c3 where a.c1='A_C1_03';

C1                                                           C2
------------------------------------------------------------ -----------------------------------------------------------
A_C1_03                                                      B_C2_03


执行计划
----------------------------------------------------------
Plan hash value: 426852710

-----------------------------------------------------------------------------------------
| Id  | Operation                    | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |          |     1 |    68 |     3   (0)| 00:00:01 |
|   1 |  NESTED LOOPS OUTER          |          |     1 |    68 |     3   (0)| 00:00:01 |
|   2 |   TABLE ACCESS BY INDEX ROWID| A        |     1 |    34 |     2   (0)| 00:00:01 |
|*  3 |    INDEX RANGE SCAN          | IDX_A_C1 |     1 |       |     1   (0)| 00:00:01 |
|   4 |   TABLE ACCESS BY INDEX ROWID| B        |     1 |    34 |     1   (0)| 00:00:01 |
|*  5 |    INDEX RANGE SCAN          | IDX_B_C3 |     1 |       |     0   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------

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

   3 - access("A"."C1"='A_C1_03')
   5 - access("A"."C3"="B"."C3"(+))

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


统计信息
----------------------------------------------------------
         12  recursive calls
          0  db block gets
         26  consistent gets
          0  physical reads
          0  redo size
        605  bytes sent via SQL*Net to client
        523  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed


-- 右外连接优化
SQL> select a.c1,b.c2 from a right join b on a.c3=b.c3 where a.c1='A_C1_03';

C1                                                           C2
------------------------------------------------------------ -----------------------------------------------------------
A_C1_03                                                      B_C2_03


执行计划
----------------------------------------------------------
Plan hash value: 3356106362

------------------------------------------------------------------------------------------
| Id  | Operation                     | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |          |     1 |    68 |     3   (0)| 00:00:01 |
|   1 |  NESTED LOOPS                 |          |     1 |    68 |     3   (0)| 00:00:01 |
|   2 |   NESTED LOOPS                |          |     1 |    68 |     3   (0)| 00:00:01 |
|   3 |    TABLE ACCESS BY INDEX ROWID| A        |     1 |    34 |     2   (0)| 00:00:01 |
|*  4 |     INDEX RANGE SCAN          | IDX_A_C1 |     1 |       |     1   (0)| 00:00:01 |
|*  5 |    INDEX RANGE SCAN           | IDX_B_C3 |     1 |       |     0   (0)| 00:00:01 |
|   6 |   TABLE ACCESS BY INDEX ROWID | B        |     1 |    34 |     1   (0)| 00:00:01 |
------------------------------------------------------------------------------------------

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

   4 - access("A"."C1"='A_C1_03')
   5 - access("A"."C3"="B"."C3")

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


统计信息
----------------------------------------------------------
         10  recursive calls
          0  db block gets
         26  consistent gets
          0  physical reads
          0  redo size
        605  bytes sent via SQL*Net to client
        523  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed


-- 全表外联优化
SQL> select a.c1,b.c2 from a full join b on a.c3=b.c3 where a.c1='A_C1_03';

C1                                                           C2
------------------------------------------------------------ ------------------------------------------------------------
A_C1_03                                                      B_C2_03


执行计划
----------------------------------------------------------
Plan hash value: 708156715

-----------------------------------------------------------------------------------------
| Id  | Operation                    | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |          |     1 |    68 |     4   (0)| 00:00:01 |
|   1 |  NESTED LOOPS OUTER          |          |     1 |    68 |     4   (0)| 00:00:01 |
|*  2 |   TABLE ACCESS FULL          | A        |     1 |    34 |     3   (0)| 00:00:01 |
|   3 |   TABLE ACCESS BY INDEX ROWID| B        |     1 |    34 |     1   (0)| 00:00:01 |
|*  4 |    INDEX RANGE SCAN          | IDX_B_C3 |     1 |       |     0   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------

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

   2 - filter("A"."C1"='A_C1_03')
   4 - access("A"."C3"="B"."C3"(+))

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


统计信息
----------------------------------------------------------
          0  recursive calls
          0  db block gets
         11  consistent gets
          0  physical reads
          0  redo size
        605  bytes sent via SQL*Net to client
        523  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed


-- theta 连接优化
SQL> select a.c1,b.c2 from a join b on a.c3 != b.c3 where a.c1='A_C1_03';

C1                                                           C2
------------------------------------------------------------ ------------------------------------------------------------
A_C1_03                                                      B_C2_01
A_C1_03                                                      B_C2_02
A_C1_03                                                      B_C2_04
A_C1_03                                                      B_C2_05


执行计划
----------------------------------------------------------
Plan hash value: 4030965610

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |     4 |   272 |     6   (0)| 00:00:01 |
|   1 |  NESTED LOOPS      |      |     4 |   272 |     6   (0)| 00:00:01 |
|*  2 |   TABLE ACCESS FULL| A    |     1 |    34 |     3   (0)| 00:00:01 |
|*  3 |   TABLE ACCESS FULL| B    |     4 |   136 |     3   (0)| 00:00:01 |
---------------------------------------------------------------------------

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

   2 - filter("A"."C1"='A_C1_03')
   3 - filter("A"."C3"<>"B"."C3")

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


统计信息
----------------------------------------------------------
          0  recursive calls
          0  db block gets
         16  consistent gets
          0  physical reads
          0  redo size
        698  bytes sent via SQL*Net to client
        523  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          4  rows processed
Eric.zhong
关注
  • 0
    点赞
  • 踩
  • 1
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
专栏目录
关于Oracle多表连接,提高效率,性能优化操作
01-19
执行路径:ORACLE的这个功能大大地提高了SQL的执行性能并节省了内存的使用:我们发现,单表数据的统计比多表统计的速度完全是两个概念.单表统计可能只要0.02秒,但是2张表联合统计就可能要几十表了. 这是因为ORACLE只对简单的表提供高速缓冲(cache buffering) ,这个功能并不适用于多表连接查询..数据库管理员必须在init.ora中为这个区域设置合适的参数,当这个内存区域越大,就可以保留更多的语句,当然被共享的可能性也就越大了. 当你向ORACLE提交一个SQL语句,ORACLE会首先在这块内存中查找相同的语句. 这里需要注明的是,ORACLE对两者采取的是一种严格匹配,要
oracle-01122,oracle sql优化之多表连接优化
weixin_32659841的博客
04-03 107
oracle sql优化之多表连接优化Use equality first.使用等连接Use range operators only where equality does not apply.只有在等连接不可用的情况下事由区间连接Avoid use of negatives in the form of !=or NOT.避免使用 ！= 或者 notAvoid LIKE pattern mat...
oracle如何优化多表连接,多表连接SQL优化如何处理
weixin_35065221的博客
04-03 189
类似：WITH T_VAS(SELECT DISTINCT SUBSTR(yg5.CLASS_FLAG, 1, 9) AS CLASS_FLAG,yg5.ZQID_jk005,yg5.sectname,p205.secid_zj205FROM TABLE(arr_repdate) ARR1,jk005 yg5,zj205 p205WHERE ARR1.COLUMN_VALUE = SUBSTR(y...
Oracle性能优化-5-表连接
qq_34153210的博客
04-27 255
表连接，就是俗称的“=”，包括左连接右连接外连接，我们这里讨论的是优化器的连接，在Oracle性能优化-3-提示，3.13-3.15涉及到了嵌套循环，排序合并，哈希连接。3.6INDEX_JOIN索引合并。 1.连接原理 1.1嵌套循环 由执行计划得知，ph作为驱动表，pll作为内表（执行计划靠上的是驱动表），从驱动表读取一条数据，然后去内表找所有po_header_id对应的所有数据。 要点： 驱动表数据行源小，上面的例子中ph由于指定了po_header_id,返回数据只有一条，默认的驱动
oracle连接多张表优化,oracle 多表查询优化
weixin_29469949的博客
04-04 1781
ORACLE中的高速缓冲用于保存查询过的sql语句，单只对单表查询起作用单表查询优化：写查询语句时尽量统一规范，只要有一点不一样，都不能使用高速缓冲。多表优化：(1)FROM子句后面的表顺序ORACLE在解析sql语句的时候对FROM子句后面的表名是从右往左解析的，在对多表查询中，一定要把小表写在最右边，例如tableA有100w条记录 ，tableB有1w条记录select count(*) f...
ORACLE9i_优化设计与系统调整
01-16
第一部分 ORACLE系统优化基本知识 23 第1章 ORACLE结构回顾 23 §1.1 Oracle数据库结构 23 §1.1.1 Oracle数据字典 23 §1.1.2 表空间与数据文件 24 §1.1.3 Oracle实例(Instance) 24 §1.2 Oracle文件 26 §1.2.1...
构建最高可用Oracle数据库系统 Oracle 11gR2 RAC管理、维护与性能优化
05-11
2.8.1连接方式测试 2.8.2异常情况测试 2.9虚拟机搭建RAC 2.9.1虚拟机Xen简介 2.9.2启动主机Xen内核 2.9.3 Xen虚拟机创建网络环境 2.9.4创建Xen存储服务器 2.9.5创建Xen数据库服务器 2.10本章小结 第3章 ...
oracle实验报告
01-10
2、多表查询 四、 SQL*PLUS常用命令 表1 常用报表格式化名命令 命令 定义 Btitle 为报表的每一页设置底端标题 Column 设置列的标题和格式 Compute 让SQL*PLUS计算各种值 Remark 将某些字标记为注释 Set linesize ...
（E文）基于成本的Oracle优化法则.pdf
03-13
12.5 多表连接 318 12.6 本章小结 321 12.7 测试用例 321 第13章 排序与归并连接 323 13.1 入门 324 13.1.1 内存的使用 329 13.1.2 CPU的使用 330 13.1.3 sort_area_retained_size 333 13.1.4 pga_aggregate_target ...
韩顺平oracle学习笔记
07-08
Application,development，sql*plus,worksheet,,4)pl/sql,developer,属于第三方软件，主要用于开发，测试，优化oracle,pl/sql,的 存储过程.如:触发器，此软件oracle不带，需要单独安装。,5)oracle的企业管理器(oem,...
Oracle 多表查询优化
09-06
Oracle 多表查询优化
oracle如何优化多表连接,oracle sql优化之多表连接优化
weixin_32256861的博客
04-03 402
oracle sql优化之多表连接优化Use equality first.使用等连接Use range operators only where equality does not apply.只有在等连接不可用的情况下事由区间连接Avoid use of negatives in the form of !=or NOT.避免使用 ！= 或者 notAvoid LIKE pattern mat...
【Oracle】表链接的优化
陶洲川的博客
03-11 225
一、问题 在写SQL表与表之间的关联时，如何优化表执行效率； 二、分析及借据 1、首先我们要明确一个概念——【驱动表】，所谓驱动表，就是Oracle分析器首先切入你整个SQL的第一个表，是整个SQL分析的切入点，因为Oracle分析器会从后向前解析你的SQL，如果驱【驱动表】的数据量比较小，将【驱动表】放在最后，将会较大提升查询效率； 2、当SQL中有交叉表时，作为关联其他表的中间表，建议将关联表...
oracle 表连接性能优化,Oracle SQL性能优化
weixin_39970166的博客
04-03 123
Oracle SQL性能优化1、用EXISTS替代IN，NOT EXISTS替代NOT IN： 在子查询中，NOT 在许多基于基础表的查询中，为了满足一个条件，往往需要对另一个表进行联接.在这种情况下， 使用EXISTS(或NOT EXISTS)通常将提高查询的效率。[@more@]SELECT *FROM EMP (基础表)WHERE EMPNO > 0AND EXISTS (SELECT...
数据库查询优化之多表连接时的优化
qq_41082092的博客
03-23 554
当我们连接多张表时 有时候会跟where条件 例如下面这个sql语句： select s.patient_id, p.name plan_name, p.way plan_way, p.class_time, p.desc plan_desc, ...
SQL优化 MySQL版 - 多表优化及细节详讲
weixin_33860737的博客
03-04 376
SQL优化 MySQL版 - 多表优化及细节详讲 优化准备 首先我们需要有一个数据库，coursedb，分别创建course（课程表）、teacher（老师表），有以下字段，我们接下来将用以下这张表来做优化实例； teacher表里面的id 是代表一号老师教2号课程2号老师教1号课程3号老师教三号课程；id是主键 course表就是课程表1号代表ja...
【mysql优化实战】第四章 多表连接优化分析
无bug无江湖
07-08 397
前面的章节我们一起观察了单表100万条记录下的查询时间和通过explain观察了执行计划，讲解了如何通过看explain的结果来分析是否需要优化sql。 这一章，我们将添加一个uid_table表，给这个表添加12记录，然后与100W条记录的big_table表进行连接时使用。来看一下多表连接的情况下，sql的执行速度和通过explain来分析是否需要优化。 uid_table的表结构和插入记录的语句如下： INSERT INTO uid_table (uid) SELECT uid from big
性能调优7：多表连接 - join
悦光阴的博客
01-14 160
在产品环境中，往往存在着大量的表连接情景，不管是inner join、outer join、cross join和full join(逻辑连接符号)，在内部都会转化为物理连接(Physical Join)，SQL Server共有三种物理连接：Nested Loop(嵌套循环)，Merge Join(合并连接)和Hash Join(哈希连接)。这三个物理连接的处理方式不同，分别应用在不同的场景中...
ORACLE表连接方式的分析与优化-tony
chuyuqing的专栏
04-29 972
一 引言 数据仓库技术是目前已知的比较成熟和被广泛采用的解决方案，用于整和电信运营企业内部所有分散的原始业务数据，并通过便捷有效的数据访问手段，可以支持企业内部不同部门，不同需求，不同层次的用户随时获得自己所需的信息。数据仓库系统需要能够及时地追踪和分析大量的历史数据，并能够及时做出分析和预测，因此实时性是一个非常重要的指标。ORACLE由于可靠性、高性能等方面的特点，在电信行业大部分的数据仓库
oracle in 优化
最新发布
05-27
优化是 Oracle 数据库管理的重要部分，通过优化可以提高数据库的性能和响应速度。以下是一些优化技巧和建议： 1. 确认数据库结构的正确性，包括索引、表空间、存储过程等。 2. 优化 SQL 查询语句，使用索引、避免...

“相关推荐”对你有帮助么？

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值