我正在尝试优化前同事的一些SQL代码,但出现了一些问题。我想知道是左联接到整个表还是左联接到只有所需列的表的一部分(由子查询创建(更好?
有没有办法测试这两种情况之间的性能?例:
SELECT A.*, B.COL1, B.COL2, B.COL3
FROM TABLE_A A
LEFT JOIN TABLE_B ON A.ID = B.ID;
SELECT A.*, C.*
FROM TABLE_A
LEFT JOIN
(SELECT B.ID, B.COL1, B.COL2, B.COL3 FROM TABLE_B) C ON C.ID = A.ID
在这种情况下没有区别。可以通过获取两个查询的执行计划来验证这一点。有很多方法可以做到这一点。
获取可以在这样的问答网站上分享的计划的一个好方法是:
- 在运行查询之前,请
set serverouput off
- 使用
gather_plan_statistics
提示运行查询 - 通过致电
dbms_xplan.display_cursor
获取他们的执行计划
这样做,你会看到:
create table table_a (
id int
);
create table table_b (
id int,
col1 int,
col2 int,
col3 int
);
insert into table_a values ( 1 );
insert into table_a values ( 2 );
insert into table_b values ( 1, 1, 1, 1 );
insert into table_b values ( 3, 3, 3, 3 );
commit;
set serveroutput off
select /*+ gather_plan_statistics */
a.*, b.col1, b.col2, b.col3
from table_a a
left join table_b b
on a.id = b.id;
select *
from table(dbms_xplan.display_cursor(null, null, 'IOSTATS LAST'));
PLAN_TABLE_OUTPUT
SQL_ID 64516xvpa898t, child number 1
-------------------------------------
select /*+ gather_plan_statistics */ a.*, b.col1, b.col2, b.col3
from table_a a left join table_b b on a.id = b.id
Plan hash value: 1267695137
----------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 2 |00:00:00.01 | 14 |
|* 1 | HASH JOIN OUTER | | 1 | 2 | 2 |00:00:00.01 | 14 |
| 2 | TABLE ACCESS FULL| TABLE_A | 1 | 2 | 2 |00:00:00.01 | 7 |
| 3 | TABLE ACCESS FULL| TABLE_B | 1 | 2 | 2 |00:00:00.01 | 7 |
----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("A"."ID"="B"."ID")
Note
-----
- dynamic sampling used for this statement (level=2)
select /*+ gather_plan_statistics */
a.*, c.*
from table_a a
left join (
select b.id, b.col1, b.col2, b.col3
from table_b b
) c
on c.id = a.id;
select *
from table(dbms_xplan.display_cursor(null, null, 'IOSTATS LAST'));
PLAN_TABLE_OUTPUT
SQL_ID b0abq59kzw8df, child number 0
-------------------------------------
select /*+ gather_plan_statistics */ a.*, c.* from table_a a
left join ( select b.id, b.col1, b.col2, b.col3 from table_b b )
c on c.id = a.id
Plan hash value: 1267695137
----------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 2 |00:00:00.01 | 14 |
|* 1 | HASH JOIN OUTER | | 1 | 2 | 2 |00:00:00.01 | 14 |
| 2 | TABLE ACCESS FULL| TABLE_A | 1 | 2 | 2 |00:00:00.01 | 7 |
| 3 | TABLE ACCESS FULL| TABLE_B | 1 | 2 | 2 |00:00:00.01 | 7 |
----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("B"."ID"="A"."ID")
Note
-----
- dynamic sampling used for this statement (level=2)
请注意:
- 两个查询的"计划哈希值"相同 (1267695137(
- 计划的"开始"、"A 行和缓冲区"列中的值相同
=> 查询使用相同的计划并执行相同的工作量。