Re: indexes in big character columns

   I believe that there are two sides to your question.

First of all, from a technical point of view, an index is basically a tree plugged over an ordered list of keys and locators (in other words, the values that are indexed and the rowids where to find them). The bigger the keys, the bigger the index, the more bytes you have to shuffle when you insert or delete rows, or update the value. Note though that you can have index compression (it basically removes common prefixes/suffixes from successive key values) which improves the byte count, at the cost of additional table accesses for some queries since you no longer find the full value in the index. And it's a question of actual value size, not column size.

The second side is that index search is based on equality between input and key value. The longer the key, the higher the odds of having divergences. If you take the example of book titles, for instance, if people look for "Don Quixote" (the full title of which is "The Ingenious Gentleman Don Quixote of La Mancha") they will never find it in the index. That's where full text indexing chimes in.

For me, the second reason is the bigger problem.

HTH,

-- 
Stephane Faroult
RoughSea Ltd <http://www.roughsea.com>
Konagora <http://www.konagora.com>
RoughSea Channel on Youtube <http://www.youtube.com/user/roughsealtd>




On 12/26/2012 06:53 AM, Orlando L wrote:


> All,

> Me and a colleague were having discussion about creating indexes in

> character columns that are big in size. He is of the opinion that big

> character columns should not be indexed. He could not explain why. I

> remember hearing about it, but did not know why that was the case. Does it

> still apply and can someone please explain. Thanks for the help.

>

> Orlando.

>

>

> --

> http://www.freelists.org/webpage/oracle-l

>

>

>




--
http://www.freelists.org/webpage/oracle-l