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