Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
 Чем провинилось условие is null в фильтрованном индексе?  [new]
Гавриленко Сергей Алексеевич
Member

Откуда: Moscow
Сообщений: 37138
Репро:
set nocount on
go
use tempdb
go
if object_id('dbo.TestIsNull') is not null
    drop table dbo.TestIsNull
go
create table dbo.TestIsNull (
    id      int         not null    identity     primary key
    , name  varchar(50) not null
    , rn    int             null
)     
go
insert dbo.TestIsNull (name, rn)
select 'is null', null
union all select 'is not null', 1
union all select 'exact check', 0

create index IX__TestIsNull__is_null            on dbo.TestIsNull( name ) where ( rn is     null )
create index IX__TestIsNull__is_not_null        on dbo.TestIsNull( name ) where ( rn is not null )
create index IX__TestIsNull__exact_check        on dbo.TestIsNull( name ) where ( rn = 0         )
go
declare @a varchar (50)

set statistics profile on

print 'Откуда лукап в кластерный индекс?****************************************************'
select @a = a.name from dbo.TestIsNull a with ( forceseek ) where a.name = '' and a.rn is null

print 'Здесь лукапа нет: ****************************************************'
select @a = a.name from dbo.TestIsNull a with ( forceseek ) where a.name = '' and a.rn is not null

print 'И здесь лукапа нет: ****************************************************'
select @a = a.name from dbo.TestIsNull a with ( forceseek ) where a.name = '' and a.rn = 0

set statistics profile off

create index IX__TestIsNull__is_null_include    on dbo.TestIsNull( name ) include ( rn ) where ( rn is null ) 

set statistics profile on

print 'И если воткнуть фильтрованное поле в инклуд, лукап тоже пропадает: ****************************************************'
select @a = a.name from dbo.TestIsNull a with ( forceseek ) where a.name = '' and a.rn is null

set statistics profile off


Результат:

Откуда лукап в кластерный индекс?****************************************************
Rows                 Executes             StmtText                                                                                                                                                                                                                                                          StmtId      NodeId      Parent      PhysicalOp                     LogicalOp                      Argument                                                                                                                                                                                                                          DefinedValues         EstimateRows  EstimateIO    EstimateCPU   AvgRowSize  TotalSubtreeCost OutputList            Warnings Type                                                             Parallel EstimateExecutions
-------------------- -------------------- ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ----------- ----------- ----------- ------------------------------ ------------------------------ --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- --------------------- ------------- ------------- ------------- ----------- ---------------- --------------------- -------- ---------------------------------------------------------------- -------- ------------------
0                    1                    select @a = a.name from dbo.TestIsNull a with ( forceseek ) where a.name = '' and a.rn is null                                                                                                                                                                    1           1           0           NULL                           NULL                           NULL                                                                                                                                                                                                                              NULL                  1             NULL          NULL          NULL        0,00657038       NULL                  NULL     SELECT                                                           0        NULL
0                    1                      |--Nested Loops(Inner Join, OUTER REFERENCES:([a].[id]))                                                                                                                                                                                                        1           2           1           Nested Loops                   Inner Join                     OUTER REFERENCES:([a].[id])                                                                                                                                                                                                       NULL                  1             0             4,18E-06      24          0,00657038       [a].[name]            NULL     PLAN_ROW                                                         0        1
0                    1                           |--Index Seek(OBJECT:([tempdb].[dbo].[TestIsNull].[IX__TestIsNull__is_null] AS [a]), SEEK:([a].[name]='') ORDERED FORWARD)                                                                                                                                 1           3           2           Index Seek                     Index Seek                     OBJECT:([tempdb].[dbo].[TestIsNull].[IX__TestIsNull__is_null] AS [a]), SEEK:([a].[name]='') ORDERED FORWARD                                                                                                                       [a].[id], [a].[name]  1             0,003125      0,0001581     24          0,0032831        [a].[id], [a].[name]  NULL     PLAN_ROW                                                         0        1
0                    0                           |--Clustered Index Seek(OBJECT:([tempdb].[dbo].[TestIsNull].[PK__TestIsNu__3213E83F7C4AD388] AS [a]), SEEK:([a].[id]=[tempdb].[dbo].[TestIsNull].[id] as [a].[id]),  WHERE:([tempdb].[dbo].[TestIsNull].[rn] as [a].[rn] IS NULL) LOOKUP ORDERED FORWARD)  1           5           2           Clustered Index Seek           Clustered Index Seek           OBJECT:([tempdb].[dbo].[TestIsNull].[PK__TestIsNu__3213E83F7C4AD388] AS [a]), SEEK:([a].[id]=[tempdb].[dbo].[TestIsNull].[id] as [a].[id]),  WHERE:([tempdb].[dbo].[TestIsNull].[rn] as [a].[rn] IS NULL) LOOKUP ORDERED FORWARD  NULL                  1             0,003125      0,0001581     11          0,0032831        NULL                  NULL     PLAN_ROW                                                         0        1

Здесь лукапа нет: ****************************************************
Rows                 Executes             StmtText                                                                                                                          StmtId      NodeId      Parent      PhysicalOp                     LogicalOp                      Argument                                                                                                         DefinedValues EstimateRows  EstimateIO    EstimateCPU   AvgRowSize  TotalSubtreeCost OutputList  Warnings Type                                                             Parallel EstimateExecutions
-------------------- -------------------- --------------------------------------------------------------------------------------------------------------------------------- ----------- ----------- ----------- ------------------------------ ------------------------------ ---------------------------------------------------------------------------------------------------------------- ------------- ------------- ------------- ------------- ----------- ---------------- ----------- -------- ---------------------------------------------------------------- -------- ------------------
0                    1                    select @a = a.name from dbo.TestIsNull a with ( forceseek ) where a.name = '' and a.rn is not null                                2           1           0           NULL                           NULL                           NULL                                                                                                             NULL          1             NULL          NULL          NULL        0,0032831        NULL        NULL     SELECT                                                           0        NULL
0                    1                      |--Index Seek(OBJECT:([tempdb].[dbo].[TestIsNull].[IX__TestIsNull__is_not_null] AS [a]), SEEK:([a].[name]='') ORDERED FORWARD)  2           2           1           Index Seek                     Index Seek                     OBJECT:([tempdb].[dbo].[TestIsNull].[IX__TestIsNull__is_not_null] AS [a]), SEEK:([a].[name]='') ORDERED FORWARD  [a].[name]    1             0,003125      0,0001581     20          0,0032831        [a].[name]  NULL     PLAN_ROW                                                         0        1

И здесь лукапа нет: ****************************************************
Rows                 Executes             StmtText                                                                                                                          StmtId      NodeId      Parent      PhysicalOp                     LogicalOp                      Argument                                                                                                         DefinedValues EstimateRows  EstimateIO    EstimateCPU   AvgRowSize  TotalSubtreeCost OutputList  Warnings Type                                                             Parallel EstimateExecutions
-------------------- -------------------- --------------------------------------------------------------------------------------------------------------------------------- ----------- ----------- ----------- ------------------------------ ------------------------------ ---------------------------------------------------------------------------------------------------------------- ------------- ------------- ------------- ------------- ----------- ---------------- ----------- -------- ---------------------------------------------------------------- -------- ------------------
0                    1                    select @a = a.name from dbo.TestIsNull a with ( forceseek ) where a.name = '' and a.rn = 0                                        3           1           0           NULL                           NULL                           NULL                                                                                                             NULL          1             NULL          NULL          NULL        0,0032831        NULL        NULL     SELECT                                                           0        NULL
0                    1                      |--Index Seek(OBJECT:([tempdb].[dbo].[TestIsNull].[IX__TestIsNull__exact_check] AS [a]), SEEK:([a].[name]='') ORDERED FORWARD)  3           2           1           Index Seek                     Index Seek                     OBJECT:([tempdb].[dbo].[TestIsNull].[IX__TestIsNull__exact_check] AS [a]), SEEK:([a].[name]='') ORDERED FORWARD  [a].[name]    1             0,003125      0,0001581     20          0,0032831        [a].[name]  NULL     PLAN_ROW                                                         0        1

И если воткнуть фильтрованное поле в инклуд, лукап тоже пропадает: ****************************************************
Rows                 Executes             StmtText                                                                                                                                                                                             StmtId      NodeId      Parent      PhysicalOp                     LogicalOp                      Argument                                                                                                                                                                            DefinedValues EstimateRows  EstimateIO    EstimateCPU   AvgRowSize  TotalSubtreeCost OutputList  Warnings Type                                                             Parallel EstimateExecutions
-------------------- -------------------- ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ----------- ----------- ----------- ------------------------------ ------------------------------ ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ------------- ------------- ------------- ------------- ----------- ---------------- ----------- -------- ---------------------------------------------------------------- -------- ------------------
0                    1                    select @a = a.name from dbo.TestIsNull a with ( forceseek ) where a.name = '' and a.rn is null                                                                                                       4           1           0           NULL                           NULL                           NULL                                                                                                                                                                                NULL          1             NULL          NULL          NULL        0,0032831        NULL        NULL     SELECT                                                           0        NULL
0                    1                      |--Index Seek(OBJECT:([tempdb].[dbo].[TestIsNull].[IX__TestIsNull__is_null_include] AS [a]), SEEK:([a].[name]=''),  WHERE:([tempdb].[dbo].[TestIsNull].[rn] as [a].[rn] IS NULL) ORDERED FORWARD)  4           2           1           Index Seek                     Index Seek                     OBJECT:([tempdb].[dbo].[TestIsNull].[IX__TestIsNull__is_null_include] AS [a]), SEEK:([a].[name]=''),  WHERE:([tempdb].[dbo].[TestIsNull].[rn] as [a].[rn] IS NULL) ORDERED FORWARD  [a].[name]    1             0,003125      0,0001581     24          0,0032831        [a].[name]  NULL     PLAN_ROW                                                         0        1
2 июл 13, 15:04    [14510856]     Ответить | Цитировать Сообщить модератору
 Re: Чем провинилось условие is null в фильтрованном индексе?  [new]
Гавриленко Сергей Алексеевич
Member

Откуда: Moscow
Сообщений: 37138
Воспроизводится на 2008, 2008R2, 2012.
2 июл 13, 15:34    [14511089]     Ответить | Цитировать Сообщить модератору
 Re: Чем провинилось условие is null в фильтрованном индексе?  [new]
Maxx
Member [скрыт]

Откуда:
Сообщений: 24290
Microsoft SQL Server 2008 R2 (SP1) - 10.50.2500.0 (X64)
Jun 17 2011 00:54:03
Copyright (c) Microsoft Corporation
Enterprise Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1)

+1
2 июл 13, 15:44    [14511164]     Ответить | Цитировать Сообщить модератору
 Re: Чем провинилось условие is null в фильтрованном индексе?  [new]
Гость333
Member

Откуда:
Сообщений: 3683
Filtered index not used and key lookup with no output
Microsoft
this is actually not a bug but rather a known gap in functionality. This is now an active DCR for a future release of SQL Server.
2 июл 13, 15:48    [14511191]     Ответить | Цитировать Сообщить модератору
 Re: Чем провинилось условие is null в фильтрованном индексе?  [new]
Ruuu
Member

Откуда: Иркутск
Сообщений: 4272
Гавриленко Сергей Алексеевич,

Filtered index not used and key lookup with no output

Closed as Won't Fix
2 июл 13, 15:49    [14511199]     Ответить | Цитировать Сообщить модератору
 Re: Чем провинилось условие is null в фильтрованном индексе?  [new]
iap
Member

Откуда: Москва
Сообщений: 47045
Ruuu
Гавриленко Сергей Алексеевич,

Filtered index not used and key lookup with no output

Closed as Won't Fix
Красиво!
"Не хотим фиксить! И всё тут!"
2 июл 13, 15:53    [14511221]     Ответить | Цитировать Сообщить модератору
 Re: Чем провинилось условие is null в фильтрованном индексе?  [new]
Гавриленко Сергей Алексеевич
Member

Откуда: Moscow
Сообщений: 37138
Оу. Теперь не только Bug или Feature, но еще и Functionality Gap. Они классные.

За ссылку спасибо.
2 июл 13, 16:11    [14511407]     Ответить | Цитировать Сообщить модератору
 Re: Чем провинилось условие is null в фильтрованном индексе?  [new]
Mnior
Member

Откуда: Кишинёв
Сообщений: 6723
Functionality Gap
Не понял восклицания.
Вполне очевидное объяснение - движок в текущем состоянии логически не способен на такое, а костыль сопоставим с целым движком.
А Bug или Feature - это пользовательское выбор. Пользователь не может (не должен) знать о внутреннем функционале.

А то что они закрывают - так это ихняя последняя (уже несколько лет) фишка. Всё что не касается их текущих планов - фтопку.
2 июл 13, 19:18    [14512676]     Ответить | Цитировать Сообщить модератору
 Re: Чем провинилось условие is null в фильтрованном индексе?  [new]
Mnior
Member

Откуда: Кишинёв
Сообщений: 6723
Возможно это связано с пресловутой NULL проблемой.
Оно выглядит очевидно на простых запросах, или логически. Но если копаться в теории оптимизации планов (к примеру) - то там не всё так просто реализуемо.

Правило IS NOT NULL или A = B - топорно и безусловно.
А вот правило IS NULL не работает всегда - LEFT JOIN пример. Т.е. условно.
А текущие правила подстановки в оптимизаторе не могут работать с условными операторами (к примеру).

Отмазал?
2 июл 13, 19:30    [14512704]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить