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

[S.Kuskov]

На некотроых таблицах уровня SYS наблюдается наличие уникального индекса в состав, которого по мимо прочих полей входит поле RecId (например индекс ItemIdx на таблице InventItemBarcode). Известно что если не включать RecId в индекс и не делать его уникальным, то система по умолчанию на уровне БД сама создаст его (Проблема с индексами).

Отсюда вопрос. Какой смысл вручную создавать такой абсурдный с точки зрения приложения индекс (уникальность RecId - это всё же забота ядра и БД). И не в этом ли кроются ошибки, такие как Существуют аргументы, почему неуникальность баркода у номенклатуры не является багой?

Т.е. на мой взгляд, уникальные индексы необходимы для ограничения дубликатов строк, а какой в них толк, если они содержат поле RecID, уникальное априори? Следовательно либо требование уникальности здесь избыточно, либо наличие в уникальном индексе поля RecId является ошибкой проектирования, вызванной попыткой уменьшить число индексов на таблице.

[ZVV]

Цитата:

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

На некотроых таблицах уровня SYS наблюдается наличие уникального индекса в состав, которого по мимо прочих полей входит поле RecId (например индекс ItemIdx на таблице InventItemBarcode). Известно что если не включать RecId в индекс и не делать его уникальным, то система по умолчанию на уровне БД сама создаст его (Проблема с индексами).

Отсюда вопрос. Какой смысл вручную создавать такой абсурдный с точки зрения приложения индекс (уникальность RecId - это всё же забота ядра и БД). И не в этом ли кроются ошибки, такие как Существуют аргументы, почему неуникальность баркода у номенклатуры не является багой?

Т.е. на мой взгляд, уникальные индексы необходимы для ограничения дубликатов строк, а какой в них толк, если они содержат поле RecID, уникальное априори? Следовательно либо требование уникальности здесь избыточно, либо наличие в уникальном индексе поля RecId является ошибкой проектирования, вызванной попыткой уменьшить число индексов на таблице.

Вы же сами ответили на свой вопрос...
Здесь уже писал об алгоритме: Не выделять RecId

Т.е. это добавление идёт только в случае когда в таблице нет вообще индексов уникальных.. А уникальный индекс Аксапте нужен...

Например для работы .reread()...

[S.Kuskov]

Цитата:

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

Т.е. это добавление идёт только в случае когда в таблице нет вообще индексов уникальных.. А уникальный индекс Аксапте нужен...

Возможно я что-то путаю, но разве авотматическое добаление RecId в индекс не ограничивается лишь уровенем БД? В смысле меняется ли автоматически индекс и в самом приложении (АОТ)? Если меняется, то в какой момент? при сохранении таблицы? при синхронизации?

[S.Kuskov]

И ещё возник такой вопрос. Если уникальность RecId контролируется составным индексом, где помимо RecId присутствуют ещё и другие поля, то уникальность отдельно самого поля RecId, вообще говоря, уже не обеспечивается. Не является ли это потенциальным источником ошибок?

[fed]

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

[ZVV]

Цитата:

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

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

Аксапта, насколько я знаю, так не работает (перебирает неуникальные индексы) по указанной выше причине - всегда есть уникальный индекс и она его использует. В частности да, при выполнении .update() или .delete(), в чём можно убедиться, включив лог операторов SQL.

Или это было о том как теоретически могло бы быть?

[fed]

Цитата:

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

Аксапта, насколько я знаю, так не работает (перебирает неуникальные индексы) по указанной выше причине - всегда есть уникальный индекс и она его использует. В частности да, при выполнении .update() или .delete(), в чём можно убедиться, включив лог операторов SQL.

Или это было о том как теоретически могло бы быть?

А это вообще не на аксаптовском уровне происходит. И даже не на уровне трейсинга SQL-операторов в SQL Profiler. Вот представь себе: У тебя есть таблица персонала. Есть куча индексов, один из которых по полю Пол. Ты говоришь - удалить сотрудника с employeeId=='Иванов И.И.'. Система находит по индексу emplIdIdx физический адрес записи и фетчит ее. Далее - надо удалить из всех индексов ключи, которые на эту запись ссылаются. Система рассчитывает значения индексных ключей (по данным из записи) и пытается найти и удалить все индексные ключи. Для этого она ПЕРЕБИРАЕТ все индексные ключи со значением равным вычисленному до тех пор, пока не наткнется на ключ, ссылающийся на нужную запись (то есть с сохраненным в индексном входе Row_Id==Row_ID нашей записи). Если индекс уникален, то этот перебор не требуется. Если относительно уникален (ну скажем - номер паспорта без серии) - то перебор будет недолгим. А вот если это индекс по полю типа Пол (два возможных значения) - перебор будет медленным и печальным. Собственно - по этому в книжках и не советуют строить индексы по полям с 2-5-10 возможными значениями - обновление такой индекс затормозит, а при выборке редко будет нужен. Но тем не менее - иногда приходится строить индексы по достаточно часто повторяющемуся полю. Даже если на каждое значение будет приходиться порядка 200-300 записей - все равно обновление тормозить будет изрядно.

Насколько я знаю - некоторые БД пытались решить эту проблему за счет того, что индекс подспудно сортировался по сочетанию ключ+физический адрес записи (ROW_ID). (То есть - значение ссылки на запись становилась некой виртуальной частью ключа). Однако - на практике это приводило к изрядным проблемам, поскольку приводило к усиленной перебалансировке дерева страниц при вставке новых записей. Кроме того - при реорганизации и упаковке таблиц, это усложняло перестроение индексов.
Так что - насколько я понимаю, в текущих версиях и SQL Server и Oracle используется именно такой подход к удалению ключей, который я описал...

[ZVV]

petr, fed -- теперь я понял о чём вы, это всё понятно и верно, но непонятно какое отношение к исходному вопросу?

[ZVV]

Цитата:

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

Возможно я что-то путаю, но разве авотматическое добаление RecId в индекс не ограничивается лишь уровенем БД? В смысле меняется ли автоматически индекс и в самом приложении (АОТ)? Если меняется, то в какой момент? при сохранении таблицы? при синхронизации?

не путаете, в Аот - не сохраняет, тем не менее где-то запоминает...
можете попробовать сами: на таблице с индексом по Field1 (неуникальным) в случае .update() на БД уйдёт запрос вида:

UPDATE TABLE2 SET FIELD1=?,RECVERSION=? WHERE ((((DATAAREAID=?) AND (FIELD1=?)) AND (RECID=?)) AND (RECVERSION=?))

Цитата:

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

И ещё возник такой вопрос. Если уникальность RecId контролируется составным индексом, где помимо RecId присутствуют ещё и другие поля, то уникальность отдельно самого поля RecId, вообще говоря, уже не обеспечивается. Не является ли это потенциальным источником ошибок?

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

[fed]

Цитата:

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

petr, fed -- теперь я понял о чём вы, это всё понятно и верно, но непонятно какое отношение к исходному вопросу?

Топикстартер спрашивает - зачем создавать индексы с фиктивной уникальностью, добавляя поле recid, если в соответствии с информацией из сообщения Проблема с индексами

система сама добавляет поле recId в первый попавшийся индекс и делает его уникальным ?

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

Ну и гипотеза насчет coverage index тоже весьма правдоподобна.
Таким образом - ручное добавление recId в конец некоторых индексов вызвано не необходимостью отслеживания уникальности, а возможностью повысить производительность работы сервера БД с данным индексом.

[belugin]

Еще есть такой прием (в одной книжке по ораклу вычитал) как overindexing - в индекс добавляется поле, которое нужно выбирать при выборке по индексу. Таким образом не нужна операция по подъему из базы страниц с данными - значение берется из индекса.

то есть если будет запрос

select recID from T where T.ItemID = 'z'

то будет выбрана одна страница с индексом, а не сначала страница с индексом, а потом с страница с данными самой таблицы.

[ZVV]

Цитата:

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

Еще есть такой прием (в одной книжке по ораклу вычитал) как overindexing - в индекс добавляется поле, которое нужно выбирать при выборке по индексу. Таким образом не нужна операция по подъему из базы страниц с данными - значение берется из индекса.

то есть если будет запрос

select recID from T where T.ItemID = 'z'

то будет выбрана одна страница с индексом, а не сначала страница с индексом, а потом с страница с данными самой таблицы.

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

Кстати в SQL2005 есть новая фича, называется "Included columns", тоже на эту тему... Правда вот Аксапте это вряд ли поможет

[belugin]

Цитата:

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

Первый раз слышу такой термин, если честно...

может и напутал.

[Андре]

Цитата:

Насколько я знаю - некоторые БД пытались решить эту проблему за счет того, что индекс подспудно сортировался по сочетанию ключ+физический адрес записи (ROW_ID). (То есть - значение ссылки на запись становилась некой виртуальной частью ключа). Однако - на практике это приводило к изрядным проблемам, поскольку приводило к усиленной перебалансировке дерева страниц при вставке новых записей. Кроме того - при реорганизации и упаковке таблиц, это усложняло перестроение индексов.
Так что - насколько я понимаю, в текущих версиях и SQL Server и Oracle используется именно такой подход к удалению ключей, который я описал...

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

Правда для того, чтобы Аксапта создавала такие индексы ее придется слегка допилить.

[fed]

Цитата:

Сообщение от Андре

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

Правда для того, чтобы Аксапта создавала такие индексы ее придется слегка допилить.

Ну bitmap индексы хороши в тех случаях когда у тебя совсем мало возможных значений у индексного поля - в пределах 10-20. А вот в ситуации когда у тебя в таблице миллион записей и тысяча возможных значений - bitmap слишком тяжел, а обычный b-tree индекс не уникальный слишком тормозит.

[ZVV]

Цитата:

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

Ну bitmap индексы хороши в тех случаях когда у тебя совсем мало возможных значений у индексного поля - в пределах 10-20. А вот в ситуации когда у тебя в таблице миллион записей и тысяча возможных значений - bitmap слишком тяжел, а обычный b-tree индекс не уникальный слишком тормозит.

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

[Андре]

Цитата:

Еще есть такой прием (в одной книжке по ораклу вычитал) как overindexing - в индекс добавляется поле, которое нужно выбирать при выборке по индексу.

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

[SRF]

Цитата:

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

Отсюда вопрос. Какой смысл вручную создавать такой абсурдный с точки зрения приложения индекс (уникальность RecId - это всё же забота ядра и БД).

Первое, что не стоит забывать так это то, что ядро автоматически в любой select добавляет поле RecId, соотвествено такой индекс как написал Андре будет покрывающим (covering index)(в случае выбора только полей индекса без RecId), но стиль программирования в AX не тот, чтобы задумываться об использовании покрывающих индексов (как правило фетчится весь курсор), поэтому ради этого не стали бы создавать данный индекс.

Второе - в AX директива index в запросе не игнорируется и записи сортируются в порядке полей индекса.

Цитата:

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

И ещё возник такой вопрос. Если уникальность RecId контролируется составным индексом, где помимо RecId присутствуют ещё и другие поля, то уникальность отдельно самого поля RecId, вообще говоря, уже не обеспечивается. Не является ли это потенциальным источником ошибок?

В AX 3.0 маловероятно, поскольку RecId должен быть уникален в разрезе всей компании, а не только таблицы, а вот в DAX 4.0 и старше проблема уже представляется более серьезно

P.S. Насколько мне известно термин "overindexing" означает "перегруженность" таблицы индексами

[lev]

Цитата:

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

В AX 3.0 маловероятно, поскольку RecId должен быть уникален в разрезе всей компании, а не только таблицы

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

[ZVV]

Цитата:

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

Цитата:

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

В AX 3.0 маловероятно, поскольку RecId должен быть уникален в разрезе всей компании, а не только таблицы

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

Редко такое встречали говорите... ))))
Было бы интересно взглянуть хоть на одного...

Включение потабличного RecID в 3-ке