From: "Barbara Kennedy" Newsgroups: comp.databases.oracle.misc References: <#5r3XN2LAHA.327@net025s> <_fuD5.21076$O7.308818@ozemail.com.au> Subject: Re: Index Limit Lines: 61 X-Priority: 3 X-MSMail-Priority: Normal X-Newsreader: Microsoft Outlook Express 5.00.3018.1300 X-MimeOLE: Produced By Microsoft MimeOLE V5.00.3018.1300 Message-ID: Date: Tue, 10 Oct 2000 06:48:37 -0700 NNTP-Posting-Host: 216.26.5.147 X-Complaints-To: abuse@onemain.com X-Trace: nntp2.onemain.com 971185652 216.26.5.147 (Tue, 10 Oct 2000 09:47:32 EDT) NNTP-Posting-Date: Tue, 10 Oct 2000 09:47:32 EDT If you want to do a text search (like if it was a document) then use the Intermedia index and it can work on larger fields such as CLOBS and longs (use CLOBS if you can). I believe that Oracle won't let you make an index (conventional) where a key value is larger than half a database block. So I suspect you have 8K blocks. Jim "Robbo7" wrote in message news:_fuD5.21076$O7.308818@ozemail.com.au... > Reiner, > > The Varchar2(4000) column contains text data which is constantly retrieved, > sorted and referenced. This data is the very essence of our application. > There are frequent updates to this field and we need to do operations like, > (a) find all rows in the table that have the same contents in this column, > (b) represent all rows in a view sorted by this column, (c) quickly retrieve > all rows that contain some user specified text in this column. > > We originally defined this column as a LONG datatype because occasionally we > get large data items but we then moved to VARCHAR2(4000) because of the > restriction on LONGs. Now unfortunately, it seems, that we are restricted > by the indexing and we cannot reduce further the size of this field. > > Thanks for your input, > Robbo7 > > "Reinier" wrote in message > news:#5r3XN2LAHA.327@net025s... > > It may seem silly to you, but I'm wondering why you want to index a > > Varchar2(4000) field. If it's because you're joining two tables and this > > field is one of the joining columns than I think you have a redundancy > > problem. I can't think of any other reason to index it. > > > > But as to your question, I don't think it's possible. > > > > Reinier. > > > > Robbo7 wrote in message > > news:ud4D5.19976$O7.285509@ozemail.com.au... > > > Hi, > > > > > > I have a column defined as VARCHAR2(4000). When I try to index this column > > > I get a message, "ORA-01450: maximum key length (3218) exceeded". Is there > > > a way of changing this so that I can index my text column? It would seem > > > silly if Oracle allows me a 4000 character column but won't allow me to > > > index it. > > > > > > > > > > > >