Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
 MERGE/HASH JOIN on persisted computed columns  [new]
Алексей_гость_131233
Guest
Сервер
SELECT @@VERSION
Microsoft SQL Server 2008 R2 (SP2) - 10.50.4000.0 (X64) Jun 28 2012 08:36:30 
Copyright (c) Microsoft Corporation Standard Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1)


Две таблички
SET ANSI_NULLS ON
SET ANSI_PADDING ON
SET ANSI_WARNINGS ON
SET ARITHABORT ON
SET CONCAT_NULL_YIELDS_NULL ON
SET QUOTED_IDENTIFIER ON

SET NUMERIC_ROUNDABORT OFF

CREATE TABLE T1 ( 
	ID	INT IDENTITY(1, 1) PRIMARY KEY, 
	dt	DATETIME, -- дата + время
	dt_p	DATETIME, -- только дата
	dt_c_p  AS ( CONVERT(DATETIME, CONVERT(VARCHAR, dt, 112), 112 ) ) PERSISTED -- только дата. Вычисляемая колонка
)

CREATE TABLE T2 ( dt DATETIME PRIMARY KEY )

INSERT INTO T2 SELECT '20130101' UNION SELECT '20130102' UNION SELECT '20130103' 

INSERT INTO T1 SELECT * FROM 
	(VALUES  ('20130101 10:00:00', '20130101'), ('20130101 11:00:00', '20130101'), ('20130101 12:00:00', '20130101'), 
		('20130102 10:00:00', '20130102'), ('20130102 11:00:00', '20130102'), ('20130102 12:00:00', '20130102'),
		('20130103 00:00:00', '20130103') ) T(A, B)


CREATE INDEX T1_dt_p ON T1 (dt_p) 
CREATE INDEX T1_dt_c_p ON T1 (dt_c_p) 


Создан индекс по вычисляемой колонке, который в общем-то используется:
SELECT T1.ID, T1.dt_c_p  FROM T1 WHERE dt_c_p = '20130101'

  |--Compute Scalar(DEFINE:([Statistics_test_asdf].[dbo].[T1].[dt_c_p]=[Statistics_test_asdf].[dbo].[T1].[dt_c_p]))
       |--Index Seek(OBJECT:([Statistics_test_asdf].[dbo].[T1].[T1_dt_c_p]), SEEK:([Statistics_test_asdf].[dbo].[T1].[dt_c_p]=CONVERT_IMPLICIT(datetime,[@1],0)) ORDERED FORWARD)


Он также используется при объединении таблиц по этому вычисляемому полю, если соединение nested loops
SELECT T1.ID, T1.dt_c_p, T2.dt  FROM T1 INNER JOIN T2 ON T1.dt_c_p = T2.dt

|--Compute Scalar(DEFINE:([Statistics_test_asdf].[dbo].[T1].[dt_c_p]=[Statistics_test_asdf].[dbo].[T1].[dt_c_p]))
       |--Nested Loops(Inner Join, OUTER REFERENCES:([Statistics_test_asdf].[dbo].[T2].[dt]))
            |--Clustered Index Scan(OBJECT:([Statistics_test_asdf].[dbo].[T2].[PK__T2__32136E5F4F67C174]))
            |--Index Seek(OBJECT:([Statistics_test_asdf].[dbo].[T1].[T1_dt_c_p]), SEEK:([Statistics_test_asdf].[dbo].[T1].[dt_c_p]=[Statistics_test_asdf].[dbo].[T2].[dt]) ORDERED FORWARD)


Однако, если выбрать другой тип соединения, то не используется не только индекс, но и само вычисляемое поле.
SELECT T1.ID, T1.dt_c_p, T2.dt  FROM T1 INNER MERGE JOIN T2 ON T1.dt_c_p = T2.dt

|--Compute Scalar(DEFINE:([Statistics_test_asdf].[dbo].[T1].[dt_c_p]=CONVERT(datetime,CONVERT(varchar(30),[Statistics_test_asdf].[dbo].[T1].[dt],112),112)))
       |--Merge Join(Inner Join, MANY-TO-MANY MERGE:([Expr1006])=([Statistics_test_asdf].[dbo].[T2].[dt]), RESIDUAL:(CONVERT(datetime,CONVERT(varchar(30),[Statistics_test_asdf].[dbo].[T1].[dt],112),112)=[Statistics_test_asdf].[dbo].[T2].[dt]))
            |--Sort(ORDER BY:([Expr1006] ASC))
            |    |--Compute Scalar(DEFINE:([Expr1006]=CONVERT(datetime,CONVERT(varchar(30),[Statistics_test_asdf].[dbo].[T1].[dt],112),112)))
            |         |--Clustered Index Scan(OBJECT:([Statistics_test_asdf].[dbo].[T1].[PK__T1__3214EC274B973090]))
            |--Clustered Index Scan(OBJECT:([Statistics_test_asdf].[dbo].[T2].[PK__T2__32136E5F4F67C174]), ORDERED FORWARD)

SELECT T1.ID, T1.dt_c_p, T2.dt  FROM T1 INNER HASH JOIN T2 ON T1.dt_c_p = T2.dt

|--Compute Scalar(DEFINE:([Statistics_test_asdf].[dbo].[T1].[dt_c_p]=CONVERT(datetime,CONVERT(varchar(30),[Statistics_test_asdf].[dbo].[T1].[dt],112),112)))
       |--Hash Match(Inner Join, HASH:([Expr1006])=([Statistics_test_asdf].[dbo].[T2].[dt]), RESIDUAL:([Expr1006]=[Statistics_test_asdf].[dbo].[T2].[dt]))
            |--Compute Scalar(DEFINE:([Expr1006]=CONVERT(datetime,CONVERT(varchar(30),[Statistics_test_asdf].[dbo].[T1].[dt],112),112)))
            |    |--Clustered Index Scan(OBJECT:([Statistics_test_asdf].[dbo].[T1].[PK__T1__3214EC274B973090]))
            |--Clustered Index Scan(OBJECT:([Statistics_test_asdf].[dbo].[T2].[PK__T2__32136E5F4F67C174]))

SELECT T1.ID, T1.dt_c_p, T2.dt  FROM T1 WITH (INDEX=T1_dt_c_p) INNER MERGE JOIN T2 ON T1.dt_c_p = T2.dt

  |--Compute Scalar(DEFINE:([Statistics_test_asdf].[dbo].[T1].[dt_c_p]=CONVERT(datetime,CONVERT(varchar(30),[Statistics_test_asdf].[dbo].[T1].[dt],112),112)))
       |--Merge Join(Inner Join, MANY-TO-MANY MERGE:([Expr1006])=([Statistics_test_asdf].[dbo].[T2].[dt]), RESIDUAL:(CONVERT(datetime,CONVERT(varchar(30),[Statistics_test_asdf].[dbo].[T1].[dt],112),112)=[Statistics_test_asdf].[dbo].[T2].[dt]))
            |--Sort(ORDER BY:([Expr1006] ASC))
            |    |--Compute Scalar(DEFINE:([Expr1006]=CONVERT(datetime,CONVERT(varchar(30),[Statistics_test_asdf].[dbo].[T1].[dt],112),112)))
            |         |--Nested Loops(Inner Join, OUTER REFERENCES:([Statistics_test_asdf].[dbo].[T1].[ID]))
            |              |--Index Scan(OBJECT:([Statistics_test_asdf].[dbo].[T1].[T1_dt_c_p]))
            |              |--Clustered Index Seek(OBJECT:([Statistics_test_asdf].[dbo].[T1].[PK__T1__3214EC274B973090]), SEEK:([Statistics_test_asdf].[dbo].[T1].[ID]=[Statistics_test_asdf].[dbo].[T1].[ID]) LOOKUP ORDERED FORWARD)
            |--Clustered Index Scan(OBJECT:([Statistics_test_asdf].[dbo].[T2].[PK__T2__32136E5F4F67C174]), ORDERED FORWARD)


Можно ли как-то заставить использовать индекс при соединении отличном от nested loops?
24 май 13, 13:34    [14344815]     Ответить | Цитировать Сообщить модератору
 Re: MERGE/HASH JOIN on persisted computed columns  [new]
SomewhereSomehow
Member

Откуда: Moscow
Сообщений: 2480
Блог
Алексей_гость_131233,

Когда оптимизатор начинает строить план запроса, он раскрывает вашу вычисляемую колонку в выражение над dt.
Позже, на стадии нормализации он биндит выражение над dt к dt_c_p.
Когда вы указали явный хинт в джойне, вы тем самым задали строкий порядок таблиц.
О чем сервер предупреждает: "Warning: The join order has been enforced because a local join hint is used."
Когда форсирован порядок таблиц, то стадия нормализации пропускается, т.е. не выполняется.
Сравните:
SELECT T1.ID, T1.dt_c_p, T2.dt  FROM T2 INNER JOIN T1 ON T1.dt_c_p = T2.dt;
SELECT T1.ID, T1.dt_c_p, T2.dt  FROM T2 INNER JOIN T1 ON T1.dt_c_p = T2.dt option(force order);


А ответ на ваш вопрос, да можно использовав подсказку не влияющую на порядок таблиц:
SELECT T1.ID, T1.dt_c_p, T2.dt  FROM T1 INNER JOIN T2 ON T1.dt_c_p = T2.dt option(merge join);
24 май 13, 14:37    [14345339]     Ответить | Цитировать Сообщить модератору
 Re: MERGE/HASH JOIN on persisted computed columns  [new]
Алексей_гость_131233
Guest
SomewhereSomehow,

Огромное спасибо за детальное объяснение :)
24 май 13, 16:27    [14346218]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить