Re: Index Limit

Robbo7 wrote:
>
> 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.
> > >
> > >
> >
> >

True - but given the most benefit from an index comes from the leading part then you'd only get use from an index on such a column as long as people ONLY specified the leading part of the column when searching - which for a varchar2(4000) I would say is very unlikely...

If you knew that people always would specify the leading part, then you would extract the leading (say) 30 chars and put them in a separate column and index that...

Standard indexes are not going to help with what you want - you may wish to pursue the intermedia option.

HTH

-- 
===========================================
Connor McDonald
http://www.oracledba.co.uk

We are born naked, wet and hungry...then things get worse