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

Thread: How NUMBER length impact performance?

  1. #1
    Join Date
    Nov 2000
    Posts
    212
    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.

  2. #2
    Join Date
    Jan 2001
    Posts
    18
    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.

  3. #3
    Join Date
    Oct 2000
    Posts
    90
    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)


  4. #4
    Join Date
    Nov 2000
    Posts
    212
    "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.

  5. #5
    Join Date
    Nov 2000
    Posts
    212
    more?

  6. #6
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    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....
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

  7. #7
    Join Date
    Nov 2000
    Posts
    212
    convinced w/o testing, thanx.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width