Re: indexes in big character columns

• Original Message ----- From: "Stephane Faroult" <sfaroult_at_roughsea.com> To: <oralrnr_at_gmail.com> Cc: <oracle-l_at_freelists.org> Sent: Wednesday, December 26, 2012 7:31 AM Subject: Re: indexes in big character columns
  | 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 index compression deduplicates the prefixes in the block, but that doesn't mean Oracle can't reconstruct the full index entry. Basically Oracle's code will say: "these 10 index entries all start with prefix 1; there 14 start with prefix 2" and so on.

| 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.
|

Or, to view another aspect of the problem, the longer the key, the lower the probability that someone will key it in correctly.

Perhaps the "shouldn't be indexed" is really related to the fact that the statistics on character columns that are longer than a few characters can be really bad. Essentially Oracle has some stats about the first 6 bytes of a character column - though this can go up to 15 bytes for histograms on character columns, and in special cases 32 bytes. If your 'long character columns" all start with http://www. then the optimizer may say that there's only one distinct value for the column across the whole table !

Regards
Jonathan Lewis

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