Re: indexes in big character columns
- From: Stephane Faroult <sfaroult@xxxxxxxxxxxx>
- To: oralrnr@xxxxxxxxx
- Date: Wed, 26 Dec 2012 08:31:58 +0100
Orlando,
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.
>
>
> --
> //www.freelists.org/webpage/oracle-l
>
>
>
--
//www.freelists.org/webpage/oracle-l
Other related posts:
