This content has been marked as final. Show 6 replies
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..
on a table temp having column seq_num whose datatype is varchar2Yikes, how does that happen?
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 ;-)
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.
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.