6 Replies Latest reply: Mar 25, 2009 2:25 PM by Hoek RSS

    Global Partitioned index

      I am trying to create a global partitioned index
      on a table temp having column seq_num whose datatype is varchar2

      CREATE INDEX indx_1 ON temp (seq_num)
      (PARTITION p1 VALUES LESS THAN (1000000000000),
      PARTITION p2 VALUES LESS THAN (5500000000000),
      PARTITION p3 VALUES LESS THAN (10000000000000),
      PARTITION P4 VALUES LESS THAN (55000000000000),

      i am geting the error as

      ORA-14037: partition bound of partition "P2" is too high
      I also tried putin the values in partition in single Quotes
      bt same error. Can some1 please let me know how can i achieve this
        • 1. Re: Global Partitioned index
          Is this comparable to your situation?


          I think it is, so you need another datatype to use.
          Don't store numbers (or dates) in VARCHAR2!!

          You always end up in troubles.
          Now you cannot partition on that column anymore, f.i....
          Another f.i. :If you wanted to sort on seq_num, you'll need to_number, or it won't sort proper at all, so probably unless you have a function based index on that, no index used in such queries..
          • 2. Re: Global Partitioned index
            on a table temp having column seq_num whose datatype is varchar2
            Yikes, how does that happen?
            • 3. Re: Global Partitioned index
              Perhaps the designer/data modeler thought it was 'nice' or 'easy'?
              Only Njafri can tell why that number is stored in a varchar2.
              And promise us NEVER to do that again ;-)
              • 4. Re: Global Partitioned index
                Hi Hoek
                I got your point that i cannot create global index on this column because it is varchar2
                I did not understand your last 2 lines......Should i create a function based index to speed up
                my query ? please elaborate
                • 5. Re: Global Partitioned index
                  It is a possibility, an assumption, I know nothing of your table right now.
                  How many records (if you consider partitioning, I guess large).
                  All that's for sure is that seq_num is stored in a varchar, and that is a wrong design.
                  Perhaps your seq_num column is a PK or FK, and often joined to other tables...
                  Is that the case?
                  Perhaps you need to query on seq_num ranges? (where seq_num between this and that)
                  If that's the case you really need to_number(seq_num) or you can get the wrong results.
                  Do you have performance problems regarding this table/column?

                  If you can provide more details on how seq_num is used, on the table, I can elaborate more, now I'm frankly only guessing, based on my experiences and from what I've read in similar problems ;-).

                  A function based index might help.
                  And it's easy to create one and test if it really helps.
                  • 6. Re: Global Partitioned index
                    One more thing:

                    You could also add an extra column (NUMBER) to your table update it with seq_num
                    (if possible) just drop the VARCHAR2 column
                    (if not possible) let other tables refer to that column

                    Anyway, you could partition on THAT column then.