How NUMBER length impact performance?

[LND]

Whats is impact of length of table column of datatype NUMBER for performance:
1) performance better as oracle can use the fact that number will never exceed max length?
2) performance lower as Oracle has to perform a check for update/insert

The same question for PL/SQL variable of datatype NUMBER.

[Ispirer]

Hi,

I think that performance is better for short numbers, because for example, total table or index size is less, so it needs less time to scan them.
It maybe very significant for large tables.

Best regards, Dmitry.
--
----
http://www.ispirer.com - Database migration tools and services for Oracle and IBM DB2.
http://www.ispirer.com/chyfo.html - Tool exports data to CSV flat file, generates CREATE TABLE, CREATE INDEX scripts for Oracle and control files for SQL Loader.

[m1l]

I'm not 100% sure, but nearly 100% but I think that whatever size you put on a number, it is exactly the same size in the database, so NUMBER(1) is the same as NUMBER(20)

[LND]

"I think that performance is better for short numbers, because for example, total table or index size is less, so it needs less time to scan them"

I mean difference of performace when maximum length of number is the same, just one case is when number length is specified and thus enforced by DB engine and the other case when not.

[LND]

more?

[jmodic]

I don't think there is any considerable performance penalty in constraining column length.

I know that for SELECTs Oracle is too dumb to to consider column length in the following case:

- You have a table TAB1 with column COL1 NUMBER(3). If you do "SELECT * FOM tab1 WHERE col1 > 10000" Oracle will still perform a full table scan, although it should know that no row could satisfy this condition.

For INSERTS and UPDATES I also don't think there will be any difference. Oracle has to perform datatype check anyway, so I don't think checking for length and precision brings any additional costs. Anyway, you could make a very simple test yourself. Make a batch that will be inserting/updating same values to two different tables - one with constrained number and one with unconstrained number datatype. I don't think you will find any considerable time difference between both. But, as allways, I might be wrong....

[LND]

convinced w/o testing, thanx.