Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
Топик располагается на нескольких страницах: Ctrl  назад   1 [2] 3   вперед  Ctrl      все
 Re: Индексация FOREIGN KEY  [new]
Relic Hunter
Member

Откуда: AB
Сообщений: 7584
Владислав Колосов,

автор
часто возникает необходимость создания индекса для внешнего ключа вручную
И как часто, с чисто практической точки зрения, если в системе сотни таблиц и десятки сотен внешних ключей? Будем думать над каждым? PowerDesigner автоматически индексирует внешние ключи. Вот с ним и пришла такая практика.
автор
Впрочем, создание индекса не является обязательным
Угу. Когда удаляются записи из мастера, автоматически накладывается блокировка уровня таблицы на деталь, даже если там и нет записей по ключу. Наступал как-то на такие грабли. Пользовательские сессии висели на этой маленькой _пустой_ табличке в ожидании блокировки.
17 июн 14, 19:04    [16178035]     Ответить | Цитировать Сообщить модератору
 Re: Индексация FOREIGN KEY  [new]
Mnior
Member

Откуда: Кишинёв
Сообщений: 6724
a_voronin
джойн в обратном направлении
Опана, новый "термин", чувствую что скоро появятся посты про "направление джоина":
- Я правильно указала направление джоина, но всё равно не работает, какой CA надо накатить?

автор
Когда удаляются записи из мастера
А что, код позволяет сделать запрещённую операцию? (если об этом речь, иначе это оговорено в первом же ответе 16172999 в словах "конкретной цели")
Ну чтобы предотвратить эту нелепицу, можно и явно DENY DELETE ... ;)
18 июн 14, 03:28    [16179409]     Ответить | Цитировать Сообщить модератору
 Re: Индексация FOREIGN KEY  [new]
Relic Hunter
Member

Откуда: AB
Сообщений: 7584
Mnior
А что, код позволяет сделать запрещённую операцию? (если об этом речь, иначе это оговорено в первом же ответе 16172999 в словах "конкретной цели")
Ну чтобы предотвратить эту нелепицу, можно и явно DENY DELETE ... ;)
При удаленияx из мастера и id:1 и id:2 в разных сессиях будет блокироваться вся таблица-деталь вне зависимости от значения id. При наличии индекса внешнего ключа взаимных блокировок сессий не возникнет, т.к. блокируются узлы индекса в по id.
18 июн 14, 03:48    [16179443]     Ответить | Цитировать Сообщить модератору
 Re: Индексация FOREIGN KEY  [new]
babona
Member [заблокирован]

Откуда: Батуринск
Сообщений: 1816
хоть бы кто-нибудь обмолвился о степени избирательности данных в FK-полях с точки зрения полезности индексов.
В Oracle мне индексы по FK помогали при JOIN-ах больших таблиц.
18 июн 14, 09:31    [16179855]     Ответить | Цитировать Сообщить модератору
 Re: Индексация FOREIGN KEY  [new]
sphinx_mv
Member [заблокирован]

Откуда:
Сообщений: 1672
babona
хоть бы кто-нибудь обмолвился о степени избирательности данных в FK-полях с точки зрения полезности индексов.
В Oracle мне индексы по FK помогали при JOIN-ах больших таблиц.
Если кому-то "очень надо" удалять данные или менять значение ключа на стороне мастера, наличие даже очень неизбирательного индекса предпочтительнее фуллскана по таблице.
18 июн 14, 10:31    [16180249]     Ответить | Цитировать Сообщить модератору
 Re: Индексация FOREIGN KEY  [new]
babona
Member [заблокирован]

Откуда: Батуринск
Сообщений: 1816
sphinx_mv,

В DWH мы не удаляем и не апдейтим суррогатные ключи
18 июн 14, 10:44    [16180340]     Ответить | Цитировать Сообщить модератору
 Re: Индексация FOREIGN KEY  [new]
sphinx_mv
Member [заблокирован]

Откуда:
Сообщений: 1672
babona
В DWH мы не удаляем и не апдейтим суррогатные ключи
В таком случае наводящий вопрос: какое отношение использование индексов в DWH имеет непосредственно к вопросу о полезности/бесполезности индексов, обслуживающих FK?

ЗЫ. Честно говоря, целесообразность использования FK в DWH - тоже очень интересный вопрос...
18 июн 14, 18:11    [16184681]     Ответить | Цитировать Сообщить модератору
 Re: Индексация FOREIGN KEY  [new]
Dmitry V. Liseev
Member [заблокирован]

Откуда: Санкт-Петербург
Сообщений: 5489
Провёл тест на MS SQL. Создал две таблицы: мастер и деталь. Забил данными. Попытался удалить одну запись в мастере. Планы показывает разные в зависимости от наличия или отсутствия индекса. Но оценка затрат одинакова. При этом в операции сканирования таблицы детали (или индекса при его наличии) указано FORCEDINDEX.
18 июн 14, 18:49    [16184912]     Ответить | Цитировать Сообщить модератору
 Re: Индексация FOREIGN KEY  [new]
Dmitry V. Liseev
Member [заблокирован]

Откуда: Санкт-Петербург
Сообщений: 5489
Значение TotalSubtreeCost одинаково как с индексом, так и без него. Я что неправильно делаю?
SET NOCOUNT ON;

CREATE TABLE Main
(
	ID INT

	CONSTRAINT PK_Main PRIMARY KEY CLUSTERED(ID)
);

CREATE TABLE Child
(
	MainID INT NOT NULL
		CONSTRAINT FK_Child_MainID REFERENCES Main(ID),
	Val INT
);

--CREATE INDEX IDX_Child_MainID ON Child(MainID);

INSERT INTO Main VALUES(1), (2);

WITH Series(a)
AS
(
	SELECT 1
	UNION ALL
    SELECT a+1 FROM Series WHERE a < 100000
)
INSERT INTO Child(MainID, Val)
SELECT 1, a FROM Series OPTION (MAXRECURSION 0);
GO

SET STATISTICS PROFILE ON;
GO
DELETE FROM Main WHERE ID = 2;
GO
SET STATISTICS PROFILE OFF;
GO

DROP TABLE Child;
DROP TABLE Main;
18 июн 14, 18:54    [16184946]     Ответить | Цитировать Сообщить модератору
 Re: Индексация FOREIGN KEY  [new]
babona
Member [заблокирован]

Откуда: Батуринск
Сообщений: 1816
sphinx_mv
babona
В DWH мы не удаляем и не апдейтим суррогатные ключи
В таком случае наводящий вопрос: какое отношение использование индексов в DWH имеет непосредственно к вопросу о полезности/бесполезности индексов, обслуживающих FK?

ЗЫ. Честно говоря, целесообразность использования FK в DWH - тоже очень интересный вопрос...


с FK в DWH я сплю спокойно и далее при аналитич. разработках не беспокоюсь о целостности данных. Кстати, включение (Enable) может быть отложенным, в Oracle - в несколько параллелей для особо больших таблиц.

ДВХ в виде просто так себе складирования табличек - я называю помойкой, другие называют - хоронилищем.
18 июн 14, 20:02    [16185241]     Ответить | Цитировать Сообщить модератору
 Re: Индексация FOREIGN KEY  [new]
Владислав Колосов
Member

Откуда:
Сообщений: 8807
Dmitry V. Liseev,

CREATE TABLE Child
(
	MainID INT NOT NULL
		CONSTRAINT FK_Child_MainID REFERENCES Main(ID) [b]ON UPDATE CASCADE ON DELETE  CASCADE[/b],
	Val INT
);
18 июн 14, 23:49    [16186168]     Ответить | Цитировать Сообщить модератору
 Re: Индексация FOREIGN KEY  [new]
Владислав Колосов
Member

Откуда:
Сообщений: 8807
Bold теги не сработали. Анализатор сразу предлагает построить индекс.
18 июн 14, 23:51    [16186173]     Ответить | Цитировать Сообщить модератору
 Re: Индексация FOREIGN KEY  [new]
sphinx_mv
Member [заблокирован]

Откуда:
Сообщений: 1672
babona
sphinx_mv
Честно говоря, целесообразность использования FK в DWH - тоже очень интересный вопрос...
с FK в DWH я сплю спокойно и далее при аналитич. разработках не беспокоюсь о целостности данных.
Перпендикулярно. FK для этого совершенно не является необходимым. О целостности данных должен заботиться скрипт/процедура/етц - то, что загружает данные. И если ссылочной целостности нет в исходных данных, декларативная ссылочная целостность в DWH тоже не поможет.
babona
Кстати, включение (Enable) может быть отложенным, в Oracle - в несколько параллелей для особо больших таблиц.
Постоянное включение/отключение констрэинтов на особо больших таблицах - это весьма "сексуальное" мероприятие...
Про оракловую отложенную проверку констрэйнтов я тоже как-то в курсе... Да, полезная вещь. Но для DWH практическое применение лично мне не представляется целесоообразным: тут (обычно) не бывает многократного обновления записей при операциях - один раз, но сразу много данных...
babona
ДВХ в виде просто так себе складирования табличек - я называю помойкой, другие называют - хоронилищем.
"Аминь!" (с) :)
19 июн 14, 01:36    [16186447]     Ответить | Цитировать Сообщить модератору
 Re: Индексация FOREIGN KEY  [new]
Mnior
Member

Откуда: Кишинёв
Сообщений: 6724
Relic Hunter
Mnior
А что, код позволяет сделать запрещённую операцию?
Ну чтобы предотвратить эту нелепицу, можно и явно DENY DELETE ... ;)
При удаленияx из мастера...
Я про Фому вы про Ерёму? Иначе зачем меня цитировать?
Удалять это общий подход (средний по больгице) не всегда полезный, тяжело пометь через поле Deleted, Active и т.п.?
И удалять раз на миллион можно и долго, падумаешь падаждать (решить организационно, отложенно), некуй было хрень создавать, зато экономия на память и время вставки/изменения данных.
Dmitry V. Liseev
Но оценка затрат одинакова. Значение TotalSubtreeCost одинаково как с индексом, так и без него. Я что неправильно делаю?
Ещё один наивынй верит в абсолютные вещи.
Гёдель? Не не слышал.
Абстракции текут? Не не слышал.
Всё в мире линейно и поддаётся вычислению, за бесплатно. Можно узнать всё из ничего. Чега?


Надо понимать когда что-то уже не применимо. Хотя бы знать что такое бывает и иметь здравый смысл, (вера в него выходит из него самого).
Багов больше там где меньше глаз.

Dmitry V. Liseev
Я что неправильно делаю?
Практически всё неправильно.
+ Код
Не забудьте включить "Include Actual Plan" (Ctrl+M)
USE tempdb;
GO
CREATE TABLE dbo.Parent1 (
	ID	Int		CONSTRAINT PK_Parent1 PRIMARY KEY
);
CREATE TABLE dbo.Child1 (
	ID	Int IDENTITY	CONSTRAINT PK_Child1 PRIMARY KEY
,	Parent	Int NOT NULL	CONSTRAINT FK_Child1_Parent REFERENCES dbo.Parent1(ID) ON DELETE CASCADE
,	Val	Int
);
CREATE TABLE dbo.Parent2 (
	ID	Int		CONSTRAINT PK_Parent2 PRIMARY KEY
);
CREATE TABLE dbo.Child2 (
	ID	Int IDENTITY	CONSTRAINT PK_Child2 PRIMARY KEY
,	Parent	Int NOT NULL	CONSTRAINT FK_Child2_Parent REFERENCES dbo.Parent2(ID) ON DELETE CASCADE
,	Val	Int
);
CREATE INDEX IX_Child2_Parent ON dbo.Child2(Parent);

INSERT dbo.Parent1 VALUES(1), (2);

INSERT	dbo.Child1	(Parent, Val)
SELECT	TOP(10000)	      1,N1.number
FROM	master.dbo.spt_values N1 CROSS
JOIN	master.dbo.spt_values N2;

INSERT dbo.Parent2 VALUES(1), (2);

INSERT	dbo.Child2	(Parent, Val)
SELECT			 Parent, Val
FROM	dbo.Child1

UPDATE STATISTICS dbo.Parent1;
UPDATE STATISTICS dbo.Child1;
UPDATE STATISTICS dbo.Parent2;
UPDATE STATISTICS dbo.Child2;

DELETE	dbo.Parent1
WHERE	ID = 2;

DELETE	dbo.Parent2
WHERE	ID = 2;
GO
DROP TABLE dbo.Child2, dbo.Parent2, dbo.Child1, dbo.Parent1;
Дьявол в деталях. Критично относитесь ко всему, с таким подходом можете сами себе доказать что 2+2=5.

Владислав Колосов
CREATE TABLE dbo.Child (
	MainID	INT NOT NULL	CONSTRAINT FK_Child_MainID REFERENCES dbo.Main(ID) ON UPDATE CASCADE ON DELETE CASCADE
,	Val	INT
);
Bold теги не сработали
Вот так (смотрите через цитирование)
19 июн 14, 03:02    [16186528]     Ответить | Цитировать Сообщить модератору
 Re: Индексация FOREIGN KEY  [new]
Dmitry V. Liseev
Member [заблокирован]

Откуда: Санкт-Петербург
Сообщений: 5489
Владислав Колосов
Dmitry V. Liseev,

CREATE TABLE Child
(
	MainID INT NOT NULL
		CONSTRAINT FK_Child_MainID REFERENCES Main(ID) [b]ON UPDATE CASCADE ON DELETE  CASCADE[/b],
	Val INT
);
У меня не было цели удалять дочерние записи.
19 июн 14, 08:13    [16186710]     Ответить | Цитировать Сообщить модератору
 Re: Индексация FOREIGN KEY  [new]
babona
Member [заблокирован]

Откуда: Батуринск
Сообщений: 1816
sphinx_mv
babona
пропущено...
с FK в DWH я сплю спокойно и далее при аналитич. разработках не беспокоюсь о целостности данных.
Перпендикулярно. FK для этого совершенно не является необходимым. О целостности данных должен заботиться скрипт/процедура/етц - то, что загружает данные.


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


[quot sphinx_mv]
babona
пропущено...
Постоянное включение/отключение констрэинтов на особо больших таблицах - это весьма "сексуальное" мероприятие...
Про оракловую отложенную проверку констрэйнтов я тоже как-то в курсе... Да, полезная вещь. Но для DWH практическое применение лично мне не представляется целесоообразным: тут (обычно) не бывает многократного обновления записей при операциях - один раз, но сразу много данных...


сексуем на отдельной таблице, которую потом подменяем как горячую партицию- делов то!

декларативные связи позволяют быстрее понять как чего логиески устроено в ДВХ и говорит о зрелости архитектора и руководства. Потребители ДВХ будут долго выяснять через глухой телефон, спотыкаться о нестыковки. Есть, конечно, и это распространено, увы, повсеместно - так такие и по трассе будут переть по встречке и в потоке лететь - плевать на всех
19 июн 14, 09:40    [16187040]     Ответить | Цитировать Сообщить модератору
 Re: Индексация FOREIGN KEY  [new]
sphinx_mv
Member [заблокирован]

Откуда:
Сообщений: 1672
babona
sphinx_mv
FK для этого совершенно не является необходимым. О целостности данных должен заботиться скрипт/процедура/етц - то, что загружает данные.
должен, кто бы спорил, но на надежность ETL, отвественность ETL-щиков расчитывать не приходится, прокидывают осиротевшие записи и не даже не взрагивают - ну не им же аналитические расчеты/отчеты делать и писать доклады на топ- менеджмента
Это - сугубо организационный вопрос, который техническими средствами плохо решается.
Любой уровень отвественности при отсутствии должной квалификации и мотивации никак не способствует исключению эксцессов...
Мотивацию в виде "волшебного пенделя" от ТОП-менеджмента, который должен быть заинтересован в отсутствии бардака в отчетах, как бы никто не отменял.
В-общем, подобная ситуация может быть охарактеризована замечательной булгаковская фраза про "разруха [...] в головах"
babona
сексуем на отдельной таблице, которую потом подменяем как горячую партицию- делов то!
Данивапрос! :)
Вот только даже "отдельная таблица" может быть сама по себе "особо большой"... Сугубо потому, что критерий "большести" сильно зависит от "конкретной" ситуации...
19 июн 14, 11:18    [16187747]     Ответить | Цитировать Сообщить модератору
 Re: Индексация FOREIGN KEY  [new]
babona
Member [заблокирован]

Откуда: Батуринск
Сообщений: 1816
sphinx_mv
Любой уровень отвественности при отсутствии должной квалификации и мотивации никак не способствует исключению эксцессов...
Мотивацию в виде "волшебного пенделя" от ТОП-менеджмента, который должен быть заинтересован в отсутствии бардака в отчетах, как бы никто не отменял.
В-общем, подобная ситуация может быть охарактеризована замечательной булгаковская фраза про "разруха [...] в головах"


Про разруху в головах и пенделя от ТОП-менеджмента - это не панацея, хотя в разумной мере должно быть.
И тем не менее автоматическую защиту последней надежды, т.к. любой скрипт, любой разработчик может ошибиться. Так, в самолетах есть несколько контуров защиты от нештатных ситуаций.
19 июн 14, 11:36    [16187885]     Ответить | Цитировать Сообщить модератору
 Re: Индексация FOREIGN KEY  [new]
Кот Матроскин
Member

Откуда: Москва
Сообщений: 8933
babona
должен, кто бы спорил, но на надежность ETL, отвественность ETL-щиков расчитывать не приходится


Если ETL ненадежен - то в любом случае в хранилище будет лажа, и использование FK в этом случае подобно установке двери с замком в чистом поле
19 июн 14, 11:38    [16187902]     Ответить | Цитировать Сообщить модератору
 Re: Индексация FOREIGN KEY  [new]
Владислав Колосов
Member

Откуда:
Сообщений: 8807
Dmitry V. Liseev,

автор
У меня не было цели удалять дочерние записи.

В этом случае и обсуждать нечего.
19 июн 14, 11:57    [16188050]     Ответить | Цитировать Сообщить модератору
 Re: Индексация FOREIGN KEY  [new]
babona
Member [заблокирован]

Откуда: Батуринск
Сообщений: 1816
Кот Матроскин
babona
должен, кто бы спорил, но на надежность ETL, отвественность ETL-щиков расчитывать не приходится


Если ETL ненадежен - то в любом случае в хранилище будет лажа, и использование FK в этом случае подобно установке двери с замком в чистом поле


не следует передергивать. Возможна ситуация, когда сбоит именно ETL, например, изменилась логика и массовая заливка данных какого-то подмножества данных. ДВХ проглотило неконсистентные данные, которые потом расползлись по кубам, скорингам-датамайнингам, отчетам. И вы об этом узнаете много позже от разгневанных пользователей, у которых остается осадочек, что вы пропустили трояна. В то время как можно было обнаруживать это самим и оповещать пользователей о временной задержки готовности данных
19 июн 14, 12:26    [16188297]     Ответить | Цитировать Сообщить модератору
 Re: Индексация FOREIGN KEY  [new]
sphinx_mv
Member [заблокирован]

Откуда:
Сообщений: 1672
babona
sphinx_mv
Любой уровень отвественности при отсутствии должной квалификации и мотивации никак не способствует исключению эксцессов...
Мотивацию в виде "волшебного пенделя" от ТОП-менеджмента, который должен быть заинтересован в отсутствии бардака в отчетах, как бы никто не отменял.
В-общем, подобная ситуация может быть охарактеризована замечательной булгаковская фраза про "разруха [...] в головах"


Про разруху в головах и пенделя от ТОП-менеджмента - это не панацея, хотя в разумной мере должно быть.
И тем не менее автоматическую защиту последней надежды, т.к. любой скрипт, любой разработчик может ошибиться.
Хм... Расссмотрим техпроцесс...
Real-life ETL cycle
The typical real-life ETL cycle consists of the following execution steps:
  • Cycle initiation
  • Build reference data
  • Extract (from sources)
  • Validate
  • Transform (clean, apply business rules, check for data integrity, create aggregates or disaggregates)
  • Stage (load into staging tables, if used)
  • Audit reports (for example, on compliance with business rules. Also, in case of failure, helps to diagnose/repair)
  • Publish (to target tables)
  • Archive
  • Clean up
  • Места применению FK тут не наблюдается: контроль ссылочной целостности должен выполняться ДО загрузки данных.
    Нарушения техпроцесса - не повод для его подпирания костылями.
    babona
    Так, в самолетах есть несколько контуров защиты от нештатных ситуаций.
    в самолетах главное все делать по инструкции. И только если соответствующего пункта для сложившейся ситуации в инструкции нет - голова/руки/опыт и немного надежды на удачу...
    19 июн 14, 12:32    [16188341]     Ответить | Цитировать Сообщить модератору
     Re: Индексация FOREIGN KEY  [new]
    sphinx_mv
    Member [заблокирован]

    Откуда:
    Сообщений: 1672
    sphinx_mv
    Real-life ETL cycle
    ...

    Извиняюсь... ссылка "спряталась"... :)
    19 июн 14, 12:35    [16188363]     Ответить | Цитировать Сообщить модератору
     Re: Индексация FOREIGN KEY  [new]
    Кот Матроскин
    Member

    Откуда: Москва
    Сообщений: 8933
    babona
    Кот Матроскин
    пропущено...


    Если ETL ненадежен - то в любом случае в хранилище будет лажа, и использование FK в этом случае подобно установке двери с замком в чистом поле


    не следует передергивать. Возможна ситуация, когда сбоит именно ETL

    Если ETL может сбойнуть - он может сбойнуть 100500 вариантами. Ну закроете Вы проблему с записями-"зомби" при помощи FK (небесплатно причем закроете, ценой увеличения времени загрузки) - а остальные 100499 пропустите. И легче ли Вашим пользователям станет оттого, что продажи одного продавца в хранилище не потерялись, а перевесились на другого? это гораздо труднее заметить - все итоговые цифры совпадают, все хорошо.
    "ETL может сбойнуть" - это логика того чукчи, который не дурак и у которого проездной. ETL - можно сказать, ключевой вопрос при построении хранилища. ETL дырявый -> хранилище дырявое, без вариантов.
    Сбои в ETL ловятся не "скриптами последней надежды", а правильным процессом проверки и отладки chang'ей в системе. На тестовом контуре для ETL - можно и FK включать, и сложные DM-сценарии на аномалии в данных по результатам пускать (тоже, кстати, увлекательное дело), и черта в ступе.
    19 июн 14, 13:04    [16188650]     Ответить | Цитировать Сообщить модератору
     Re: Индексация FOREIGN KEY  [new]
    babona
    Member [заблокирован]

    Откуда: Батуринск
    Сообщений: 1816
    Кот Матроскин
    хранилище не потерялись, а перевесились на другого? это гораздо труднее заметить - все итоговые цифры совпадают, все хорошо.
    "ETL может сбойнуть" - это логика того чукчи, который не дурак и у которого проездной. ETL - можно сказать, ключевой вопрос при построении хранилища. ETL дырявый -> хранилище дырявое, без вариантов.
    Сбои в ETL ловятся не "скриптами последней надежды", а правильным процессом проверки и отладки chang'ей в системе. На тестовом контуре для ETL - можно и FK включать, и сложные DM-сценарии на аномалии в данных по результатам пускать (тоже, кстати, увлекательное дело), и черта в ступе.


    еще раз повторю - не следует передергивать - контроль данных должен обеспечиваться разными возможными средствами, несколько контуров "защиты" на разных стадиях. Идеальный ETL вы не построите никогда. Я не вижу проблемы - с сервера не убудет, пусть трудится железяка. Факап на ровном месте хорошо лечится депремированием разработчиков.
    19 июн 14, 13:41    [16188985]     Ответить | Цитировать Сообщить модератору
    Топик располагается на нескольких страницах: Ctrl  назад   1 [2] 3   вперед  Ctrl      все
    Все форумы / Microsoft SQL Server Ответить