Зачем нужен уникальный index, содержащий RecId?

[fed]

Цитата:

Сообщение от AndyD

По-этому, о том, что, к примеру, номер журнала в его шапке является уникальным, он знает. И уточнение статистики по дополнительным компаниям ничего к этому знанию не прибавит.

Возможно сильно разное распределение ключей по компаниям. Например у нас одна компания очень активно используется для сводного, и в ней в reqTrans полмиллиона записей, принадлежащих 7 разным планам. Из оставшихся компаний, парочка имеет только по одному плану и еще штук шесть просто не используются для сводного. Поэтому в зависимости от компании, условие dataareaid==_dataareaid && reqPlanId==_reqplanId обладают разной селективностью. Подозреваю что если у тебя есть статистика по reqPlanId без условия по коду компании, то отимизатор будет чаще промахиваться и выбирать некорректный план. (Скажем - выборку по коду плана, для тех компаний в которых он только один)...

[AndyD]

Цитата:

Сообщение от fed

Возможно сильно разное распределение ключей по компаниям. Например у нас одна компания очень активно используется для сводного, и в ней в reqTrans полмиллиона записей, принадлежащих 7 разным планам. Из оставшихся компаний, парочка имеет только по одному плану и еще штук шесть просто не используются для сводного. Поэтому в зависимости от компании, условие dataareaid==_dataareaid && reqPlanId==_reqplanId обладают разной селективностью. Подозреваю что если у тебя есть статистика по reqPlanId без условия по коду компании, то отимизатор будет чаще промахиваться и выбирать некорректный план. (Скажем - выборку по коду плана, для тех компаний в которых он только один)...

Ну, моя реплика была в ответ на слова Logger, как я их понял

А вообще, дополнительная статистика по компаниям имеет смысл, если значение поля имеет разную селективность в разных компаниях (применительно к Аксапте)

[Logger]

Цитата:

Сообщение от AndyD

А вообще, дополнительная статистика по компаниям имеет смысл, если значение поля имеет разную селективность в разных компаниях (применительно к Аксапте)

Так я про это и написал !

[mazzy]

возможно, связанный вопрос.
А, может быть, совсем другой ax2009. зачем нужно создавать индекс по recID, если включены CreatedDateTime или ModifiedDateTime?

[AndyD]

Цитата:

Сообщение от S.Kuskov

Получается что от уникальности индекса только одна польза причём без каких-либо затрат?

Действительно. Предлагаю, обсудив преимущества уникальных индексов, найти теперь у них ограничения,не позволяющие использовать их повсеместно.

Как-то у вас все смешалось

Я говорил, что для СУЩЕСТВУЮЩЕГО индекса включение уникальности ничего не добавляет в плане ухудшения производительности.

Если речь идет о добавлении ДОПОЛНИТЕЛЬНОГО (дополнительных) столбца для обеспечения более высокой скорости вставки/изменения, то надо рассматривать два варианта.

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

Второй - у таблицы нет кластерного индекса. Соответственно, все индесные записи ссылаются на файл с данными, номер страницы в нем и номер записи в странице (все это представляется в виде 64-х битного поля), так называемый Heap RID. Так вот, этот самый Heap RID выступает в этом случае, как еще одно индексное поле, данные отсортированы в том числе и по нему. Т.е. если мы вставляем или изменяем записи в таблице, поиск по индексу идет в том числе и по Heap RID. И добывление полей, так же будет вести к увеличению размера хранимых данных


В общем, мое резюме
Не будет выигрыша. Даже для низкоселективных индексов
А проигрыш будет.

Да, еще добавлю.
Я пишу про MS SQL (по крайней мере, от 2005-го). Для Oracle, возможно, соображения будут другие

[fed]

А где ты прочитал что входы в индексных страницах (в смысле у обычных индексов), отстортированы по ключу кластерного индекса или Heap RID?
Просто я в свое время пытался эту информацию найти и не смог...

[AndyD]

Ну, я не прочитал, а наблюдал
DBCC PAGE рулит

А из практических соображений - у нас отключен двухвалютный склад и в таблице InventTrans индекс OpenSecCurItemIdx_RU вырождается в индекс по DataAreaId и ItemId. При наличии в таблице нескольких сот записей и на каждую номенкларуту по несколько десятков тысяч проводок - вставка с перебором по значению ключа будет превращаться в очень-очень длительный процесс.
Чего не происходит на практике (замечу, что есть индексы в этой же таблице, основанные на одном значении поля)

[fed]

Кстати, я тут еще подумал: Чтобы гарантировать отсортированность индексных входов по Heap RID/Кластерному ключу (это помимо подразумеваемой сортировки по основному набору индексных ключей), нам бы пришлось значения RID/Cluster keys тащить на индексные страницы второго и последующих уровней (на которых по классике, кроме значений ключа и ссылок на страницы предыдущего уровня не должно быть). Как-то это сильно противоречит тому, что в открытых источниках пишут по поводу организации индексов в SQL Server и вообще B-деревьев...

[fed]

Цитата:

Сообщение от AndyD

Ну, я не прочитал, а наблюдал
DBCC PAGE рулит

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

[AndyD]

Несколько иллюстраций к моим словам

Таблица VendnvoiceJour, индекс VatNumIdx (индекс стандартный, кластерного ключа нет)


Root Page для индекса. DataAreaId одинаково на рисунке


Child Page для выделенной строки из Root Page.


Последний уровень дерева индексных страниц, поиск идет до этой страницы. Дальше - ссылка непосредственно на данные.

Как видите, в данном случае, для поиска индексного входа достаточно просмотреть три страницы

Предположение, что это следстиве перестройки индекса - неверно. На рабочей базе индексы не пересчитываются, однако такую картину наблюдаю на всех таблицах без кластерного индекса.

Прошу прощения, картинки из личного альбома не показывались всем. Пофиксил

[fed]

Охотно верю, что в рамках одной страницы, индексные входы отсортированы по HEAP RID. Но совсем не уверен, что они отсортированы в пределах нескольких страниц. Как я уже написал, для того чтобы достигнуть такого эффекта, в нелистовые страницы пришлось бы тащить кроме RID листовой страницы, еще и максимальный (ну или минимальный) RID ДАННЫХ, на которые листовая страница ссылается.

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

[fed]

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

[AndyD]

Цитата:

Сообщение от fed

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

А почему вставка должна замедлиться?
Давай рассмотрим процесс?

Сначала, вставку куда получится

У нас есть 10тыс. уже вставленных значений индекса.
По ключу, мы хватаем первую страницу, но она уже заполнена (допустим, на страницу влазиет по 300 строк).
Ладно, хватаем следующую. Опять облом.
И так повторяем, пока не дойдем до последней (или берем ее сразу? Но тогда, как заполнять незанятое место?).

Получается, для предложенных условий, дергаем 34 страницы + количество уровней - 1 ( и это я еще не учел возможный переход по страницам верхних уровней). В самом оптимистическом случае количество страниц равно количеству уровней


Теперь, вставка при отсортированном Heap RID (или кластерном ключе)

Ну, тут очевидно, что открыто будет ВСЕГДА одно и тоже количество страниц, равное количеству уровней.

Ну и где здесь преимущество первого варианта?


По поводу окончания места в странице нижнего уровня.

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

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

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


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

[fed]

Если у тебя стоит FillFactor не равный 100, то есть большие шансы что подходящая страница будет найдена до просмотра всех страниц с данным ключем. Особенно если у нас индекс обновлялся и страницы малость фрагментированы и свободное место есть. При этом вероятность сплита (из за того что единственная нужная нам страница была на 100% заполнена и пришлось ее сплитить), в моем случае заметно меньше (потому что больше шансов найти страничку со свободным местом). А сплит страниц - штука трудоемкая. В максимально плохом случае может понадобится куда больше чм 34 страницы прочитать и прописать.

А еще - можно меня долго агитировать за советскую власть, но я тебе не поверю пока ты мне не расскажешь, как обеспечивается сортировка по RID данных для страниц ненулевого уровня Вот если у меня есть 10000 записей и 34 страницы с данным ключем, как мне при чтении страницы первого (то есть - первого не листового уровня) понять - в какую из листовых страниц мне вставлять очередную запись? То есть - правило железное: Если ты хочешь сортировать дерево по какому-то признаку, ты должен включить этот признак в ключ. Соответственно - для случая сортировки по RID страницы данных, нам надо этот RID включить в ключ данных и тащить на нелистовые страницы... Судя по тому, что я читал об индексах в SQL Server,в страницы 1ого и более высоких уровней, ключи кластерного индекса/RID данных не тянутся...

[AndyD]

Почему сразу агитирую?
Ты высказываешь контраргументы - я пытаюсь их парировать и привожу в свою очередь аргументы

По поводу присутствия RID (или кластерного ключа) на каждой странице - так оно и есть, на самом деле. Взгляни на иллюстрации. Три уровня дерева. На каждом уровне есть RID

[AndyD]

Еще немного иллюстраций

Таблица неаксаптовская. Сервер совершенно отличается от того, на котором делались предыдущие скрины

В таблице есть кластерный ключ по полю LAUNCH_NUM (неуникальный)

Иллюстрации относятся к индексу по полю ALLOCATION_RULE. Обратите внимание на добавление столбца UNIQUIFIER, добавляемого SQL-сервером к неуникальным кластерным ключам


Root Page


Страница второго уровня


Страница третьего уровня (последний) - так как таблица имеет кластерный индек, то ссылка идет не на страницу с данными (Heap RID в предыдущем случае), а на полный кластерный ключ.

[belugin]

страница второго уровня вроде не отсортирована по UNIQUEFIER(...

[AndyD]

Если ты про четырнадцатую строку, то там LAUNCH_NUM поменялся. Т.е. сортирвка - по порядку перечисления полей слева направо.

[fed]

Гм. Поигрался с DBCC IND и DBCC PAGE. Вроде бы выводы AndyD подтверждаются. Только все равно - как-то это очень и очень несовпадает с тем что пишут во всех книжках по индексной организации в SQL Server. И получается, что я, таким макаром, могу создать индекс в котором больше 16 полей ? Типа создать кластерный индекс по 5 полям, потом поверх него - некластерный по еще 16 полям. И внутри все будет отсортировано по 21 полю ?

Непонятно тогда, почему осталось ограничение на число полей в индексе...

Может все-таки DBCC PAGE для удобства просмотра умудряется подспудно выбирать RID/Кластерный ключ из листовой страницы, на которую ссылается данная страница?

[AndyD]

А я добавлю еще один гвоздь

Уникальные индексы НЕ ДОБАВЛЯЮТ дополнительные столбцы в корневую и промежуточные страницы. При этом, если таблица не имеет кластерный индекс, то на последнем уровне есть информация об Heap RID. Для таблицы с кластерным индексом на последнем уровне добавляется информация из кластерного ключа.

Замечу, что эти стобцы не помечены как ключевые "(key)", в отличие от неуникальных индексов

Так же, DBCC может выводить дампы памяти, в которых видны данные всех записанных столбцов (в том числе, Heap RID или кластерный ключ). Не составляет никакого труда найти данные в файле по этому дампу и убедиться, что все честно