Пятничная задача: Проверка существования Function-based индекса

Здравствуйте !
Это будет неклассическая пятничная задачка, поскольку в ней у меня есть и шкурный
интерес :-) Хотя решение и есть, но был бы очень рад, если бы нашлось что- то получше.
Так как пятничные задачки сейчас довольно редки, надеюсь Вы не будете
на меня в обиде. Задачка взята из повседневной практики ....

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

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

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

И все шло хорошо, пока не появились FBI. Оракл оптимизирует индексы и то,
что лежит в dba_ind_expressions становится совершенно не похоже на то,
что лежит в нашей таблице, пришедшей от разработчиков.
Я не говорю в данном случае о строчных / прописных буквах , о двойных кавычках,
и даже о количестве пробелов.
Пример

SQL> create index scott.emp_dat_format on scott.emp(nvl(hiredate, '01.01.1980'))
  2  /
 
Index created
 
SQL> select column_expression from dba_ind_expressions
  2  where index_name = 'EMP_DAT_FORMAT'
  3  /
 
COLUMN_EXPRESSION
--------------------------------------------------------------------------------
NVL("HIREDATE",TO_DATE('1980-01-01 00:00:00', 'yyyy-mm-dd hh24:mi:ss'))
 
SQL> 

Понятно, что Оракл поступает хорошо :-), исправляя грязную работу разработчика.
Но нам от этого не легче. Как результат в огромной базе неизменившиеся FBI
удалялись и снова создавались, что привело к огромным и ненужным
потерям времени.

Что делать ? :-)

dmidek

Оракл оптимизирует индексы и то, что лежит в dba_ind_expressions становится совершенно не похоже на то, что лежит в нашей таблице, пришедшей от разработчиков.

Создавать необходимые индексы в тестовой\девелоуперской среде, а уже потом на основе содержимого dba_ind_expressions заполнять "вашу табличку". Подойдет?
-------------------------------------------------------
Автор благодарит алфавит за любезно предоставленные ему буквы.

Может есть возможность сделать механизм нумерации версий объектов и "отвязаться" от DDL?

Apex

dmidek Оракл оптимизирует индексы и то, что лежит в dba_ind_expressions становится совершенно не похоже на то, что лежит в нашей таблице, пришедшей от разработчиков. Создавать необходимые индексы в тестовой\девелоуперской среде, а уже потом на основе содержимого dba_ind_expressions заполнять "вашу табличку". Подойдет?

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

Хотя я поразмыслю над этим...

dmidek

Что делать ? :-)

Я бы попробовал

create table dummy_t as select * from t where rownum < 1;
create index ... on dummy_t ...

и сравнил.

йфяцыч

Может есть возможность сделать механизм нумерации версий объектов и "отвязаться" от DDL?

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

1) Сравнивать проще всего эталоны ("информацию о созданных элементах в специальные таблицы") на основе которых потом код генерируется. Правда сравнивать результат надежнее :( плюс придется хранить в каждой БД исходное описание объектов.
2) Использовать однозначное преобразование "исходная структура индекса -> имя индекса" при генерации объектов из их описаний, тогда просто не получиться создать дважды такой индекс. Поскольку индексы таки не таблицы думаю для них вполне допустимы даже такие имена как EMP_HIREDATE$X1ZF58 не говоря уж о EMP_NVL_HIREDATE_D01011980. "Человеческое" название используем когда оно влезает в 30 символов, иначе - шифровано-хэшировано-сжатое (выбрать по вкусу ;)).

Elic

dmidek Что делать ? :-) Я бы попробовал create table dummy_t as select * from t where rownum < 1; create index ... on dummy_t ... и сравнил.

Ага. Это то самое "наше решение", на котором мы пока остановились :-) :-)
Оно вполне хорошее, но какое- то на мой вкус чересчур экзотическое
и "капитулянтское", что ли ...
Тебе так не кажется ? Считаешь, пойдет ?

dmidek

Пример nvl(hiredate, '01.01.1980')

А за это вы@$ал бы разработчиков во все дыры.

dmidek

Я не говорю в данном случае о строчных / прописных буквах , о двойных кавычках, и даже о количестве пробелов.

При использовании варианта 2 такие вещи естественно сначала нормализуются.