DBAsupport.com Forums - Powered by vBulletin
Page 2 of 2 FirstFirst 12
Results 11 to 20 of 20

Thread: Stored indexes

  1. #11
    Join Date
    Jul 2003
    Location
    Sofia, Bulgaria
    Posts
    91
    Originally posted by slimdave
    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?
    Yes, exactly, some kind of nomenclature from numbers.

    Originally posted by jmodic
    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.
    SQL> create table testn(a number(1));

    Table created.

    SQL> select COLUMN_NAME, DATA_TYPE, DATA_LENGTH, DATA_PRECISION, DATA_SCALE from dba_tab_columns where table_name='TESTN';

    COLUMN_NAME DATA_TYPE DATA_LENGTH DATA_PRECISION DATA_SCALE
    ----------- --------- ----------- -------------- ----------
    A NUMBER 22 1 0

    Can you explain this then?
    You are right, numbers are stored exactly the same way, and they take 22 bytes.

  2. #12
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    Originally posted by stancho
    SQL> select COLUMN_NAME, DATA_TYPE, DATA_LENGTH, DATA_PRECISION, DATA_SCALE from dba_tab_columns where table_name='TESTN';

    COLUMN_NAME DATA_TYPE DATA_LENGTH DATA_PRECISION DATA_SCALE
    ----------- --------- ----------- -------------- ----------
    A NUMBER 22 1 0

    Can you explain this then?
    And what's to explain? What's your point? Perhaps the fact that your query returns DATA_LENGTH = 22? Well, FYI, for the variable-length datatypes this column doesn't show the amount of bytes required to store values. With NUMBER datatype DATA_LENGTH will allways be 22, but this is just the maximum number of bytes that any number in oracle can occupy (regardless of it's precision and scale). But in your test

    You are right, numbers are stored exactly the same way, and they take 22 bytes.
    It's time for you to take Oracle Concepts Manual and start reading about how Oracle stores various datatypes. For NUMBER datatype, read this: http://download-west.oracle.com/docs...3datyp.htm#784

    Also, maybe you want to find out what function VSIZE() is and what does it shows - read about it in SQL manual.

    In any case, any number stored in your TESTN table will occupy somewhere between 1 and 3 bytes - not even a byte more than that. Certanly none of them will occupy 22 bytes! And the same goes with numbers in indexes.
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

  3. #13
    Join Date
    Dec 2002
    Location
    Bangalore ( India )
    Posts
    2,434
    Originally posted by stancho
    Can you explain this then?


    Oracle stores numeric data in variable-length format. Each value is stored in scientific notation, with 1 byte used to store the exponent and up to 20 bytes to store the mantissa. The resulting value is limited to 38 digits of precision. Oracle does not store leading and trailing zeros. For example, the number 412 is stored in a format similar to 4.12 x 102, with 1 byte used to store the exponent(2) and 2 bytes used to store the three significant digits of the mantissa(4,1,2). Negative numbers include the sign in their length.

    Taking this into account, the column size in bytes for a particular numeric data value NUMBER(p), where p is the precision of a given value, can be calculated using the following formula:

    ROUND((length(p)+s)/2))+1


    wheres equals zero if the number is positive, ands equals 1 if the number is negative.

    Zero and positive and negative infinity (only generated on import from Version 5 Oracle databases) are stored using unique representations. Zero and negative infinity each require 1 byte; positive infinity requires 2 bytes.
    Abhay.
    funky...

    "I Dont Want To Follow A Path, I would Rather Go Where There Is No Path And Leave A Trail."

    "Ego is the worst thing many have, try to overcome it & you will be the best, if not good, person on this earth"

  4. #14
    Join Date
    Jul 2003
    Location
    Sofia, Bulgaria
    Posts
    91
    Originally posted by jmodic
    And what's to explain? What's your point? Perhaps the fact that your query returns DATA_LENGTH = 22? Well, FYI, for the variable-length datatypes this column doesn't show the amount of bytes required to store values. With NUMBER datatype DATA_LENGTH will allways be 22, but this is just the maximum number of bytes that any number in oracle can occupy (regardless of it's precision and scale).
    My mistake, sorry, maybe it is time that I should burn my 2 OCPs and start from scratch
    Yes, because of DATA_LENGTH = 22 I was thinking that numbers allways occupy 22bytes, because they are in a binary format.

    Sorry again that I caused inconvenience

  5. #15
    Join Date
    Dec 2002
    Location
    Bangalore ( India )
    Posts
    2,434
    Jurij :
    We indeed pointed the same quotes & you beat me by a sec
    funky...

    "I Dont Want To Follow A Path, I would Rather Go Where There Is No Path And Leave A Trail."

    "Ego is the worst thing many have, try to overcome it & you will be the best, if not good, person on this earth"

  6. #16
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    Originally posted by stancho
    My mistake, sorry, maybe it is time that I should burn my 2 OCPs and start from scratch
    Yes, because of DATA_LENGTH = 22 I was thinking that numbers allways occupy 22bytes, because they are in a binary format.

    Sorry again that I caused inconvenience
    Stancho,

    Perhaps I also owe an appology. Maybe my responses in this thread sounded a bit harsh and unfrendly - this certanly wasn't my intention. It's just that my blood presure raises when I see one of those common myths about Oracle reapearing again and again - and that "22-bytes for any NUMBER" is one of those myths. I have not intervened in this thread to give a lesson to you - it was more to prevent less experienced members from accepting that myth. Because that's exactly what happens most of the times: beginners usually take such "facts" as a solid gold and remember them for life. And perhaps this is the reason why "myths and legends about Oracle database are dying such a slow and painfull death", as Tom Kyte once said.
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

  7. #17
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    Originally posted by stancho
    Yes, exactly, some kind of nomenclature from numbers.
    There's nothing wrong with that situation, IMHO.

    If you are more likely to concatanate such codes, or substr() them, then it's more appropriate to store them as varchar2 orchar than as numbers. I see the fact that they could be stored as numbers merely as coincidental.

    But note that I wouldn't base the decision on what data type to store them as on how many bytes the different types would occupy -- the decision here is based on the kind of operation you want to perform on the column.
    David Aldridge,
    "The Oracle Sponge"

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

    Oracle ACE

  8. #18
    Join Date
    Jul 2003
    Location
    Sofia, Bulgaria
    Posts
    91
    Originally posted by jmodic
    Stancho,

    Perhaps I also owe an appology. Maybe my responses in this thread sounded a bit harsh and unfrendly - this certanly wasn't my intention. It's just that my blood presure raises when I see one of those common myths about Oracle reapearing again and again - and that "22-bytes for any NUMBER" is one of those myths. I have not intervened in this thread to give a lesson to you - it was more to prevent less experienced members from accepting that myth. Because that's exactly what happens most of the times: beginners usually take such "facts" as a solid gold and remember them for life. And perhaps this is the reason why "myths and legends about Oracle database are dying such a slow and painfull death", as Tom Kyte once said.
    No problem, when I see an error in something I show it too to the man that is making the mistake, and I am always ready to learn something new, or to understand it better

    Originally posted by slimdave
    There's nothing wrong with that situation, IMHO.

    If you are more likely to concatanate such codes, or substr() them, then it's more appropriate to store them as varchar2 orchar than as numbers. I see the fact that they could be stored as numbers merely as coincidental.

    But note that I wouldn't base the decision on what data type to store them as on how many bytes the different types would occupy -- the decision here is based on the kind of operation you want to perform on the column.
    That depends sometime on the hardware resources, and here we are poor . Several month ago I had to install Oracle 9i on an Windows XP box with 96MB ram

  9. #19
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    Originally posted by stancho
    That depends sometime on the hardware resources, and here we are poor . Several month ago I had to install Oracle 9i on an Windows XP box with 96MB ram
    Well, to some extent i appreciate your point -- I've seen situations where a client is willing to spend 10's of thousands of dollars in salary in order that they avoid spending $1000 on a couple of new disks.

    However that's a case of good design practice being impacted by dumb management, and that's a whole new topic.
    David Aldridge,
    "The Oracle Sponge"

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

    Oracle ACE

  10. #20
    Join Date
    Nov 2002
    Location
    Geneva Switzerland
    Posts
    3,142
    Originally posted by slimdave
    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.
    I think it is a important design issue to identify these cases. I've seen so many cases where a human allocated "number" became alphanum (e.g. when running out of digits). Unless you're going to do number/date arithmetic on the column (e.g. do you really want to know the average leger code?), I'd go for varchar2 every time.

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