Home » SQL & PL/SQL » SQL & PL/SQL » NUMBER or NUMBER(12) (Oracle 10.2.0)
NUMBER or NUMBER(12) [message #365260] Thu, 11 December 2008 04:25 Go to next message
eigeneachse
Messages: 37
Registered: July 2008
Member
Hi@all,
i asked myself if there is a difference in oracle when i exactly say what datatype a column should have.
I.e. is there a difference if i have an primary key colum definded with NUMBER definded with NUMBER(12).

Do i have an effort for queries on that table when using this column in my where part.

Maybe the database could use other datatype internalle, i.e. using integer rather than floats or something like this.

Thanks in advance for any comments on that.

Regards

eigeneachse
Re: NUMBER or NUMBER(12) [message #375830 is a reply to message #365260] Mon, 15 December 2008 01:33 Go to previous messageGo to next message
Frank Naude
Messages: 4502
Registered: April 1998
Senior Member
See NUMBER.
Re: NUMBER or NUMBER(12) [message #375846 is a reply to message #375830] Mon, 15 December 2008 02:43 Go to previous messageGo to next message
eigeneachse
Messages: 37
Registered: July 2008
Member
Frank Naude wrote on Mon, 15 December 2008 08:33
See NUMBER.


Hi Frank,
i did the following

create table tmp_test
(
    zahl1   number,
    zahl2   number(12)
);


insert into tmp_test(zahl1, zahl2) select object_id, object_id from all_objects;


So i have an table with two different columns specified.

Now when i dump it then i get the following results.

select dump( zahl1, 10) from tmp_test where zahl1 = 17945;
Typ=2 Len=4: 195,2,80,46

select dump( zahl2, 10) from tmp_test where zahl2 = 17945;
Typ=2 Len=4: 195,2,80,46



So what is the conclusion? Does Oracle store the values internally with the same type? I would say yes. What do you think?

Regards

eigeneachse
Re: NUMBER or NUMBER(12) [message #375850 is a reply to message #375846] Mon, 15 December 2008 03:07 Go to previous messageGo to next message
Frank Naude
Messages: 4502
Registered: April 1998
Senior Member
Yes, Oracle stores the values exactly the same.

The only difference is that NUMBER(12) cannot have values bigger than 999 999 999 999.
Re: NUMBER or NUMBER(12) [message #375886 is a reply to message #375850] Mon, 15 December 2008 04:29 Go to previous messageGo to next message
eigeneachse
Messages: 37
Registered: July 2008
Member
Frank Naude wrote on Mon, 15 December 2008 10:07
Yes, Oracle stores the values exactly the same.

The only difference is that NUMBER(12) cannot have values bigger than 999 999 999 999.


I have one more question. I there propably an difference when indexes on these columns are used in queries? I mean perhaps oracle knows that less memory needs to be allocated when using number(12) instead of number, which could be much bigger.
I hope the question is clear.

Thanks in advance.

regards

eigeneachse

[Updated on: Mon, 15 December 2008 04:31]

Report message to a moderator

Re: NUMBER or NUMBER(12) [message #375893 is a reply to message #375886] Mon, 15 December 2008 05:01 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
No difference in any way.

Regards
Michel
Re: NUMBER or NUMBER(12) [message #375907 is a reply to message #375893] Mon, 15 December 2008 05:44 Go to previous messageGo to next message
Frank Naude
Messages: 4502
Registered: April 1998
Senior Member
W.r.t. good style: it is always better to specify NUMBER(n) instead of just NUMBER. If shows that you have thought about it and know what you're doing. If you don't, it shows that you're either too lazy or don't actually know.
Re: NUMBER or NUMBER(12) [message #376047 is a reply to message #365260] Mon, 15 December 2008 15:06 Go to previous messageGo to next message
eigeneachse
Messages: 37
Registered: July 2008
Member
Thank you all for your replies.

Regards

eigeneachse
Re: NUMBER or NUMBER(12) [message #376048 is a reply to message #375907] Mon, 15 December 2008 15:21 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10672
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
>>it shows that you're either too lazy or don't actually know

There are too many tables in Oracle data dictionary itself like that. Smile.


chum > desc v$database;
 Name						       Null?	Type
 ----------------------------------------------------- -------- ------------------------------------
 DBID								NUMBER

[Updated on: Mon, 15 December 2008 15:21]

Report message to a moderator

Re: NUMBER or NUMBER(12) [message #376071 is a reply to message #376048] Mon, 15 December 2008 23:14 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
v$database is not a table, it is a view and more a dynamic view based on fixed and not updatable tables, so its number datatype is just a SQL datatype mapping to internal type, just to say it is a number.
OK, this is true for other SYS tables but dictionary and SYS objects are NOT part of a database application. Oracle makes so much effort to let us see its internals with SQL, in the same way we used to see our objects that we forget RDBMS is not a database application.
Never compare SYS with any other schema or user.

Regards
Michel
Re: NUMBER or NUMBER(12) [message #376123 is a reply to message #376071] Tue, 16 December 2008 03:07 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10672
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
>>v$database is not a table,
Michel,
I understand that and I was just making fun. Not really comparing anything.
Regards
Re: NUMBER or NUMBER(12) [message #376138 is a reply to message #376123] Tue, 16 December 2008 04:37 Go to previous message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
The answer was more for beginners than for you.

Regards
Michel
Previous Topic: Load Utility in DB2 - Alternative in Oracle?
Next Topic: user_tab_columns (merged)
Goto Forum:
  


Current Time: Sat Dec 10 10:29:45 CST 2016

Total time taken to generate the page: 0.09685 seconds