Menu
If I query my DataBase (Oracle) with unique Index on multiple columns, Will there be any performance difference if I change one of the columns from VARCHAR2 to NUMBER?
If there is, is it significant?
(It's varchar2 because I need '0' at the beginning but I can change it in the presentation layer in my app)
234
Searching is faster in CHAR as all the strings are stored at a specified position from the each other, the system doesnot have to search for the end of string. Whereas in VARCHAR the system has to first find the end of string and then go for searching.
You can insert any VARCHAR2(n) value into a LONG database column because the maximum width of a LONG column is 2147483648 bytes or two gigabytes. However, you cannot retrieve a value longer than 32767 bytes from a LONG column into a VARCHAR2(n) variable.
So if you have “Normal” character set in most of the rows then varchar2 is better than nvarchar2 if your database character set is UTF%, since varchar2 will use 1 byte for every normal character and nvarchar2 will use 2 bytes for every normal character.
More knowledgeable people than me tell me that in Oracle the performance difference between number and varchar2 depends entirely on your data, since internally they have very similar representations. See the following:
https://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:2669498700346402356
but also look at the "Internal Numeric Format" section of:
http://docs.oracle.com/cd/B28359_01/server.111/b28318/datatype.htm#CNCPT012
If, for example, your varchar data was actually single characters (such as 'Y', 'N') then you'd probably be better off with varchars than numerics. If you've got fewer than 100 codes but your string equivalents were 8 characters long then you'd probably be better off with numerics than varchars.
174
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
