Stored indexes
DBAsupport.com Forums - Powered by vBulletin
Page 1 of 2 12 LastLast
Results 1 to 10 of 20

Thread: Stored indexes

  1. #1
    Join Date
    Jul 2003
    Location
    Cincinnati
    Posts
    68

    Stored indexes

    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

  2. #2
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    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.
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

  3. #3
    Join Date
    Jul 2003
    Location
    Cincinnati
    Posts
    68
    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

  4. #4
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    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.
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

  5. #5
    Join Date
    Jul 2003
    Location
    Cincinnati
    Posts
    68
    Thanks slimdave.. It's crystal now..
    st2003

  6. #6
    Join Date
    Jul 2003
    Location
    Sofia, Bulgaria
    Posts
    91
    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).

  7. #7
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    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).
    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?
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

  8. #8
    Join Date
    Jul 2003
    Location
    Sofia, Bulgaria
    Posts
    91
    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

  9. #9
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    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?
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

  10. #10
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    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)
    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.

    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.
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width