| How indexes behave on a varchar field [message #65062] |
Mon, 12 April 2004 22:03  |
Shibaji Ghosh
Messages: 39
Registered: April 2002
|
Member |
|
|
Hi,
How indexes behave on a varchar field in Oracle 9i database. Do we have the normal index operation just like numeric fields or is it that the indexes are not used. Although the question sounds stupid but I have made some benchmarks where I have witnessed that indexes behave better if done on a numeric field. Is it the trend. Can anyone give me proper explanation of the same. Also am interested to know whether the question at all makes sense. This is totally based on some testing I have performed and some observations of mine.
|
|
|
|
| Re: How indexes behave on a varchar field [message #65063 is a reply to message #65062] |
Mon, 12 April 2004 23:58  |
Daljit Singh
Messages: 290
Registered: October 2003
Location: Texas
|
Senior Member |
|
|
Hi,
According to me there is no diff between indexes created on number field and on varchar field. Basically the indexes just store the data and rowid of the data to quickly access the row. Here the important thing is on which column u have created the index i.e. how data is distributed in that column. In another words how skewed it is? You can use the Histograms to increase performance even if ur data is not evenly distributed, regardless whether u have index in number or varchar.
In ur case i think u r facing the same problem the data in numeric field is evenly distributed and in varchar its not and u are not using histograms at all. Thats y u r facing such problem.
Whether the index will be used or not doesn't depend upon the datatype of the field it highly depend upon the standard of query u r using.
The whole piucture will get cleared only if u share ur testing and benchmarks with us. After that only we will be able to answer y u r getting such kind of response.
Daljit Singh.
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
