Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Сравнение СУБД Новый топик    Ответить
Топик располагается на нескольких страницах: [1] 2   вперед  Ctrl      все
 Не вижу join elimination в Oracle и Sql Server'e для примитивной выборки master-detail  [new]
Таблоид
Member

Откуда:
Сообщений: 9456
Блог
Здравствуйте.

Не могу понять, почему и Ora 11.2 и SS 2005 XE (другого нет под рукой) не делают join elimitation в следующем случае.
DDL:
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:
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 таблиц
+
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
+
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-таблицы:
select d.* from td d inner join tm m on m.id=d.pid;
- выдаёт разумный план только в Оракле:
+

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)
31 авг 12, 18:25    [13097052]     Ответить | Цитировать Сообщить модератору
 Re: Не вижу join elimination в Oracle и Sql Server'e для примитивной выборки master-detail  [new]
ScareCrow
Member

Откуда: Белый город
Сообщений: 17472
автор
Microsoft SQL Server 2005

2012 год на дворе.
31 авг 12, 18:44    [13097156]     Ответить | Цитировать Сообщить модератору
 Re: Не вижу join elimination в Oracle и Sql Server'e для примитивной выборки master-detail  [new]
Таблоид
Member

Откуда:
Сообщений: 9456
Блог
ScareCrow
автор
Microsoft SQL Server 2005

2012 год на дворе.
ну, и что там в SS 2012 для вышеприведенного ?
31 авг 12, 18:45    [13097163]     Ответить | Цитировать Сообщить модератору
 Re: Не вижу join elimination в Oracle и Sql Server'e для примитивной выборки master-detail  [new]
+100500
Guest
Таблоид, вы оставьте по одной записи в каждой таблице - сравните план, потом по 100000/50000 ...
31 авг 12, 23:06    [13098039]     Ответить | Цитировать Сообщить модератору
 Re: Не вижу join elimination в Oracle и Sql Server'e для примитивной выборки master-detail  [new]
locky
Member

Откуда: Харьков, Украина
Сообщений: 62034
Таблоид,

ну, во первых, я бы сказал что сравнивать экспресс и энтерпрайз - как минимум грешно.


во вторых я бы сказал, что при тех данных, что использованы в тестовом примере - сервер может вообще планами не заморачиваться, а делать как попало - хоть декарт+фильтрация
31 авг 12, 23:18    [13098095]     Ответить | Цитировать Сообщить модератору
 Re: Не вижу join elimination в Oracle и Sql Server'e для примитивной выборки master-detail  [new]
locky
Member

Откуда: Харьков, Украина
Сообщений: 62034
Ага, сорри, не досмотрел немного (что, вообще говоря, не отменяет того что я сказал выше)

вот это вот "Этот запрос всегда выдаёт полное кол-во строк из таблицы tm, т.к. тут внешнее соединение. Далее, в select-списке нет ни одного поля из таблицы td ==> соединение здесь вообще можно не проводить." - бред
Потому и результат такой.
31 авг 12, 23:22    [13098110]     Ответить | Цитировать Сообщить модератору
 Re: Не вижу join elimination в Oracle и Sql Server'e для примитивной выборки master-detail  [new]
Таблоид
Member

Откуда:
Сообщений: 9456
Блог
locky
при тех данных, что использованы в тестовом примере - сервер может вообще планами не заморачиваться
OK, вот побольше данных:
create table nx(i int);
commit;
delete from nx;
insert into nx 
select number i
from master..spt_values
where type='P' and number<1000;
----------
delete from td;
delete from tm;

insert into tm(id, m01)
select n1.i*1000+n0.i k, 1
from nx n0, nx n1
where n1.i*1000+n0.i<100000;

insert into td(id, pid, d01)
select n1.i*1000+n0.i k, (n1.i*1000+n0.i)%100000, 1
from nx n0, nx n1
where n1.i*1000+n0.i<500000;

update statistics tm;
update statistics td;
go
SET SHOWPLAN_ALL ON;
go
select m.* from tm m left join td d on m.id=d.pid;
select d.* from td d join tm m on m.id=d.pid;
go
SET SHOWPLAN_ALL OFF;


Результат:
select m.* from tm m left join td d on m.id=d.pid;
|--Hash Match(Left Outer Join, HASH:([m].[id])=([d].[pid]), RESIDUAL:([misc].[dbo].[tm].[id] as [m].[id]=[misc].[dbo].[td].[pid] as [d].[pid]))
|--Clustered Index Scan(OBJECT:([misc].[dbo].[tm].[PK__tm__7C8480AE] AS [m]))
|--Clustered Index Scan(OBJECT:([misc].[dbo].[td].[PK__td__7E6CC920] AS [d]))
select d.* from td d join tm m on m.id=d.pid;
|--Hash Match(Inner Join, HASH:([m].[id])=([d].[pid]), RESIDUAL:([misc].[dbo].[td].[pid] as [d].[pid]=[misc].[dbo].[tm].[id] as [m].[id]))
|--Clustered Index Scan(OBJECT:([misc].[dbo].[tm].[PK__tm__7C8480AE] AS [m]))
|--Clustered Index Scan(OBJECT:([misc].[dbo].[td].[PK__td__7E6CC920] AS [d]))
locky
вот это вот <...> - бред
Более подробное объяснение будет ? Вы DDL точно внимательно посмотрели ?
31 авг 12, 23:30    [13098139]     Ответить | Цитировать Сообщить модератору
 Re: Не вижу join elimination в Oracle и Sql Server'e для примитивной выборки master-detail  [new]
locky
Member

Откуда: Харьков, Украина
Сообщений: 62034
Таблоид
locky
вот это вот <...> - бред
Более подробное объяснение будет ? Вы DDL точно внимательно посмотрели ?


Да. Я мало того что его посмотрел, я всё скопировал и выполнил
и что я могу сказать - в исходной таблице tm - 5 строк, а результатом выполнения вашего запроса являются 7 строк, что не в полной мере отвечает заявленном "Этот запрос всегда выдаёт полное кол-во строк из таблицы tm" и уж точно противоречит "соединение здесь вообще можно не проводить."
31 авг 12, 23:32    [13098145]     Ответить | Цитировать Сообщить модератору
 Re: Не вижу join elimination в Oracle и Sql Server'e для примитивной выборки master-detail  [new]
locky
Member

Откуда: Харьков, Украина
Сообщений: 62034
И, кстати, глубоко по шарабану - внешнее там или унутренее объединение
Исходная посылка - неправильная, следовательно - не следует удивляться тому что реальный результат не соответствует якобы ожидаемому
31 авг 12, 23:34    [13098149]     Ответить | Цитировать Сообщить модератору
 Re: Не вижу join elimination в Oracle и Sql Server'e для примитивной выборки master-detail  [new]
locky
Member

Откуда: Харьков, Украина
Сообщений: 62034
вот если сделать вот так
select d.* from td d left join tm m on m.id=d.pid;

тогда да, ненужный джойн убирается
31 авг 12, 23:40    [13098165]     Ответить | Цитировать Сообщить модератору
 Re: Не вижу join elimination в Oracle и Sql Server'e для примитивной выборки master-detail  [new]
Таблоид
Member

Откуда:
Сообщений: 9456
Блог
locky
в исходной таблице tm - 5 строк, а результатом выполнения вашего запроса являются 7 строк,
да, пардон: с запросом tm left join td я глупость сморозил, бывает :-)
Но с "обратным" запросом - почему так ?
я про то, что
select d.* from td d INNER join tm m on m.id=d.pid;
и
select d.* from td d LEFT join tm m on m.id=d.pid; 
должны выдавать одинаковое число строк: каждой строке из 'd' может соотв-вать только одна строка из tm. И лезть в tm тут точно нет нужды, ибо не нужно от неё ничего. А он таки лезет, когда там inner join.
31 авг 12, 23:42    [13098170]     Ответить | Цитировать Сообщить модератору
 Re: Не вижу join elimination в Oracle и Sql Server'e для примитивной выборки master-detail  [new]
Таблоид
Member

Откуда:
Сообщений: 9456
Блог
locky
тогда да, ненужный джойн убирается
я чуть опоздал со своим ответом.
Так почему этот самый ненужный джойн стал ему нужен при INNER join'e ?
31 авг 12, 23:42    [13098176]     Ответить | Цитировать Сообщить модератору
 Re: Не вижу join elimination в Oracle и Sql Server'e для примитивной выборки master-detail  [new]
locky
Member

Откуда: Харьков, Украина
Сообщений: 62034
Таблоид
locky
тогда да, ненужный джойн убирается
я чуть опоздал со своим ответом.
Так почему этот самый ненужный джойн стал ему нужен при INNER join'e ?



Потому что результаты запроса - могут быть разными, знаете-ли, при внешнем и внутреннем объединении

Хотя, по хорошему, надо смотреть на trusted констрейнта - и пропускать соединение
31 авг 12, 23:45    [13098183]     Ответить | Цитировать Сообщить модератору
 Re: Не вижу join elimination в Oracle и Sql Server'e для примитивной выборки master-detail  [new]
locky
Member

Откуда: Харьков, Украина
Сообщений: 62034
Таблоид
locky
в исходной таблице tm - 5 строк, а результатом выполнения вашего запроса являются 7 строк,
да, пардон: с запросом tm left join td я глупость сморозил, бывает :-)
Но с "обратным" запросом - почему так ?
я про то, что
select d.* from td d INNER join tm m on m.id=d.pid;
и
select d.* from td d LEFT join tm m on m.id=d.pid; 
должны выдавать одинаковое число строк: каждой строке из 'd' может соотв-вать только одна строка из tm. И лезть в tm тут точно нет нужды, ибо не нужно от неё ничего. А он таки лезет, когда там inner join.

Да, "должны". Но могут и "не" :)
31 авг 12, 23:45    [13098187]     Ответить | Цитировать Сообщить модератору
 Re: Не вижу join elimination в Oracle и Sql Server'e для примитивной выборки master-detail  [new]
Таблоид
Member

Откуда:
Сообщений: 9456
Блог
locky
о хорошему, надо смотреть на trusted констрейнта - и пропускать соединение
DBCC CHECKCONSTRAINTS WITH ALL_CONSTRAINTS; - не выдал ничего "красным цветом". Да и таблицы-то были наполнены только что и одноразово. Откудова там нарушениям констрейнтов взяться.
Или вы про другое говорите ?
31 авг 12, 23:53    [13098221]     Ответить | Цитировать Сообщить модератору
 Re: Не вижу join elimination в Oracle и Sql Server'e для примитивной выборки master-detail  [new]
locky
Member

Откуда: Харьков, Украина
Сообщений: 62034
Таблоид
locky
о хорошему, надо смотреть на trusted констрейнта - и пропускать соединение
DBCC CHECKCONSTRAINTS WITH ALL_CONSTRAINTS; - не выдал ничего "красным цветом". Да и таблицы-то были наполнены только что и одноразово. Откудова там нарушениям констрейнтов взяться.
Или вы про другое говорите ?


trsuted у констренйта - не немного не то что "нарушение целостности" :)

в целом я думаю - просто перестраховка
с lj в любом случае результат не поменяется, независимо от состояния FK, в то врмя как ij таки зависит от.
31 авг 12, 23:55    [13098225]     Ответить | Цитировать Сообщить модератору
 Re: Не вижу join elimination в Oracle и Sql Server'e для примитивной выборки master-detail  [new]
Andrey Sribnyak
Member

Откуда: Киев
Сообщений: 600
Таблоид
Но с "обратным" запросом - почему так ?
я про то, что
select d.* from td d INNER join tm m on m.id=d.pid;
и
select d.* from td d LEFT join tm m on m.id=d.pid; 
должны выдавать одинаковое число строк: каждой строке из 'd' может соотв-вать только одна строка из tm. И лезть в tm тут точно нет нужды, ибо не нужно от неё ничего. А он таки лезет, когда там inner join.



второй так и выдает:

select d.* from dbo.td d LEFT join dbo.tm m on m.id=d.pid;
  |--Clustered Index Scan(OBJECT:([tempdb].[dbo].[td].[PK__td__3213E83FAEC3A5AF] AS [d]))


Microsoft SQL Server 2012 - 11.0.2100.60 (X64) 
	Feb 10 2012 19:39:15 
	Copyright (c) Microsoft Corporation
	Express Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1)
1 сен 12, 16:59    [13099556]     Ответить | Цитировать Сообщить модератору
 Re: Не вижу join elimination в Oracle и Sql Server'e для примитивной выборки master-detail  [new]
Таблоид
Member

Откуда:
Сообщений: 9456
Блог
Andrey Sribnyak,

да, я это вижу. Но вопрос про td inner join tm: почему он при этому начинает лезть в 'tm'.
1 сен 12, 19:35    [13099805]     Ответить | Цитировать Сообщить модератору
 Re: Не вижу join elimination в Oracle и Sql Server'e для примитивной выборки master-detail  [new]
SergSuper
Member

Откуда: SPb
Сообщений: 5488
Таблоид
Andrey Sribnyak,

да, я это вижу. Но вопрос про td inner join tm: почему он при этому начинает лезть в 'tm'.
а вдруг там две записи?
1 сен 12, 23:31    [13100372]     Ответить | Цитировать Сообщить модератору
 Re: Не вижу join elimination в Oracle и Sql Server'e для примитивной выборки master-detail  [new]
locky
Member

Откуда: Харьков, Украина
Сообщений: 62034
SergSuper
Таблоид
Andrey Sribnyak,

да, я это вижу. Но вопрос про td inner join tm: почему он при этому начинает лезть в 'tm'.
а вдруг там две записи?

ну, вообще-то, чисто формально такое невозможно
то что в реальной жизни "случается всякое" - это да....
но формально - невозможно
1 сен 12, 23:41    [13100398]     Ответить | Цитировать Сообщить модератору
 Re: Не вижу join elimination в Oracle и Sql Server'e для примитивной выборки master-detail  [new]
MasterZiv
Member

Откуда: Питер
Сообщений: 34709

On 08/31/2012 07:25 PM, Таблоид wrote:

> select m.* from tm mleft join td don m.id=d.pid;
>
> Этот запрос всегда выдаёт полное кол-во строк из таблицы tm, т.к. тут внешнее
> соединение. Далее, в select-списке нет ни одного поля из таблицы td ==>
> соединение здесь вообще можно не проводить.

0) а нахрена-ж ты тогда вообще JOIN пишешь, если он тебе не нужен ?

1) Кол-во записей в результате может увеличится за счёт JOIN-а.
соответственно, запрос с JOIN-ом и без -- разные запросы.

Posted via ActualForum NNTP Server 1.5

3 сен 12, 11:30    [13104047]     Ответить | Цитировать Сообщить модератору
 Re: Не вижу join elimination в Oracle и Sql Server'e для примитивной выборки master-detail  [new]
Таблоид
Member

Откуда:
Сообщений: 9456
Блог
MasterZiv
0) а нахрена-ж ты тогда вообще JOIN пишешь, если он тебе не нужен ?

1) Кол-во записей в результате может увеличится за счёт JOIN-а.
соответственно, запрос с JOIN-ом и без -- разные запросы.
0) не я пишу такие запросы, но некоторые мои коллеги (бывшие), чьи авгиевы конюшни приходится разгребать.
1) я уже признал выше свой косяк для этого варианта. Повторяться не буду.
3 сен 12, 14:50    [13106106]     Ответить | Цитировать Сообщить модератору
 Re: Не вижу join elimination в Oracle и Sql Server'e для примитивной выборки master-detail  [new]
softwarer
Member

Откуда: 127.0.0.1
Сообщений: 67476
Блог
locky
ну, вообще-то, чисто формально такое невозможно то что в реальной жизни "случается всякое" - это да.... но формально - невозможно

Разное бывает, к сожалению.

Connected to Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 
Connected as test
 
SQL> create table master(id integer, data varchar2(20));
 
Table created
 
SQL> insert into master values (1, 'Первая строка');
 
1 row inserted
 
SQL> insert into master values (2, 'Вторая строка');
 
1 row inserted
 
SQL> insert into master values (1, 'Третья строка');
 
1 row inserted
 
SQL> create index master_pk on master(id);
 
Index created
 
SQL> alter table master add primary key(id) novalidate;
 
Table altered
 
SQL> insert into master values (3, 'Четвёртая строка');
 
1 row inserted
 
SQL> insert into master values (1, 'Пятая строка');
 
insert into master values (1, 'Пятая строка')
 
ORA-00001: нарушено ограничение уникальности (TEST.SYS_C0011825)
 
SQL> create table detail (id integer, master_id integer references master);
 
Table created
 
SQL> insert into detail values (1, 1);
 
1 row inserted
 
SQL> insert into detail values (2, 2);
 
1 row inserted
 
SQL> explain plan for select d.* from detail d join master m on (d.master_id = m.id);
 
Explained
 
SQL> select * from table(dbms_xplan.display());
 
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 1315321948
----------------------------------------------------------------------------
| Id  | Operation         | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |        |     2 |    52 |     3   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| DETAIL |     2 |    52 |     3   (0)| 00:00:01 |
----------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter("D"."MASTER_ID" IS NOT NULL)
Note
-----
   - dynamic sampling used for this statement (level=2)
 
17 rows selected
 
SQL> select d.* from detail d join master m on (d.master_id = m.id);
 
                                     ID                               MASTER_ID
--------------------------------------- ---------------------------------------
                                      1                                       1
                                      2                                       2
 
SQL> select * from master;
 
                                     ID DATA
--------------------------------------- --------------------------------------------------------------------------------
                                      1 Первая строка
                                      2 Вторая строка
                                      1 Третья строка
                                      3 Четвёртая строка
 
3 сен 12, 19:24    [13108273]     Ответить | Цитировать Сообщить модератору
 Re: Не вижу join elimination в Oracle и Sql Server'e для примитивной выборки master-detail  [new]
locky
Member

Откуда: Харьков, Украина
Сообщений: 62034
softwarer
locky
ну, вообще-то, чисто формально такое невозможно то что в реальной жизни "случается всякое" - это да.... но формально - невозможно

Разное бывает, к сожалению.


Вот-вот
Однако, хочу отметить - ведь в данном случае PK находится в состоянии not trusted (спасибо novalidate) , так шта....

А вот в том случае, когда PK проверен и всё такое - можно было бы джойн и не делать
3 сен 12, 20:09    [13108383]     Ответить | Цитировать Сообщить модератору
 Re: Не вижу join elimination в Oracle и Sql Server'e для примитивной выборки master-detail  [new]
locky
Member

Откуда: Харьков, Украина
Сообщений: 62034
а, стоп, у оракула что - всё наоборот?
джойн убирается хотя не должен был?
3 сен 12, 20:11    [13108387]     Ответить | Цитировать Сообщить модератору
Топик располагается на нескольких страницах: [1] 2   вперед  Ctrl      все
Все форумы / Сравнение СУБД Ответить