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

Откуда: от верблюда
Сообщений: 408
таблица имеет поля "код" и "связь"
на входе имеем код. на выходе должна вернуться запись с этим кодом и все остальные, имеющие такое же значение в поле "связь" что и у записи с запрашиваемым кодом
если связь=0 (не задана), то вернуть только запись с запрашиваемым кодом

DECLARE @Clients table (ID int, code int,  relation int, unique(code,relation))
INSERT @Clients values(1,1,0)
INSERT @Clients values(2,2,0)
INSERT @Clients values(3,3,1)
INSERT @Clients values(4,4,2)
INSERT @Clients values(5,5,0)
INSERT @Clients values(6,6,2)
INSERT @Clients values(7,7,1)
INSERT @Clients values(8,8,3)
INSERT @Clients values(9,9,3)
INSERT @Clients values(10,10,0)
INSERT @Clients values(11,11,0)
INSERT @Clients values(12,12,1)

select
    isnull(c2.code,c1.code) code
from
    @Clients c1
left join @Clients c2 on c2.relation=c1.relation and c1.relation <> 0
where
    c1.code = 3


при внутреннем соединении индекс работает, при левом - нет
где я туплю?
ткните носом :'(
30 ноя 15, 16:30    [18493184]     Ответить | Цитировать Сообщить модератору
 Re: при левом соединение таблицы с самой собой индекс не работает  [new]
TJ001
Member

Откуда: от верблюда
Сообщений: 408
или это из-за unique(code,relation)?
30 ноя 15, 16:36    [18493225]     Ответить | Цитировать Сообщить модератору
 Re: при левом соединение таблицы с самой собой индекс не работает  [new]
Glory
Member

Откуда:
Сообщений: 104760
TJ001
при внутреннем соединении индекс работает, при левом - нет
где я туплю?

А что для вас собственно есть "индекс работает" в данном случае ?
30 ноя 15, 16:38    [18493242]     Ответить | Цитировать Сообщить модератору
 Re: при левом соединение таблицы с самой собой индекс не работает  [new]
WarAnt
Member

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

а где индекс то который не работает?
30 ноя 15, 16:38    [18493243]     Ответить | Цитировать Сообщить модератору
 Re: при левом соединение таблицы с самой собой индекс не работает  [new]
TJ001
Member

Откуда: от верблюда
Сообщений: 408
я извиняюсь, друзья, поспешил!
это все из-за табличной переменной...
на нормальной таблице все работает как и хотел
спасибо!
извиняюсь еще раз, что потратил ваше время без повода :)
30 ноя 15, 16:48    [18493309]     Ответить | Цитировать Сообщить модератору
 Re: при левом соединение таблицы с самой собой индекс не работает  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 31442
TJ001
на нормальной таблице все работает как и хотел
Что "работает", делает indes seek по индексу (code,relation), при подсоединению таблицы по полю relation?
Не верю.
30 ноя 15, 19:28    [18494093]     Ответить | Цитировать Сообщить модератору
 Re: при левом соединение таблицы с самой собой индекс не работает  [new]
SomewhereSomehow
Member

Откуда: Moscow
Сообщений: 2480
Блог
alexeyvg
Что "работает", делает indes seek по индексу (code,relation), при подсоединению таблицы по полю relation?
Не верю.

ТС не пояснил, что значит "работает", но если "работает", это поиск по индексу - присоединяюсь к alexeyvg.

Принципиальную возможность поиска по индексу - всегда можно проверить подсказкой with(forceseek).

Если заменить в запросе на постоянную таблицу и сделать запрос с этой подсказкой, то:
+
use tempdb;
go
create table Clients (ID int, code int,  relation int, unique(code,relation))
INSERT Clients values(1,1,0)
INSERT Clients values(2,2,0)
INSERT Clients values(3,3,1)
INSERT Clients values(4,4,2)
INSERT Clients values(5,5,0)[src]

INSERT Clients values(6,6,2)
INSERT Clients values(7,7,1)
[/SRC]
INSERT Clients values(8,8,3)
INSERT Clients values(9,9,3)
INSERT Clients values(10,10,0)
INSERT Clients values(11,11,0)
INSERT Clients values(12,12,1)

select
    isnull(c2.code,c1.code) code
from
    Clients c1
	left join Clients c2 with(forceseek) on c2.relation=c1.relation and c1.relation <> 0
where
    c1.code = 3


Результат:
Msg 8622, Level 16, State 1, Line 17
Query processor could not produce a query plan because of the hints defined in this query. Resubmit the query without specifying any hints and without using SET FORCEPLAN.


Но если то же самое, где в предикате задан первый ключ индекса, то
select
    isnull(c2.code,c1.code) code
from
    Clients c1
	left join Clients c2 with(forceseek) on c2.relation=c1.relation and c1.relation <> 0
where
    c1.code = 3 and
	c2.code = 3

И план:
автор
|--Compute Scalar(DEFINE:([Expr1006]=[tempdb].[dbo].[Clients].[code] as [c2].[code]))
|--Nested Loops(Inner Join, OUTER REFERENCES:([c1].[relation]))
|--Index Seek(OBJECT:([tempdb].[dbo].[Clients].[UQ__Clients__DBE8DCF9541DC7C6] AS [c1]), SEEK:([c1].[code]=(3) AND [c1].[relation] < (0) OR [c1].[code]=(3) AND [c1].[relation] > (0)) ORDERED FORWARD)
|--Index Seek(OBJECT:([tempdb].[dbo].[Clients].[UQ__Clients__DBE8DCF9541DC7C6] AS [c2]), SEEK:([c2].[code]=(3) AND [c2].[relation]=[tempdb].[dbo].[Clients].[relation] as [c1].[relation]) ORDERED FORWARD)


В это можно поверить.
30 ноя 15, 20:03    [18494253]     Ответить | Цитировать Сообщить модератору
 Re: при левом соединение таблицы с самой собой индекс не работает  [new]
TJ001
Member

Откуда: от верблюда
Сообщений: 408
я же говорю - все дело в табличной переменной, т.е. из-за ее ограничений
индекс не составной
на такой структуре все работает корректно:
use tempdb;
go

create table Clients (ID int, code int,  relation int)
CREATE NONCLUSTERED INDEX ix_1 on Clients (CODE ASC) ON [PRIMARY]
CREATE NONCLUSTERED INDEX ix_2 on Clients (relation ASC) ON [PRIMARY]

INSERT Clients values(1,1,0)
INSERT Clients values(2,2,0)
INSERT Clients values(3,3,1)
INSERT Clients values(4,4,2)
INSERT Clients values(5,5,0)
INSERT Clients values(6,6,2)
INSERT Clients values(7,7,1)
INSERT Clients values(8,8,3)
INSERT Clients values(9,9,3)
INSERT Clients values(10,10,0)
INSERT Clients values(11,11,0)
INSERT Clients values(12,12,1)

select
    isnull(c2.code,c1.code) code
from
    Clients c1
left join Clients c2 on c2.relation=c1.relation and c1.relation <> 0
where
    c1.code = 3

drop table Clients




ничего нового я не открыл, признаюсь еще раз - тупанул
1 дек 15, 09:47    [18495396]     Ответить | Цитировать Сообщить модератору
 Re: при левом соединение таблицы с самой собой индекс не работает  [new]
Glory
Member

Откуда:
Сообщений: 104760
TJ001
я же говорю - все дело в табличной переменной, т.е. из-за ее ограничений

И вы огласите/покажите это ограничение ?

TJ001
на такой структуре все работает корректно:

А корректно - это как именно ?
1 дек 15, 09:50    [18495415]     Ответить | Цитировать Сообщить модератору
 Re: при левом соединение таблицы с самой собой индекс не работает  [new]
TJ001
Member

Откуда: от верблюда
Сообщений: 408
Glory,

может это не ограничение, а мое незнание как проиндексировать два поля на sql2000
корректно - в плане два index seek, вместо двух table scan
1 дек 15, 09:59    [18495449]     Ответить | Цитировать Сообщить модератору
 Re: при левом соединение таблицы с самой собой индекс не работает  [new]
Glory
Member

Откуда:
Сообщений: 104760
TJ001
корректно - в плане два index seek, вместо двух table scan

В предоставленном вами запросе всегда как минимум один index seek
1 дек 15, 10:03    [18495480]     Ответить | Цитировать Сообщить модератору
 Re: при левом соединение таблицы с самой собой индекс не работает  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 31442
TJ001
может это не ограничение, а мое незнание как проиндексировать два поля на sql2000
Вы постарайтесь писать яснее.

Вы задали вопрос: "при левом соединение таблицы с самой собой индекс не работает"

Теперь оказывается, индекс не работает при левом соединении по той причине, что вы не знаете, как его создать :-)
Так что он и при правом соединении не будет работать, и вообще без соединения - потому что его нет.

Нужно было задать другой вопрос: "как создавать индексы на таблицах-переменных?"
2 дек 15, 12:17    [18502106]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить