RE: Cannot create an index based on a function

  • From: "Mark W. Farnham" <mwf@xxxxxxxx>
  • To: <jonathan@xxxxxxxxxxxxxxxxxx>
  • Date: Wed, 22 Jun 2016 12:05:45 -0400

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@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx]
On Behalf Of Jonathan Lewis
Sent: Wednesday, June 22, 2016 9:08 AM
To: mwf@xxxxxxxx
Cc: thomas.kellerer@xxxxxxxxxx; oracle-l@xxxxxxxxxxxxx
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@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



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


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


Other related posts:

  1. Home
  2. oracle-l
  3. Archive
  4. 06-2016