Четыре вопроса по CONSTRAINT'ам

GrayCity
Дата: 11.06.2006 17:27:50
hi all.

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

1. Если на некий столбец указать ограничение NOT NIULL:
ALTER TABLE someTable MODIFY(someField  NOT NULL);
- то Оракл даст ему какое-то свое имя вида SYS_nnnnn.
Будут ли у меня траблы с этим именем позже при выполнении EXPDP и последующем IMPDP на другой машине ? Что там, на другой машине, будет с этим именем, если оно ранее было задействовано в другой таблице ?
Короче: нужно ли сразу переименовывать системно-присвоенные имена для таких ограничений ?

2. Есть шесть столбцов. Все они должны быть NOT NULL. Можно, конечно, сделать ALTER TABLE и указать по очереди для каждого столбца ограничение NOT NULL. А можно создать одно-единое CHECK-огранничение вида
field1 IS NOT NULL AND field2 IS NOT NULL AND field3 IS NOT NULL AND
field4 IS NOT NULL AND field5 IS NOT NULL AND field6 IS NOT NULL

Что лучше с точки зрения производительности ?

3. Есть много столбцов, на которые установлено ограничение внешнего ключа (т.е. это поля в дочерних таблицах, ссылающиеся на какие-то поля в родительских таблицах). Если просто задать ограничение вида FOREIGN KEY, то в такое поле можно вводить NULL-значения (что также явно говорится в Ora 10.2 Concepts Guide, page 384), но в этих столбцах NULL-ы НИКОГДА не должны попадать, таковы условия. Получается, что на каждое такое поле надо городить еще и второе CHECK-ограничение NOT NULL.

Вопрос: что будет с производительностью ? не заклинит ли при числе АКТИВНО работающих пользователей около 300, числе таблиц около 100 и при том, что в каждой таблице есть от 4-х до 8 таких "сильно ограниченных" столбцов ? (Linux-сервак, двухмоторный Pentium 2.4, RAID-5, RAM 2Гб)

Гуру, поделитесь, плз, как правильно делать такие схемы: нужно ли навешивать ограничения целостности "везде и всюду", или перекладывать какие-то проверки на триггера ?

4. В нескольких таблицах имеется по три одинаковых столбца с одинаковыми характеристиками: размером NUMBER(7,2) и с CHECK-требованием
NOT NULL AND имя_поля >= 0
.
Можно ли создать объектный тип из этих трёх столбцов, в котором указать не только их имена и размерности, но и вышеуказанные ограничения ? (В Писании и у Кайта что-то ничего не говорится по поводу именно возможности задания в объектном типе CHECK-ограничений... Неужели этого нельзя сделать ?!)

Заранее спасибо за ответы.
dmidek
Дата: 11.06.2006 18:25:10
Ответить на все Ваши вопросы поможет KISS-метод, "чем проще, тем лучше".

1. Конечно, лучше называть констрейнты самому для облегчения читабельности , поиска и т.д. Лучше всего завести свои стандарты PK, FK, _NN
и т.д.
Системные имена неудобны, но к трагедии при экспорте / импорте привести не должны. Точного ответа на Ваш вопрос не нашел, но есть у меня большие подозрения, что при экспорте Oracle будет переименовывать "свои" "старые" констрейнты...

2. 6 отдельных констрейнтов.

3. Надо городить :) Не совсем понял связи с производительностью. Если надо ограничивать по логике, то делайте это через констрейнты. Oracle проверит быстрее, чем Вы.

4. Тут по конкретному вопросу ничего не скажу, кроме того, что если этого и можно добиться, то ИМХО расходы не оправдают себя. Не ломайте голову и делаете для каждой колонки отдельное ограничение.
GrayCity
Дата: 11.06.2006 18:36:03
dmidek
Не совсем понял связи с производительностью.
- это из Интербейза 6.5 у меня воспоминание... :-) Там опытные люди советовали не делать много ограничений, а заменять их проверками в коде ХП, т.к. иначе база начинает сильно мучиться от большого их числа.

ЗЫ. По поводу 4-го вопроса: в Интербейзе с самого раннего детства были доступны т.н. "домены" --что-то напоминающее Оракловые объектные типы, только без методов. Зато с возможностью всадить в такой домен CHECK-условие, что ОЧЕНЬ удобно! Создал такой доменчик, а затем в 20 таблицах вместо указывания для одинаковых столбцов этих CHECK'ов просто объявляешь столбец с типом = имени домена. Все CHECK'и будут, сами понимаете, выполняться далее автоматом.
SY
Дата: 11.06.2006 18:48:03
1. I'll give you a hint. If you check data dictionary views DBA/ALL/USER_CONSTRAINTS, you'll notice column GENERATED. Check column value and you will have your answer.

2. Hint: Create index on field1 and use WHERE ... NOT IN (SELECT field1...)

3. Получается, что надо

4. Нельзя

SY.
grexhide
Дата: 11.06.2006 19:00:18
GrayCity

1. Если на некий столбец указать ограничение NOT NIULL:
ALTER TABLE someTable MODIFY(someField  NOT NULL);
- то Оракл даст ему какое-то свое имя вида SYS_nnnnn.


В идеале - пишется даже собственный скрипт из отряда ALTER ... RENAME

для PK - TABLENAME$COLUMNNAME$PK
для FK - TABLENAME$COLUMNNAME_REFTABLENAME$REFCOLUMNNAME_FK

для NN - проблем особых нет (в смысле можно, но зачем ?)

для "навернутых" CHECK..... отдельная песня (признаюсь, сам предпочитаю их выносить в триггеры и/или обработку на клиенте, т.ч. разумных доводов для применения пока не видел).

GrayCity

Будут ли у меня траблы с этим именем позже при выполнении EXPDP и последующем IMPDP на другой машине ?

Не будет. Траблы были замечены на 9iR2 (когда констрейны, полученные через ALTER - не всегда нормально экпортировались и импортировались), но это было давно, возможно - не правда, а в данном случае - мы имеем дело с 10g.

С системно назначаемыми именами - аналогично, проблем не будет. Oracle для них каждый раз будет назначать генерированные от системного сиквенса имена.

GrayCity

Короче: нужно ли сразу переименовывать системно-присвоенные имена для таких ограничений ?

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

GrayCity

2. Есть шесть столбцов. Все они должны быть NOT NULL. Можно, конечно, сделать ALTER TABLE и указать по очереди для каждого столбца ограничение NOT NULL. А можно создать одно-единое CHECK-огранничение вида
field1 IS NOT NULL AND field2 IS NOT NULL AND field3 IS NOT NULL AND
field4 IS NOT NULL AND field5 IS NOT NULL AND field6 IS NOT NULL

Интересный вопрос. Нужно ставить тесты. Теоретически, возможно, будет выигрыш, но с практической точки зрения - это не удобно и не верно. Проще говоря, в данном случае - будет запись в ALL_CONSTRAINTS, но в ALL_TAB_COLUMNS поле NULLABLE будет иметь значение 'Y', что не соотвествует действительности (оптимизатор в отдельных случаях учитывает значение данного поля).



GrayCity

Вопрос: что будет с производительностью ? не заклинит ли при числе АКТИВНО работающих пользователей около 300, числе таблиц около 100 и при том, что в каждой таблице есть от 4-х до 8 таких "сильно ограниченных" столбцов ? (Linux-сервак, двухмоторный Pentium 2.4, RAID-5, RAM 2Гб)
Гуру, поделитесь, плз, как правильно делать такие схемы: нужно ли навешивать ограничения целостности "везде и всюду", или перекладывать какие-то проверки на триггера ?

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

GrayCity

4. В нескольких таблицах имеется по три одинаковых столбца с одинаковыми характеристиками: размером NUMBER(7,2) и с CHECK-требованием
NOT NULL AND имя_поля >= 0
.
Можно ли создать объектный тип из этих трёх столбцов, в котором указать не только их имена и размерности, но и вышеуказанные ограничения ? (В Писании и у Кайта что-то ничего не говорится по поводу именно возможности задания в объектном типе CHECK-ограничений... Неужели этого нельзя сделать ?!)


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

Сам Кайт в принципе положительно отзывается об объектных расширениях, но в целом, на практике, их применение чаще создает больше проблем, чем пользы.
не Сам Кайт
Дата: 11.06.2006 19:10:23
grexhide
Сам Кайт в принципе положительно отзывается об объектных расширениях...

У него работая такая
GrayCity
Дата: 11.06.2006 19:30:07
С первым вопросом всё понятно, кроме одного:
SY
If you check data dictionary views DBA/ALL/USER_CONSTRAINTS, you'll notice column GENERATED. Check column value and you will have your answer.
- вьюху эту я посмотрел, там в поле GENERATED записано либо "GENERATED NAME", либо "USER NAME". Только непонятно, а где Oracle хранит имена ограничений, которые были ЯВНО заданы мною ? Там ведь для обоих типов имён в графе "CONSTRAINT NAME" записаны кракозябры типа BIN$PvOclYlmTdisFkQHYffi0A==$0.

По второму вопросу просьба к SY: нельзя ли поподробнее, что значит
SY
Hint: Create index on field1 and use WHERE ... NOT IN (SELECT field1...)
(я не понял, что именно надо делать - шесть индексов? или индекс по шести полям ?)
andrey_anonymous
Дата: 11.06.2006 19:44:59
grexhide
В идеале - пишется даже собственный скрипт из отряда ALTER ... RENAME
И где тут идеал?
Хотите внятные имена - давайте внятные имена.
SQL> create table ane_test(a number constraint ane_test$nn$a not null, b number);

Table created

SQL> alter table ane_test modify b constraint ane_test$nn$b not null;

Table altered

SQL> alter table ane_test add (c date constraint ane_test$nn$c not null);

Table altered

SQL> select owner, TABLE_NAME, CONSTRAINT_NAME, SEARCH_CONDITION, GENERATED 
   2 from user_constraints u where u.table_name='ANE_TEST';

OWNER  TABLE_NAME CONSTRAINT_NAME SEARCH_CONDITION GENERATED
------ ---------- --------------- ---------------- ---------
ANDREY ANE_TEST   ANE_TEST$NN$A   "A" IS NOT NULL  USER NAME
ANDREY ANE_TEST   ANE_TEST$NN$B   "B" IS NOT NULL  USER NAME
ANDREY ANE_TEST   ANE_TEST$NN$C   "C" IS NOT NULL  USER NAME

SQL> 

grexhide
GrayCity

Короче: нужно ли сразу переименовывать системно-присвоенные имена для таких ограничений ?
Нужно. Потому как в случае системноназначенных - нужны доп. телодвижения для идентификации проблемы. Собственно о чем будет говорить пользователю (или поддержке) неожиданное сообщение SYS_000123 violated ?
Как бы Вам сказать... Приличное приложение должно пропустить эту ошибку через собственную систему обработки ошибок и выдать пользователю диагностику в терминах предметной области. "Нарушено ограничение целостности ane_test$nn$a" таковым не является.
grexhide
Проще говоря, в данном случае - будет запись в ALL_CONSTRAINTS, но в ALL_TAB_COLUMNS поле NULLABLE будет иметь значение 'Y', что не соотвествует действительности (оптимизатор в отдельных случаях учитывает значение данного поля).
"Отдельный случай" - это, к примеру, будет ли рассматриваться индексный доступ в некоторых запросах :)
grexhide
Сам Кайт в принципе положительно отзывается об объектных расширениях.

Он должен рекламировать товар :)
grexhide
Дата: 11.06.2006 19:48:08
GrayCity
С первым вопросом всё понятно, кроме одного:
SY
If you check data dictionary views DBA/ALL/USER_CONSTRAINTS, you'll notice column GENERATED. Check column value and you will have your answer.
- вьюху эту я посмотрел, там в поле GENERATED записано либо "GENERATED NAME", либо "USER NAME". Только непонятно, а где Oracle хранит имена ограничений, которые были ЯВНО заданы мною ? Там ведь для обоих типов имён в графе "CONSTRAINT NAME" записаны кракозябры типа BIN$PvOclYlmTdisFkQHYffi0A==$0.

Это удаленная таблица. Recycle Bin (см. аналог в Windows - там тоже с именами, не того).

GrayCity

По второму вопросу просьба к SY: нельзя ли поподробнее, что значит
SY
Hint: Create index on field1 and use WHERE ... NOT IN (SELECT field1...)
(я не понял, что именно надо делать - шесть индексов? или индекс по шести полям ?)


Шесть явный констрейнов not null на шесть полей - явно.
См. еще раз про колонку nullable и конструкцию IN
andrey_anonymous
Дата: 11.06.2006 19:53:23
grexhide
Это удаленная таблица. Recycle Bin (см. аналог в Windows - там тоже с именами, не того).

Эх, секции/партиции...
Долго не мог понять, при чем тут вообще remote table...