DBAsupport.com Forums - Powered by vBulletin
Results 1 to 9 of 9

Thread: Column_length

  1. #1
    Join Date
    Apr 2001
    Location
    Bangalore, India
    Posts
    727

    Column_length

    Hi

    I was estimating the length of the row in an index. Now I found

    in an index

    COLUMN_LENGTH = 22 (from dba_ind_columns) and
    column definition is Number (8) (from the table definition)

    Confused the relation between number(8) and the value 22.. Share your knowledge with me.. Please


    Thanking in advance.


    Thomas
    Last edited by Thomasps; 02-17-2003 at 08:51 PM.
    Thomas Saviour(royxavier@yahoo.com)
    Technical Lead (Databases)
    Thomson Reuters (Markets)

    http://ora600tom.wordpress.com/

  2. #2
    Join Date
    Feb 2001
    Location
    UAE
    Posts
    304
    By default a number's length is considered as 22, which is clear from the following example:
    SQL>desc dummy
    Name Type
    ------------- -------------
    N0 NUMBER
    N1 NUMBER(8)
    N2 NUMBER(1)
    N3 NUMBER(38)

    SQL>select COLUMN_LENGTH, COLUMN_NAME from dba_ind_columns where TABLE_NAME='DUMMY';
    COLUMN_LENGTH COLUMN_NAME
    ------------- ------------------------
    22 N0
    22 N1
    22 N2
    22 N3
    Last edited by agasimani; 02-17-2003 at 06:21 AM.
    Agasimani
    OCP(10g/9i/8i/8)

  3. #3
    Join Date
    Feb 2001
    Location
    UAE
    Posts
    304
    Since you want to know the reason, here is excerpts from Oracle documents:

    "when you enter a number into a column with a number datatype, a conversion takes place that alters the value into base 100. Therefore, you cannot control the length (in bytes) that any particular number may take. The maximum, however, is 22 bytes for any number that is allowable to be recorded in a number field. Therefore, when datalength is calculated for number fields, the maximum of 22 must be specified."
    Agasimani
    OCP(10g/9i/8i/8)

  4. #4
    Join Date
    Apr 2001
    Location
    Bangalore, India
    Posts
    727

    Yes..

    Hi

    Thanks for the reply

    Well, agree.. It is obvious it is 22 bytes.. But how it is reaching 22 bytes.

    Got info from a site as

    NUMBER Datatype:
    Stores fixed and floating-point numbers up to 38 digits of precision. In this datatype you can specify precision (total number of digits) and scale (number of digits to the rigth of the decimal point). If no scale is specified the scale is zero. If you specify a negative scale, Oracle rounds the actual data to the specified number of places to the left of the decimal point.
    Oracle stores numeric data in variable-length format. Each value is stored in scientific notation with one byte used to store the exponent and up to 20 bytes to store the mantissa.It does not store leading and trailing zeros.

    Still reaching up 21 (20 Exponent + 1 mantissa). So what is the relation to 22..?

    Thanks

    Thomas
    Thomas Saviour(royxavier@yahoo.com)
    Technical Lead (Databases)
    Thomson Reuters (Markets)

    http://ora600tom.wordpress.com/

  5. #5
    Join Date
    Nov 2002
    Location
    Geneva Switzerland
    Posts
    3,142
    And the sign?
    "The power of instruction is seldom of much efficacy except in those happy dispositions where it is almost superfluous" - Gibbon, quoted by R.P.Feynman

  6. #6
    Join Date
    Apr 2001
    Location
    Bangalore, India
    Posts
    727

    sign

    DaPi

    If no scale is specified the scale is zero. If you specify a negative scale, Oracle rounds the actual data to the specified number of places to the left of the decimal point.
    Is it including sign..?

    Thanks

    Thomas
    Thomas Saviour(royxavier@yahoo.com)
    Technical Lead (Databases)
    Thomson Reuters (Markets)

    http://ora600tom.wordpress.com/

  7. #7
    Join Date
    Feb 2003
    Posts
    1
    You can use TOAD to help you to estimate the space of index.

  8. #8
    Join Date
    Apr 2001
    Location
    Bangalore, India
    Posts
    727

    THANKS

    Thanks Guys..

    Any how I don't have TOAD to explore more..


    Thomas
    Thomas Saviour(royxavier@yahoo.com)
    Technical Lead (Databases)
    Thomson Reuters (Markets)

    http://ora600tom.wordpress.com/

  9. #9
    Join Date
    Feb 2000
    Location
    New York,U.S.A.
    Posts
    245
    You can download TOAD from http://www.toadsoft.com
    The full version is free until 03/08/03.
    It has free version though if you don't want to purchase the full version.

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