Dear Tom

I cannot create an index on a virtual column of type RAW or RAW(30). I've used virtual columns several times in the past and I also indexed them, with no issues so far. This this the first attempt to do so with a RAW column, but I fail.

Why do I want to index a virtual column of type RAW(30)? - Please read the section at the bottom.

When I try to create the index on a virtual column of type RAW, then Oracle returns "ORA-01450: maximum key length (6397) exceeded" even though the actual keys are shorter. I guess this is due to the fact that the length of a RAW column is only limited by the technical length limit of type RAW (which happens to be 32767).

create or replace function calc_sort_key return raw deterministic 
is 
    l_result raw(30) := hextoraw('41424344'); -- 'ABCD' for the sake of simplicity
begin
    -- the "real" function calculates a sort key of 30 bytes
    return l_result; 
end;
/
create table x ( 
    id number(38) primary key, 
    sort_key as (calc_sort_key()) 
)
/
create unique index sort_key_ui on x (sort_key)
/
-- ORA-01450: maximum key length (6398) exceeded

Ok, then let's try to create a virtual column of limited size, like RAW(30). When I do so, then Oracle returns "ORA-12899: value too large for column "SORT_KEY" (actual: 32767, maximum: 30)". What confuses me with ORA-12899 is: According to the description of ORA-12899 ( https://docs.oracle.com/en/error-help/db/ora-12899/?r=19c ) this generally happens upon insert or update. But I did not even attempt to insert or update any rows in the table, in fact, the table is empty - it happened after a DDL statement (create table or alter table)!

drop table x
/
create table x ( 
    id number(38) primary key, 
    sort_key raw(30) as (calc_sort_key()) 
)
/
-- ORA-12899: value too large for column "SORT_KEY" (actual: 32767, maximum: 30)

I also compared the behaviour with a physical column of type RAW(30)

drop table x
/
create table x ( 
    id number(38) primary key, 
    physical_column raw(30) 
)
/
create unique index physical_column_ui on x (physical_column)
/

No problem so far. I re-declare the function calc_sort_key such that it returns x.physical_column%type instead of RAW, add a virtual column of type RAW(30) and index it:

create or replace function calc_sort_key return x.physical_column%type deterministic 
is 
    l_result raw(30) := hextoraw('41424344'); -- 'ABCD' 
begin 
    return l_result; 
end;
/
alter table x add ( 
    sort_key raw(30) as (calc_sort_key()) 
)
/
-- ORA-12899: value too large for column "SORT_KEY" (actual: 32767, maximum: 30)
alter table x add ( 
    sort_key as (calc_sort_key()) 
)
/
create unique index sort_key_ui on x (sort_key)
/
-- ORA-01450: maximum key length (6398) exceeded

It seems impossible to create a virtual column of type RAW and have an index on that column. Let't see if I can create a function-based index, without the virtual column:

alter table x drop ( 
    sort_key
)
/
create unique index sort_key_ui on x (sort_key)
/
-- ORA-01450: maximum key length (6398) exceeded

Again, the same error - Do I miss here something I should know? Is this a bug in Oracle? Is there a workaround (apart from a phyical column, updated in a trigger)? What technique do you recommend for the use case outlined below in section "Why do I want to index a virtual column of type RAW(30)?"

Thank you very much for your advice!
Regards, Andreas

LiveSQL: https://livesql.oracle.com/ords/livesql/s/c1185a57sawdvdafir02v6ffn
(adding the link in the form does not work - validation message on clicking "Next >": "1 error has occurred - LiveSQL link must point to a valid shared script link")

=====================================

Why do I want to index a virtual column of type RAW(30)?

Here's the use case: I have a large table "x" that users should be able to easily and quickly scroll through in a very specific order (I don't go into the details of the actual order). To satisfy that requirement with as little resources as possible, I follow the approach:

- Create a function "calc_sort_key" that takes the column values as parameters and calculates a short but unique sort key for a row (similar to NLSSORT). The function's return type is RAW(30) for two reasons:
a) RAW is not subject to the complications of NLS settings (as opposed to VARCHAR2)
b) 30 bytes is wide enough for my purposes (as opposed to NUMBER(38)) but way shorter than the concatenated columns that affect the order (minimizing index size)

- Create a virtual column "sort_key" based on function "calc_sort_key".

- Create a unique index "sort_key_ui" on virtual column "sort_key".

Queries should be easily written and and efficiently executed. The initial query for the first 20 rows:

select * from x
order by sort_key desc
fetch first 20 rows only

The subsequent query for the next 20 rows:

select * from x
where sort_key < $last_sort_key -- from previous query
order by sort_key desc
fetch first 20 rows only

SQL> set lines 70
SQL>
SQL> create or replace function calc_sort_key return raw deterministic
  2  is
  3      l_result raw(30) := hextoraw('41424344'); -- 'ABCD' for the sake of simplicity
  4  begin
  5      return l_result;
  6  end;
  7  /

Function created.

SQL> create table x (
  2      id number(38) primary key,
  3      sort_key as (calc_sort_key())
  4  )
  5  /

Table created.

SQL> desc x
 Name                                Null?    Type
 ----------------------------------- -------- ------------------------
 ID                                  NOT NULL NUMBER(38)
 SORT_KEY                                     RAW(32767)

SQL>
SQL> drop table x purge;

Table dropped.

SQL>
SQL> create table x (
  2      id number(38) primary key,
  3      sort_key raw(100) as (cast(calc_sort_key() as raw(100)))
  4  )
  5  /

Table created.

SQL> desc x
 Name                                Null?    Type
 ----------------------------------- -------- ------------------------
 ID                                  NOT NULL NUMBER(38)
 SORT_KEY                                     RAW(100)

SQL>
SQL> create unique index sort_key_ui on x (sort_key);

Index created.

SQL>
SQL>