title: How should I range partition an index with a varchar2 column in Oracle? Is it a bad idea?
body: <p>I am using Oracle 10g Enterprise edition.</p>
  
  <p>A table in our Oracle database stores the soundex value representation of another text column. We are using a custom soundex implementation in which the soundex values are longer than are generated by traditional soundex algorithms (such as the one Oracle uses). That's really beside the point. </p>
  
  <p>Basically I have a varchar2 column that has values containing a single character followed by a dynamic number of numeric values (e.g. 'A12345', 'S382771', etc). The table is partitioned by another column, but I'd like to add a partitioned index to the soundex column since it is often searched. When trying to add a range partitioned index using the first character of the soundex column it worked great:</p>
  
  <pre><code>create index IDX_NAMES_SOUNDEX on NAMES_SOUNDEX (soundex)
  global partition by range (soundex) (
      partition IDX_NAMES_SOUNDEX_PART_A values less than ('B'),  -- 'A%'
      partition IDX_NAMES_SOUNDEX_PART_B values less than ('C'),  -- 'B%'
      ...
  );
  </code></pre>
  
  <p>However, I in order to more evenly distribute the size of the partitions, I want to define some partitions by the first two chars, like so:</p>
  
  <pre><code>create index IDX_NAMES_SOUNDEX on NAMES_SOUNDEX (soundex)
  global partition by range (soundex) (
      partition IDX_NAMES_SOUNDEX_PART_A5 values less than ('A5'), -- 'A0% - A4%'
      partition IDX_NAMES_SOUNDEX_PART_A values less than ('B'),   -- 'A4% - A9%'
      partition IDX_NAMES_SOUNDEX_PART_B values less than ('C'),   -- 'B%'
      ...
  );
  </code></pre>
  
  <p>I'm not sure how to properly range partition using varchar2 columns. I'm sure this is a less than ideal choice, so perhaps someone can recommend a better solution. Here's a distribution of the soundex data in my table:</p>
  
  <pre><code>-----------------------------------
  |  SUBSTR(SOUNDEX,1,1)  |  COUNT  |
  -----------------------------------
  |                    A  | 6476349 |
  |                    B  |  854880 |
  |                    D  |  520676 |
  |                    F  | 1200045 |
  |                    G  |  280647 |
  |                    H  | 3048637 |
  |                    J  |  711031 |
  |                    K  | 1336522 |
  |                    L  |  348743 |
  |                    M  | 3259464 |
  |                    N  | 1510070 |
  |                    Q  |  276769 |
  |                    R  | 1263008 |
  |                    S  | 3396223 |
  |                    V  |  533844 |
  |                    W  |  555007 |
  |                    Y  |  348504 |
  |                    Z  | 1079179 |
  -----------------------------------
  </code></pre>
  
  <p>As you can see, the distribution is not evenly spread, which is why I want to define range partitions using the first two characters instead of just the first character.</p>
  
  <p>Suggestions?</p>
  
  <p>Thanks!</p>