Здравствуйте.
Не могу понять, почему и Ora 11.2 и SS 2005 XE (другого нет под рукой) не делают join elimitation в следующем случае.
DDL:
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
create table tm(id int primary key, m01 int); -- master table
create table td(id int primary key, pid int references tm, d01 int); -- detail table
insert into tm values(1, 100);
insert into tm values(2, 200);
insert into tm values(3, 300);
insert into tm values(4, 400);
insert into tm values(5, 500);
insert into td values(10, 1, 111);
insert into td values(20, 2, 222);
insert into td values(30, 2, 223);
insert into td values(40, 3, 333);
insert into td values(50, 3, 334);
commit;
Query:
1.
select m.* from tm m left join td d on m.id=d.pid;
Этот запрос всегда выдаёт полное кол-во строк из таблицы tm, т.к. тут внешнее соединение. Далее, в select-списке нет ни одного поля из таблицы td ==> соединение здесь вообще можно не проводить.
Тем не менее, получаю вот следующие результаты.
1. Oracle 11.2.0.3.0: hash join таблиц
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
SQL> set autot trace explain
SQL> select m.* from tm m left join td d on m.id=d.pid;
Затрач.время: 00:00:00.00
План выполнения
----------------------------------------------------------
Plan hash value: 100464988
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 5 | 195 | 5 (20)| 00:00:01 |
|* 1 | HASH JOIN OUTER | | 5 | 195 | 5 (20)| 00:00:01 |
| 2 | TABLE ACCESS FULL| TM | 5 | 130 | 2 (0)| 00:00:01 |
| 3 | TABLE ACCESS FULL| TD | 5 | 65 | 2 (0)| 00:00:01 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("M"."ID"="D"."PID"(+))
2. MS SQL 2005 Express Edition: nested loops
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
set statistics profile on;
select m.* from tm m left join td d on m.id=d.pid;
set statistics profile off;
(7 row(s) affected)
Table 'td'. Scan count 1, logical reads 11 ...
Table 'tm'. Scan count 1, logical reads 2 ...
Rows Executes StmtText
7 1 select m.* from tm m left join td d on m.id=d.pid;
7 1 |--Nested Loops(Left Outer Join, WHERE:([misc].[dbo].[tm].[id] as [m].[id]=[misc].[dbo].[td].[pid] as [d].[pid]))
5 1 |--Clustered Index Scan(OBJECT:([misc].[dbo].[tm].[PK__tm__7C8480AE] AS [m]))
25 5 |--Clustered Index Scan(OBJECT:([misc].[dbo].[td].[PK__td__7E6CC920] AS [d]))
Кстати, "обратный" запрос, в котором надо получить только поля из detail-таблицы:
1.
select d.* from td d inner join tm m on m.id=d.pid;
- выдаёт разумный план только в Оракле:
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
SQL> select d.* from td d join tm m on m.id=d.pid;
Затрач.время: 00:00:00.01
План выполнения
----------------------------------------------------------
Plan hash value: 556202680
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 5 | 195 | 2 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| TD | 5 | 195 | 2 (0)| 00:00:01 |
--------------------------------------------------------------------------
В SQL Server'e - снова чтение двух таблиц + Nested Loops. Только когда detail-таблица становится ведущей в left join'e, SS2005 догадывается не ходить за данными в tm.
Какие будут мнения по этому вопросу ?
PS.
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options
---------------------------------------------------------------------------------------------------------------------
Microsoft SQL Server 2005 - 9.00.5000.00 (Intel X86) Dec 10 2010 10:56:29 Copyright (c) 1988-2005 Microsoft Corporation Express Edition
with Advanced Services on Windows NT 5.0 (Build 2195: Service Pack 4)
