Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
 Индекс на computed column не используется оптимизатором.  [new]
ananas2
Member

Откуда:
Сообщений: 174
Добрый день,

Помогите, пожалуйста, разобраться почему индекс на computed column не отрабатывает в следующем запросе (SQL Server 2008 R2) :

SELECT Count(DISTINCT tab1_.id)
FROM   tab1 tab1_
       RIGHT OUTER JOIN tab2 tab2_ 
                     ON tab1_.id = tab2_.tab1_ID
       RIGHT OUTER JOIN tab3 tab3_ 
                     ON tab2_.id = tab3_.tab2_ID
       INNER JOIN tab4 tab4_ 
               ON tab2_.tab4_ID = tab4_.id 
       INNER JOIN tab5 tab5_ 
               ON tab4_.id = tab5_.tab4_ID 
WHERE  upper(tab1_.tab1Number) = 'A600000'


ACCESS PLAN:
  |--Compute Scalar(DEFINE:([Expr1010]=CONVERT_IMPLICIT(int,[Expr1018],0)))
|--Stream Aggregate(DEFINE:([Expr1018]=COUNT([server1].[dbo].[tab1].[id] as [tab1_].[id])))
|--Sort(DISTINCT ORDER BY:([tab1_].[id] ASC))
|--Parallelism(Gather Streams)
|--Filter(WHERE:([Expr1011]=N'A600000'))
|--Compute Scalar(DEFINE:([Expr1011]=upper([server1].[dbo].[tab1].[tab1Number] as [tab1_].[tab1Number])))
|--Hash Match(Left Outer Join, HASH:([tab2_].[tab1_ID])=([tab1_].[id]), RESIDUAL:([server1].[dbo].[tab1].[id] as

[tab1_].[id]=[server1].[dbo].[tab1].[tab1_ID] as [tab2_].[tab1_ID]))
|--Parallelism(Repartition Streams, Hash Partitioning, PARTITION COLUMNS:([tab2_].[tab1_ID]))
| |--Hash Match(Inner Join, HASH:([tab4_].[id])=([tab5_].[tab4_ID]), RESIDUAL:([server1].[dbo].[tab4].[id] as

[tab4_].[id]=[server1].[dbo].[tab5].[tab4_ID] as [tab5_].[tab4_ID]))
| |--Bitmap(HASH:([tab4_].[id]), DEFINE:([Bitmap1017]))
| | |--Hash Match(Inner Join, HASH:([tab4_].[id])=([tab2_].[tab4_ID]), RESIDUAL:([server1].[dbo].[tab1].[tab4_ID] as

[tab2_].[tab4_ID]=[server1].[dbo].[tab4].[id] as [tab4_].[id]))
| | |--Bitmap(HASH:([tab4_].[id]), DEFINE:([Opt_Bitmap1013]))
| | | |--Parallelism(Repartition Streams, Hash Partitioning, PARTITION COLUMNS:([tab4_].[id]))
| | | |--Clustered Index Scan(OBJECT:([server1].[dbo].[tab4].[PK_tab4_ID] AS [tab4_]))
| | |--Parallelism(Repartition Streams, Hash Partitioning, PARTITION COLUMNS:([tab2_].[tab4_ID]))
| | |--Hash Match(Inner Join, HASH:([tab3_].[tab1_ID])=([tab2_].[id]), RESIDUAL:([server1].[dbo].[tab1].[id] as

[tab2_].[id]=[server1].[dbo].[tab3].[tab1_ID] as [tab3_].[tab1_ID]))
| | |--Parallelism(Repartition Streams, Hash Partitioning, PARTITION COLUMNS:([tab3_].[tab1_ID]))
| | | |--Index Scan(OBJECT:([server1].[dbo].[tab3].[IDX_CI_tab1_ID] AS [tab3_]))
| | |--Hash Match(Inner Join, HASH:([tab2_].[id])=([tab2_].[id]), RESIDUAL:([server1].[dbo].[tab1].[id] as [tab2_].[id]

= [server1].[dbo].[tab1].[id] as [tab2_].[id]))
| | |--Parallelism(Repartition Streams, Hash Partitioning, PARTITION COLUMNS:([tab2_].[id]))
| | | |--Index Scan(OBJECT:([server1].[dbo].[tab1].[idx_tab4_ID] AS [tab2_]),

WHERE:(PROBE([Opt_Bitmap1013],[server1].[dbo].[tab1].[tab4_ID] as [tab2_].[tab4_ID])))
| | |--Parallelism(Repartition Streams, Hash Partitioning, PARTITION COLUMNS:([tab2_].[id]))
| | |--Index Scan(OBJECT:([server1].[dbo].[tab1].[IDX_tab1_CACCOUNT_ID] AS [tab2_]))
| |--Parallelism(Repartition Streams, Hash Partitioning, PARTITION COLUMNS:([tab5_].[tab4_ID]))
| |--Index Scan(OBJECT:([server1].[dbo].[tab5].[idx_tab5_Info] AS [tab5_]),

WHERE:(PROBE([Bitmap1017],[server1].[dbo].[tab5].[tab4_ID] as [tab5_].[tab4_ID])))
|--Parallelism(Repartition Streams, Hash Partitioning, PARTITION COLUMNS:([tab1_].[id]))
|--Index Scan(OBJECT:([server1].[dbo].[tab1].[UQ__tab1__17D0878A06CD04F7] AS [tab1_]))

SQL Server parse and compile time: 
CPU time = 63 ms, elapsed time = 66 ms.

(1 row(s) affected)
Table 'tab4'. Scan count 5, logical reads 11469, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'tab3'. Scan count 5, logical reads 5219, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'tab1'. Scan count 10, logical reads 8132, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'tab5'. Scan count 5, logical reads 7931, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'tab1'. Scan count 5, logical reads 5855, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

(1 row(s) affected)

SQL Server Execution Times:
CPU time = 10186 ms, elapsed time = 2637 ms.

DDL:

CREATE TABLE [dbo].[tab1](
	[id] [numeric](19, 0) IDENTITY(1,1) NOT NULL,
...
	[tab1Number] [nvarchar](20) NULL,
	[tab1NumberUpper]  AS (upper([tab1Number])),
 CONSTRAINT [PK__tab1__3213E83F03F0984C] PRIMARY KEY CLUSTERED 
(
	[id] ASC
)
 CONSTRAINT [UQ__tab1__17D0878A06CD04F7] UNIQUE NONCLUSTERED 
(
	[tab1Number] ASC
)

CREATE NONCLUSTERED INDEX [tab1NumberUpperIdx] ON [dbo].[tab1] 
(
[tab1NumberUpper] ASC
)



Если убрать функцию Upper из запроса тогда все отрабатывает шикарно:




|--Compute Scalar(DEFINE:([Expr1010]=CONVERT_IMPLICIT(int,[Expr1011],0)))
|--Stream Aggregate(DEFINE:([Expr1011]=Count(*)))
|--Stream Aggregate(DEFINE:([tab1_].[id]=ANY([server1].[dbo].[tab1].[id] as [tab1_].[id])))
|--Nested Loops(Inner Join, OUTER REFERENCES:([tab4_].[id]) OPTIMIZED)
|--Nested Loops(Inner Join, OUTER REFERENCES:([tab2_].[tab4_ID]))
| |--Nested Loops(Inner Join, OUTER REFERENCES:([tab2_].[id]) OPTIMIZED)
| | |--Nested Loops(Inner Join, OUTER REFERENCES:([tab2_].[id]) OPTIMIZED)
| | | |--Nested Loops(Inner Join, OUTER REFERENCES:([tab1_].[id]))
| | | | |--Index Seek(OBJECT:([server1].[dbo].[tab1].[UQ__tab1__17D0878A06CD04F7] AS [tab1_]),

SEEK:([tab1_].[tab1Number]=N'A600000') ORDERED FORWARD)
| | | | |--Index Seek(OBJECT:([server1].[dbo].[tab1].[IDX_tab1_CACCOUNT_ID] AS [tab2_]),

SEEK:([tab2_].[tab1_ID]=[server1].[dbo].[tab1].[id] as [tab1_].[id]) ORDERED FORWARD)
| | | |--Clustered Index Seek(OBJECT:([server1].[dbo].[tab1].[PK__tab1__3213E83F764C846B] AS [tab2_]),

SEEK:([tab2_].[id]=[server1].[dbo].[tab1].[id] as [tab2_].[id]) LOOKUP ORDERED FORWARD)
| | |--Index Seek(OBJECT:([server1].[dbo].[tab3].[IDX_CI_tab1_ID] AS [tab3_]), SEEK:([tab3_].[tab1_ID]=[server1].[dbo].[tab1].[id] as

[tab2_].[id]) ORDERED FORWARD)
| |--Clustered Index Seek(OBJECT:([server1].[dbo].[tab4].[PK_tab4_ID] AS [tab4_]), SEEK:([tab4_].[id]=[server1].[dbo].[tab1].[tab4_ID] as

[tab2_].[tab4_ID]) ORDERED FORWARD)
|--Index Seek(OBJECT:([server1].[dbo].[tab5].[idx_tab5_Info] AS [tab5_]), SEEK:([tab5_].[tab4_ID]=[server1].[dbo].[tab4].[id] as [tab4_].[id])

ORDERED FORWARD)


SQL Server parse and compile time:
CPU time = 8 ms, elapsed time = 8 ms.

(1 row(s) affected)
Table 'tab5'. Scan count 1, logical reads 4, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'tab4'. Scan count 0, logical reads 3, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'tab3'. Scan count 1, logical reads 3, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'tab1'. Scan count 1, logical reads 7, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'tab1'. Scan count 0, logical reads 3, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

(1 row(s) affected)

SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 2 ms.


Пробовал делать сделать аппер колонку persisted - не помогло, та же история.
Статистику по индексам собирал так : update statistics table_name;
В таблицах примерно от 300К до 1.5М записей.
Да, простые запросы, типа как следующий, индекс используют на ура, проблемы начинаются когда в запросе один или несколько аутер джойнов.

SELECT  tab1_.id 
FROM   tab1 tab1_
WHERE  upper(tab1_.tab1Number) = 'A600000'



С SQL server'ом работаю только пару дней, так что какие-то простешие вещи мог упустить.

P.S.
Да, я знаю что так использовать функцию не стоит в запросах и то, что OUTER джойны в этом запросе излишни. Хочу именно разобраться почему оптимизатор не хочет использовать индекс. Заранее спасибо за помощь!
11 фев 14, 23:16    [15554185]     Ответить | Цитировать Сообщить модератору
 Re: Индекс на computed column не используется оптимизатором.  [new]
invm
Member

Откуда: Москва
Сообщений: 9838
Потому что надо
WHERE  tab1_.tab1NumberUpper = 'A600000'
11 фев 14, 23:40    [15554239]     Ответить | Цитировать Сообщить модератору
 Re: Индекс на computed column не используется оптимизатором.  [new]
tpg
Member

Откуда: Novosibirsk
Сообщений: 23902
Ну или другими словами - потому что статистика строится по столбцам, индексам и их сочетаниям, но (скорее всего пока) никак ни по функциям от значений столбца и при запросах с функциями в предложении WHERE, как у вас, сервер втупую сканирует таблицу.

Сообщение было отредактировано: 12 фев 14, 06:53
12 фев 14, 06:53    [15554690]     Ответить | Цитировать Сообщить модератору
 Re: Индекс на computed column не используется оптимизатором.  [new]
ananas2
Member

Откуда:
Сообщений: 174
tpg
Ну или другими словами - потому что статистика строится по столбцам, индексам и их сочетаниям, но (скорее всего пока) никак ни по функциям от значений столбца и при запросах с функциями в предложении WHERE, как у вас, сервер втупую сканирует таблицу.


А почему он тогда все-таки использует индекс tab1NumberUpperIdx если запрос вида:

SELECT  tab1_.id 
FROM   tab1 tab1_
WHERE  upper(tab1_.tab1Number) = 'A600000'
12 фев 14, 08:02    [15554751]     Ответить | Цитировать Сообщить модератору
 Re: Индекс на computed column не используется оптимизатором.  [new]
tpg
Member

Откуда: Novosibirsk
Сообщений: 23902
Ну, план то посмотрите.
Очевидно, что для выборки ему достаточно просканировать только этот индекс, не обращаясь к кластерному или какому другому.
12 фев 14, 08:19    [15554768]     Ответить | Цитировать Сообщить модератору
 Re: Индекс на computed column не используется оптимизатором.  [new]
ananas2
Member

Откуда:
Сообщений: 174
tpg
Ну, план то посмотрите.
Очевидно, что для выборки ему достаточно просканировать только этот индекс, не обращаясь к кластерному или какому другому.


Допустим в таком запросе(меняем один из аутеров на иннер) он тоже использует аппер индекс:

SELECT Count(DISTINCT tab1_.id)
FROM   tab1 tab1_
       INNER JOIN tab2 tab2_ 
                     ON tab1_.id = tab2_.tab1_ID
       RIGHT OUTER JOIN tab3 tab3_ 
                     ON tab2_.id = tab3_.tab2_ID
       INNER JOIN tab4 tab4_ 
               ON tab2_.tab4_ID = tab4_.id 
       INNER JOIN tab5 tab5_ 
               ON tab4_.id = tab5_.tab4_ID 
WHERE  upper(tab1_.tab1Number) = 'A600000'



Извиняюсь за занудство, просто гуглением накопал кучу примеров оптимизации запросов с помощью computed columns + index и везде в примерах они именно функцию от реальной колонки в условии where используют, а не виртуальную колонку. Поэтому думал, что работает также как в оракле с function-based индексами.

Судя по всему, при больших объемах данных и большом кол-ве операций в запросе оптимизатор просто не справляется с переписыванием
upper(tab1_.tab1Number) = 'A600000'
на
tab1_.tab1NumberUpper = 'A600000'
почему-то.

Всем спасибо за помощь.
12 фев 14, 08:48    [15554790]     Ответить | Цитировать Сообщить модератору
 Re: Индекс на computed column не используется оптимизатором.  [new]
tpg
Member

Откуда: Novosibirsk
Сообщений: 23902
Он не переписывает запрос, он просто выбирает скан индекса - в этом индексе есть все данные, которые нужны для запроса и он гараздо меньше, чем сама таблица (кластерный индекс), следовательно, даже при сканированиии ему придется прочитать меньше страниц, чем при сканировании кластерного индекса.
План посмотрите.
12 фев 14, 09:00    [15554814]     Ответить | Цитировать Сообщить модератору
 Re: Индекс на computed column не используется оптимизатором.  [new]
SomewhereSomehow
Member

Откуда: Moscow
Сообщений: 2480
Блог
ananas2,

Оптимизатор может использовать статистику по вычисляемым полям, даже если в запросе указано выражение а не название вычисляемого поля. В том числе может использовать индекс, что он и делает в некоторых запросах, которые вы сами же и приводили.

Механизм выбора Seek vs Scan ничем не отличается от обычного, т.е. основывается на оценках предполагаемого числа строк, которые отличаются для разных логических операций (например, left join vs inner join), для разных запросов и т.д. Основываясь на оценке числа строк (то что принято называть селективностью предиката) оптимизатор принимает решение использовать поиск или просмотр.

В вашем примере, допустим, индекс по колонке tab1Number - [UQ__tab1__17D0878A06CD04F7] UNIQUE NONCLUSTERED - уникальный, а по колонке tab1NumberUpper просто CREATE NONCLUSTERED INDEX [tab1NumberUpperIdx]. Как минимум по этому могут отличаться оценки предикатов tab1_.tab1Number = 'A600000' и upper(tab1_.tab1Number) = 'A600000' соответственно. Так что, посмотрите на оценки в ваших планах запросов (кстати планы в тексте не очень удобно читать, имхо, удобнее в .sqlplan)

Сопоставление выражения с вычисляемой колонкой не зависит от объемов данных, оно происходит еще до того как начинается непосредственно оптимизация и уж тем более выполнение. Единственное должно быть полное посимвольное соответствие (вплоть до пробелов и регистра, если я не ошибаюсь) выражения и определения вычисляемой колонки.
12 фев 14, 10:07    [15555041]     Ответить | Цитировать Сообщить модератору
 Re: Индекс на computed column не используется оптимизатором.  [new]
ananas2
Member

Откуда:
Сообщений: 174
SomewhereSomehow
ananas2,

Оптимизатор может использовать статистику по вычисляемым полям, даже если в запросе указано выражение а не название вычисляемого поля. В том числе может использовать индекс, что он и делает в некоторых запросах, которые вы сами же и приводили.

В вашем примере, допустим, индекс по колонке tab1Number - [UQ__tab1__17D0878A06CD04F7] UNIQUE NONCLUSTERED - уникальный, а по колонке tab1NumberUpper просто CREATE NONCLUSTERED INDEX [tab1NumberUpperIdx]. Как минимум по этому могут отличаться оценки предикатов tab1_.tab1Number = 'A600000' и upper(tab1_.tab1Number) = 'A600000' соответственно.


Спасибо, за развернутый ответ. Но судя по всему он все-таки не умеет адекватно использовать статистику по вычисляемым полям, если в запросе указано выражение.
Потому что в одном и том же запросе(1-й в верхнем посте)
с
WHERE  upper(tab1_.tab1Number) = 'A600000'

он выбирает index_scan по ПК индексу,
а с
WHERE  tab1_.tab1NumberUpper = 'A600000'

он все-таки использует seek scan по tab1NumberUpperIdx
12 фев 14, 11:59    [15555869]     Ответить | Цитировать Сообщить модератору
 Re: Индекс на computed column не используется оптимизатором.  [new]
Glory
Member

Откуда:
Сообщений: 104751
ananas2
Но судя по всему он все-таки не умеет адекватно использовать статистику по вычисляемым полям, если в запросе указано выражение.

А где в запросе с WHERE upper(tab1_.tab1Number) = 'A600000' вычисляемое поле то ?
Вычисляемое _поле_ - это tab1NumberUpper, а upper(tab1_.tab1Number) - это вычисляемое _выражение_ по полю tab1Number
12 фев 14, 13:50    [15556795]     Ответить | Цитировать Сообщить модератору
 Re: Индекс на computed column не используется оптимизатором.  [new]
SomewhereSomehow
Member

Откуда: Moscow
Сообщений: 2480
Блог
ananas2,

Вроде бы получилось воспроизвести, ситуация вырисовывается довольно забавная.
Версия: Microsoft SQL Server 2008 R2 (SP2) - 10.50.4000.0

Возможно, то же самое происходит и у вас, по симптомам, похоже.

Данные
use tempdb;
go
create table t1(a int primary key, b int not null, c int);
insert into t1(a,b,c) select number, number%100+1, number%50+1 from master..spt_values where type = 'p' and number between 1 and 1000;
CREATE TABLE [dbo].[tab1](
	[id] [numeric](19, 0) IDENTITY(1,1) NOT NULL,
	[tab1Number] [nvarchar](20) NOT NULL,
	[tab1NumberUpper]  AS (upper([tab1Number])),
	CONSTRAINT [PK__tab1__3213E83F03F0984C] PRIMARY KEY CLUSTERED ([id] ASC),
	CONSTRAINT [UQ__tab1__17D0878A06CD04F7] UNIQUE NONCLUSTERED ([tab1Number] ASC)
)
CREATE NONCLUSTERED INDEX [tab1NumberUpperIdx] ON [dbo].[tab1] ([tab1NumberUpper] ASC);
go
drop table [dbo].[tab1], t1;


Проблема, есть два запроса, запрос 1 использует выражение, запрос 2 вычисляемую колонку:

-- 1. Expression
SELECT 
	tab1_.id,
	t1.a
FROM   
	tab1 tab1_
	right join t1 on t1.a = tab1_.id
WHERE (upper([tab1Number])) = 'A600000'

-- 2. Computed column
SELECT 
	tab1_.id,
	t1.a
FROM   
	tab1 tab1_
	right join t1 on t1.a = tab1_.id
WHERE [tab1NumberUpper] = 'A600000'


Запросы имеют соответствующие планы:
Картинка с другого сайта.

В первом случае индекс по вычисляемой колонке не используется, во втором используется при этом выполняется Index Seek.
Вопрос, почему.

Объяснение

Как я уже говорил, есть две возможные причины, это вопрос селективности, как и для любого другого индекса и вопрос сопоставления выражению вычисляемой колонке, вопрос соответствия (кстати, эксперимент показывает, что полного совпадения литералов не требуется).

Вопрос с селективностью отметаем сразу, в репро участвует таблица, в которой вообще нет данных, кардинальность 0 и оценочное число строк всегда 1.

А вот на вопросе сопоставления выражения и определения колонки нужно остановиться подробнее. Это делается до того как начинается основной процесс оптимизации на фазе упрощения дерева операторов. Можно назвать ее пред-оптимизацией и она состоит примерно из следующих шагов:

  • 1. Парсинг текста запроса, построение на его основе дерева логиечских операторов привязка к объектам бд.
  • 2. Упрощение дерева (исключение избыточных условий, противоречий, упрощение соединений, проталкивание предикатов вниз к операторам доступа и т.д.).
  • 3. Исключение лишних соединений.
  • 4. Нормализация проекций (в том числе сопоставление выражений вычисляемым колонкам).
  • 5. Оптимизация.

    В этом процессе фаза упрощения соединений и проталкивания предикатов вниз к операторам доступа происходит раньше, чем сопоставление вычисляемой колонки. Это приводит к интересному эффекту.

    Оптимизатор имеет алгоритмы для преобразования left, right outer соединения в inner путем упрощения этого соединения, это реализовано в виде двух правил преобразования дерева SimplifyLOJN и SimplifyROJN. Это стандартное действие, применяемое сервером, когда пишут выражения в виде …left join t2 on … where t2.a = 10. Фактически условием where внешнее соединение превращается во внутреннее, т.к. мы отметаем NULL.

    Если выполнить второй запрос, отключив эти два правила, мы увидим следующий план:

    -- 2. Computed column + Disable Rules
    SELECT 
    	tab1_.id,
    	t1.a
    FROM   
    	tab1 tab1_
    	right join t1 on t1.a = tab1_.id
    WHERE [tab1NumberUpper] = 'A600000'
    option(queryruleoff SimplifyLOJN, queryruleoff SimplifyROJN)
    

    Картинка с другого сайта.

    Видно, что теперь индекс снова не используется.

    Почему правила не применяются в случае выражения? Дело в том, что правила применяются не все подряд ко всем узлам дерева, а только те, что соответствуют определенному «паттерну» в дереве операторов. Видимо, правила упрощения внешних соединений требуют определенный паттерн, в том числе и по операторам, которые соответствуют соединяемым колонкам. Но, поскольку выражение заменяется на идентификатор вычисляемой колонки позже, в процессе упрощения, то правило просто не может быть применено. Соединение остается не упрощенным - внешним.

    Это в свою очередь не дает применить поиск по индексу, поскольку в таком случае предикат (условие поиска) должен находится на уровне доступа к данным, но протолкнуть вниз до соединения сервер его не может, т.к. будет искажен смысл запроса.
    Сравним оригинал и семантически другой запрос, в котором сервер может выполнить проверку условия до соединения (запросы разные по смыслу, но иллюстрируют сказанное):

    -- Moved expression
    SELECT 
    	tab1_.id,
    	t1.a
    FROM   
    	tab1 tab1_
    	right join t1 on t1.a = tab1_.id
    WHERE (upper([tab1Number])) = 'A600000'
    
    SELECT 
    	tab1_.id,
    	t1.a
    FROM   
    	tab1 tab1_
    	right join t1 on t1.a = tab1_.id and (upper([tab1Number])) = 'A600000'
    

    Картинка с другого сайта.

    План второго запроса содержит поиск! Хотя мы не использовали вычисляемую колонку напрямую. Это возможно благодаря тому, что сервер может поместить предикат поиска на самый нижний уровень, уровень доступа к данным. Если вы меняете в запросе тип соединение на внутреннее руками, то сервер также спокойно может использовать поиск.

    Итого

    Сопоставление вычисляемых колонок происходит после упрощения соединений, которое в свою очередь не учитывает вычисляемые колонки, что приводит к невозможности упрощения внешнего соединения, что в свою очередь блокирует возможность протолкнуть предикат вниз на уровень доступа к данным за счет того, что в таком случае семантика запроса будет искажена.

    Резюмируя, можно сказать что много нам открытий чудных =) Можно попробовать запостить на коннект, если там еще нет такого (я не нашел по сочетанию computed column, хотя там 87 пунктов, мож проглядел просто), но думаю велика вероятность ответа By Design.
  • 12 фев 14, 14:28    [15557068]     Ответить | Цитировать Сообщить модератору
     Re: Индекс на computed column не используется оптимизатором.  [new]
    o-o
    Guest
    alter table dbo.tab1
    add tab1Number_CI as tab1Number collate SQL_Latin1_General_CP1_CI_AS;
    
    create nonclustered index tab1Number_CI_Idx ON dbo.tab1 (tab1Number_CI asc);
    


    можно вообще вычисляемый столбец создать с регистронезависимым коллэйшеном + индекс по нему
    и забыть про upper-ы.
    12 фев 14, 14:45    [15557222]     Ответить | Цитировать Сообщить модератору
     Re: Индекс на computed column не используется оптимизатором.  [new]
    ananas2
    Member

    Откуда:
    Сообщений: 174
    SomewhereSomehow,

    Да, это многое объясняет. Спасибо, благодаря Вам, сегодня узнал много нового про работу оптимизатора SQL Server :)
    12 фев 14, 15:10    [15557455]     Ответить | Цитировать Сообщить модератору
     Re: Индекс на computed column не используется оптимизатором.  [new]
    SomewhereSomehow
    Member

    Откуда: Moscow
    Сообщений: 2480
    Блог
    ananas2,

    Не за что, я сам многое узнаю, благодаря интересным и правильно заданным вопросам =)
    12 фев 14, 19:48    [15559027]     Ответить | Цитировать Сообщить модератору
    Все форумы / Microsoft SQL Server Ответить