Function Based Index and java.lang.String

Hi.

I have a java stored procedure whose spec is as follows:

CREATE OR REPLACE FUNCTION DECODE_SEQUENCE(SEQUENCE VARCHAR2) RETURN
VARCHAR2
DETERMINISTIC
AS LANGUAGE JAVA
NAME 'kr.ac.dke.protein.compression.SequenceCompressor. decode(java.lang.String)
return java.lang.String';
/

AND I want to make an index like below:

CREATE INDEX K0_IDX ON K0(DECODE_SEQUENCE(TYPESTR), TYPELEN)
NOLOGGING
COMPRESS 2
/

However, above statement raises an error, saying that 'Maximum size of
index field is excceded.' Hence, I've changed the index creation
statement.

CREATE INDEX K0_IDX ON K0(SUBSTR(DECODE_SEQUENCE(TYPESTR),1,1),
TYPELEN)
NOLOGGING
COMPRESS 2
/

This works.

Here's the question. Can't I create an index on java.lang.String
without using SUBSTR function?

Regards,
Minkoo Seo

re: Function Based Index and java.lang.String

"Min-Koo Seo" <pool0078@hanmail.net> wrote in message
news:4c458db8.0406210740.561bf50c@posting.google.c om...[color=blue]
> Hi.
>
> I have a java stored procedure whose spec is as follows:
>
> CREATE OR REPLACE FUNCTION DECODE_SEQUENCE(SEQUENCE VARCHAR2) RETURN
> VARCHAR2
> DETERMINISTIC
> AS LANGUAGE JAVA
> NAME[/color]
'kr.ac.dke.protein.compression.SequenceCompressor. decode(java.lang.String)[color=blue]
> return java.lang.String';
> /
>
> AND I want to make an index like below:
>
> CREATE INDEX K0_IDX ON K0(DECODE_SEQUENCE(TYPESTR), TYPELEN)
> NOLOGGING
> COMPRESS 2
> /
>
> However, above statement raises an error, saying that 'Maximum size of
> index field is excceded.' Hence, I've changed the index creation
> statement.
>
> CREATE INDEX K0_IDX ON K0(SUBSTR(DECODE_SEQUENCE(TYPESTR),1,1),
> TYPELEN)
> NOLOGGING
> COMPRESS 2
> /
>
> This works.
>
> Here's the question. Can't I create an index on java.lang.String
> without using SUBSTR function?
>
> Regards,
> Minkoo Seo[/color]

It doesn't know how large a key is and a key cannot be larger than half a db
block. But with substring the key cannot exceed half a database block.
Jim