RE: Cannot create an index based on a function

Perhaps:

create unique index tk_test on
orders(code,cast(coalesce_wrapper1(p_versionid) as varchar2(255 byte)));

will work. I think casting the return to your desired maximum length *might* be the most performant.

Unfortunately I don't have a database handy with extended varchar2, so I cannot test that for you.

mwf

-----Original Message-----
From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Jonathan Lewis
Sent: Wednesday, June 22, 2016 9:08 AM
To: mwf_at_rsiz.com
Cc: thomas.kellerer_at_mgm-tp.com; oracle-l_at_freelists.org Subject: 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@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


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