Function based indexes

[jto]

Hello,
I have tried the following:
create table TEST_FBI
(
UNIQUE_ID NUMBER(16)
);
create index FBI on TEST_FBI bitand(UNIQUE_ID, 4);
create index FBI2 on TEST_FBI decode(BITAND(UNIQUE_ID,4), 0, 1, NULL);

in
PL/SQL Developer Version 6.0.4.906 (MBCS)
Windows XP version 6.0 (build 2600) Service Pack 2
Oracle 9.2.0.4

And then I extracted DDL of that table and both indexes were missing first word, e.g.

create index FBI on TEST_FBI (UNIQUE_ID, 4)
create index FBI2 on TEST_FBI (BITAND(UNIQUE_ID,4), 0, 1, NULL)

am'I doing something wrong or is this a bug? Can you reproduce it?

best regards
Jiri

 

[Marco Kalter]

I can indeed reproduce this. We'll fix it.

 

[LMW]

Hello,

as a workaround create the indexes with embracing brackets

create index FBI on TEST_FBI (bitand(UNIQUE_ID, 4));
create index FBI2 on TEST_FBI (decode(BITAND(UNIQUE_ID,4), 0, 1, NULL));

then extracted dll will be the same

best regards
Wilhelm

 

[jto]

ok. It's probably related to the same code but to be on the safe side - the same behaviour can be observed in "Edit table" screen on the Indexes tab.
regards
Jiri

 

[jto]

Originally posted by Wilhelm Lamers:
as a workaround create the indexes with embracing brackets

well I can't recreate all FBIs in our production databases...

best regards
Jiri