Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Oracle Новый топик    Ответить
Топик располагается на нескольких страницах: Ctrl  назад   1 2 3 [4] 5   вперед  Ctrl      все
 Re: Реализация уникальности без ключей и индексов  [new]
graycode
Member

Откуда:
Сообщений: 468
НеофитSQL
Цель была - понять есть ли в языке достаточно возможностей для реализации несложных constraints по колонке.

Есть, инкапсулируешь логику работы с сущностью в пакете и не даешь прямого доступа к таблице. Триггеры, как ты уже убедился, не самый лучший способ реализации бизнес логики.
17 ноя 20, 14:40    [22233791]     Ответить | Цитировать Сообщить модератору
 Re: Реализация уникальности без ключей и индексов  [new]
andrey_anonymous
Member

Откуда: Москва
Сообщений: 18398
НеофитSQL
Эффективно - значит без table lock или строгой сериализации доступа к таблице.

Материализованное представление с первичным ключем и check constraint в режиме refresh fast on commit.
17 ноя 20, 14:43    [22233798]     Ответить | Цитировать Сообщить модератору
 Re: Реализация уникальности без ключей и индексов  [new]
НеофитSQL
Member

Откуда: Маями
Сообщений: 760
graycode
НеофитSQL
Цель была - понять есть ли в языке достаточно возможностей для реализации несложных constraints по колонке.

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


Извините, это общие слова.
PL/SQL он одинаковый, что в триггере что в функции.

Например, я реализую constraint двуникальности.

Допустим я готов сделать таблицу недоступной, тем самым поломав парадигму SQL.
Я умею написать пакет с функциями, но я не умею:

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

Constraint по колонке - это непростая в целом задача для многопользовательского режима.
17 ноя 20, 14:55    [22233813]     Ответить | Цитировать Сообщить модератору
 Re: Реализация уникальности без ключей и индексов  [new]
НеофитSQL
Member

Откуда: Маями
Сообщений: 760
andrey_anonymous
НеофитSQL
Эффективно - значит без table lock или строгой сериализации доступа к таблице.

Материализованное представление с первичным ключем и check constraint в режиме refresh fast on commit.


Первичный ключ не даст мне вставить второе значение, или я чего-то не понял.
17 ноя 20, 14:56    [22233815]     Ответить | Цитировать Сообщить модератору
 Re: Реализация уникальности без ключей и индексов  [new]
SY
Member

Откуда: Middlebury, CT USA
Сообщений: 10051
andrey_anonymous

Материализованное представление с первичным ключем и check constraint в режиме refresh fast on commit.


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

SY.
17 ноя 20, 15:14    [22233845]     Ответить | Цитировать Сообщить модератору
 Re: Реализация уникальности без ключей и индексов  [new]
andrey_anonymous
Member

Откуда: Москва
Сообщений: 18398
SY
А зачем с первичным ключем? Ну и следует упомянуть это будет имитация отложенного ключа.

Для упрощения жизни - mat.view все-таки.
...deferred - да.
17 ноя 20, 15:20    [22233854]     Ответить | Цитировать Сообщить модератору
 Re: Реализация уникальности без ключей и индексов  [new]
SY
Member

Откуда: Middlebury, CT USA
Сообщений: 10051
andrey_anonymous

Для упрощения жизни - mat.view все-таки.


A что GROUP BY может выдать дубли?

SY.
17 ноя 20, 15:26    [22233866]     Ответить | Цитировать Сообщить модератору
 Re: Реализация уникальности без ключей и индексов  [new]
graycode
Member

Откуда:
Сообщений: 468
НеофитSQL,

А кто тебе сказал, что реальная бизнес логика должна решаться исключительно парадигмой SQL, и что такое парадигма SQL?

Если массовые вставки не планируются и вторых значений не много, то можно инкапсулировать вставку в триггере на вьюхе или процедуре, добавить поле номер двууникального значения и сделать уникальный ключ, вставка в процедуре/триггере off вьюхи идет с номером 1, если ошибка по уникальности то повторяем вставку с номером 2, если все равно ошибка по уникальности, то прокидываем ее наверх.
17 ноя 20, 15:34    [22233878]     Ответить | Цитировать Сообщить модератору
 Re: Реализация уникальности без ключей и индексов  [new]
НеофитSQL
Member

Откуда: Маями
Сообщений: 760
graycode
НеофитSQL,

А кто тебе сказал, что реальная бизнес логика должна решаться исключительно парадигмой SQL, и что такое парадигма SQL?

Если массовые вставки не планируются и вторых значений не много, то можно инкапсулировать вставку в триггере на вьюхе или процедуре, добавить поле номер двууникального значения и сделать уникальный ключ, вставка в процедуре/триггере off вьюхи идет с номером 1, если ошибка по уникальности то повторяем вставку с номером 2, если все равно ошибка по уникальности, то прокидываем ее наверх.


> что такое парадигма SQL?
Я имел в виду, когда к данным применимы табличные операции.

Если я правильно понял ваше решение, оно полагается на составной PK из двух колонок - одно для значения, второе для счетчика (1-2).
17 ноя 20, 16:15    [22233922]     Ответить | Цитировать Сообщить модератору
 Re: Реализация уникальности без ключей и индексов  [new]
graycode
Member

Откуда:
Сообщений: 468
НеофитSQL
Я имел в виду, когда к данным применимы табличные операции.

К представлениям применимы табличные операции.

НеофитSQL
Если я правильно понял ваше решение, оно полагается на составной PK из двух колонок - одно для значения, второе для счетчика (1-2).

Да.
17 ноя 20, 16:22    [22233929]     Ответить | Цитировать Сообщить модератору
 Re: Реализация уникальности без ключей и индексов  [new]
НеофитSQL
Member

Откуда: Маями
Сообщений: 760
graycode
НеофитSQL
Я имел в виду, когда к данным применимы табличные операции.

К представлениям применимы табличные операции.


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

Так что ли? И как мне "прятать" исходную таблицу, чтобы руками не лезли, а только через представление?
17 ноя 20, 16:35    [22233942]     Ответить | Цитировать Сообщить модератору
 Re: Реализация уникальности без ключей и индексов  [new]
Вячеслав Любомудров
Member

Откуда: Владивосток
Сообщений: 18486
НеофитSQL
Вопрос по теме: как в Оракле можно эффективно реализовать constraint двуникальности?
Чтобы в одной колонке находилось не более двух одинаковых значений?
ЗадачкаНасколько помню, все закончилось тем, что это жутко неэффективно, если вообще возможно (покрывает все случаи)

Ну и я таки разочаровался в твоей адекватности
17 ноя 20, 16:41    [22233947]     Ответить | Цитировать Сообщить модератору
 Re: Реализация уникальности без ключей и индексов  [new]
НеофитSQL
Member

Откуда: Маями
Сообщений: 760
Вячеслав Любомудров
НеофитSQL
Вопрос по теме: как в Оракле можно эффективно реализовать constraint двуникальности?
Чтобы в одной колонке находилось не более двух одинаковых значений?
ЗадачкаНасколько помню, все закончилось тем, что это жутко неэффективно, если вообще возможно (покрывает все случаи)


С удовольствием почитал 15-летнюю тему, где все еще были молодые, решали задачки и не боялись ошибаться :)
Да, там было много решений, но ни одно так и не оказалось полным.
Решение с построчным dbmslock мне понравилось больше всего, т.к. у меня похоже, только я использую ITL locks, которых больше.
17 ноя 20, 17:34    [22234002]     Ответить | Цитировать Сообщить модератору
 Re: Реализация уникальности без ключей и индексов  [new]
НеофитSQL
Member

Откуда: Маями
Сообщений: 760
graycode
НеофитSQL
Если я правильно понял ваше решение, оно полагается на составной PK из двух колонок - одно для значения, второе для счетчика (1-2).

Да.


К сожалению, такое не работает для двух сессий. Жаль, выглядит просто и понятно (как это обычно и происходит в мире с одной сессией).
17 ноя 20, 17:35    [22234005]     Ответить | Цитировать Сообщить модератору
 Re: Реализация уникальности без ключей и индексов  [new]
andrey_anonymous
Member

Откуда: Москва
Сообщений: 18398
НеофитSQL
я использую ITL locks, которых больше.

Вы уверены что понимаете что есть ITL?
А решение есть - и оно Вам было озвучено и тут, и там. Mat.view Refresh on commit + constraint на mat.view.
17 ноя 20, 17:37    [22234007]     Ответить | Цитировать Сообщить модератору
 Re: Реализация уникальности без ключей и индексов  [new]
graycode
Member

Откуда:
Сообщений: 468
НеофитSQL
К сожалению, такое не работает для двух сессий. Жаль, выглядит просто и понятно (как это обычно и происходит в мире с одной сессией).

У тебя штатный PK перестал работать для двух сессий?

Просто и понятно, но катастрофически для производительности при массовых вставках или большом количестве дублей.
17 ноя 20, 18:39    [22234068]     Ответить | Цитировать Сообщить модератору
 Re: Реализация уникальности без ключей и индексов  [new]
НеофитSQL
Member

Откуда: Маями
Сообщений: 760
andrey_anonymous
НеофитSQL
я использую ITL locks, которых больше.

Вы уверены что понимаете что есть ITL?
А решение есть - и оно Вам было озвучено и тут, и там. Mat.view Refresh on commit + constraint на mat.view.


Я услышал про Mat.view Refresh on commit. Там совершенно другое поведение, запоздалое, с уникальными граблями.
https://dnikiforov.wordpress.com/2011/08/25/materialized-view-and-unique-constraints/

Кроме того, в этой теме обсуждается реализация unique constraint без использования встроенного, прикрепленного к чему-то другому.
17 ноя 20, 18:59    [22234085]     Ответить | Цитировать Сообщить модератору
 Re: Реализация уникальности без ключей и индексов  [new]
graycode
Member

Откуда:
Сообщений: 468
НеофитSQL
Кроме того, в этой теме обсуждается реализация unique constraint без использования встроенного, прикрепленного к чему-то другому.

Ты до сих пор носишься с этой бессмысленной идеей? В PL/SQL нет средств построения своих хранимых, независящих от таблиц индексов и блокировки их листов, поэтому решить эффективно можно только пользуясь стандартными индексами.
17 ноя 20, 19:09    [22234092]     Ответить | Цитировать Сообщить модератору
 Re: Реализация уникальности без ключей и индексов  [new]
НеофитSQL
Member

Откуда: Маями
Сообщений: 760
graycode
НеофитSQL
К сожалению, такое не работает для двух сессий. Жаль, выглядит просто и понятно (как это обычно и происходит в мире с одной сессией).

У тебя штатный PK перестал работать для двух сессий?

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


Я думал о следующем примере:

сессия1> insert into two_max values ('aa');
1 row inserted
------------------------------------------
сессия2> insert into two_max values ('aa');
(подвис на попытке вставить аа:1)


Неоправданное блокирование второй сессии.
17 ноя 20, 19:13    [22234097]     Ответить | Цитировать Сообщить модератору
 Re: Реализация уникальности без ключей и индексов  [new]
SY
Member

Откуда: Middlebury, CT USA
Сообщений: 10051
НеофитSQL

Неоправданное блокирование второй сессии.


А что, при обычной уникальности не висит?

SY.
17 ноя 20, 19:24    [22234104]     Ответить | Цитировать Сообщить модератору
 Re: Реализация уникальности без ключей и индексов  [new]
НеофитSQL
Member

Откуда: Маями
Сообщений: 760
graycode
НеофитSQL
Кроме того, в этой теме обсуждается реализация unique constraint без использования встроенного, прикрепленного к чему-то другому.

Ты до сих пор носишься с этой бессмысленной идеей? В PL/SQL нет средств построения своих хранимых, независящих от таблиц индексов и блокировки их листов, поэтому решить эффективно можно только пользуясь стандартными индексами.


Это "hello world" для constraints по колонкам. Если я могу решить задачу уникальности, я могу решить общий случай, где критерий уникальности ключа является внешней функцией Distance(key1,key2) < М.

Например: на период пандемии натуральным id не разрешается соприкасаться (разница должна быть > 1) :)
Реализовать эффективный constraint на колонке, удовлетворяющий это правило.

Второй пример: для таблицы регистрации торговых знаков нужно реализовать constraint, который не позволит им быть слишком похожими друг на друга. Похожесть определяется функцией расстояния DistanceTM(clob, clob) (описание торгового знака содержится в колонке типа CLOB).

Третий пример: для улучшения диверсити лотереи гринкарты было решено отсеять "слишком похожих" кандидатов, соблюдая очередность поступления. Для этого DistanceGC(row,row) считает корреляцию по 300 параметрам, и кандидат "слишком похожий" на уже имеющихся в базе, считается "дупликатом".

Насколько я понимаю, в многосессионном режиме Оракл такое не умеет.
17 ноя 20, 19:36    [22234112]     Ответить | Цитировать Сообщить модератору
 Re: Реализация уникальности без ключей и индексов  [new]
НеофитSQL
Member

Откуда: Маями
Сообщений: 760
SY
НеофитSQL

Неоправданное блокирование второй сессии.


А что, при обычной уникальности не висит?

SY.


При обычной уникальности висит.

При "двуникальности" не должен, т.к. разрешено одно повторение.

При "стоникальности" подвисание 99 сессий без необходимости становится серьезным барьером для производительности.
17 ноя 20, 19:39    [22234115]     Ответить | Цитировать Сообщить модератору
 Re: Реализация уникальности без ключей и индексов  [new]
Кобанчег
Member

Откуда: Рахів
Сообщений: 841
НеофитSQL
Это "hello world" для constraints по колонкам. Если я могу решить задачу уникальности, я могу решить общий случай, где критерий уникальности ключа является внешней функцией Distance(key1,key2) < М.

Например: на период пандемии натуральным id не разрешается соприкасаться (разница должна быть > 1) :)
Реализовать эффективный constraint на колонке, удовлетворяющий это правило.

Второй пример: для таблицы регистрации торговых знаков нужно реализовать constraint, который не позволит им быть слишком похожими друг на друга. Похожесть определяется функцией расстояния DistanceTM(clob, clob) (описание торгового знака содержится в колонке типа CLOB).

Третий пример: для улучшения диверсити лотереи гринкарты было решено отсеять "слишком похожих" кандидатов, соблюдая очередность поступления. Для этого DistanceGC(row,row) считает корреляцию по 300 параметрам, и кандидат "слишком похожий" на уже имеющихся в базе, считается "дупликатом".

Насколько я понимаю, в многосессионном режиме Оракл такое не умеет.
Допустим ты решил простой случай через сериализацию.

Кстати, раньше один эксперт хорошо известный в узких кругах пытался продавать такой продукт.
В гугл - Oracle RuleGen
Сейчас он сайт удалил и многое выпилено (но при умении искать можно найти), а раньше была доступна вся документация и даже исходники.
Так вот "фишка" его решения была сериализация через dbms_lock.

Ключевое различие в "id не разрешается соприкасаться" и озученного тобой ранее что-то типа "расстояние Левенштейна не превышает N"
это то что в первом случае достаточно сравнить с двумя соседями а во втором со всеми записями в таблице.
Вот тут можно пытаться сводить проблему полного перебора к неполному с помощью domain indexes (как, например, сделано в Spatial).
17 ноя 20, 20:01    [22234130]     Ответить | Цитировать Сообщить модератору
 Re: Реализация уникальности без ключей и индексов  [new]
НеофитSQL
Member

Откуда: Маями
Сообщений: 760
Кобанчег

Ключевое различие в "id не разрешается соприкасаться" и озученного тобой ранее что-то типа "расстояние Левенштейна не превышает N"
это то что в первом случае достаточно сравнить с двумя соседями а во втором со всеми записями в таблице.


Для меня это не ключевое различие, это свойство функции расстояния, которое может повлиять на скорость.
На логику кода реализующего constraint уникальности влияет мало. Индекс это полезная, но ортогональная оптимизация.

Если строки короткие, я могу построить индекс в пространстве Левенштейна (а не линейный) и вместо полного перебора сравнивать только 2N соседей в N измерениях.

Мое желание скопировать поведение PK связано с тем, что оно хорошо отполировано и широко известно.
Лучше для общего случая я не придумаю.

Пока я решил для insert (в пределах возможностей моих 3-сессионных тестов).
Если не найду интереснее задачек, допилю delete/update.
17 ноя 20, 20:16    [22234144]     Ответить | Цитировать Сообщить модератору
 Re: Реализация уникальности без ключей и индексов  [new]
НеофитSQL
Member

Откуда: Маями
Сообщений: 760
Почитал про Oracle RuleGen, интересно.

Учитывая количество подводных камней для multirow constraints, было бы намного лучше чтобы такие задачи решались профессионально. Еще лучше - чтобы были встроены в БД производителем, или продавались как надстройка.

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

Свои цели в этом вопросе я достиг, обработку конфликтов в PK понимаю намного лучше чем пару дней назад, использовал select..for update почти по прямому назначению, и увидел что большинство решений тяготеют или к MV-on-commit или GTT+автономные транзакции (я использовал второе).
17 ноя 20, 20:40    [22234158]     Ответить | Цитировать Сообщить модератору
Топик располагается на нескольких страницах: Ctrl  назад   1 2 3 [4] 5   вперед  Ctrl      все
Все форумы / Oracle Ответить