|
View previous topic :: View next topic
|
| Author |
Message |
knn9413
New User
Joined: 23 Jul 2009
Posts: 17
Location: US
|
|
|
|
Hi,
Here is my problem. I have a column defined as CHAR(10) and it has an index defined on it. I need to fetch the max value in this column and add 1 to it. The contents of the column is present as 129, 92, 356.. (Whoever designed the database wasn't thinking).
I need to order them as 92,129,356 and add 1 to the highest number (356 in this case). CAST coupled with the ORDER BY can achieve this, however I believe the performance will be impacted. (The table is pretty large with around 15 million rows)
My question is:
Is it possible to define another index with the CAST function on the same column? If yes, how do I use that given the fact that I have 2 different indices on the same column
An replies or suggestions will be highly appreciated
Thanks in advance |
|
| Back to top |
|
 |
gylbharat
Active Member
.jpg)
Joined: 31 Jul 2009
Posts: 565
Location: Bangalore
|
|
|
|
| Which DB2 version? |
|
| Back to top |
|
|
dbzTHEdinosauer
Global Moderator
Joined: 20 Oct 2006
Posts: 6966
Location: porcelain throne
|
|
|
|
why don't you just add an integer column,
populate it with a one-time = int(char(column))
and put an index on new int-column
modify existing logic:
and when you increment the counter, also set char-column to the newly incremented int-column
that way you have this idiot char-column still available for what ever-use
except for incrementing, thus you would not suffer the impact of search for a numeric value in a char column. |
|
| Back to top |
|
|
knn9413
New User
Joined: 23 Jul 2009
Posts: 17
Location: US
|
|
|
|
| dbzTHEdinosauer wrote: |
why don't you just add an integer column,
populate it with a one-time = int(char(column))
and put an index on new int-column
modify existing logic:
and when you increment the counter, also set char-column to the newly incremented int-column
that way you have this idiot char-column still available for what ever-use
except for incrementing, thus you would not suffer the impact of search for a numeric value in a char column. |
Thanks Dick. That was the last option that I had on my plate..
gylbharat, we are using db2 v 9 |
|
| Back to top |
|
|
GuyC
Senior Member
Joined: 11 Aug 2009
Posts: 1281
Location: Belgium
|
|
|
|
| Code: |
create index testindex on testtable (int(col1))
select strip(char(max(int(col1))+1)) from testtable |
should work & use testindex index-only as accesspath |
|
| Back to top |
|
|
|
|
