Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Oracle Новый топик    Ответить
 Как обойти ORA-01450 при создании индекса по функции?  [new]
Кореец
Member

Откуда:
Сообщений: 361
Обьясните пожалуйста такой момент.
допустим у нас есть:
create table t_tbl(s varchar2(100));

создаем индекс по функции
SQL> create index f3_idx on t_tbl(upper(s));

Index created

Все ок.

А когда потом пробуем тоже самое но через свою функцию
create or replace function ff_upper(s varchar2) return varchar2 deterministic
is
begin
return upper(s);
null;
end;

SQL> create index f4_idx on t_tbl(ff_upper(s));

create index f4_idx on t_tbl(ff_upper(s))

ORA-01450: превышена максимальная длина (3118) ключа

Почему появляется ORA-01450? Возможно дело в том что Return varchar2 воспринимается как Return varchar2(4000) и Оракл предполагает, что нехватит размера блока.
Но я пробовал реализовать ее и в пакете используя subtype

create or replace package p_pak is
subtype t_String is varchar2(1);


function f_upper(s t_String) return t_String deterministic;

end;
create or replace package body p_pak is

function f_upper(s t_String) return t_String
is
begin
return upper(s);
null;
end;
end;


и в итоге таже ошибка.
Кто-то знает как обойти? Научите плз)))
Спасибо.
3 ноя 06, 09:52    [3351310]     Ответить | Цитировать Сообщить модератору
 Re: Как обойти ORA-01450 при создании индекса по функции?  [new]
Кореец
Member

Откуда:
Сообщений: 361
Забыл сказать про
Oracle 9.2.0.7/Windows2003
3 ноя 06, 09:54    [3351317]     Ответить | Цитировать Сообщить модератору
 Re: Как обойти ORA-01450 при создании индекса по функции?  [new]
Щукина Анна
Member

Откуда:
Сообщений: 1489
create or replace 
function ff_upper(s varchar2) 
  return varchar2 
deterministic
   str t_tbl.s%type;
is 
begin
   str := upper(s);
   return str
end;
3 ноя 06, 10:00    [3351360]     Ответить | Цитировать Сообщить модератору
 Re: Как обойти ORA-01450 при создании индекса по функции?  [new]
Ананас Мангович
Guest
Создай его в табл. пр-ве с размером блока 8k или больше
3 ноя 06, 10:01    [3351366]     Ответить | Цитировать Сообщить модератору
 Re: Как обойти ORA-01450 при создании индекса по функции?  [new]
Кореец
Member

Откуда:
Сообщений: 361
Щукина Анна
create or replace 
function ff_upper(s varchar2) 
  return varchar2 
deterministic
   str t_tbl.s%type;
is 
begin
   str := upper(s);
   return str
end;


к сожалению не помогло. А у вас прокатило?
3 ноя 06, 10:12    [3351429]     Ответить | Цитировать Сообщить модератору
 Re: Как обойти ORA-01450 при создании индекса по функции?  [new]
Щукина Анна
Member

Откуда:
Сообщений: 1489
Кореец
к сожалению не помогло. А у вас прокатило?
У меня работает. Причем как с Вашей функцией, так и с моей...

Оракл 9.2.0.6
3 ноя 06, 10:16    [3351455]     Ответить | Цитировать Сообщить модератору
 Re: Как обойти ORA-01450 при создании индекса по функции?  [new]
SeaGate
Member

Откуда: Новосибирск
Сообщений: 1635
Кореец

к сожалению не помогло. А у вас прокатило?

На Металинке: Note:136158.1 есть по этому вопросу, ссылку давать лень.
3 ноя 06, 10:17    [3351463]     Ответить | Цитировать Сообщить модератору
 Re: Как обойти ORA-01450 при создании индекса по функции?  [new]
Кореец
Member

Откуда:
Сообщений: 361
SeaGate
Кореец

к сожалению не помогло. А у вас прокатило?

На Металинке: Note:136158.1 есть по этому вопросу, ссылку давать лень.


у меня и доступа туда нет.
3 ноя 06, 10:23    [3351505]     Ответить | Цитировать Сообщить модератору
 Re: Как обойти ORA-01450 при создании индекса по функции?  [new]
Кореец
Member

Откуда:
Сообщений: 361
SeaGate
Кореец

к сожалению не помогло. А у вас прокатило?

На Металинке: Note:136158.1 есть по этому вопросу, ссылку давать лень.



Мне бы сразу весь текст этой ноты..)))
3 ноя 06, 10:24    [3351512]     Ответить | Цитировать Сообщить модератору
 Re: Как обойти ORA-01450 при создании индекса по функции?  [new]
SQL*Plus
Member

Откуда: Россия, Москва
Сообщений: 8131
Oracle9i 9.2.0.1
Connected to: Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production
SQL> CREATE OR REPLACE FUNCTION killme_upper(s VARCHAR2)
  2  RETURN VARCHAR2 deterministic
  3  IS
  4  BEGIN
  5     RETURN UPPER(s);
  6     NULL; -- Непонятно зачем это здесь???
  7  END;
  8  /
Function created.

SQL> DROP TABLE killme_tbl;
Table dropped.

SQL> CREATE TABLE killme_tbl(s VARCHAR2(100));
Table created.

SQL> --создаем индекс по функции
SQL> CREATE INDEX killme_tbl_idx ON killme_tbl(killme_UPPER(s));
Index created.

Oracle9i Enterprise Edition Release 9.2.0.8
Connected to: Oracle9i Enterprise Edition Release 9.2.0.8.0 - Production
SQL> CREATE OR REPLACE FUNCTION killme_upper(s VARCHAR2)
  2  RETURN VARCHAR2 deterministic
  3  IS
  4  BEGIN
  5     RETURN UPPER(s);
  6     NULL; -- Непонятно зачем это здесь???
  7  END;
  8  /
Function created.

SQL> CREATE TABLE killme_tbl(s VARCHAR2(100));
Table created.

SQL> --создаем индекс по функции
SQL> CREATE INDEX killme_tbl_idx ON killme_tbl(killme_UPPER(s));
Index created.
Как видим прошло в Oracle 9.2.0.1 и 9.2.0.8.

В 9.2.0.1 чтобы избежать при создании индекса возникала ошибка ORA-01031: insufficient privileges
Исправилась после предоставления привилегии QUERY_REWRITE.
В 9.2.0.8 такой проблемы не было.
3 ноя 06, 10:26    [3351520]     Ответить | Цитировать Сообщить модератору
 Re: Как обойти ORA-01450 при создании индекса по функции?  [new]
Кореец
Member

Откуда:
Сообщений: 361
Щукина Анна
Кореец
к сожалению не помогло. А у вас прокатило?
У меня работает. Причем как с Вашей функцией, так и с моей...

Оракл 9.2.0.6


Возможно у вас размер блока позволяет. на моей базе он 4К. И tablspace с blocksize>4K он не позволяет создать.

а настраивать параметры
db_2k_cache_size, db_4k_cache_size, db_8k_cache_size, db_16k_cache_size, db_32K_cache_size
я не могу.

Хотелось бы найти возможность обойти это вообще без создания доп табличных пространств. Он же делает индекс по своей upper...
скорее всего ведь дело в типе, который return из функции..
3 ноя 06, 10:28    [3351537]     Ответить | Цитировать Сообщить модератору
 Re: Как обойти ORA-01450 при создании индекса по функции?  [new]
Вячеслав Любомудров
Member

Откуда: Владивосток
Сообщений: 18484
Создавай индекс по substr(func(), 1, 400), например
И запрос соответственно переписывай (или вьюху делай)
3 ноя 06, 10:30    [3351559]     Ответить | Цитировать Сообщить модератору
 Re: Как обойти ORA-01450 при создании индекса по функции?  [new]
SQL*Plus
Member

Откуда: Россия, Москва
Сообщений: 8131
...
Как видим прошло в Oracle 9.2.0.1 и 9.2.0.8.

Платформа обеих баз - Windows XP Professional (rus) + SP2
Размер блока в табличных пространствах пользователя для обеих баз 8192
3 ноя 06, 10:31    [3351568]     Ответить | Цитировать Сообщить модератору
 Re: Как обойти ORA-01450 при создании индекса по функции?  [new]
Кореец
Member

Откуда:
Сообщений: 361
SQL*Plus
Oracle9i 9.2.0.1
Connected to: Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production
SQL> CREATE OR REPLACE FUNCTION killme_upper(s VARCHAR2)
  2  RETURN VARCHAR2 deterministic
  3  IS
  4  BEGIN
  5     RETURN UPPER(s);
  6     NULL; -- Непонятно зачем это здесь???
  7  END;
  8  /
Function created.

SQL> DROP TABLE killme_tbl;
Table dropped.

SQL> CREATE TABLE killme_tbl(s VARCHAR2(100));
Table created.

SQL> --создаем индекс по функции
SQL> CREATE INDEX killme_tbl_idx ON killme_tbl(killme_UPPER(s));
Index created.

Oracle9i Enterprise Edition Release 9.2.0.8
Connected to: Oracle9i Enterprise Edition Release 9.2.0.8.0 - Production
SQL> CREATE OR REPLACE FUNCTION killme_upper(s VARCHAR2)
  2  RETURN VARCHAR2 deterministic
  3  IS
  4  BEGIN
  5     RETURN UPPER(s);
  6     NULL; -- Непонятно зачем это здесь???
  7  END;
  8  /
Function created.

SQL> CREATE TABLE killme_tbl(s VARCHAR2(100));
Table created.

SQL> --создаем индекс по функции
SQL> CREATE INDEX killme_tbl_idx ON killme_tbl(killme_UPPER(s));
Index created.
Как видим прошло в Oracle 9.2.0.1 и 9.2.0.8.

В 9.2.0.1 чтобы избежать при создании индекса возникала ошибка ORA-01031: insufficient privileges
Исправилась после предоставления привилегии QUERY_REWRITE.
В 9.2.0.8 такой проблемы не было.





6 NULL; -- Непонятно зачем это здесь??? Это не нужно. просто привычка сначала создать каркас потом писать тело функции.

Попробую QUERY_REWRITE.
А какой у вас размер блокаданных в табличном пространсве где индекс создавался?
3 ноя 06, 10:32    [3351581]     Ответить | Цитировать Сообщить модератору
 Re: Как обойти ORA-01450 при создании индекса по функции?  [new]
Кореец
Member

Откуда:
Сообщений: 361
SQL*Plus
...
Как видим прошло в Oracle 9.2.0.1 и 9.2.0.8.

Платформа обеих баз - Windows XP Professional (rus) + SP2
Размер блока в табличных пространствах пользователя для обеих баз 8192


Спасибо
3 ноя 06, 10:33    [3351584]     Ответить | Цитировать Сообщить модератору
 Re: Как обойти ORA-01450 при создании индекса по функции?  [new]
Кореец
Member

Откуда:
Сообщений: 361
Вячеслав Любомудров
Создавай индекс по substr(func(), 1, 400), например
И запрос соответственно переписывай (или вьюху делай)


Опа! Вот это сработало!!!
Маленький пост но дельный, сразу и не заметил.)))
Спасибо за мысль!
Теперь запрос подделаем...
3 ноя 06, 10:42    [3351652]     Ответить | Цитировать Сообщить модератору
 Re: Как обойти ORA-01450 при создании индекса по функции?  [new]
Кореец
Member

Откуда:
Сообщений: 361
Если будут еще у кого варианты с удовольствием воспользуюсь.
А так большое всем спасибо.
3 ноя 06, 10:43    [3351664]     Ответить | Цитировать Сообщить модератору
 Re: Как обойти ORA-01450 при создании индекса по функции?  [new]
contr
Member

Откуда:
Сообщений: 1909
Кореец
Если будут еще у кого варианты с удовольствием воспользуюсь.
А так большое всем спасибо.

Да нет, реально обходится только так.
Более того, на девятке в MODEL при объявлении varchar2-measures тоже приходилось явно прописывать размер посредством lpad.
Вообще c varchar2 у Oracle последнее время подобных фокусов встречается :)
3 ноя 06, 10:47    [3351704]     Ответить | Цитировать Сообщить модератору
 Re: Как обойти ORA-01450 при создании индекса по функции?  [new]
softy
Member

Откуда: from Russia
Сообщений: 5911
Кореец
Вячеслав Любомудров
Создавай индекс по substr(func(), 1, 400), например
И запрос соответственно переписывай (или вьюху делай)


Опа! Вот это сработало!!!


Сработало что - индекс создался? Или план показал его использование?
3 ноя 06, 10:50    [3351740]     Ответить | Цитировать Сообщить модератору
 Re: Как обойти ORA-01450 при создании индекса по функции?  [new]
Кореец
Member

Откуда:
Сообщений: 361
softy
Кореец
Вячеслав Любомудров
Создавай индекс по substr(func(), 1, 400), например
И запрос соответственно переписывай (или вьюху делай)


Опа! Вот это сработало!!!


Сработало что - индекс создался? Или план показал его использование?


индекс создался.
сейчас протестим и план выполнения..
3 ноя 06, 10:52    [3351753]     Ответить | Цитировать Сообщить модератору
 Re: Как обойти ORA-01450 при создании индекса по функции?  [new]
Кореец
Member

Откуда:
Сообщений: 361
...индекс создался.
сейчас протестим и план выполнения..
индекс используется!еще каааак используется))
тока с каким нить хинтом /*+ <hint> */ для включения CBO.

12 секунд первратились в 0,3. правда теперь надо подумать как его сопровождать. но все равно впечатляет.))))

я так понимают что если изменю потом функцию мне индекс самому перестраивать(достаточно будет rebuшld? ) или оракл сам это сделает?
3 ноя 06, 11:04    [3351894]     Ответить | Цитировать Сообщить модератору
 Re: Как обойти ORA-01450 при создании индекса по функции?  [new]
softy
Member

Откуда: from Russia
Сообщений: 5911
Я к тому, что в запрос substr тоже придётся включить:
where
 substr(func('...'), 1, 400) = '...'
3 ноя 06, 11:04    [3351897]     Ответить | Цитировать Сообщить модератору
 Re: Как обойти ORA-01450 при создании индекса по функции?  [new]
Кореец
Member

Откуда:
Сообщений: 361
softy
Я к тому, что в запрос substr тоже придётся включить:
where
 substr(func('...'), 1, 400) = '...'


так и сделал.
+ хинт к запросу. Без хинта не пользовал он индекс.
3 ноя 06, 11:06    [3351922]     Ответить | Цитировать Сообщить модератору
 Re: Как обойти ORA-01450 при создании индекса по функции?  [new]
silw
Guest
у Кайта:
The reason we must SUBSTR the user-written function that returns a string is that such functions return VARCHAR2(4000) types. That may well be too big to be indexed—index entries must fit within about three quarters the size of a block. If we tried, we would receive (in a tablespace with a 4KB blocksize) the following:
ops$tkyte@ORA10G> create index emp_soundex_idx on
  2 emp( my_soundex(ename) ) tablespace ts4k;
emp( my_soundex(ename) ) tablespace ts4k
                                      *
ERROR at line 2:
ORA-01450: maximum key length (3118) exceeded
It is not that the index actually contains any keys that large, but that it could as far as the database is concerned. But the database understands SUBSTR.
3 ноя 06, 11:07    [3351936]     Ответить | Цитировать Сообщить модератору
 Re: Как обойти ORA-01450 при создании индекса по функции?  [new]
softy
Member

Откуда: from Russia
Сообщений: 5911
Кореец

+ хинт к запросу. Без хинта не пользовал он индекс.


Можно и без хинта, если собрать статистику по таблице или указать режим оптимизатора для CBO.
3 ноя 06, 11:11    [3351975]     Ответить | Цитировать Сообщить модератору
Все форумы / Oracle Ответить