Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> Is this a bug?

Is this a bug?

From: Bobak, Mark <Mark.Bobak_at_il.proquest.com>
Date: Fri, 24 Feb 2006 12:09:13 -0500
Message-ID: <AA29A27627F842409E1D18FB19CDCF2706F4C451@AABO-EXCHANGE02.bos.il.pqe>


Consider this:

Connected to:
Oracle9i Enterprise Edition Release 9.2.0.6.0 - 64bit Production With the Partitioning, Real Application Clusters, OLAP and Oracle Data Mining options
JServer Release 9.2.0.6.0 - Production

SQL> create table test_fbi(a varchar2(4000));

Table created.

SQL> create index test_fbi_length on test_fbi(upper(a)) online compute statistics;
create index test_fbi_length on test_fbi(upper(a)) online compute statistics

                                               *
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1 ORA-01450: maximum key length (3215) exceeded

When I ran into this, I had an index created in my preprod environment, and Oracle was refusing to let me create it in my identical prod environment....I was stumped, how could it error on this here, but exist in preprod, which is identical in terms of version, O/S, block size, etc?

Now, I'm aware that any function that returns varchar2 implicitly returns varchar2(4000). Tom Kyte has a write up on this available here http://asktom.oracle.com/~tkyte/article1/index.html that explains it, and demonstrates how to work around it by wrapping the output in a SUBSTR() and then hiding that inside of a view. That's all fine and well and good. What I didn't understand is HOW I was ever allowed to create the index in preprod.....and then I discovered this:

SQL> create index test_fbi_length on test_fbi(upper(a)) online compute statistics;
create index test_fbi_length on test_fbi(upper(a)) online compute statistics

                                               *
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1 ORA-01450: maximum key length (3215) exceeded

SQL> c/online/
  1* create index test_fbi_length on test_fbi(upper(a)) compute statistics
SQL> / Index created.

Why does it work when you drop the ONLINE keyword?? In fact, it doesn't even need to be an FBI to demonstrate this behavior. Observe:

SQL> create index test_ind_key_length on test_fbi(a) online compute statistics;
create index test_ind_key_length on test_fbi(a) online compute statistics

                                    *

ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1 ORA-01450: maximum key length (3215) exceeded

SQL> c/online/
  1* create index test_ind_key_length on test_fbi(a) compute statistics SQL> / Index created.

Anyone else think this is a bit weird, not to mention inconsistent, behavior?

Any thoughts/ideas?

-Mark

--

Mark J. Bobak
Senior Oracle Architect
ProQuest Information & Learning

"Exception: Some dividends may be reported as qualified dividends but are not qualified dividends. These include:

--

http://www.freelists.org/webpage/oracle-l Received on Fri Feb 24 2006 - 11:09:13 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US