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:

• Dividends you received on any share of stock that you held for less than 61 days during the 121-day period that began 60 days before the ex-dividend date. The ex-dividend date is the first date following the declaration of a dividend on which the purchaser of a stock is not entitled to receive the next dividend payment. When counting the number of days you held the stock, include the day you disposed of the stock but not the day you acquired it. See the examples below. Also, when counting the number of days you held the stock, you cannot count certain days during which your risk of loss was diminished. See Pub. 550 for more details." --IRS, Form 1040-A Instruction Booklet, Line 9b: Qualified Dividends

--

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