Home » RDBMS Server » Performance Tuning » How indexes behave on a varchar field
How indexes behave on a varchar field [message #65062] Mon, 12 April 2004 22:03 Go to next message
Shibaji Ghosh
Messages: 39
Registered: April 2002

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 Go to previous messageGo to next message
Daljit Singh
Messages: 290
Registered: October 2003
Location: Texas
Senior Member

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.
Re: How indexes behave on a varchar field [message #65064 is a reply to message #65062] Tue, 13 April 2004 08:22 Go to previous message
Messages: 1089
Registered: May 2002
Senior Member
Also,see this post --> Index on string


[Updated on: Fri, 18 February 2005 23:32]

Report message to a moderator

Previous Topic: Is Between faster?
Goto Forum:

Current Time: Sun Sep 20 16:26:42 CDT 2020