Hello DBAs;
How does oracle store index values? Basically I wanted to find out if there is a performance difference when index is an interger vs varchar. Does reverse indexes have any part to play?
oracle 9i R2 ..
Thx, ST2003
Printable View
Hello DBAs;
How does oracle store index values? Basically I wanted to find out if there is a performance difference when index is an interger vs varchar. Does reverse indexes have any part to play?
oracle 9i R2 ..
Thx, ST2003
Given comparable numbers of bytes to index, there is no appreciable performance difference between indexes of numeric, char, varchar2, or date columns.
Since column data types shuold be chosen to be appropriate to the type of value being stored (eg. don't store dates as characters or numbers), it's really a moot point.
Thanks slimdave.. can you explain once again, if you dont mind.I understand there is no difference between char and number, but unable to understand the reason clearly, what you meant.. I understood the part about avoiding further implicit conversions..
thx, st2003
There will clearly be a difference between indexing a varchar2 column that has an average column size of 400 bytes, and indexing a number column that has an average column size of 4 bytes (for example) because the index would be much bigger for the varchar2 type in this case. If the average number of bytes was the same, then the indexes would be about the same size, and it would be difficult to find a difference in performance.
Thanks slimdave.. It's crystal now..
st2003
Also have in mind that index on number columns allways takes 22 bytes of space for one value, even if your column is number(1), so you can save space if your column can be varchar2(1) (for that example).
Can you give an example of a situation where it would be beneficial to avoid using a number data type in favour of a varchar2, in order to get the benefit of that space saving?Quote:
Originally posted by stancho
Also have in mind that index on number columns allways takes 22 bytes of space for one value, even if your column is number(1), so you can save space if your column can be varchar2(1) (for that example).
In one of my previous jobs we had a table with CDR. There were 2-3 mnl records per day. Several of the columns was a varchar2, and the data was numbers, but because the queries were using exact match, and not range or something simular, there were not problem with using VARCHAR2 columns, and with that big tables (and we really had a problem with disk space) the tablespace savings was very usefull.
I hope that you understand my english :)
There have been situations where i have chosen to express a numeric as a character, but they were confined to those where the numeric was some kind of business code (generally referencing a lookup table) that just happened to be restricted to numeric characters (for example, with the US Treasuries Standard General Ledger Codes) -- in other words the code was not representative of a "real" number, it was just a string of characters that happened to be non-alpha.
Is this the kind of situation you mean?
That of course is total nonsence! Where did you get that from? This is obviously a variation of one of the popular myths (urban legends, fairy tales) about how numbers are stored in Oracle database. It of course has nothing in common with reality.Quote:
Originally posted by stancho
Also have in mind that index on number columns allways takes 22 bytes of space for one value, even if your column is number(1)
Numbers in index entries are stored exactly the same way as they are stored in tables and they require the same ammount of storage in indexes as they do in tables.