Re: Cannot create an index based on a function

A function returning varchar2 returns a maximum length varchar2 so the index has to be on a substr of it.

It looks like you've enabled extended varchar2 - I.e. 32K

Sent from my iPad

> On 22 Jun 2016, at 13:55, Mark W. Farnham <mwf_at_rsiz.com> wrote:
>
> Did you try creating a variable, making the assignment, and then returning the variable?
>
> mwf
>
> -----Original Message-----
> From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Thomas Kellerer
> Sent: Wednesday, June 22, 2016 8:14 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
>
>

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