MasterZiv
> Об этот вопрос обломано немало копий. Как минимум еще есть комбинация
> idFSotrudni,idFOtdel. По поводу кластерного индекса тоже все неоднозначно.

И что ж там неоднозначно? Этот первичный ключ будет чуть ли не единственным
индексом в этой таблице, и к тому же он первичный ключ. Ну, возможно нужен
будет ещё обратный индекс: (idFOtdel,idFSotrudni).
Либо один, либо другой надо делать первичным ключём, и один из них надо
делать кластерным, если это MSSQLServer.

Я бы еще предложил добавить по одному индексу на каждый внешний ключ...
Не зависимо от платформы...

MasterZivЛибо один, либо другой надо делать первичным ключём, и один из них надо
делать кластерным, если это MSSQLServer.

Кстати, у Oracle подобное называется "индекс-организованные таблицы"...

MasterZiv
> Я бы еще предложил добавить по одному индексу на каждый внешний ключ...

А именно ?

Если из этого примера, то один - по idFOtdel, второй - по idFSotrudnic...
Не считая составного первичного/уникального по обоим этим полям.

Dimitry Sibiryakovsphinx_mvНе зависимо от платформы...
Эт ты погорячился... Некоторые SQL сервера создают индексы на FK самостоятельно.

А это как-то отменяет факт наличия соответствующего индекса?
Это всего лишь означает, что его в-ручную не надо делать, но сам индекс - необходим.

MasterZiv
> > Я бы еще предложил добавить по одному индексу на каждый внешний ключ...
>
> А именно ?
>
>
> Если из этого примера, то один - по idFOtdel, второй - по idFSotrudnic...
> Не считая составного первичного/уникального по обоим этим полям.

Эти индексы не нужны, если есть оба индекса по двум полям:
(idFOtdel,idFSotrudnic)
и
(idFSotrudnic,idFOtdel)


Может, я и не прав, но "обратный" индекс я бы добавлял в последнюю очередь - когда уж совсем "прижмет", и именно он "спасет"...

MasterZiv
> Может, я и не прав, но "обратный" индекс я бы добавлял в последнюю очередь -
> когда уж совсем "прижмет", и именно он "спасет"...

Это любой индекс надо так добавлять, но вообще-то таблица реализует
связь "многие-ко-многим", и обычно связь используется в БД для
"прохода" как от таблицы А к таблице В, так и наоборот, от таблицы
В к таблице А.

В некотором роде это заблуждение...

Начиная с того, что индекс может использоваться только если оптимизатор посчитает, что он достаточно эффективен для этого. Если, конечно, не рассматривать вариант с "ручным" прописыванием хинтов (которое даже не всегда срабатывает)...
И заканчивая тем, что "среднепотолочная" селективность и стоимость, что "прямого", что "обратного" индексов примерно одинаковы. Следовательно, пользы от подобной "копии" можно даже не пытаться предполагать.

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

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

MasterZivМне как бы надоело спорить по пустякам, но ...

> И заканчивая тем, что "среднепотолочная" селективность и стоимость, что
> "прямого", что "обратного" индексов примерно одинаковы. Следовательно, пользы от
> подобной "копии" можно даже не пытаться предполагать.

Если у тебя одни запросы используют ТОЛЬКО одно поле, а другие -- ТОЛЬКО другое,
тут как бы выбора нет (если таблица большая, конечно, а не 20-30 строк).
В таблице связей (из 2 полей) нужно иметь 3 (три!) индекса...
Один из них - составной ПК(ПОЛЕ1,ПОЛЕ2), два других - по полям внешних ключей ИНД1(ПОЛЕ1) и ИНД2(ПОЛЕ2).
Любой из этих индексов может быть (а может и НЕ быть) использован при выполнении любых запросов из таблиц, участвующих в этой реализации схемы "много-во-много". И что не так?

MasterZiv> К тому, же я уже сталкивался с тем, что в некоторых серверах БД просто
> запрещается создавать индексы с одинаковым набором полей (независимо от их
> порядка).

Давай пример. Не верю.
Ну, Информикс. Полегчало?
Попытка создания друг за другом двух индексов вида ИНД1(ПОЛЕ1,ПОЛЕ2) и ИНД2(ПОЛЕ2,ПОЛЕ1), помнится (давно было дело), приводила к ошибке с сообщением о наличии в БД индекса с повторяющимся набором полей.

MasterZiv А для некоторых других серверов это очень открытым текстом просто не
> рекомендуют делать...

Давай пример. Не верю.
Все тот же Информикс - и это было раз...
Оракл - а это было два... Как раз читаю апресовское издание "Expert Oracle Database Architecture" Тома Кайта - на 488 странице подробно аккурат про это написано...

MasterZiv> При всем этом создание индексов по полям, участвующим во внешних ключах,
> является вполне устоявшейся и рекомендуемой практикой.

Не всегда это нужно. Но некоторые СУБД действительно принудительно их создают.
Принудительное создание индексов для внешних ключей - это будет по-круче, чем просто "рекомендуемая практика"...
Это будет уже " настоятельно рекомендуемая практика"... Даже если, кому-то покажется, что это "не всегда нужно"...

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

Напиши полный список индексов, с полями, и с их порядком.
Афигеть! В таблице аж два поля!..
Вам действительно настолько проблематично самостоятельно "нарисовать" один составной индекс с любым из двух вариантов первого/второго поля и два простых?
Мне не сложно - мне лень... Хотя, по ходу дела, я их уже все равно расписал...

MasterZiv
> В таблице связей (из 2 полей) нужно иметь 3 (три!) индекса...
> Один из них - составной ПК(ПОЛЕ1,ПОЛЕ2), два других - по полям внешних ключей
> ИНД1(ПОЛЕ1) и ИНД2(ПОЛЕ2).

Индексы ПК(ПОЛЕ1,ПОЛЕ2) и ИНД1(ПОЛЕ1) избыточны. Можно использовать либо один,
либо другой.
"Ссылку на документацию" не затруднитесь?

Потому как я могу привести ссылку на документацию, как минимум, для одного из серверов, где будет указана необходимость всех этих трех индексов... Более того - индексы по внешним ключам будут созданы автоматически, если отсутствуют.
Что означает, что разработчики вполне серьезной платформы ВИДЯТ настоятельную необходимость во ВСЕХ этих индексах...
Я даже больше скажу: там как раз будет приведен вариант реализации M:N-связи - со всеми необходимыми индексами (в количестве 3-х штук)

Ну, а пример другого сервера, который поступает точно так же (автоматически создает индексы для внешних ключей) уже приводился...
MasterZiv> Любой из этих индексов *может быть* (а может и *НЕ* быть) использован при
> выполнении любых запросов из таблиц, участвующих в этой реализации схемы
> "много-во-много". И что не так?

См. выше.
Я почему-то уверен, что вещание истины в последней инстанции - несколько не Ваша прерогатива.
MasterZiv> Ну, Информикс. Полегчало?
> Попытка создания друг за другом двух индексов вида ИНД1(ПОЛЕ1,ПОЛЕ2) и
> ИНД2(ПОЛЕ2,ПОЛЕ1), помнится (давно было дело), приводила к ошибке с сообщением о
> наличии в БД индекса с повторяющимся набором полей.

Ссылку на документацию, пожалуйста, где описано, что запрещено, и почему запрещено.

Бинго! Тролль-левел-ап...

Это "безобразие" описано в "IBM Informix Guide to SQL: Syntax"... Раздел "CREATE INDEX"... Ну, и там же подпункт "Restrictions on the Number of Indexes on a Set of Columns".
С учетом моих подозрений, что кое-кто гуглю не обучен - вот ссылка на документацию...
MasterZiv> Все тот же Информикс - и это было раз...

Ссылку давай.

А все по той же самой...
И в том же самом разделе...
MasterZiv> Принудительное создание индексов для внешних ключей - это будет по-круче, чем
> просто "рекомендуемая практика"...
> Это будет уже "*настоятельно* рекомендуемая практика"... Даже если, кому-то
> покажется, что это "не всегда нужно"...

Отнюдь.

Вы лучше разработчиков IBM знаете, как лучше и какие индексы нужны для ОПТИМАЛЬНОЙ работы сервера БД?
Тогда с нетерпением жду появления и широчайшего распространения новой промышленной платформы MasterZiv SQL Server, которая всех заткнет за пояс.
MasterZivИндексы на поля FK в дочрней таблице нужны, когда 1) дочерняя таблица
большая 2) из родительской таблицы удаляется запись (или меняется её PK, но
это уже клинический случай, не рассматриваю.

Начиная с того, что "большая"/"маленькая" таблица - критерий совершенно не формализованный, и заканчивая тем, отсутствие индекса по внешнему ключу ОЧЕНЬ часто (регулярно) приводит к конфликтам доступа к дочерним таблице даже на не очень нагруженных серверах, Ваши аргументы - очень теоретические. И при этом теоретически вполне правильные...
MasterZivСоответственно, если дочерняя таблица маленькая, или из родительской никогда
не удаляют записи, то индекс на поля FK в дочерней таблице не нужен.
Также он может быть не нужен, если в дочерней табилце поля FK, все или
частично, уже есть в составе какого-то индекса.

Мне не встречалось ни одной БД, из которых ни разу не удалялись данные.
И ни один составной индекс НИКОГДА не будет эффективнее (быстрее, лучше и т.п.), чем составной...
Соответственно, это Ваше рассуждение в качестве аргумента я не воспринимаю.
MasterZiv> Афигеть! В таблице аж два поля!..
> Вам действительно настолько проблематично самостоятельно "нарисовать" один
> составной индекс с любым из двух вариантов первого/второго поля и два простых?
> Мне не сложно - мне лень... Хотя, по ходу дела, я их уже все равно расписал...

Я просто хотел тебе явно указать, какие индексы ты бы нарисовал лишними.
Ну, не хочешь -- не надо.

Вы видите "лишние индексы". Очень многие другие эти индексы лишними не считают.
Соответственно, Ваше слово против их слова... К сожалению для Вас, ИХ слово - как-то по-весомее...

MasterZiv> Потому как я могу привести ссылку на документацию, как минимум, для одного из
> серверов, где будет указана необходимость всех этих трех индексов... Более того

Давай. (только я должен оговориться, что я подразумеваю что индексы -- это B+tree)
А пофиг какие индексы - тип индекса не указан. Он просто ДОЛЖЕН быть создан ЛЮБЫМ способом...
IBM Informix Database Design and Implementation Guide... Resolving m:n Relationships... Просветляться тут ...
Если возникнут "вопросы" по предлагаемой диаграмме, уточняю еще раз, что для для каждого ВК нужен индекс, и ПК нужен уникальный индекс... Индексы "нужны" - вплоть до того, что они создаются автоматически.
MasterZivЭто ?

http://publib.boulder.ibm.com/infocenter/idshelp/v10/index.jsp?topic=/com.ibm.sqls.doc/sqls204.htm
Да. Где-то там...

MasterZiv> Вы лучше разработчиков IBM знаете, как лучше и какие индексы нужны для
> ОПТИМАЛЬНОЙ работы сервера БД?

Моей БД -- да, лучше. Потому что они мою БД естественно не знают.

ВОТ!!! Для ВАШЕЙ БД...
Но кто Вам сказал, что Вы знаете СЕРВЕР своей БД лучше разработчиков?
Что по обсуждаемой теме пишут в документации на используемый Вами сервер БД? Ссылочку, плииииз...

Короче... Никакая категоричность с Вашей стороны не уместна...
Но именно в категоричном ключе Вы указываете на неправильность такого дизайна.
И именно с Вашей категоричностью я не согласен.
MasterZiv> Индексы на поля FK в дочрней таблице нужны, когда 1) дочерняя таблица
> большая 2) из родительской таблицы удаляется запись (или меняется её PK, но
> это уже клинический случай, не рассматриваю.
>
>
> Начиная с того, что "большая"/"маленькая" таблица - критерий совершенно не
> формализованный,

Формализованый. Размер таблицы (кол-во листовых страниц данных) меньше высоты
дерева индекса (в страницах).

Ссылку на документацию, где приводится Ваше определение, пожалуйста...

Особенно в плане того, что в некоторых типах индексов листовые уровни представлены непосредственно страницами данных.
Кластерные и их варианты, если не очень понятно, что я подразумеваю. Навскидку, как минимум, 3 (разных!) сервера БД поддерживают такие типы индексов.
MasterZiv> очень нагруженных серверах, Ваши аргументы - очень теоретические. И при этом
> теоретически вполне правильные...

Блин, какие уж там теоретические. Годами наша БД работала без индексов на
FK в дочерней таблице, и только когда кому-то понадобилось в очередной
"глухой" таблице удалить очередную левую запись, выявлялась сия необходимось --
иметь индекс. Ибо без индекса оно не заканчивалось вообще никогда.
(удаление). Оно часто и вообще не заканчивалось ничем, в смысле, индекс
таки решали не создавать, как-то выкручивались без удаления записи.
Так что наоборот, сугубо практические аргументы.

"Ежики плакали, кололись, но продолжали есть кактусы" (с)

Указанные телодвижения над структурой БД в процессе эксплуатации - признак не очень хорошего ее изначального дизайна...
С учетом того, что построение индекса в подавляющем количестве случаев требует блокировки индексируемой таблицы на весьма существенное время - НУ, ОЧЕНЬ эффективное решение...
Таки, теоретик... Да...
Потому как практически проще ОДИН раз создать индекс, чтобы потом (судя по всему) неоднократно принимать решения о необходимости его создания или заниматься прочей непродуктивной "химией" и "извращениями"...
MasterZiv> Мне не встречалось ни одной БД, из которых ни разу не удалялись данные.

А я знаешь ли наоборот 10 лет работал с БД, из которой данные никогда не
удалялись. Она и сейчас работает, и данные так и не удаляются.

И ни разу такой необходимости не возникало?!
Врете батенька - НАГЛО! А я такого, как бы это... не одобряю...
Двумя квотами выше Вы "совершенно ни разу не хотели удалять"... Ага... И даже индекс под это дело ни разу не думали создавать...
Понятно, что дело закончилось "костылями"... Но сам факт имевшей место необходимости удаления - показателен...
MasterZiv> И ни один составной индекс НИКОГДА не будет эффективнее (быстрее, лучше и т.п.),
> чем составной...

Ну, бред обсуждать не собираюсь.
Да. Опечатка. При желании, конечно можно было бы понять, что имелось ввиду... Но, похоже, это - не Ваш метод...
Ну, и ладно...
MasterZiv> Вы видите "лишние индексы". Очень многие другие эти индексы лишними не считают.
> Соответственно, Ваше слово против их слова... К сожалению для Вас, ИХ слово -
> как-то по-весомее...

Да не ну они конечно иногда могут быть и полезны. несоставной индекс тупо
меньше. зато составной может быть покрывающим. Только у них есть одна такая
особенность, у индексов этих -- они блин место в БД занимают. Всё бы ничего,
если бы не этот маленький затык. Так что тут надо 10 раз подумать, прежде
чем создавать новый.
Экономия на спичках? Видел...
А потом такие "экономисты для бедных" останавливают на неопределенное время базу на сервере, чтобы по желанию левой пятки правой ноги построить индекс "одноразового" использования, выдавая это за "The Best Practice" в проектировании и обслуживании БД...

Фактически, все уперлось в "место на диске жалко"... А мне - нет!
Максимум, что я согласен рассмотреть в качестве мало-мальски значительного аргумента для отказа от индекса по внешнему ключу - замедления при вставке и удалении, связанные с перестроением этого индекса. Но пусть уж мне АРГУМЕНТИРОВАННО докажут, что ИМЕННО этот индекс "вешает все"... И даже в этом случае эта "значительность" будет соизмерима с уровнем погрешности метода измерения.