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" <robbo7NoSpam_at_ozemail.com.au> wrote in message
news:_fuD5.21076$O7.308818_at_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" <Reinier_Dickhout_at_hetnet.nl> wrote in message
> news:#5r3XN2LAHA.327_at_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 <robbo7NoSpam_at_ozemail.com.au> wrote in message
> > news:ud4D5.19976$O7.285509_at_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.
> > >
> > >
> >
> >
>
>
Received on Tue Oct 10 2000 - 08:48:37 CDT
