Skip to Main Content

Breadcrumb

May 4th

Question and Answer

Chris Saxon

Thanks for the question, Vikas.

Asked: October 14, 2001 - 11:41 pm UTC

Last updated: October 10, 2019 - 8:18 am UTC

Version: 8.0.3

Viewed 100K+ times! This question is

You Asked

Hi Tom,

I declared the datatype of a column of a table as NUMBER & Presumed it to be Number(38) to be specific.

But What I found to be strange that, it is accepting digits beyong 38 digits.ie. 1.111E+125. If the Datatype's precision is 38 and scale can range from -84 to 127 how this value can insert into the column.


In one of the System owned tables, USER_TAB_PRIVS there is a column known as Table_Name but under this column you will find all the objects of a schema which has been granted object privs. whether it is table,view or any sequence.

Shouldn't it be more precise to define this column name as Object_name rather that the table_name.


Please do explain & clarify.


Thanks in Anticipation,
Vikas Khanna
Vkhanna@quark.stpm.soft.net

and Tom said...

1.111e+125 only has 4 digits of precision (number of digits needed to represent the number -- in this case 1111).


The 38 is for 38 digits of precision. The SCALE dictates how many places to the right or left of the decimal place you may have.


It would be more clear to have USER_TAB_PRIVS say that -- however, it would break lots of stuff. User_tab_privs pre-dates many of these object types and retains its naming for backwards compatibility reasons.

Rating

  (78 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

More Clarification

Vikas Khanna, October 16, 2001 - 2:01 am UTC

I entered a number 12345678901234567890123456789012345678901234567890123456789012345678901234567890 into that particular column.

It showed me the result as
12345678901234567890123456789012345678900000000000000000000000000000000000000000, when set numwidth 2000 was issued.
(SQL Worksheet accepts it)

When issued set numwidth 80 it displayed ,
1.2345678901234567890123456789012345678900000000000000000000000000000000000000000E+79

Why it is so??Moreover I was not able to understand the concept behind this.Can u please explain in more detail how Oracle does store the Numbers internally. Take relevant examples for precision & scale.


Tom Kyte
October 16, 2001 - 8:08 am UTC

Well first, I hope you have read the documentation (always a great place to start). see

http://docs.oracle.com/docs/cd/A81042_01/DOC/server.816/a76965/c10datyp.htm#743

there is a nice table there with various precisions/scales that demonstrate the outcome.


Numbers are stored with an assured (at least) 38 digits of precision.  It is allowed to go higher (you got 39 digits of precision).  Numbers that exceed the PRECISION when the precision is not specified are rounded to fit in there (just like if you stuff a 15 digit number into a C float/double -- it'll be made to "fit" since they hold numbers with 6/13 digits of precision only)

The number is like number(*) (wildcard)

ops$tkyte@ORA717DEV.US.ORACLE.COM> create table t ( x number(*) );
Table created.

ops$tkyte@ORA717DEV.US.ORACLE.COM> insert into t values ( 12345678901234567890123456789012345678901234567890  );

1 row created.

ops$tkyte@ORA717DEV.US.ORACLE.COM> select to_char(x,rpad('9',60,'9')) from t;

TO_CHAR(X,RPAD('9',60,'9'))
-------------------------------------------------------------
           12345678901234567890123456789012345678900000000000



With a numwidth of 2000 -- there was enough room to display the number in its entirety without using scientific notation -- so it did.  With a numwidth of 80, there was NOT enough room -- so it used scientific notation to display the number in your requested field size.  That part seems all very logical (to me anyway, it was just doing exactly what you asked it to do).

 

Vijay, July 04, 2002 - 3:41 am UTC

When I create a table like:
create table test(no number(7)) ;

insert into test values(1.00e+25) ;
it gives the following error message:
ORA-01438: value larger than specified precision allows for this column.

Can u pls. explain this.

Tom Kyte
July 04, 2002 - 11:12 am UTC

you said 7 digits, you gave it 25

number(7) is very different from number. Number is really like number(*) -- an number(*) is acceptable and has a meaning.

read the link provided above (well, what the link points to)

Regarding Number datatype

A reader, July 04, 2002 - 12:22 pm UTC

Vijay..

Better go back and read the Oracle manual before coding.....

declaring number vs number(n)

A reader, June 30, 2003 - 7:37 am UTC

Hi

What the difference between declaring a column using datatype

number
and
number(n)?

Ay advantage or disadvantge declaring one way and the another?

Tom Kyte
June 30, 2003 - 7:50 am UTC

number(n) is a number with a constraint and edit.

ops$tkyte@ORA920> create table t ( x number, y number(5) );

Table created.

ops$tkyte@ORA920>
ops$tkyte@ORA920> insert into t values ( 123.456, 123.456 );

1 row created.

ops$tkyte@ORA920> insert into t values ( 123.999, 123.999 );

1 row created.

ops$tkyte@ORA920> insert into t values ( 12345, 12345 );

1 row created.

ops$tkyte@ORA920> insert into t values ( 123456, 123456 );
insert into t values ( 123456, 123456 )
                               *
ERROR at line 1:
ORA-01438: value larger than specified precision allows for this column


ops$tkyte@ORA920> select * from t;

         X          Y
---------- ----------
   123.456        123
   123.999        124
     12345      12345


See how the number(5) behaves?  5 digits, no decimals, rounded.  


The advantage is 

a) you have 5 digits only, never more
b) you have no decimals, it is an integer type


consider it a constraint -- like a primary key, NOT NULL, check, whatever.  


If your data is such that the number should never be more then 5 digits, the only correct way to implement it would be as a number(5) 

How do we express infinity in Oeacle?

A Reader, June 30, 2003 - 8:40 am UTC

Like 1 divided by infinity=0.
Thanks.

Tom Kyte
June 30, 2003 - 9:21 am UTC

null

there is no "infinity".

Dave, June 30, 2003 - 10:20 am UTC

Time to roll out the trivia.

You could have a positive or negative "infinity" value by importing it from Oracle 5, apparantly.

5 was before my time, but are operations on such special numbers still supported in 9i Tom? Not the generation of the value, but multiplication, SUM() etc?

Tom Kyte
June 30, 2003 - 10:27 am UTC

those NAN and infinity values from v5 and before would cause some amount of "issues"

how number and char datatype behave different from varchar/varchar2

A reader, July 23, 2003 - 11:11 am UTC

Dear Tom,

My question is how number and char datatype behave different from varchar/varchar2?
As you see in my example, I can't reduce the size for Number & Char datatype when the table has data in it,
but I can reduce the size of varchar/varchar2 even if the table is not empty.

1- Is it because when you define a column as Number(n) or Char(n) it allocates all storage? Aren't they dynamic?
2- Is the only difference between Number and Number(n) is a constraint

Could you please explain this.

Thanks in advance,
Arash


system@T800> create table t (x number(10));

Table created.

system@T800> alter table t modify x number(9);

Table altered.

system@T800> insert into t values(10);

1 row created.

system@T800> alter table t modify x number(8);
alter table t modify x number(8)
*
ERROR at line 1:
ORA-01440: column to be modified must be empty to decrease precision or scale


system@T800> delete from t;

1 row deleted.

system@T800> alter table t modify x number(5);

Table altered.

---------------------------------------------------

system@T800> create table t1(x varchar(10));

Table created.

system@T800> alter table t1 modify x varchar(9);

Table altered.

system@T800> insert into t1 values('aaa');

1 row created.

system@T800> alter table t1 modify x varchar(8);

Table altered.

system@T800> alter table t1 modify x char(8);

Table altered.

system@T800> alter table t1 modify x char(7);
alter table t1 modify x char(7)
*
ERROR at line 1:
ORA-01441: cannot decrease column length because some value is too big

system@T800> delete from t1;

1 row deleted.

system@T800> alter table t1 modify x char(7);

Table altered.



Tom Kyte
July 23, 2003 - 6:59 pm UTC

it is relatively new that you can reduce the size of a varchar.

a CHAR by very definition cannot be as the entire table needs to be rewritten -- a char(8) has 8 characters -- to make it a char(7) would involve shrinking each and every column.


A number type just doesn't support being "sized down" right now.

A reader, July 24, 2003 - 10:56 am UTC

Just one word Tom. You are great! Thanks for your time. Thanks for all of your support even during your vacation.

Have a pleasant vacation,
Arash


How to distinguish between NUMBER and FLOAT datatypes?

onsh76, October 15, 2003 - 12:32 pm UTC

Hi Tom,

Will you please explain a little bit more about new feature introduced in Oracle 9i patchset#2761332 for Windows, release 9.2.0.3. event 10499:
--------------------------------
It is not sufficient to just test the scale alone to distinguish between these two datatypes, one needs to test for a scale of -127 and a non-zero precision to identify a FLOAT.
--------------------------------

how can there be scale=-127 and precision=0?
What's Oracle saying, that would not be a FLOAT?

Thanks,

onsh76


Tom Kyte
October 15, 2003 - 6:37 pm UTC

don't really know anything about it personally. float has always been synonymous with number -- we don't really "do floats" (until 10g)

Number size and space usage

A reader, December 16, 2003 - 12:49 pm UTC

Hi Tom,

Regarding space usage for number types, do I need two times of space to store a column of NUMBER(10) than a column of NUMBER(5)?

I read in an article that they are the same, even a NUMBER(38), but I am not sure.

Thanks.



Tom Kyte
December 16, 2003 - 1:57 pm UTC

number(n) is an edit -- restricting the number to n digits in length.

if you store the number '55', it takes the same space in a number(2) as it does in a number(38).

the storage required = function of the number actually stored.


99999 takes more space to store then
10000 does

(you can use vsize() to see the size)

is there any way I can get the precision

A reader, March 18, 2004 - 11:00 am UTC


 i have table like 

SQL> create table t ( id number(9));

Table created.

SQL> insert into t values(3.2);

1 row created.

SQL> select * from t;

        ID
----------
         3

SQL> column id format 999,999,999
SQL> l
  1* select * from t
SQL> /

          ID
------------
           3

is there any any any any way I can get 3.2 back 
from t ?
 

Tom Kyte
March 18, 2004 - 11:08 am UTC

umm, you told us "number(9)"  that is (9,0).  it is an integer.

your very definition precludes the 0.2 from being saved.

No, that number was rounded to 3 upon insert as per your request.

ops$tkyte@ORA9IR2> create table t ( id number(9));
 
Table created.
 
ops$tkyte@ORA9IR2> insert into t values ( 3.4 );
 
1 row created.
 
 
ops$tkyte@ORA9IR2> insert into t values ( 3.5 );
 
1 row created.
 
ops$tkyte@ORA9IR2> select * from t;
 
        ID
----------
         3
         4
 
ops$tkyte@ORA9IR2>


You told us "integerize this please" 

Thanks !! we have to alter the column but table has 5M records !!

A reader, March 18, 2004 - 11:11 am UTC


Tom Kyte
March 18, 2004 - 3:03 pm UTC

so? it'll only take a couple of milli-seconds?

big_table@ORA9IR2> select count(*), count(some_number) from big_table;

COUNT(*) COUNT(SOME_NUMBER)
---------- ------------------
1000000 1000000

so, 1,000,000 non-null values (on a desktop pc to boot)....

big_table@ORA9IR2> set timing on
big_table@ORA9IR2> alter table big_table modify some_number number(11,2);

Table altered.

Elapsed: 00:00:00.01

it is a simple DDL change to give it 2 decimal places... no data was touched in this operation

big_table@ORA9IR2> desc big_table;
Name Null? Type
----------------------------------- -------- ------------------------
ID NOT NULL NUMBER
OWNER NOT NULL VARCHAR2(30)
OBJECT_NAME NOT NULL VARCHAR2(30)
SUBOBJECT_NAME VARCHAR2(30)
OBJECT_ID NOT NULL NUMBER
DATA_OBJECT_ID NUMBER
OBJECT_TYPE VARCHAR2(18)
CREATED NOT NULL DATE
LAST_DDL_TIME NOT NULL DATE
TIMESTAMP VARCHAR2(19)
STATUS VARCHAR2(7)
TEMPORARY VARCHAR2(1)
GENERATED VARCHAR2(1)
SECONDARY VARCHAR2(1)
SOME_NUMBER NUMBER(11,2)



And there you go, your 9 digit integer field is now an 11 digit field with 2 decimal places.

yep

A reader, March 18, 2004 - 4:42 pm UTC

but it has to

1.) get approved/change mgmt.
2.) put it in a script.
3.) test against all related apps.
4.) full qa cycle ***
5.) done

it is not hard to code or run. but make sure everything works with that change. Insted of that if I can query it in
different way for new apps. no change to the
**existing **Working apps.

but, I know it is integer so it is integer. will schedule it.

Thanks for your answers..
Regards,


Tom Kyte
March 18, 2004 - 5:02 pm UTC

but those steps are "regardless of number of records" -- they take the same time.

Apps that are expecting a 9 digit integer could well break, yes.

Doubt - Number Vs Number(n)

Dan, August 12, 2004 - 9:05 am UTC

Hi tom,

I am doing database sizeing. I am expecting that one of my table will have 50,000,000 records in future(say within 6 month). That table has composite primary key(5 columns). All the columns are NUMBER data type. In All_tab_column I could see the data length is always 22. So I tried to decrease the length( Number(10)). Then also its saying data length is 22. But I Know that I can't enter data in this field more than 10 digits. So In this case it should be 12. But why its showing 22(Data_length field in All_tab_columns).

My question is, for my sizeing calculation, should I take this length 12 or 22?.

Thanks

Tom Kyte
August 12, 2004 - 9:34 am UTC

that is the max length -- numbers are stored as varying length character strings.

The number 1,000,000,000 takes much less space than 1,000,000,001


the only way to size is load some representative data, some percentage of the total number of rows, analyze it, and then multiply (eg: load 1% of the data, analyze, see how big it is, multiply by 100)

Maximum length 22 for storage - is that for index too ?

Kim Berg Hansen, October 12, 2004 - 5:11 am UTC

Hi, Tom

I understand your explanation that storage length in the table for a number depends on the actual number value and NOT on the definition of the number column - i.e. NUMBER or NUMBER(10,0).

How about the storage length in an index on that column?
Will that always require a length equal to the maximum length of 22 (similar to indexes on varchar2 columns?)

Thanks for your practically unlimited patience :-)


Tom Kyte
October 12, 2004 - 8:20 am UTC

ops$tkyte@ORA9IR2> create table t
  2  as
  3  select to_number( rpad('9',38,'0') )+object_id x, object_id y
  4    from all_objects;
 
Table created.
 
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> create index t_idx1 on t(x);
 
Index created.
 
ops$tkyte@ORA9IR2> create index t_idx2 on t(y);
 
Index created.
 
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> analyze index t_idx1 validate structure;
 
Index analyzed.
 
ops$tkyte@ORA9IR2> create table i as select * from index_stats;
 
Table created.
 
ops$tkyte@ORA9IR2> analyze index t_idx2 validate structure;
 
Index analyzed.
 
ops$tkyte@ORA9IR2> insert into i select * from index_stats;
 
1 row created.
 
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> select a.cname, a.val t1, b.val  t2
  2    from table( cols_as_rows( 'select * from i where name = ''T_IDX1'' ' ) ) a,
  3         table( cols_as_rows( 'select * from i where name = ''T_IDX2'' ' ) ) b
  4   where a.rnum = b.rnum
  5     and a.cname = b.cname
  6     and ( a.val <> b.val or ((a.val is null or b.val is null) and a.val||b.val is not null) )
  7  /
 
CNAME           T1                   T2
--------------- -------------------- --------------------
BLOCKS          256                  72
BR_ROWS         124                  60
BR_ROWS_LEN     3469                 698
BTREE_SPACE     1007528              495784
LF_BLKS         125                  61
LF_ROWS_LEN     892425               436160
NAME            T_IDX1               T_IDX2
USED_SPACE      895894               436858
 
8 rows selected.


(cols_as_rows is from Effective Oracle By Design -- code available from oraclepress.com with the book.  useful to compare two rows column/column)


The size of the number affects the size of the index itself.  The numbers are not stored using fixed lengths. 

shreelakshmi nayak, January 07, 2005 - 7:03 am UTC

Thank you tom Please Send a Copy of every such problems with solution

Tom Kyte
January 07, 2005 - 9:21 am UTC

huh?

Data dictionary

Ajay, March 02, 2005 - 4:41 pm UTC

Tom,
Are the upper and lower limits for numeric datatypes stored anywhere in the data dictionary?


Tom Kyte
March 02, 2005 - 5:25 pm UTC

they are stored in the "documentation", not in the dictionary.

Right

Ajay, March 02, 2005 - 5:33 pm UTC

I was hoping to not have to hard-code the values. BTW, the 9ir2 database reference doesn't have the limits. A documentation bug? Its fixed in the 10gr1 database reference.

Tom Kyte
March 02, 2005 - 5:45 pm UTC

concepts guide has always had this

</code> http://docs.oracle.com/docs/cd/B10501_01/server.920/a96524/c13datyp.htm#16210 <code>


'hard code'? why would you even need them in your code? but if you do, just use a constant in a package -- in one place.

Hard coding max value

Ajay, March 02, 2005 - 9:36 pm UTC

I have to modify a query used by Oracle Reports to accept an optional parameter, and the where clause would be something like

WHERE rank between 0 and NVL(&&rank, <MAX_VALUE>).

I was hoping to replace <MAX_VALUE> with an expression that would select the max possible numeric value from the data dictionary. Cleaner than having magic numbers, I thought, even if 9.99...9 x POWER(10, 125) is slightly more than the number of rows we have.


Tom Kyte
March 02, 2005 - 9:39 pm UTC

seems like you could pick a reasonable max based on rank, it's datatype

say

to_number(rpad('9',38,'9'))



Reasonable max

Ajay, March 02, 2005 - 9:44 pm UTC

Thats what I'll do. Thanks for your help, Tom.

Another solution?

Frank, March 03, 2005 - 2:23 am UTC

I might have another solution for Ajay's problem:
since rank is per definition <= MAX_VALUE you could change your where-clause to

where rank between 0 and nvl(&&rank and rank)

If Oracle decides to increase the max_value of a number in versions to come, there is no need to change your code.

(or am I overseeing something?)

hth

Tom Kyte
March 03, 2005 - 7:27 am UTC

with nvl() or expansion and the CBO -- that is certainly reasonable.

Very nice

Ajay, March 03, 2005 - 7:36 am UTC

Thank you, Frank!

Rajiv, April 18, 2005 - 12:40 pm UTC

Tom, I am confused about the number of bytes required to store a number. I read the documentation </code> http://download-west.oracle.com/docs/cd/B10501_01/server.920/a96524/c13datyp.htm#16210 <code>

It says:

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.

I am really confused about the "length(p)" part. That means the precision has a very little effect on deciding the bytes required to store the number.

For example, according to the fomula, the bytes required to store a number with precision 9 and scale 0 would be:
round((length(9)+0)/2))+1=2. That does not match what is given by vsize when I did this small test:

appdba@RDEV>create table t (
2 n1 number(9,0),
3 n2 number(9,2)
4 )
5 /

Table created.

appdba@RDEV>insert into t values (999999999, 9999999.99)
2 /

1 row created.

appdba@RDEV>select vsize(n1), vsize(n2) from t;

VSIZE(N1) VSIZE(N2)
---------- ----------
6 6

1 row selected.

That looks like round((9+0)/2)+1. Is it possible the correct formula then could be: ROUND((p+s)/2))+1? Can it be a documentation bug or am I totally missing the point?



Tom Kyte
April 18, 2005 - 1:23 pm UTC

the p's and s's are based on the actual VALUE you are storing, not the defined precision and scale:

ops$tkyte@ORA10G> select round((length(999999999)+0)/2)+1 from dual;
 
ROUND((LENGTH(999999999)+0)/2)+1
--------------------------------
                               6


the P in the forumula is the number of digits you are asking to actually STORE.


9000000000000
has one digit of precision
1111111111111
has 13 digits of precision 

A reader, April 18, 2005 - 6:15 pm UTC

ah! got it, thank you.

One doubt still, What would be the bytes requirement for storing the value 9999999.99?
Wouldn't it be:

select round((length(999999999)+2)/2)+1 from dual
/

ROUND((LENGTH(999999999)+2)/2)+1
--------------------------------
7

Why is it different from the value given by vsize:


select vsize(9999999.99) from dual;
/

VSIZE(9999999.99)
-----------------
6

Thanks for your time.

Tom Kyte
April 18, 2005 - 7:01 pm UTC

<quote src=link above>
where s equals zero if the number is positive, and s equals 1 if the number is negative.
</quote>

ops$tkyte@ORA10G> select  round((length(999999999)+0)/2)+1 from dual;
 
ROUND((LENGTH(999999999)+0)/2)+1
--------------------------------
                               6


it was confusing of them to use P and S I agree. 

NaN vs Inf

Petri Rautakoski, March 02, 2006 - 4:13 am UTC

In our software NaN, Inf and -Inf should be separated. NaN is easy to represent with NULL bu what about this infinity problem? Tom, do You have any suggestion?

Tom Kyte
March 02, 2006 - 12:18 pm UTC

are you using floats/doubles? they support nan and +/-inf

Stop rounding

Richard, June 12, 2006 - 2:54 pm UTC

Hello Tom.

Is there a way to stop the rounding of the numbers stored into the field? I want Oracle to throw an error if I try to enter more than two decimal digits.

For example...
SQL> create table foo (f1 number(5,2));

Table created.

SQL> insert into foo values (123.45);

1 row created.

SQL> insert into foo values (48.648);

1 row created.

SQL> select * from foo;

        F1
----------
    123.45
     48.65

SQL> 



I would like the second insert statement to fail, not round up the data. Is that possible?

 

Tom Kyte
June 13, 2006 - 10:50 am UTC

yes and no.  Yes, we can achieve your goal.  No, we cannot use the number(5,2) anymore.


ops$tkyte@ORA10GR2> create table t ( x number check (cast(x as number(5,2)) = x));

Table created.

ops$tkyte@ORA10GR2> insert into t values ( 123.45 );

1 row created.

ops$tkyte@ORA10GR2> insert into t values ( 123.456 );
insert into t values ( 123.456 )
*
ERROR at line 1:
ORA-02290: check constraint (OPS$TKYTE.SYS_C008057) violated


ops$tkyte@ORA10GR2> insert into t values ( 12345 );
insert into t values ( 12345 )
                         *
ERROR at line 1:
ORA-01438: value larger than specified precision allowed for this column
 

Thanks

Richard, June 13, 2006 - 4:33 pm UTC


Very neat trick. I had not thought of that.
Thanks, Tom.


Number calculations

Oleksandr Alesinskyy, February 15, 2007 - 7:51 am UTC

Hello,

one somewhat-related question - what is with precision and scale of intermediate results in number calculations?

E.g. I have columns (or PL/SQL variables) of number datatypes with different scales and precisions and write some arithemtic expression (that involves intermeddiate results ((n1+n2)*n3/n4+n5)*n6 ... .

How it will be calculated? I vaguely remember, that in Oracle7 documentation was stated (may be in other words), that all calculation are done in full (38 digits) precision and result is rounded to precision/scale on target column (insert, update) or variable (PL/SQL assignment).

I have not found anything about it in 9i documentation. IS it still valid? Is it documented somewhere?

Regards,
Oleksandr
Tom Kyte
February 15, 2007 - 12:00 pm UTC

number is used and then if the result is assigned to something, it is 'converted' to that type.

offending columsn

shahid, February 21, 2007 - 4:40 am UTC

Today we had a serious issue in one of the mission critical apps where we started to receive ORA-01438: value larger than specified precision allows for this column.
The problem that the error was seen in the application log(its a third party app) and all we can see is that the error is caused by XYZ stored procedure. We then went inside that stored procedure and found 10 insert statements. We looked into each of the 10 tables which has many columns defined as numeric. After trying to catch the message that was coming from java thin client connectsion (which itself was hectic as there are many), and trying to manually run these 10s of insert statement from sql, we finally able to find which table and which column. We lost an hour in this.

Question:
1. Yes we will try to code the proc in such a way that when it throws an exception it will also write some identity like line no etc to the exception log so atleast we will know which of the table is having the error.
But the table has 10 numeric columns, how can we also throw which column has actually hit the limit?

Thanks
Tom Kyte
February 21, 2007 - 11:01 am UTC

in 10g, they can use dbms_utility format error stack to get the entire thing.

In all releases 8i and above, you can use dbms_trace to get it logged into a sys owned table.

http://asktom.oracle.com/pls/ask/search?p_string=dbms_trace

where to find info

shahid, February 22, 2007 - 5:34 pm UTC

Hi,
You mentioned :
n 10g, they can use dbms_utility format error stack to get the entire thing.
And we are on 10g, can you please give me an example of the above which I can then test it the plsql proc.
What we are doing right now is to store sqlerrm text into error table only.
cheers
ps: the other link you provided does not lead you to the doc.

Tom Kyte
February 22, 2007 - 7:54 pm UTC

I know it did not lead to doc, it lead to examples using dbms_trace on this site.

http://docs.oracle.com/docs/cd/B19306_01/appdev.102/b14258/d_util.htm#sthref9386

STILL NOT HELPFUL

Shahid, February 24, 2007 - 3:27 am UTC

Hi TOM,
The tracing sessions solution is not practically applicable when i have hundreds of 10gas sessions connected to my db.
We do not have the time window to perform the tracing to know which column.
Opening tar gets a strange response that it is an apps issue. I argued with the oracle engineer that we are only asking them to help our developers as its a data issue which comes from external source and we did not anticipate the length. Offcourse after finding we increased the length.
But we are only asking oracle to help us.

For example if we do the same test on char column we get the table name, the column name etc very clearly.
Look:
SQL> DESC TEST_PRECI
Name Null? Type
----------------------------------------- -------- ----------------------------
ID NUMBER(2)
NAME CHAR(2)
NAME1 VARCHAR2(2)

SQL> INSERT INTO TEST_PRECI VALUES (1,'1','123');
INSERT INTO TEST_PRECI VALUES (1,'1','123')
*
ERROR at line 1:
ORA-12899: value too large for column "LETSTEST"."TEST_PRECI"."NAME1" (actual:
3, maximum: 2)


SQL> INSERT INTO TEST_PRECI VALUES (222,'1','1');
INSERT INTO TEST_PRECI VALUES (222,'1','1')
*
ERROR at line 1:
ORA-01438: value larger than specified precision allowed for this column


SQL>


Now I also tried the other option you gave but it did not give much information except the line number, but that too needs lot of further checking if the code is compiled or wrapped.
Wrote file afiedt.buf

1 declare
2 V_ERROR1 VARCHAR2(4000);
3 V_ERROR2 VARCHAR2(4000);
4 begin
5 INSERT INTO TEST_PRECI VALUES (123,'AA','BB');
6 exception
7 when others then
8 V_ERROR1 := DBMS_UTILITY.FORMAT_ERROR_STACK();
9 V_ERROR2 := DBMS_UTILITY.FORMAT_ERROR_BACKTRACE();
10 INSERT INTO DEL1 VALUES (V_ERROR1);
11 INSERT INTO DEL1 VALUES (V_ERROR2);
12 DBMS_OUTPUT.PUT_LINE(V_ERROR1);
13 DBMS_OUTPUT.PUT_LINE(V_ERROR2);
14* end;
SQL> /
ORA-01438: value larger than specified precision allowed for this column

ORA-06512: at line 5


PL/SQL procedure successfully completed.

This is really a bug inside oracle that they perhaps would fix in 12 or 13g.

I have for now opened an enhancement request which I do not think will help us much.
Cheers

Storage representation for zero

Sean, March 06, 2007 - 10:25 am UTC

Hi Tom,
Today I found out an interesting thing in one Oracle 9205 database. It's about the storage scheme for 0. The column type is NUMBER(1,0). Normally, the storage representation for 0 should be:

23:16:43 SQL> select dump(0) from dual;

DUMP(0)
----------------
Typ=2 Len=1: 128

However, I found something strange for 0 in a table. It's like:

Typ=2 Len=2: 193, 1

I don't know under what condition an zero would be stored this way. Can you please explain?
Tom Kyte
March 06, 2007 - 11:18 am UTC

that is not what would be normally stored for zero there - what code puts this number in there - do they use the binary format?

More information about the storage for zero

Sean, March 06, 2007 - 8:47 pm UTC

09:18:32 > desc t
 Name                                                                     Null?    Type
 ------------------------------------------------------------------------ -------- -------------------------------------------------
 CHARGE_ELEMENT_ID                                                        NOT NULL NUMBER(10)
 CE_DET_VERSION_NO                                                        NOT NULL NUMBER(10)
 PRICE_DETERMINANT_ID                                                     NOT NULL NUMBER(10)
 PRICE_DETERMINANT_NAME                                                   NOT NULL VARCHAR2(200)
 PRODUCT_ID                                                               NOT NULL NUMBER(10)
 COUNTRY_CODE                                                             NOT NULL CHAR(3)
 REGION_ID                                                                NOT NULL NUMBER(10)
 DISPLAY_ORDER                                                            NOT NULL NUMBER(10)
 IS_MANDATORY                                                             NOT NULL NUMBER(1)
 CREATED_DATE                                                             NOT NULL DATE
 CREATED_BY                                                               NOT NULL VARCHAR2(20)
 UPDATED_DATE                                                             NOT NULL DATE
 UPDATED_BY                                                               NOT NULL VARCHAR2(20)

09:18:50 > select is_mandatory, dump(is_mandatory) storage from t where is_mandatory != 1;

IS_MANDATORY STORAGE
------------ ------------------------------
           0 Typ=2 Len=1: 128
           0 Typ=2 Len=1: 128
           0 Typ=2 Len=2: 193,1
           0 Typ=2 Len=2: 193,1
           0 Typ=2 Len=2: 193,1
           0 Typ=2 Len=2: 193,1
           0 Typ=2 Len=2: 193,1
           0 Typ=2 Len=2: 193,1
           0 Typ=2 Len=2: 193,1

9 rows selected.

Elapsed: 00:00:00.01
09:19:00 > select is_mandatory, dump(is_mandatory) storage from t where is_mandatory = 0;

IS_MANDATORY STORAGE
------------ ------------------------------
           0 Typ=2 Len=1: 128
           0 Typ=2 Len=1: 128

2 rows selected.

Elapsed: 00:00:00.01


Hi Tom, the queries above may give you more information on this. I am wondering why there are two different storage representations for the number 0. The column is_mandatory is updated by a database stored procedure which is called from a JAVA program via JDBC. The procedure takes an array of a record type as it's parameter to update tables. is_mandatory is an element of the record type. Both the array and the record type are created in the database.

create or replace type det_rec_t as object (
  charge_element_id   number(10,0),
  ce_det_version_no   number(10,0),
  is_usage       number(1,0),
  is_mrc       number(1,0),
  is_nrc       number(1,0),
  rate_type_id       number(10,0),
  start_date       date,
  end_date       date,
  determinant_id      number(10,0),
  determinant_name    varchar2(200),
  product_id       number(10,0),
  country_code       char(3),
  region_id       number(10,0),
  display_order       number(10,0),
  is_mandatory       number(1,0),
  user_id       varchar2(20)
);

create or replace type det_t is table of det_rec_t;


Is it about some number conversion between Oracle and Java? Thanks again!
Tom Kyte
March 07, 2007 - 10:07 am UTC

what is the SOURCE CODE that does the insert here. show us the java code.

better yet, give us a tiny program that does this, that creates the zero that way - in java.

Here comes the source code

Sean, March 08, 2007 - 3:04 am UTC

private ARRAY getDetermineDBArray(Connection objCONN,List detList,IPSearchCriteriaVO ipSearchCriteriaVO) throws Exception {
     StructDescriptor objSTRUCT = StructDescriptor.createDescriptor("DET_REC_T", objCONN);
     ArrayDescriptor objARRAY = ArrayDescriptor.createDescriptor("DET_T", objCONN);
 
     int size= detList.size();
     int sizeOfArray=0,deleteCount=0;     
     for(int x = 0 ;x<size ;x++)
     {
       IPDataTemplateVO ipDataTemplateVO = (IPDataTemplateVO)detList.get(x);
       if(ipDataTemplateVO.getActionMode().equals("D"))
       {
        deleteCount++;
       }
     }
     sizeOfArray = size - deleteCount;
     System.out.println("SIZE OF OBJ_TAB :"+sizeOfArray);
     Object[] obj_tab = new Object[sizeOfArray];
   try {
   // int count=0;
    Map sortedDisplayOrderMap = new TreeMap();
     for (int i=0; i < size; i++) {         
         IPDataTemplateVO ipDataTemplateVO = (IPDataTemplateVO)detList.get(i);
         if(!ipDataTemplateVO.getActionMode().equals("D"))
         {
         Object[] obj_rec=new Object[16];
         
         obj_rec[0]=ipSearchCriteriaVO.getChargeElementId();; //charge_element_id
         obj_rec[1]=ipSearchCriteriaVO.getCeDetVersionNo(); //ce_det_version_no
         obj_rec[2]=String.valueOf(EpriceUtil.replaceFlagWithNumber(ipSearchCriteriaVO.getIsUsage())); //is_usage
         obj_rec[3]=String.valueOf(EpriceUtil.replaceFlagWithNumber(ipSearchCriteriaVO.getIsMrc())); //is_mrc
         obj_rec[4]=String.valueOf(EpriceUtil.replaceFlagWithNumber(ipSearchCriteriaVO.getIsNrc())); //is_nrc
         obj_rec[5]=ipSearchCriteriaVO.getRateTypeId(); //rate_type_id
         obj_rec[6]=new Timestamp(EpriceUtil.getStringToDate(ipSearchCriteriaVO.getActiveDate()));
         
         if (ipSearchCriteriaVO.getInactiveDate()==null||ipSearchCriteriaVO.getInactiveDate().length()==0) {
           obj_rec[7]=null; //end_date
         }else {
           obj_rec[7]=new Timestamp(EpriceUtil.getStringToDate(ipSearchCriteriaVO.getInactiveDate())); //end_date
         }
 
         obj_rec[8]=ipDataTemplateVO.getPriceDeterminantId(); //determinant_id-
         obj_rec[9]=ipDataTemplateVO.getPriceDeterminantName(); //determinant_name
         obj_rec[10]=ipSearchCriteriaVO.getProductId(); //product_id
         obj_rec[11]=ipSearchCriteriaVO.getCountryCode(); //country_code
         obj_rec[12]=ipSearchCriteriaVO.getRegionId(); //region_id
         obj_rec[13]=ipDataTemplateVO.getDisplayOrder(); //display_order
         obj_rec[14]=String.valueOf(EpriceUtil.replaceFlagWithNumber(ipDataTemplateVO.getIsMandatory()));  //is_mandatory
         obj_rec[15]=ipSearchCriteriaVO.getCreatedBy(); //user_id
 
         for(int j=0;j<16;j++)
          System.out.println("obj_rec["+j+"] :"+obj_rec[j]);
          
         sortedDisplayOrderMap.put(new Integer(obj_rec[13].toString()), obj_rec);         
      //   sorted.put(new Integer(obj_rec[13]),obj_rec);
         //obj_tab[count] = new STRUCT(objSTRUCT, objCONN, obj_rec);
         //count++;
         }
         else
         {
          System.out.println("YOU CAN'T ENTER DELETE INTO ARRAY");
         }
     }
     
     Set s = sortedDisplayOrderMap.keySet();
     Iterator  it = s.iterator();
     while(it.hasNext())
     {
      System.out.println("SORTED KEY :"+it.next());
     }
     
     Object[] obj_rec_new = (Object[]) sortedDisplayOrderMap.values().toArray(new Object[sortedDisplayOrderMap.size()]);
  
     
     
     int count=0;
     for(int i=0;i<obj_rec_new.length;i++)
     {
      Object[] obj_rec = (Object[]) obj_rec_new[i];      
      
      System.out.println("Old display order :"+obj_rec[13]);
      obj_rec[13] = String.valueOf(i+1);
      System.out.println("New display order :"+obj_rec[13]);      
      obj_tab[count++] = new STRUCT(objSTRUCT, objCONN, obj_rec);
     }
 }catch (Exception e) {
     e.printStackTrace();
     throw e;
 }
 
     ARRAY objRetARRAY = new ARRAY(objARRAY, objCONN, obj_tab);
     return objRetARRAY;
 
}


the objARRAY is used as the parameter to be passed into the stored procedure. obj_rec[14] is the field to store the value of is_mandatory, that's 0 or 1. The stored procedure takes the parameter as IN and updates the tables accordingly without any conversion on this field. The insert statement in the procedure is listed below:

      insert into t (
 charge_element_id,
 ce_det_version_no,
 price_determinant_id,
 price_determinant_name,
 product_id,
 country_code,
 region_id,
 display_order,
 is_mandatory,
 created_date,
 created_by,
 updated_date,
 updated_by)
      values (p_det(i).charge_element_id,
       p_det(i).ce_det_version_no,
       p_det(i).determinant_id,
       p_det(i).determinant_name,
       p_det(i).product_id,
       p_det(i).country_code,
       p_det(i).region_id,
       p_det(i).display_order,
       p_det(i).is_mandatory,
       lv_created_date,
       lv_created_by,
       lv_updated_date,
       lv_updated_by);

Tom Kyte
March 08, 2007 - 10:28 am UTC

but...

can you make a teeny tiny standalone example that demonstrates the issue

with the create table, and entire java program that we can compile, run and see?

I am not a JAVA developer

Sean, March 08, 2007 - 8:25 pm UTC

Hi Tom, I am not a JAVA developer, unfortunately. Even the JAVA code which I gave above is from the development team. I tried to give as much as I can. I am just wondering under which conditions zero would be stored as "Len=2: 193,1". What number does "Len=2: 193, 1" represent? It seems like something between 0 and 1.

We are using JDBC thin client. And our developers suspect the statement which associates a JAVA STRUCT with the DB RECORD causes the problem.

obj_tab[count++] = new STRUCT(objSTRUCT, objCONN, obj_rec);

Tom Kyte
March 08, 2007 - 8:56 pm UTC

ask the developers to develop a small tiny program. give me a test case and I'll diagnose it. Make me make a test case that may or may not reproduce the issue and I'll just be spinning my wheels. sorry.

if your developers are curious, they would know how to whittle a test case down to its bare essentials - simply to demonstrate the issue. that is what I need from you.

Here comes some programs

Sean, March 08, 2007 - 11:23 pm UTC

Hi Tom, here comes some programs:

On the database side:

create table t1 (
  id number(10),
  name varchar2(30),
  active number(1)
);

create or replace type t1_rec as object (
  id number(10,0),
  name varchar2(30),
  active number(1,0)
);

create or replace type t1_rec_t is table of t1_rec;

create or replace procedure add_t1 (p_t1 t1_rec_t)
is
begin
  for i in 1..p_t1.count loop
    insert into t1
    values (p_t1(i).id, p_t1(i).name, p_t1(i).active);
  end loop;
  commit;
end;


on the client side, here is the small JAVA program which could re-produce the problem:

import java.io.*;
import java.util.*;
import java.sql.*;
import oracle.sql.*;
import oracle.jdbc.driver.*;

public final class test{

    public static void main(String [] args) {

        Connection objCONN = null;
        CallableStatement objCallStmt = null;
        try {
            DriverManager.registerDriver(new OracleDriver());
            String url = "jdbc:oracle:thin:@host:1521:orcl";
            objCONN = DriverManager.getConnection(url, "scott", "tiger");

            if(objCONN != null) {
                ArrayList newList = new ArrayList();
                newList.add("0");
                 newList.add("Name1");
                 newList.add("0");
                newList.add("100");
                 newList.add("Name2");
                 newList.add("1");
                newList.add("200");
                 newList.add("Name3");
                 newList.add("0");
                ARRAY arguArray = getDBArray(objCONN,newList);

                objCallStmt = objCONN.prepareCall("{call add_t1(?)}");
                objCallStmt.setArray(1,arguArray);
                }
                objCallStmt.executeUpdate();
        } catch(SQLException e) {
            System.out.println("SQL Exception  :" + e);
        } catch(Exception e) {
            System.out.println("Exception in :" + e);
        }
        finally {
            try { if (objCallStmt!=null) {objCallStmt.close();}
            } catch(Exception e){objCallStmt=null;}
            try { if (objCONN != null) { objCONN.close();}
            } catch(Exception e) {objCONN=null;}
        }
    }

    private static ARRAY getDBArray(Connection objCONN, ArrayList dataList) throws SQLException {
        int columns=3;
        StructDescriptor objSTRUCT = StructDescriptor.createDescriptor("T1_REC", objCONN);
        ArrayDescriptor objARRAY   = ArrayDescriptor.createDescriptor("T1_REC_T", objCONN);

        int size=dataList.size()/columns;
        System.out.println("Size of Array="+size);
        Object[] obj_tab = new Object[size];
         try {
            for (int i=0; i < size; i++) {
               Object obj = dataList.get(i);
               Object[] obj_rec=new Object[3];
               obj_rec[0]=dataList.get(i*3);   //id
               obj_rec[1]=dataList.get(i*3+1); //name
               obj_rec[2]=dataList.get(i*3+2); //number
               obj_tab[i] = new STRUCT(objSTRUCT, objCONN, obj_rec);
            }
         }catch (Exception e) {
           e.printStackTrace();
         }

        ARRAY objRetARRAY = new ARRAY(objARRAY, objCONN, obj_tab);
        return objRetARRAY;
    }

}


Thank you in advance!
Tom Kyte
March 09, 2007 - 11:05 am UTC

I cannot reproduce.

I am using 9208 - jdbc drivers from the same

scott%ORA9IR2> select id, name, active, dump(active) d from t1;

        ID NAME           ACTIVE D
---------- ---------- ---------- ----------------------------------------
         0 Name1               0 Typ=2 Len=1: 128
       100 Name2               1 Typ=2 Len=2: 193,2
       200 Name3               0 Typ=2 Len=1: 128

scott%ORA9IR2> insert into t1 values (400,'x',0);

1 row created.

scott%ORA9IR2> select id, name, active, dump(active) d from t1;

        ID NAME           ACTIVE D
---------- ---------- ---------- ----------------------------------------
         0 Name1               0 Typ=2 Len=1: 128
       100 Name2               1 Typ=2 Len=2: 193,2
       200 Name3               0 Typ=2 Len=1: 128
       400 x                   0 Typ=2 Len=1: 128



Different JDBC driver?

Sean, March 10, 2007 - 3:35 am UTC

Hi Tom, we are using JDBC driver 8.1.7.4 on the client side while running a 9.2.0.5 database on the server. Could you please try again using 8.1.7.4 JDBC driver and see if the problem is re-produced or not?

Thanks a lot!
Tom Kyte
March 12, 2007 - 5:52 pm UTC

how about....

you try the 9205 drivers and see if it doesn't?

I don't happen to have the 817 drivers sitting around right now....

It's JDBC 8174 problem

Sean, March 11, 2007 - 10:47 am UTC

Hi Tom, after I point to JDBC 9205 and rerun the JAVA program, number zero is stored correctly with 'Typ=2 Len=1: 128'. Seems like the problem was caused by the JDBC 8174 driver. If so, I need to upgrade our JDBC driver version to 9205. I am still wondering what value 'Len=2: 193,1' represents? Can you please advise? Thank you very much!

Typ=2 Len=2: 193,1

Alberto Dell'Era, March 11, 2007 - 2:35 pm UTC

It is definitely an invalid NUMBER; in 10.2.0.3 :
SQL> select dump ( utl_raw.cast_to_number ('C101') ) from dual;

DUMP(UTL_RAW.CAST_TO_NUMBER('C101'))
--------------------------------------------------------------------------------
Typ=2 Len=2: 193,1

SQL> select 1 / utl_raw.cast_to_number ('C101') from dual;
select 1 / utl_raw.cast_to_number ('C101') from dual
           *
ERROR at line 1:
ORA-03113: end-of-file on communication channel

Just an illegal bit representation that doesn't map to any NUMBER, and so produces unpredictable results (including process termination) if used.

Yes, it's completely unpredictable

Sean, March 14, 2007 - 5:02 am UTC

When I run some tests in different databases, I got different results.

When I run the statement below in 10201 and 9205 either locally or remotely, ORA-03113 returned.

16:37:20 OPS$SWONG@FLYHORSE > select utl_raw.cast_to_number('C101')/1 from dual;
select utl_raw.cast_to_number('C101')/1 from dual
*
ERROR at line 1:
ORA-03113: end-of-file on communication channel

ERROR:
ORA-03114: not connected to ORACLE

Elapsed: 00:00:11.16


When I run the same statement in my local XE, I got a strange number returned.

select utl_raw.cast_to_number('C101')/1 from dual

UTL_RAW.CAST_TO_NUMBER('C101')/1 
-------------------------------- 
146150163.7229892817193583822615272918645831532875 

1 rows selected

decimal shrink

Jan Karaffa, July 01, 2008 - 1:39 pm UTC

I need to update one column in very big table which is partition by day and I plan to do it in following steps:
1) create table TMP as select X/100 as X from source_tab partition (xyz)
2) alter table source_tab exchange partition (xyz) with table TMP

The issue I have is, that source_tab has column X defined as NUMBER(8). When I create table TMP, the column X is NUMBER and therefore I can't exchange partition back.

Is there any trick or workaround which would create table TMP with x number(8)? I'm forced to keep column in source_tab as number(8).
Database is 10.2.0.2.0
thanks
Tom Kyte
July 06, 2008 - 7:20 pm UTC

how big are the partitions - if the column is already populated, I'd just update it.

You'd have to index and everything else - seems like a huge hassle.


create your table with whatever types you like.

insert /*+ APPEND */ into it


or, cast

ops$tkyte%ORA11GR1> create table t as select cast( 1/1 as number(8) ) x from dual;

Table created.

ops$tkyte%ORA11GR1> desc t;
 Name                                     Null?    Type
 ---------------------------------------- -------- ----------------------------
 X                                                 NUMBER(8)


decimal shrink

Jan Karaffa, July 01, 2008 - 5:15 pm UTC

solution here ...
http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:111319200346010227

select cast(val/10 as number(8)) val from source_tab ...

always using maximum precision N(38)?

Jianhui, July 08, 2008 - 4:41 pm UTC

Hello Tom,

Is it true there is no difference in terms of storage and performance if my designer colleague always set Number datatype to Number(38) maximum precision?


I dont see storage difference from my test below, how about the performance?


SQL> create table n (n number);

Table created.

SQL> create table n5 (n number(5));

Table created.


SQL> create table n38 (n number(38));

Table created.


SQL> insert into n (select 12345 from dba_objects where rownum<=10000);

10000 rows created.

SQL> insert into n5 (select 12345 from dba_objects where rownum<=10000);

10000 rows created.

SQL> insert into n38 (select 12345 from dba_objects where rownum<=10000);

10000 rows created.

SQL> analyze table n compute statistics;

Table analyzed.

SQL> analyze table n5 compute statistics;

Table analyzed.

SQL> analyze table n38 compute statistics;

Table analyzed.


SQL> select blocks, num_rows, AVG_ROW_LEN, EMPTY_BLOCKS from user_tables where table_name in ('N','N5','N38');

    BLOCKS   NUM_ROWS AVG_ROW_LEN EMPTY_BLOCKS
---------- ---------- ----------- ------------
        19      10000           8            0
        19      10000           8            0
        19      10000           8            0


Best Regards,

Tom Kyte
July 08, 2008 - 5:39 pm UTC

...
Is it true there is no difference in terms of storage and performance if my
designer colleague always set Number datatype to Number(38) maximum precision?
.....

why does it come down to performance?

It seems so clear to me that it comes down to..............


DATA INTEGRITY.

a data type is nothing more, nothing less than a data integrity constraint. Your "designer colleague" should lose their title (I say tongue in cheek), they - the designer - should be encouraging the use of correct datatypes for data integrity. It would be laziness, sloppy the other way.


the number 554252433 stored in a number(38) or a number(10) would be stored exactly the same, precisely the same. bit for bit, byte for byte.

But a number(10) says so much about the data
Number(38) does not.

why oracle use 22 BYTE even for NUMBER (1) ?

essy pribadi, July 09, 2008 - 3:50 am UTC

Hi Experts, I wanted to make it as an own question, but i'm sure it's the right place here...

When I looked at oracle's optimizer with the explain-command, i wondered WHY the joins between numbers are HASH-Joins...

I don't find real arguments compared with index-based nested-loop-joins... both can be read in memory, but the hash must be calculated before...

it understand 100% to use has-joins, if we want to compare strings, but not if numbers...

so i really wanted to find it out why, and what i found surprised me more... As I understand, ALL Numbers in Oracle (even if it's only one digit) are stored as 22 BYTE...

that's a lot... and somehow i think this is unique in oracle, in all other databases we can use datatypes like BYTE, SHORT, LONG, ... singed and unsigned...

so we use less space in the harddisc (ok, forget it nowadays) and the optimizer can use directly nested loops an 4-byte-values rather than calculate an (not always unique) hash...


of couse i wonder why oracle change (since what version ?) to this type of numbers ?

it's even more slow to calculate with OCINumberAdd() and so on, even if we know we want to count with INTEGER-values ?

what do you think about slower joins cause of this 22-byte-numbers ? and why ONLY ORACLE doesn't let us choose the byte-size ? and why 22 Byte, not 16 or 32 ?


p.S. That's the reason why comparing NUMBER (1) and NUMBER (38) makes no performant diffrence... both is same (slow ???)

Tom Kyte
July 09, 2008 - 10:27 am UTC

... it understand 100% to use has-joins, if we want to compare strings, but not if
numbers... ...

this makes no sense whatsoever. How is a string different from a number different from a string in this respect?

... so i really wanted to find it out why, and what i found surprised me more... As
I understand, ALL Numbers in Oracle (even if it's only one digit) are stored as
22 BYTE...
...

that is absolutely false. absolutely FALSE. totally and utterly false.


I think you do not understand nested loops, indexes, hash joins.


you can use nested loops on numbers, dates, strings, whatever you like.


But if you have to join 100,000 records in t1 to 100,000 records in t2 - the fastest way will be

two full scans plus a hash join.


pretty much. If you used an index - as a nested loop would typically - that would be "the slowest way"


and a 4byte/8byte integer would not rule out/rule in/preclude/allow a different joining technique. I don't get your logic here at all.


you can nested loop join numbers, dates, strings, whatever
you can hash join numbers, dates, strings, whatever

the datatype does not preclude anything.




I don't think it's that wrong...

essy pribadi, July 09, 2008 - 1:09 pm UTC

hi tom,

what I mean before is joining two tables where BOTH have an INDEX in the join-field...(sorry, i didn't mention it)

so i wonder, why he doesn't take an nested loop... it's similar to the hash, but faster
( i just checked again about hash and nested loop-joins)


nested loop can also read in the ram, like hash, but hash has a bit more calculations per record ?

if any number in oracle has 22 byte, I would understand that it's faster to calculate a 4-byte number using an hash
and compare this smaller numbers rather than using an indexed nested loop on 22 bytes...

it just would not make a sense for me to hash a 4-byte number to another 4-byte-number...



that's why I don't understand your sentence...
... If you used an index - as a nested loop would typically - that would be "the slowest way"

???
sounds like don't use indexes, cause hash is the fastest way... (I know, that you don't want to say this,
but does the hash-join use the index or making full-table-scans ? )



22-byte-numbers...
...that is absolutely false. absolutely FALSE. totally and utterly false.


I just tested it again, using two tables created by Oracle's testdb (so you will also have it :-) )

i took the table OE.CATEGORIES_TAB, it has 4 columns


I use the command OCIAttrGet( (void*) handle_column,
(ub4) OCI_DTYPE_PARAM,
(void*) &col_width,
(ub4 *) 0,
(ub4) OCI_ATTR_DATA_SIZE,
(OCIError *) p_err
);

to get the used bytes for each column (as I understand, this function will do...), I get the following results...


Table OE.CATEGORIES_TAB:

COLUMN NAME DATATYPE SIZE (col_width)
1 CATEGORY_ID Number(2,0) 22
2 CATEGORY_NAME VARCHAR2(50 BYTE) 50
3 CATEGORY_DESCRIPTION VARCHAR2(1000 BYTE) 1000
4 PARENT_CATEGORY_ID NUMBER(2,0) 22


just to compare any other table (with number(38))

OLAPSYS.CWM$ARGUMENT: (first in that user)

1 IRID NUMBER(38,0) 22
...
4 DATE_CHANGED DATE 7
...
6 HASARGUMENT_FK_SEQ NUMBER(28,10) 22
...




and the definition of OCINumber in the orl.h file is:

#define OCI_NUMBER_SIZE 22

struct OCINumber
{
ub1 OCINumberPat[OCI_NUMBER_SIZE];
};
typdef struct OCINumber OCINumber


... and in the oacle-examples all columns in SQL-Queries, which contain numbers, are defined as OCI-Number...
I know that we can define Number-columns also as short, long and so on, as long as it's possible...



but yeah, I really wonder why numbers use so much bytes...22-byte-numbers...
...that is absolutely false. absolutely FALSE. totally and utterly false.


I just tested it again, using two tables created by Oracle's testdb (so you will also have it :-) )

i took the table OE.CATEGORIES_TAB, it has 4 columns


I use the command OCIAttrGet( (void*) handle_column,
(ub4) OCI_DTYPE_PARAM,
(void*) &col_width,
(ub4 *) 0,
(ub4) OCI_ATTR_DATA_SIZE,
(OCIError *) p_err
);

to get the used bytes for each column (as I understand, this function will do...), I get the following results...


Table OE.CATEGORIES_TAB:

COLUMN NAME DATATYPE SIZE (col_width)
1 CATEGORY_ID Number(2,0) 22
2 CATEGORY_NAME VARCHAR2(50 BYTE) 50
3 CATEGORY_DESCRIPTION VARCHAR2(1000 BYTE) 1000
4 PARENT_CATEGORY_ID NUMBER(2,0) 22


just to compare any other table (with number(38))

OLAPSYS.CWM$ARGUMENT: (first in that user)

1 IRID NUMBER(38,0) 22
...
4 DATE_CHANGED DATE 7
...
6 HASARGUMENT_FK_SEQ NUMBER(28,10) 22
...




and the definition of OCINumber in the orl.h file is:

#define OCI_NUMBER_SIZE 22

struct OCINumber
{
ub1 OCINumberPat[OCI_NUMBER_SIZE];
};
typdef struct OCINumber OCINumber


... and in the oacle-examples all columns in SQL-Queries, which contain numbers, are defined as OCI-Number...
I know that we can define Number-columns also as short, long and so on, as long as it's possible...



but yeah, I really wonder why numbers use so much bytes...22-byte-numbers...
...that is absolutely false. absolutely FALSE. totally and utterly false.


I just tested it again, using two tables created by Oracle's testdb (so you will also have it :-) )

i took the table OE.CATEGORIES_TAB, it has 4 columns


I use the command OCIAttrGet( (void*) handle_column,
(ub4) OCI_DTYPE_PARAM,
(void*) &col_width,
(ub4 *) 0,
(ub4) OCI_ATTR_DATA_SIZE,
(OCIError *) p_err
);

to get the used bytes for each column (as I understand, this function will do...), I get the following results...


Table OE.CATEGORIES_TAB:

COLUMN NAME DATATYPE SIZE (col_width)
1 CATEGORY_ID Number(2,0) 22
2 CATEGORY_NAME VARCHAR2(50 BYTE) 50
3 CATEGORY_DESCRIPTION VARCHAR2(1000 BYTE) 1000
4 PARENT_CATEGORY_ID NUMBER(2,0) 22


just to compare any other table (with number(38))

OLAPSYS.CWM$ARGUMENT: (first in that user)

1 IRID NUMBER(38,0) 22
...
4 DATE_CHANGED DATE 7
...
6 HASARGUMENT_FK_SEQ NUMBER(28,10) 22
...




and the definition of OCINumber in the orl.h file is:

#define OCI_NUMBER_SIZE 22

struct OCINumber
{
ub1 OCINumberPat[OCI_NUMBER_SIZE];
};
typdef struct OCINumber OCINumber


... and in the oacle-examples all columns in SQL-Queries, which contain numbers, are defined as OCI-Number...
I know that we can define Number-columns also as short, long and so on, as long as it's possible...



but yeah, I really wonder why numbers use so much bytes...
Tom Kyte
July 09, 2008 - 2:53 pm UTC

...
what I mean before is joining two tables where BOTH have an INDEX in the
join-field...(sorry, i didn't mention it)

......

I don't care how many indexes there are.

If you are joining two tables - without any other sort of filters, a simple

select * from t1, t2 where t1.key = t2.key;

I would not expect ANY INDEXES to be used. It would be painful.
http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:6749454952894#6760861174154

Now, if you are attempting to get the first N rows back - then maybe an index would be used (probable), but that would work for ANY DATATYPE.


lose the concept of "full scans are bad"
that concept is very wrong, very dangerous to have.


...
if any number in oracle has 22 byte, I would understand that it's faster to
calculate a 4-byte number using an hash
and compare this smaller numbers rather than using an indexed nested loop on 22
bytes...

....

you would be comparing absolutely apples to flying toaster ovens here.

first, a hash is not a 4 byte number, a hash lookup is a fast way to find things. give me a key, I'll hash it and go right there in memory and I'll have it.

But that has ABSOLUTELY NOTHING to do with the datatype - I fail utterly and completely to see the connection you have somehow made in you mind.


If I join t1 to t2 by key I could either:

full scan t1, hash it into memory by key (so that f(key) = address of row in ram).
full scan t2, and for each row in t2, hash the key, get the row(s) in t1 that match and return it.

that is the typical hash join. Works with all datatypes - key can be *ANYTHING* you pretty much want.

or, I could

for x in ( select * from t1 )
loop
read through an index (at least 3 or 4 IO's probably) to get a rowid(s)
take each rowid I get and do a table access (single block, slow io)
return record
end loop

hopefully you see how inefficient that second approach would be (read above link, it is painfully demonstrated)

and we do not care what the data type IS.



as for the number stuff, well, gee. You take a C data structure and say "this must be it, this must be the way it is stored on disk". That struct is for ANY number - any number can be held in there.


ops$tkyte%ORA10GR2> create table t ( x number ) pctfree 0;

Table created.

ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> insert into t
  2  select level from dual connect by level <= 100000;

100000 rows created.

ops$tkyte%ORA10GR2> commit;

Commit complete.

ops$tkyte%ORA10GR2> select sum(vsize(x)), count(*) * 22 from t;

SUM(VSIZE(X)) COUNT(*)*22
------------- -----------
       388892     2200000



I have the numbers between 1 and 100,000 - their vsize (function to tell you how much storage an instance of something takes) is 388,892 bytes. Quite far from the theoretical 2,200,000 bytes you claim it would take to store.


Let us say you have a database table:

create table t ( x varchar2(4000) );

Now, in a client application you have:

x char[4001];


do you mean to say that "it takes therefore 4000 bytes to store any string in that column because there is a c variable that is sized to hold the biggest string"??? That is what you are saying with NUMBERS here.


It is not true that numbers take 22 bytes.
The maximum a 38 digit of precision number could consume would be 22 (38 digits of precision - hmmm, we might even be able to write financial applications with that - not so with integers - integers cannot even hold pennies)

It has never been true.


I hope you understand the difference between a C struct that can hold *any number* and how data is really physically stored might be radically different?

And I hope you understand that you could fetch into a C int, long, float, double if you wanted ( assuming the data FIT in those small types). That you don't need to use the ocinumber type at all.



sorry, don't know why the text is doubled again !!!

essy pribadi, July 09, 2008 - 1:21 pm UTC

really, i extra checked if bevore, can you delete it and i will post it again ?? i'm sorry !!!

thaks for the good explainations !

Essy Pribadi, July 10, 2008 - 6:38 am UTC

Hi Tom,

thanks to you now I understand that hash-joins are the really better choice, I just thought nested-loop has some special optimizations...

bt how about joining three or more tables ? is it the same (if all has indexes on the join-fields ?)

also really sounds like don't use index for join-fields, they are not used, but it takes time to insert, delete or update records, is this right ?
(i talk about index JUST USED FOR Joins)


By the way, as I know, MYSQL doesn't use hash-joins at all, they use, as I know, single-sweep-multi-joins, compareable with nested-loop in an nested-loop in an nested loop...
they would also be better to use hash-joins? I don't read to often, people complain about slow joins in mysql...



back to the 22-byte-datatypes... I took another (any) sytem-table, so you also can proof it and we can talk about the same...


sys.DEPENDENCY$ cause they have the column p_obj# as Number (means NUMBER(38, 0) or ?)


in this table we have about 130.000 records, and the column p_obj# uses small and big values, that's why I choose it




SELECT vsize(p_obj#), count (*), min(p_obj#), max(p_obj#), vsize(-max(p_obj#))
FROM sys.DEPENDENCY$
GROUP BY vsize(p_obj#)
ORDER BY 1


vsize(p_obj#), count (*), min(p_obj#), max(p_obj#), vsize(-max(p_obj#))

2 4489 4 40000 3
3 31879 103 70800 4
4 94773 10001 71447 5
5 20 4294951200 4294953000 6
6 1999 4294950912 4294953031 7


does this mean, oracle stores the small numbers with small bytes and the big numbers with more bytes ?
how about if we change an big number (-4294950912) to an small number (4), oracle will rewrite the record, so it uses 2 bytes instead of 7 ?
and if we make an small number (4) to an big number (-4294950912), oracle will also rewrite the record from 2 byte to 7 bytes ?

I can't imagine, how if we update 10.000s of records, each with some number/text-column, this would be quiet slow, at least in my eyes...
but if you say it is not, I will believe it, you are the expert, that's why I asked YOU and not any other forum...

(as I know from other systems like MySQL, they will say: we have an number with 5, 6 or 9 digits, so we need maximum 4 bytes and for every row we take save numbers with 4 bytes, no matther how small they are ?
it's also more easy to read it from the harddisk, we know this column is 4 bytes, we read them instead of check how big is the number and read this size (between 2 and 22 bytes if really big numbers with floats ?)
wouldn't need oracle another byte to save the bytes used by each number in each row, if not, how can they know how big this number is and when the next column starts ?)


but yeah Tom, I am really thanksfull that you always answer my questions, even if they might sound quiet silly for you !!!

thanks, Essy
Tom Kyte
July 10, 2008 - 7:42 am UTC

... bt how about joining three or more tables ? is it the same (if all has indexes
on the join-fields ?)
...

it matters not the number of tables. nested loops is a row by row thing, hash joins are a bulk thing. If you are going for very few rows out of many - nested loops makes sense. If you go for most/many/all rows out of a lot, hash joins do.

....
also really sounds like don't use index for join-fields, they are not used, but
it takes time to insert, delete or update records, is this right ?
(i talk about index JUST USED FOR Joins)
......

if you have an index "just for joins" and you never use it, you would not want to create it. So, if you use it - so what if it 'takes time' during the other operations - you are using it to make something else run orders of magnitude faster.


Ok, for the numbers -

... does this mean, oracle stores the small numbers with small bytes and the big
numbers with more bytes ? ....

Oracle uses the number of digits of precision. The number

0.111111111111111111111

takes more space than the number

1.0

does, even though the first number is "smaller", the first has MORE DIGITS to preserve.


.... I can't imagine, how if we update 10.000s of records, each with some
number/text-column, this would be quiet slow, at least in my eyes... ...

why does your imagination come into play when you could just measure it?


The bottom line is - the Oracle number type is extremely "capable", 38 digits of precision - think about that.

Think about a float - 6 digits
Think about a double - 13 digits
Think about an integer or long and what they can/cannot hold.

As for reading from a hard disk - you are thinking WAY WAY too simple here. Databases deal with data in BLOCKS - we don't read a single field at a time, we read and process BLOCKS of data.

I have a recommendation for you, for a book - I'm going to cut and paste a small excerpt of my last book on datatypes.


NUMBER Type Syntax and Usage
The syntax for the NUMBER type is straightforward:
NUMBER( p,s )
where P and S are optional and are used to specify the following:
 * Precision, or the total number of digits. By default, the precision is 38 and has valid values in the range of 1 to 38. The character * may be used to represent 38 as well.
 * Scale, or the number of digits to the right of the decimal point. Valid values for the scale are ¿84 to 127, and its default value depends on whether or not the precision is specified. If no precision is specified, then scale defaults to the maximum range. If a precision is specified, then scale defaults to 0 (no digits to the right of the decimal point). So, for example, a column defined as NUMBER stores floating-point numbers (with decimal places), whereas a NUMBER(38) stores only integer data (no decimals), since the scale defaults to 0 in the second case.
You should consider the precision and scale to be edits for your data¿data integrity tools in a way. The precision and scale do not affect at all how the data is stored on disk, only what values are permitted and how numbers are to be rounded. For example, if a value exceeds the precision permitted, Oracle returns an error:
ops$tkyte@ORA10GR1> create table t ( num_col number(5,0) );
Table created.
 
ops$tkyte@ORA10GR1> insert into t (num_col) values ( 12345 );
1 row created.
 
ops$tkyte@ORA10GR1> insert into t (num_col) values ( 123456 );
insert into t (num_col) values ( 123456 )
                                 *
ERROR at line 1:
ORA-01438: value larger than specified precision allows for this column
So, you can use the precision to enforce some data integrity constraints. In this case, NUM_COL is a column that is not allowed to have more than five digits.
The scale, on the other hand, is used to control ¿rounding¿ of the number, for example:
ops$tkyte@ORA10GR1> create table t ( msg varchar2(10), num_col number(5,2) );
Table created.
 
ops$tkyte@ORA10GR1> insert into t (msg,num_col) values ( '123.45',  123.45 );
1 row created.
 
ops$tkyte@ORA10GR1> insert into t (msg,num_col) values ( '123.456', 123.456 );
1 row created.
 
ops$tkyte@ORA10GR1> select * from t;
 
MSG           NUM_COL
---------- ----------
123.45         123.45
123.456        123.46
Notice how the number 123.456, with more than five digits, succeeded this time. That is because the scale we used in this example was used to round 123.456 to two digits, resulting in 123.46, and then 123.46 was validated against the precision, found to fit, and inserted. However, if we attempt the following insert, it fails:
ops$tkyte@ORA10GR1> insert into t (msg,num_col) values ( '1234', 1234 );
insert into t (msg,num_col) values ( '1234', 1234 )
                                             *
ERROR at line 1:
ORA-01438: value larger than specified precision allows for this column
because the number 1234.00 has more than five digits in total. When you specify the scale of 2, at most three digits may be to the left of the decimal place and two to the right. Hence that number does not fit. The NUMBER(5,2) column can hold all values between 999.99 and ¿999.99.
It may seem strange to allow the scale to vary from ¿84 to 127. What purpose could a negative scale fulfill? It allows you to round values to the left of the decimal place. Just as the NUMBER(5,2) rounded values to the nearest .01, so a NUMBER(5,-2) would round to the nearest 100, for example:
ops$tkyte@ORA10GR1> create table t ( msg varchar2(10), num_col number(5,-2) );
Table created.
 
ops$tkyte@ORA10GR1> insert into t (msg,num_col) values ( '123.45',  123.45 );
1 row created.
 
ops$tkyte@ORA10GR1> insert into t (msg,num_col) values ( '123.456', 123.456 );
1 row created.
 
ops$tkyte@ORA10GR1> select * from t;
 
MSG           NUM_COL
---------- ----------
123.45            100
123.456           100
The numbers were rounded up to the nearest 100. We still have five digits of precision, but there are now seven digits (including the trailing two 0s) permitted to the left of the decimal point:
ops$tkyte@ORA10GR1> insert into t (msg,num_col) values ( '1234567', 1234567 );
1 row created.
 
ops$tkyte@ORA10GR1> select * from t;
 
MSG           NUM_COL
---------- ----------
123.45            100
123.456           100
1234567       1234600
 
ops$tkyte@ORA10GR1> insert into t (msg,num_col) values ( '12345678', 12345678 );
insert into t (msg,num_col) values ( '12345678', 12345678 )
                                                 *
ERROR at line 1:
ORA-01438: value larger than specified precision allows for this column
So, the precision dictates how many digits are permitted in the number after rounding, using the scale to determine how to round. The precision is an integrity constraint, whereas the scale is an ¿edit.¿
It is interesting and useful to note that the NUMBER type is, in fact, a variable length datatype on disk and will consume between 0 and 22 bytes of storage. Many times, programmers consider a numeric datatype to be a fixed-length type¿that is what they typically see when programming with 2- or 4-byte integers and 4- or 8-byte floats. The Oracle NUMBER type is similar to a variable length character string. We can see what happens with numbers that contain differing amounts of significant digits. We¿ll create a table with two NUMBER columns and populate the first column with many numbers that have 2, 4, 6, ¿ 28 significant digits. Then, we¿ll simply add 1 to each of them:
ops$tkyte@ORA10GR1> create table t ( x number, y number );
Table created.
 
ops$tkyte@ORA10GR1> insert into t ( x )
  2  select to_number(rpad('9',rownum*2,'9'))
  3    from all_objects
  4   where rownum <= 14;
14 rows created.
 
ops$tkyte@ORA10GR1> update t set y = x+1;
14 rows updated.
Now, if we use the built-in VSIZE function that shows how much storage the column takes, we can review the size differences between the two numbers in each row:
ops$tkyte@ORA10GR1> set numformat 99999999999999999999999999999
ops$tkyte@ORA10GR1> column v1 format 99
ops$tkyte@ORA10GR1> column v2 format 99
ops$tkyte@ORA10GR1> select x, y, vsize(x) v1, vsize(y) v2
  2    from t order by x;
 
                             X                              Y  V1  V2
------------------------------ ------------------------------ --- ---
                            99                            100   2   2
                          9999                          10000   3   2
                        999999                        1000000   4   2
                      99999999                      100000000   5   2
                    9999999999                    10000000000   6   2
                  999999999999                  1000000000000   7   2
                99999999999999                100000000000000   8   2
              9999999999999999              10000000000000000   9   2
            999999999999999999            1000000000000000000  10   2
          99999999999999999999          100000000000000000000  11   2
        9999999999999999999999        10000000000000000000000  12   2
      999999999999999999999999      1000000000000000000000000  13   2
    99999999999999999999999999    100000000000000000000000000  14   2
  9999999999999999999999999999  10000000000000000000000000000  15   2
 
14 rows selected.
We can see that as we added significant digits to X, the amount of storage required took increasingly more room. Every two significant digits added another byte of storage. But a number just one larger consistently took 2 bytes. When Oracle stores a number, it does so by storing as little as it can to represent that number. It does this by storing the significant digits, an exponent used to place the decimal place, and information regarding the sign of the number (positive or negative). So, the more significant digits a number contains, the more storage it consumes. 
That last fact explains why it is useful to know that numbers are stored in varying width fields. When attempting to size a table (e.g., to figure out how much storage 1,000,000 rows would need in a table), you have to consider the NUMBER fields carefully. Will your numbers take 2 bytes or 20 bytes? What is the average size? This makes accurately sizing a table without representative test data very hard. You can get the worst-case size and the best-case size, but the real size will likely be some value in between.


one more thing...

Essy Pribadi, July 10, 2008 - 6:42 am UTC

and how about the oci-function

OCIAttrGet( (void*) handle_column,
(ub4) OCI_DTYPE_PARAM,
(void*) &col_width,
(ub4 *) 0,
(ub4) OCI_ATTR_DATA_SIZE,
(OCIError *) p_err
);

does it deliver the MAXIMUM bytes used by the asked column ? As I understand you, it does, just to be sure...
Tom Kyte
July 10, 2008 - 8:21 am UTC

http://docs.oracle.com/docs/cd/B10501_01/appdev.920/a96584/oci15r20.htm


it would tell you the biggest length you would expect to receive from said column, the EXTERNAL datatype - not the internal datatype. What you might have to allocate to work with it, not what we would to store it.

http://docs.oracle.com/docs/cd/B10501_01/appdev.920/a96584/ociaahan.htm#447367

OCI_ATTR_DATA_SIZE
Description

Maximum size in bytes of the external data for the column. This can affect conversion buffer sizes.

wow, a cool and clear answer...

Essy Pribadi, July 10, 2008 - 8:47 am UTC

thanks a lot, so oracle deals numbers like strings, wow... does all DBMS-Systems do like this or is it unique in oracle ?

and what about the mysql using no hash-joins? is it really as I understand an weak point of them ? why they don't change it ?
Tom Kyte
July 10, 2008 - 9:43 am UTC

most do not (use numbers like ours).

We also do floats and doubles, and in plsql - you can use binary integers for math as well.

But it is all about precision.


As for mysql, you'd have to ask them. If you have access to my other book Effective Oracle by Design, in the chapter on Effective SQL, I cover the major access paths and join techniques in detail - you might find that illuminating.

Row length calculation

arvind mishra, December 30, 2008 - 1:51 am UTC

Hello Tom,

I am confused how to calculate rowlength when there is number datatype in a table. My problem is:
SQL> DESC TK
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 ID                                                 NUMBER
 NAME                                               CHAR(2000)

SQL> TRUNCATE TABLE TK
  2  /

Table truncated.

SQL> INSERT INTO TK VALUES(100000,'A');

1 row created.

SQL> ANALYZE TABLE TK
  2  COMPUTE STATISTICS
  3  /

Table analyzed.

SQL> SELECT VSIZE(ID) FROM TK
  2  /

 VSIZE(ID)                                                                      
----------                                                                      
         2                                                                      

SQL> SELECT AVG_ROW_LEN FROM USER_TABLES
  2  WHERE TABLE_NAME = 'TK'
  3  /

AVG_ROW_LEN                                                                     
-----------                                                                     
       2009                                                                     

SQL> SELECT BLOCKS
  2  FROM USER_TABLES
  3  .
SQL> SELECT BLOCKS, EMPTY_BLOCKS, AVG_SPACE, NUM_FREELIST_BLOCKS
  2  FROM USER_TABLES
  3   WHERE TABLE_NAME = 'TK'
  4  /

    BLOCKS EMPTY_BLOCKS  AVG_SPACE NUM_FREELIST_BLOCKS                          
---------- ------------ ---------- -------------------                          
         1           62      14259                   1                          
----------                                                                      
         1                                                                      

SQL> SHOW PARAMETER DB_BLOCK_SIZE

NAME                                 TYPE        VALUE                          
------------------------------------ ----------- ------------------------------ 
db_block_size                        integer     16384                          
SQL> SELECT 16384-14259 FROM DUAL
  2  /

16384-14259                                                                     
-----------                                                                     
       2125                                                                     

SQL> SELECT 2125 - 116 FROM DUAL
  2  /

  2125-116                                                                      
----------                                                                      
      2009 
                               
My questions are:

1. Is id column data is using 9 bytes to store?
2. If yes then why vsize is showing 2?

Regards,

Arvind
                        

Tom Kyte
January 05, 2009 - 8:41 am UTC

you are forgetting row overheads - there are length fields associated with both columns, there are null indicators associated with both columns.


Row length and table size

arvind mishra, January 07, 2009 - 6:04 pm UTC

Hello Tom,

Thanks for your comment. Can you give me hyper link to any doucumentation using which I can learn followings:

1. How to calculate row overhead and null indicators or entire row length. I am totaly confused on it.
2. How to calculate block overhead and table size. My final goal is to estimate a table size while designing database tables.

Regards,

Arvind


Tom Kyte
January 08, 2009 - 8:42 am UTC

1) pretty simple, add a couple of bytes to each column. Say "3" (1 byte null indicator, 2 byte length field). And roll with it. It is not that big of a deal.

2) there is no way to do this realistically. There are way too many variables. The simple fact is that the number 99999 takes more space to store than 99999+1 does. You have varchar2 strings with different lengths.


The only way I know to size a table is

a) create the table
b) load REPRESENTATIVE data into it, load some percent of the estimated total table size. For a table you think will be 'small', you would load say at least 10% of the expected rows. For a table you think will be large - somewhere between 0.1 and 1% of the rows probably suffices. Then - use dbms_stats to gather statistics on this table and use extrapolation (multiply) to determine what the full table would consume space wise.


Otherwise, just take

a) what you expect the average row to be
b) add 5 * number of columns to that
c) take your block size and subtract 15% (10% pctfree, 5% overhead)
d) divide (c) by (b) to get rows per block
e) divide (d) into the number of rows you expect


It is not a precise thing.

Row length calculation

Arvind, January 09, 2009 - 1:19 am UTC

GOT IT!!!! THANKS-A-LOT. I will use representative data idea in my project.

Regards,

Arvind

Number(38) OR INTEGER

Atul Gupta, October 29, 2009 - 3:45 am UTC

Hello Tom,
Pls let me know while designing new tables for storing auto-generate primary key via sequence like 1,2,3,4.....etc which is preferred for storage NUMBER(38) OR INTEGER and Why ?

Br
Atul Gupta
Tom Kyte
October 29, 2009 - 8:42 am UTC

they are the same basically.

ops$tkyte%ORA10GR2> create table t ( x number(38), y int );

Table created.

ops$tkyte%ORA10GR2> desc t
 Name                                                  Null?    Type
 ----------------------------------------------------- -------- ------------------------------------
 X                                                              NUMBER(38)
 Y                                                              NUMBER(38)


NUMBER(38) or INTEGER

Valentín Castañeda Gómez, November 13, 2009 - 11:05 am UTC

Hi Tom.

First of all, thanks a lot for sharing your knowledge with us "simple mortals".

Regarding your last answer about NUMBER(38) and INTEGER being basically the same, I just want to ask you about something I found, for which I've found no explanation on the documentation:

ORA10GR2> DROP TABLE t;

Table dropped.

ORA10GR2> create table t ( x number(38), y int );

Table created.

ORA10GR2> DESC t
 Name                                                  Null?    Type
 ----------------------------------------------------- -------- ------------------------------------
 X                                                              NUMBER(38)
 Y                                                              NUMBER(38)

ORA10GR2> SELECT column_name, data_type, data_length, data_precision, data_scale
  2  FROM user_tab_columns
  3  WHERE table_name = 'T';

COLUMN_NAME                    DATA_TYPE  DATA_LENGTH DATA_PRECISION DATA_SCALE
------------------------------ ---------- ----------- -------------- ----------
X                              NUMBER              22             38          0
Y                              NUMBER              22                         0


As you can see, the data precision is only stored when we use NUMBER(38), and is NULL for INT.

Are there any implications on performance or behavior -I guess there shouldn't be- as a result of this difference?

I've looked in the documentation but haven't found anything on this...

Thanks a lot Tom!
Tom Kyte
November 15, 2009 - 2:58 pm UTC

it would only impact things that query the dictionary really.

In truth, it would be more accurate to say that int is really number(*,0), but that syntax is so obtuse, I don't use it...

ops$tkyte%ORA10GR2> create table t ( x number(38), y int );

Table created.

ops$tkyte%ORA10GR2> select dbms_metadata.get_ddl( 'TABLE', 'T' ) from dual;

DBMS_METADATA.GET_DDL('TABLE','T')
-------------------------------------------------------------------------------

  CREATE TABLE "OPS$TKYTE"."T"
   (    "X" NUMBER(38,0),
        "Y" NUMBER(*,0)
   ) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
  TABLESPACE "USERS"

Number vs number(n)

Ogrin Rastr, November 17, 2009 - 9:15 am UTC

Would you recommend using number or number(n) when creating tables? I have a colleague who uses number and can't see a reason to use number(n) because 'it's just a number'.

I think you should always use number(n). Not doing this tells me you've not really thought about what data you would expect in the column and if you don't know, how are other developers going to know. Some tools require a size e.g. Oracle Forms, 6i at least, this means a decision needs to be made every time the columns are referenced resulting in inconsistencies. It also allows you to constrain the column.

Tom Kyte
November 23, 2009 - 2:20 pm UTC

.. Would you recommend using number or number(n) when creating tables? ...

do you want an integer or a number capable of holding anything with 38 digits of precision and a sliding scale?

that is the only thing you need to answer - once you answer that, the right datatype is sitting in front of you.


I like to use int myself for things that will be an integer. Shorthand.

number precision error

rubén, November 30, 2009 - 5:48 am UTC

Hello,

I've found that my database is inserting a number in the decimal part which I didn't set in a number(20,15) column. After playing a bit I've seen it's trying to put 2 numbers in a specific decimal position related to the whole number portion.

create table t (a number(20,10),b number(20,11),c number(20,12),d number(20,13),e number(20,14),f number(20,15))

insert into t values (20991.06,20991.06,20991.06,20991.06,20991.06,20991.06);
insert into t values (400.85,400.85,400.85,400.85,400.85,400.85);
commit;

select * from t

(I put the results in rows)

20991,0600000000
20991,06000000000
20991,060000000001
20991,0600000000013
20991,06000000000130
20991,060000000001300
400,8500000000
400,85000000000
400,850000000000
400,8500000000000
400,85000000000002
400,850000000000023

Am I forgetting something or is it a bug? How could I prevent this apart from defining the column as NUMBER without precision? I looked for it on internet but nothing appeared.
Oracle Version 10.2.0.1.0
thanks a lot

Tom Kyte
December 01, 2009 - 2:51 am UTC

You don't say what client you are using, but it looks like you are using some application that is using floating point numbers in the client to fetch into.

floating point numbers have 6 or 13 digits of precision (we have 38) and are incapable of holding many numbers without losing precision (eg: they cannot store most numbers correctly).

ops$tkyte%ORA10GR2> create table t (a number(20,10),b number(20,11),c number(20,12),d number(20,13),e number(20,14),f number(20,15));

Table created.

ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> insert into t values (20991.06,20991.06,20991.06,20991.06,20991.06,20991.06);

1 row created.

ops$tkyte%ORA10GR2> insert into t values (400.85,400.85,400.85,400.85,400.85,400.85);

1 row created.

ops$tkyte%ORA10GR2> commit;

Commit complete.

ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> set numformat 99999.0000000000000009
ops$tkyte%ORA10GR2> select * from t;

                      A                       B                       C
----------------------- ----------------------- -----------------------
                      D                       E                       F
----------------------- ----------------------- -----------------------
 20991.0600000000000000  20991.0600000000000000  20991.0600000000000000
 20991.0600000000000000  20991.0600000000000000  20991.0600000000000000

   400.8500000000000000    400.8500000000000000    400.8500000000000000
   400.8500000000000000    400.8500000000000000    400.8500000000000000


ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> select cast( f as binary_double ) from t;

 CAST(FASBINARY_DOUBLE)
-----------------------
 20991.0600000000010000
   400.8500000000000200

ops$tkyte%ORA10GR2> select cast( f as binary_float) from t;

  CAST(FASBINARY_FLOAT)
-----------------------
 20991.0605000000000000
   400.8500060000000000





So, I think the problem is not in the database (use sqlplus and you'll see) but in the client that fetches and displays this data - that client should not be used to modify any numeric data - you'll end up messing it up.

rubén, December 01, 2009 - 5:02 am UTC

Yes, you're right, it doesn't happen with sqlplus.
I'm using pl/sql developer ver 7.1.3.1381, to solve it I have to format with to_char(999999.999.....etc) to see it correctly.

thanks a lot
Tom Kyte
December 01, 2009 - 10:21 am UTC

beware of that tool then, that was a really really REALLY bad design decision on their part, beware of modifying data with that tool - the results could be "strange"

Preventing a number being rounded

Lise, January 04, 2010 - 11:17 am UTC

Hi Tom,

If I define a type like this, where the table test.col1 is a NUMBER(5,5):
TYPE ty_table IS TABLE OF test.col1%TYPE INDEX BY PLS_INTEGER;

..and I pass in a value that has a scale larger than defined say 0.1234567, it will round the number to make it fit the scale.
Is there any way I can stop this rounding from happening and instead get it to report an error?

I am on 11gR2
Tom Kyte
January 04, 2010 - 12:40 pm UTC

for the table, you can enforce the (5,5) via a check constraint on the table (the plsql index by table will always round).

that is the way numbers were designed to work, they round by design.


ops$tkyte%ORA11GR2> create table t ( x number constraint x_p5s5 check (x = cast( x as number(5,5))) );

Table created.

ops$tkyte%ORA11GR2> insert into t values ( 0.12345 );

1 row created.

ops$tkyte%ORA11GR2> insert into t values ( 0.123456 );
insert into t values ( 0.123456 )
*
ERROR at line 1:
ORA-02290: check constraint (OPS$TKYTE.X_P5S5) violated

Impossible datatype?

Drew, February 09, 2010 - 3:51 pm UTC

I had an issue with a partition exchange where there was a mismatched datatype. I found the suspect field, and it had the following properties (from USER_TAB_COLS):

DATA_TYPE DATA_LENGTH DATA_PRECISION DATA_SCALE
--------- ----------- -------------- ----------
NUMBER 22 {NULL} 0

Is this even possible? How the heck do you set PRECISION to NULL? The only way I was able to replicate was with CTAS on the original table.
Tom Kyte
February 15, 2010 - 2:57 pm UTC

... The only
way I was able to replicate was with CTAS on the original table. ...

that would be the way - the precision was not known with the CTAS, you would use CAST( expression as number(x,y)) to avoid that in the create table as select.

Broken links

Piotr, January 23, 2012 - 4:13 am UTC

Hello Tom,

thanks a lot for useful explanations.
Could you, please, update this post with correct links to Oracle documentation. The ones you quoted on the top are broken...

Question About how to map a oracle number(19,0) to hive

gnyanraj, March 15, 2012 - 4:51 am UTC

Hi Tom,
i am having a question i,e in oracle number(19,0) it holds 19 digits not more than that,so here i am creating a table in hive,so i am getting confusion .So what datatype i should declare in hive because in hive we found highest datatype is BIGINT i,e 8 bytes it holds only 18 digits.i need to hold 19 digits with integer type,pls do reply
Tom Kyte
March 15, 2012 - 7:41 am UTC

ask hive what they would do.

what else can I say?

Number > 38 digits

Olivier, July 17, 2012 - 4:09 am UTC

Hi Tom,
I've a big issue ....
I'm writting a procedure to check an IBAN code, in which I have to translate every character by a number, concatenante the whole numbers and executed a MOD 97 on it...
The problem is that for Malta, I obtain a number of 44 digits and when I do the mod(numer, 97), I get a bad answer....
Would it exist a datatype allowing 44 digits?
By example: I have this number: 22102129011000040102143027122216502116222905
When I execute MOD(22102129011000040102143027122216502116222905, 97), I should have 1, but I get 0 because the last 4 digits are truncated....
Can you help me?

Thanks

Number datatype negative precision

Amit Sonthalia, January 06, 2013 - 11:15 pm UTC

I have a simple doubt regarding number declaration. Number is declared as Number(p,s). The maximum value precision can take is 38 and the least value of scale is -84. But declaring any number with scale less than equal to -38, For eg Number(38,-39) would result in zeroes being stored in the table. So could you please explain in which cases are the scale from -38 to -84 useful in number declaration.
Tom Kyte
January 14, 2013 - 11:16 am UTC

ops$tkyte%ORA11GR2> create table t ( x number(38,-39) );

Table created.

ops$tkyte%ORA11GR2> 
ops$tkyte%ORA11GR2> insert into t values ( 12345678901234567890123456789012345678000000000000000000000000000000000000000 );

1 row created.

ops$tkyte%ORA11GR2> 
ops$tkyte%ORA11GR2> select * from t;

         X
----------
1.2346E+76



it would not just store zeros. You are saying "I want 38 digits of precision - for a really really really large number, because I said there will be 39 zeros before we start seeing digits whose value we care about"

INTEGER is not the same as NUMBER(38)

Sokrates, May 14, 2013 - 10:15 am UTC

http://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:1619552483055#2074290800346444852
"Number(38) OR INTEGER" ?
they are the same basically.

Should be probably read
the should be the same as documented on http://docs.oracle.com/cd/E11882_01/server.112/e26088/sql_elements001.htm#i54335 , but behave differently

sokrates@11.2 > create table "INTEGER" ( i integer );

Table created.

sokrates@11.2 > create table "NUMBER(38)" ( i number(38) );

Table created.

sokrates@11.2 > desc "INTEGER"
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 I                                                  NUMBER(38)

sokrates@11.2 > desc "NUMBER(38)"
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 I                                                  NUMBER(38)

sokrates@11.2 > insert into "INTEGER" values(1e38 - 1);

1 row created.

sokrates@11.2 > insert into "NUMBER(38)" values(1e38 - 1);

1 row created.

sokrates@11.2 > insert into "INTEGER" values(1e38);

1 row created.

sokrates@11.2 > insert into "NUMBER(38)" values(1e38);
insert into "NUMBER(38)" values(1e38)
                                *
ERROR at line 1:
ORA-01438: value larger than specified precision allowed for this column


?
Tom Kyte
May 14, 2013 - 12:36 pm UTC

that is strange, the integer is supporting 39 digits, you are correct. I'd still say that integer and number(38) are 'equivalent'

even ...

Sokrates, May 14, 2013 - 6:51 pm UTC

sokrates@11.2 > insert into "INTEGER" values(9 * 1e125);

1 row created.

sokrates@11.2 > select * from "INTEGER";

         I
----------
1.0000E+38
1.0000E+38
9.000E+125



Interestingly, "INTEGER" has not data precision

sokrates@11.2 > select table_name, data_precision, data_scale from cols where column_name = 'I';

TABLE_NAME                     DATA_PRECISION DATA_SCALE
------------------------------ -------------- ----------
INTEGER                                                0
NUMBER(38)                                 38          0

Tom Kyte
May 14, 2013 - 9:30 pm UTC

ok, I shall file a doc bug.

what it should say is that an integer is the same as number(*,0).

ops$tkyte%ORA11GR2> create table t ( x integer, y number(*,0), z number(38) );

Table created.

ops$tkyte%ORA11GR2> 
ops$tkyte%ORA11GR2> insert into t (x) values (1e38);

1 row created.

ops$tkyte%ORA11GR2> insert into t (y) values (1e38);

1 row created.

ops$tkyte%ORA11GR2> insert into t (z) values (1e38);
insert into t (z) values (1e38)
                          *
ERROR at line 1:
ORA-01438: value larger than specified precision allowed for this column


ops$tkyte%ORA11GR2> select column_name, data_precision, data_scale from cols  where table_name = 'T';

COLUMN_NAME                    DATA_PRECISION DATA_SCALE
------------------------------ -------------- ----------
X                                                      0
Y                                                      0
Z                                          38          0



thanks for clarification

Sokrates, May 15, 2013 - 8:19 am UTC


only a documentation bug ?

Sokrates, May 15, 2013 - 12:24 pm UTC

sokrates@11.2 > create table t ( x integer, y number(*,0), z number(38) );

Table created.

sokrates@11.2 > describe t
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 X                                                  NUMBER(38)
 Y                                                  NUMBER(38)
 Z                                                  NUMBER(38)


I don't know where describe has its information "..(38)" from, it doesn't seem to have it from the data dictionary, but shouldn't it display
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 X                                                  NUMBER(126)
 Y                                                  NUMBER(126)
 Z                                                  NUMBER(38)


instead ?

Chuck Jolley, May 15, 2013 - 4:29 pm UTC

Why 126?
Tom Kyte
May 16, 2013 - 8:35 am UTC

because that is as big as we can go

ops$tkyte%ORA11GR2> insert into t values ( rpad( '8', 126, '9' ) );

1 row created.

ops$tkyte%ORA11GR2> insert into t values ( rpad( '8', 127, '9' ) );
insert into t values ( rpad( '8', 127, '9' ) )
                       *
ERROR at line 1:
ORA-01426: numeric overflow



but we've lost the precision by then:

ops$tkyte%ORA11GR2> create table t ( x number(*,0) );

Table created.

ops$tkyte%ORA11GR2> insert into t values ( rpad( '9', 45, '9' ) );

1 row created.

ops$tkyte%ORA11GR2> select to_char( x ) from t;

TO_CHAR(X)
----------------------------------------
1.0000000000000000000000000000000000E+45

ops$tkyte%ORA11GR2> set numformat 9999999999999999999999999999999999999999999999999
ops$tkyte%ORA11GR2> select x from t;

                                                 X
--------------------------------------------------
    1000000000000000000000000000000000000000000000


see also ...

Sokrates, September 30, 2014 - 7:57 pm UTC

Jess, July 26, 2018 - 3:01 pm UTC

Hi Oracle Masters,

We have a table with column, A of type "number".
Values for "length(A)" range from 1 to 41.

Looking at some specific records, "length(A)" returns 40.
But selecting the records themselves, the value for A is displayed as 1.
Not a floating point, just 1.

But then there are other records with value of A = 1 and length(A) = 1.

What's going on here? Why some of the 1s have length = 1 and others length = 40?

Thanks as always!

Jess, July 26, 2018 - 3:16 pm UTC

And we may've answered our own question...

select to_char(A) for one of the records returns

----------------------------------------
1.00000000000000000000000000000000000003
Chris Saxon
July 26, 2018 - 4:45 pm UTC

:)

Dump of Numeric value - Typ=2 Len=3: 191,3,1

Amit, May 20, 2019 - 7:04 pm UTC

Hi Tom,

I have numeric column with NOT NULL constraint (Exadata 12.1.0.2). But when I select, I am seeing 3 records with no value. SQL developer is showing it as NULL value. When DUMP function is applied, I see following output.

ROWID NUM dump(num,10)
-----------------------------------------------------
AAJ50wAAKAAMIRcAAy Typ=2 Len=3: 191,3,1
AAJ50wAAKAAMIRmAA9 Typ=2 Len=3: 191,3,1
AAJ50wAAKAAMIRtAAd Typ=2 Len=3: 191,3,1

I did some reading on how numbers are stored in Oracle and understood that, "The first byte (8 bits) consists of 2 parts: the first bit (the most significant bit) is the sign. It will be 1 if the number is positive and 0 if it is negative. The other 7 bits represent the exponent of the number, or the location of the decimal point."
If this is correct, then it means the number is positive. But exponent value comes as "-1". Does this mean that data is corrupt?

Thanks!
Connor McDonald
May 22, 2019 - 12:25 am UTC

Can you dump the block for us ?


Incorrect assumption - Dump of Numeric value - Typ=2 Len=3: 191,3,1

Amit, May 20, 2019 - 7:18 pm UTC

My apologies; I interpreted the output wrong. first byte can be 191 and it should not be an issue. But I am not able to figure out why I dont see any value for those records.

Chuck Jolley, May 22, 2019 - 1:25 pm UTC

Column set not null novalidate after it had some null values in it?
Chris Saxon
May 24, 2019 - 8:47 am UTC

Yep, that's a possibility, running

select validated from user_constraints
where  constraint_name = :cons;


would reveal that.

How to retrieve the actual value from a number with precision greater than 38?

Alan David, October 09, 2019 - 1:38 am UTC

I inserted a column with number data type with a number with a precision of 46 digits but when i queried, I could see that the number has been replaced with 0 after 40 digits. Surprisingly, when I query with the actual value, I could still retrieve the record. Is there a way to find out the original value from the table?

SQL@12.2.0.2>create table test_number(col1 number)
2 /

Table created.

SQL@12.2.0.2>insert into test_number values (581821841852847202155114134237200249143124153)
2 /

1 row created.

SQL@12.2.0.2>select * from test_number
2 /

COL1
--------------------------------------------------
581821841852847202155114134237200249143000000

SQL@12.2.0.2>select * from test_number where col1=581821841852847202155114134237200249143124153
2 /

COL1
--------------------------------------------------
581821841852847202155114134237200249143000000
Chris Saxon
October 09, 2019 - 10:38 am UTC

Nope. Oracle Database rounds values exceeding the scale. So this information is lost!

Thanks Chris

Alan David, October 10, 2019 - 1:32 am UTC

Thanks Chris, but how does Oracle retrieve the "rounded up" stored record from the table if I provide the original value in the query?
Chris Saxon
October 10, 2019 - 8:18 am UTC

Both values are rounded, as Anton shows below.

original value is always "rounded up"

Anton, October 10, 2019 - 7:44 am UTC

@Alan David
When you retrieve your rounded value with your original number, Oracle rounds your original number too before comparing it to the (rounded) values stored in your table. See for instance this query , with two different number in the where clause:
select to_char( 58182184185284720215511413423720024914250, rpad( '0', 45, '9' ) )
     , to_char( 58182184185284720215511413423720024914349, rpad( '0', 45, '9' ) )
from dual
where 58182184185284720215511413423720024914250
    = 58182184185284720215511413423720024914349

Chris Saxon
October 10, 2019 - 8:18 am UTC

Thanks for explaining this Anton!

Thank you

A reader, October 14, 2019 - 6:11 pm UTC

Thank you, for the clarification