Re: Cannot create an index based on a function

From: Powell, Mark <mark.powell2_at_hpe.com>
Date: Wed, 22 Jun 2016 13:07:21 +0000
Message-ID: <TU4PR84MB02068E5914A16DD567403C07CC2C0_at_TU4PR84MB0206.NAMPRD84.PROD.OUTLOOK.COM>

Have you tried changing the function parameter type from varchar2 to Orders.Code%type to see if providing an explicit length to the paramer makes a difference?

---

From: oracle-l-bounce_at_freelists.org <oracle-l-bounce_at_freelists.org> on behalf of Thomas Kellerer <thomas.kellerer_at_mgm-tp.com> Sent: Wednesday, June 22, 2016 8:13:50 AM To: oracle-l_at_freelists.org
Subject: Cannot create an index based on a function

Hello,

we are trying to apply a unique index for NULL values using an index like this:

   create unique index tk_test on orders (code, coalesce(p_versionid,'-1'));

The above index is doing exactly what we want (allowing only a single NULL value for the same CODE column), except that SAP Hybris refuses to work with a database that contains such an index.

An ugly workaround the stupid Hybris restriction is to wrap the expression in the index into a function:

  CREATE OR REPLACE function coalesce_wrapper(param in varchar2)     return varchar2 deterministic
  is
  begin
    return coalesce(param, '-1');
  end;
  /

But for some reason when creating an index using:

   create unique index tk_test on orders (code, coalesce_wrapper(p_versionid));

This results in:

   ORA-01450: maximum key length (6398) exceeded

However: the column p_versionid is defined as VARCHAR2(255 Byte) and so is the CODE column - and as I said: the equivalent index using coalesce() works just fine.

It seems that Oracle uses the function's signature to decide whether or not the value can be indexed rather than the actual value returned by the function.

The statement:

  select max(length(p_versionid)), max(length(coalesce_wrapper(p_versionid)))   from orders;

returns 30 for both values. So my understanding would be that this function should be usable in an index, but apparently it isn't.

This is an Oracle 12.1.0.2 EE running on Linux.

Any ideas how I can convince Oracle that the index expression is not longer then 30 characters?

Thanks
Thomas
--

http://www.freelists.org/webpage/oracle-l

--

http://www.freelists.org/webpage/oracle-l Received on Wed Jun 22 2016 - 15:07:21 CEST