Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
 "union all vs full join" или "concatination vs hash match"  [new]
Vitaly86
Member

Откуда:
Сообщений: 600
допустим есть таблица

CREATE TABLE [dbo].[T](
	[f1] [int] NOT NULL,
	[f2] [int] NOT NULL,
	[k] [int] NOT NULL,
 CONSTRAINT [PK_T] PRIMARY KEY CLUSTERED 
(
	[f1] ASC, 
	[f2] ASC
) ON [PRIMARY]
) ON [PRIMARY]


для примера записи такие, но в реальности порядка 1 милиарда
insert into [dbo].[T]
          select 11, 21, 1121
union all select 11, 22, 1122
union all select 12, 21, 1221
union all select 12, 22, 1222


чисто теоритический вопрос:

всегда ли такой запрос будет оптимальнее
select f1, sum(k1), sum(k2)
from (
select  f1, 0 k1, k k2 from T
union all
select  f1, k k1, 0 k2 from T
) t
group by f1


  |--Stream Aggregate(GROUP BY:([Union1008]) DEFINE:([Expr1011]=SUM([partialagg1013]), [Expr1012]=SUM([partialagg1014])))
       |--Sort(ORDER BY:([Union1008] ASC))
            |--Concatenation
                 |--Stream Aggregate(GROUP BY:([dbo].[T].[f1]) DEFINE:([partialagg1013]=SUM([Expr1003]), [partialagg1014]=SUM([dbo].[T].[k])))
                 |    |--Compute Scalar(DEFINE:([Expr1003]=(0)))
                 |         |--Clustered Index Scan(OBJECT:([dbo].[T].[PK_T]), ORDERED FORWARD)
                 |--Stream Aggregate(GROUP BY:([dbo].[T].[f1]) DEFINE:([partialagg1013]=SUM([dbo].[T].[k]), [partialagg1014]=SUM([Expr1007])))
                      |--Compute Scalar(DEFINE:([Expr1007]=(0)))
                           |--Clustered Index Scan(OBJECT:([dbo].[T].[PK_T]), ORDERED FORWARD)

чем такой

select isnull(t1.f1, t2.f1), sum( isnull(t1.k, 0) ), sum( isnull(t2.k, 0) )
from T t1
     full join 
     T t2
on t1.f1 = t2.f1 and t1.f2 = t2.f2
group by isnull(t1.f1, t2.f1)


  |--Stream Aggregate(GROUP BY:([Expr1004]) DEFINE:([Expr1005]=SUM([Expr1007]), [Expr1006]=SUM([Expr1008])))
       |--Sort(ORDER BY:([Expr1004] ASC))
            |--Compute Scalar(DEFINE:([Expr1004]=isnull([dbo].[T].[f1] as [t1].[f1],[dbo].[T].[f1] as [t2].[f1]), [Expr1007]=isnull([dbo].[T].[k] as [t1].[k],(0)), [Expr1008]=isnull([dbo].[T].[k] as [t2].[k],(0))))
                 |--Merge Join(Full Outer Join, MERGE:([t1].[f1], [t1].[f2])=([t2].[f1], [t2].[f2]), RESIDUAL:([dbo].[T].[f1] as [t1].[f1]=[dbo].[T].[f1] as [t2].[f1] AND [dbo].[T].[f2] as [t1].[f2]=[dbo].[T].[f2] as [t2].[f2]))
                      |--Clustered Index Scan(OBJECT:([dbo].[T].[PK_T] AS [t1]), ORDERED FORWARD)
                      |--Clustered Index Scan(OBJECT:([dbo].[T].[PK_T] AS [t2]), ORDERED FORWARD)


Сообщение было отредактировано: 12 мар 12, 12:07
12 мар 12, 09:50    [12229434]     Ответить | Цитировать Сообщить модератору
 Re: "union all vs full join" или "concatination vs hash match"  [new]
Vitaly86
Member

Откуда:
Сообщений: 600
в примере Merge Join, потому что записей мало. "в реальности" hash match
12 мар 12, 09:52    [12229443]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить