Журавлев Денис Евгений Фадеев
2. Индексы на внешние ключи. Вопрос на засыпку: всегда ли наличие индекса ускоряет SELECT?Всегда. Оптимизатор решает использовать или нет.

Не соглашусь. К тому же и оптимизатор не всегда такой умный, как хотелось бы.
Журавлев ДенисНа каждое всегда естественно найдутся исключения, но писать лень.
В том то и дело, что эти исключения иногда играют большое значение и вполне понятно желание Евгения управлять возможностями. Как и в случае с автоматическим построением индексов для внешних ключей (как, например, у Оракла, когда автоматом не строится). Но все же, если сравнивать в общем, то я бы все таки оставил принудительное построение индексов, т.к. это намного лучше, чем когда люди вообще не строят индексы на ключах (видел пару раз такие промышленные БД на Оракле, когда у заказчика через несколько месяцев эксплуатации "вдруг" все начинало жутко тормозить, а у разработчика на малых объемах летало).

Тан Евгений Фадеев
2. Можно ли выбросить некоторые индексы на внешние ключи (например в ситуации, когда таблица длиной в несколько десятков миллионов записей ссылается на таблицу длиной в десяток записей)? Или с этим нужно жить (либо он сам такие индексы пользовать не будет, либо ему нужно хинтами это задавать)?
Почему вы думаете, что индекс вам помешает?
Пример такого "мешающего" индекса - автоиндекс на ключ "пол", где всего два значения М и Ж. Надо доказывать неэффективность такого индекса ?

АнатоЛой vasilisПример такого "мешающего" индекса - автоиндекс на ключ "пол", где всего два значения М и Ж. Надо доказывать неэффективность такого индекса ?
А можно?
Неужели на самом деле нужно доказывать неэффективность индекса всего с двумя значениями на таблице в миллионы строк ? Не верю, что вы ничего не читали об уникальности, дублировании значений, распределении данных и структуре индексного дерева. Напомню только, что в таком индексе всего два (2!!) элемента и все ссылки на строки таблицы хранятся не ввиде би-дерева (b-tree), которое эффективно для быстрого поиска, а ввиде обычного огромного массива ссылок, (точнее двух массивов в нашем случае) и для поиска нужного элемента (например, для удаления или замены) нужно "бегать" (сканировать) не по B-дереву, а по огромному массиву, по моему, даже не отсортированному.
С другой стороны, даже если забыть об этой проблеме, а просто взять индекс, через который выбирается половина всех значений таблицы, то, как минимум, он будет бесполезен, с точки зрения производительности, с лишними затратами на пространство и обслуживание.
И давайте не возвращаться к теме оптимизатора, "который сам решит" - увы, не всегда он такой умный и тому была масса примеров в прошлом (сейчас я такими вопросами уже не занимаюсь). И специально делал сбор статистики для некоторых версий IDS, в которых статистика по мелким таблицам просто "убивалась", чтобы "вумный оптимизатор" ничего о них не знал и просто сканил их без индексов. Производительность поднималась в порядки (с часа-двух до 5-10 минут), конечно это не на примитивных запросах (страниц на несколько текста и пара десятков таблиц), да еще и построенных всякими серверами приложений. Да и директив тогда не было.

Тан vasilis
Пример такого "мешающего" индекса - автоиндекс на ключ "пол", где всего два значения М и Ж. Надо доказывать неэффективность такого индекса ?
А это зависит от распределения данных.
Каким образом неэффективность зависит для конкретного примера ? Миллион значение М и миллион Ж и больше ничего.
ТанИ если согласно распределению индекс не эффективен, тогда он не будет использоватся оптимизатором.
Тогда зачем он вообще нужен для конкретного случая использования по селекту ? Впрочем, об этом оптимизаторе я уже достаточно сказал.
Да, вспомнилась известная фраза из "Двенадцати стульев" - "Дворник не был близорук, но к очкам привык и носил их с удовольствием" :)
ТанЗато без индекса большой проблемой будут попытки изменить значения ключа в главной таблице. Или записи из нее удалить.
Это уже о другом. Не надо уходить в сторону и рассказывать о том, какие замечательные свойства вообще имеют индексы.

Журавлев Денис Евгений ФадеевЯ так понимаю что речь шла об использовании индексов оптимизатором? Если в запросе будет присутствовать таблица, размер которой <=2(4,,n)кб (размер блока), оптимизатор будет рассматривать для каждого такого запроса множество планов, в этом множестве будут планы, в которых будут использоваться индексы на эту крохотную таблицу, но в подавляющем большинстве случаев эти планы будут "дороже" чем планы с seq scan этой таблицы, поэтому при выполнении запроса для доступа к данным таблицы -- индексы использоваться не будут.
Я ранее уже рассказал, что были реальные примеры, когда такие индексы использовались и сильно мешали. К тому же, если бы таких индексов не было. то оптимизатору надо было бы рассматривать меньше вариантов, а в случае, когда количество таблиц в соединении превышает 6-8 штук, то время "размышления" оптимизатора может сильно увеличиваться, т.к. число вариантов растет не слабо. Не зря Информикс ввел Optimization low и рекомендует применять его на соединениях, где 5 таблиц и более ...

Евгений Фадеев vasilisНо все же, если сравнивать в общем, то я бы все таки оставил принудительное построение индексов, т.к. это намного лучше, чем когда люди вообще не строят индексы на ключах (видел пару раз такие промышленные БД на Оракле, когда у заказчика через несколько месяцев эксплуатации "вдруг" все начинало жутко тормозить, а у разработчика на малых объемах летало).А вот здесь я не соглашусь (впрочем это сугубо субъективно), так как это вопрос ориентации инструмента. Если он ориентирован на специалистов - то любая автоматичность должна быть настраиваемой. Мне ближе более строгий подход. ююю
Наверное, не более строгий (вот такой то он как раз у Информикс), а более гибкий, говоря вашими же словами "любая автоматичность должна быть настраиваемой". Тут трудно не согласиться. Лучше бы были реализованы обе стратегии с возможностью их выбора. Причем это верно для многих случаев.
Например, если место в dbspace заканчивается, то стоит его автоматически расширять или нет ? Зависит от ситуации и психологии админа. В разных СУБД реализовано по разному, но мне хотелось бы _выбирать_ нужную стратегию. Но тогда Информикс превратился бы в Оракл и уже не был бы таким простым и маленьким.

Тан vasilis Тан vasilis
Пример такого "мешающего" индекса - автоиндекс на ключ "пол", где всего два значения М и Ж. Надо доказывать неэффективность такого индекса ?
А это зависит от распределения данных.
Каким образом неэффективность зависит для конкретного примера ? Миллион значение М и миллион Ж и больше ничего.
а если у нас в таблице миллион М и тысяча Ж?
А если миллион Ж и ни одного М ? А если... К чему эти вопросы ?
Не станете же вы утверждать, что в вашем случае распределения на среднестатистических запросах эффективность индекса сильно возрастет ?
Я же не убеждаю вас не использовать индексы, потому что может быть всего один частый запрос в прикладной системе, ради которого индекс и строится, а показываю, что индексы МОГУТ БЫТЬ НЕЭФФЕКТИВНЫМИ. Есть способы (возможности) увеличивать эффективность (скорость и полезность) таких индексов. Например, для данного случая обычно с указателем пола вытягивается и год рождения, поэтому можно сделать составной индекс год_рождения, пол и т.о. увеличить скорость работы индекса (как при поиске, так и при изменениях).