This discussion is archived
6 Replies Latest reply: Mar 25, 2009 12:25 PM by Hoek RSS

Global Partitioned index

690963 Newbie
Currently Being Moderated
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)
GLOBAL PARTITION BY RANGE (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),
PARTITION P5 VALUES LESS THAN (MAXVALUE));

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
    Hoek Guru
    Currently Being Moderated
    Is this comparable to your situation?

    http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:2460734556210#14195242881508

    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
    SomeoneElse Guru
    Currently Being Moderated
    on a table temp having column seq_num whose datatype is varchar2
    Yikes, how does that happen?
  • 3. Re: Global Partitioned index
    Hoek Guru
    Currently Being Moderated
    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
    690963 Newbie
    Currently Being Moderated
    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
    Hoek Guru
    Currently Being Moderated
    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
    Hoek Guru
    Currently Being Moderated
    One more thing:

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

    Anyway, you could partition on THAT column then.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points