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@xxxxxxxx> wrote:
Did you try creating a variable, making the assignment, and then returning
the variable?
mwf
-----Original Message-----
From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx] On ;
Behalf Of Thomas Kellerer
Sent: Wednesday, June 22, 2016 8:14 AM
To: oracle-l@xxxxxxxxxxxxx
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
--
//www.freelists.org/webpage/oracle-l
--
//www.freelists.org/webpage/oracle-l
