12 Replies Latest reply: Nov 6, 2012 8:41 AM by rp0428 RSS

    Number vs Varchar2

    Maahjoor
      Dear all,

      i want to know the difference between NUMBER and VARCHAR2 datatypes such that;

      storage difference:
      if we store 'Alaska' in varcahar2(100) field, it will not occupy 100 byte of storage, just the length of 'alaska' will be occupy.

      if we store 245 in number(10) field, will it be like varchar2? only space occupy by 245 will be taken or all the storage space for number(10) will be occupied?

      Performance difference:
      which one execute fast?

      i will be very thankfull for your kind help.
        • 1. Re: Number vs Varchar2
          953667
          Both Varchar2 and Number will work same in the process occupying space in db.
          When it comes to Char datatype, Its default size is 1. If you declare it as
          Char(10) and assigned a value to it, for ex, 'MAAH'. Total space occupied by this is 10 even its having 4 characters.

          But, Varchar2(10) and Number(10) will occupy the space for the data you have assigned to it. Remaining will be free. Both are same in the mean of performance also.
          • 2. Re: Number vs Varchar2
            Marwim
            Hello,
            DROP TABLE test_number;
            CREATE TABLE test_number (n NUMBER);
            INSERT INTO test_number VALUES (1);
            INSERT INTO test_number VALUES (1000);
            INSERT INTO test_number VALUES (100000000);
            INSERT INTO test_number VALUES (1000000000000000);
            COMMIT;
            SELECT LPAD(TO_CHAR(n),20), DUMP(n) FROM test_number;
            
            LPAD(TO_CHAR(N),20)  DUMP(N)
            -------------------- ----------------------
                               1 Typ=2 Len=2: 193,2
                            1000 Typ=2 Len=2: 194,11
                       100000000 Typ=2 Len=2: 197,2
                1000000000000000 Typ=2 Len=2: 200,11
            The undocumented function dump shows you, that the number always uses 2 Bytes, regardless of the length of the number.
            Performance difference:
            which one execute fast?
            This question makes no sense. When you need a number, then you store it as NUMBER. When you need a string you store it as VARCHAR2 or CLOB. Anything else would be bad design and lead to a lot of problemd later.

            Regards
            Marcus

            Edited by: Marwim on 06.11.2012 10:35

            Wrong example, my test case only used 10En Values and those require less space than "normal" numbers.
            • 3. Re: Number vs Varchar2
              908002
              Yes, it will be like varchar2 when comes to storage. it only take memory requuired to store 245 from your example.

              performance will not be depends on the datatype.

              it depends on data volume and how efficient your query are..
              • 4. Re: Number vs Varchar2
                Marwim
                Number(10) will occupy the space for the data you have assigned to it. Remaining will be free.
                Can you prove this?

                Edited by: Marwim on 06.11.2012 10:37
                Wrong, because my testcase was flawed.
                • 5. Re: Number vs Varchar2
                  Marwim
                  Yes, it will be like varchar2 when comes to storage. it only take memory requuired to store 245 from your example.
                  No, NUMBER will always require 2 Bytes, even when you restrict the column to NUMBER(10). This is only a contraint that does not allow to store numbers greater than 9,999,999,999.
                  DROP TABLE test_number;
                  CREATE TABLE test_number (n NUMBER(10));
                  INSERT INTO test_number VALUES (1);
                  INSERT INTO test_number VALUES (1000);
                  INSERT INTO test_number VALUES (1000000000);
                  COMMIT;
                  SELECT LPAD(TO_CHAR(n),10), DUMP(n) FROM test_number;
                  
                  LPAD(TO_CHAR(N),10) DUMP(N)
                  ------------------- ---------------------
                           1          Typ=2 Len=2: 193,2
                        1000          Typ=2 Len=2: 194,11
                  1000000000          Typ=2 Len=2: 197,11
                  Edited by: Marwim on 06.11.2012 10:38
                  Wrong, because my testcase was flawed.
                  • 6. Re: Number vs Varchar2
                    Maahjoor
                    what is the length? it is the length of the string or the length of the storage in byte?
                    i think it is the length of the string stored in the column.

                    create table test_varchar2(n varchar2(20));
                    insert into test_varchar2 values('a');
                    insert into test_varchar2 values('aa');
                    insert into test_varchar2 values('aaa');
                    commit;


                    LPAD(TO_CHAR(N),20) DUMP(N)
                    -------------------- -----------------------
                    a Typ=1 Len=1: 97
                    aa Typ=1 Len=2: 97,97
                    aaa Typ=1 Len=3: 97,97,97
                    • 7. Re: Number vs Varchar2
                      jeneesh
                      Numbers are stored in variable size from 0 to 22
                      create table test(c1 number,c2 number(10,0));
                      
                      insert into test 
                      select power(10,level)-1,power(10,level)-1
                      from dual connect by level <= 10;
                      
                      10 rows inserted.
                      
                      select c1,c2,vsize(c1) s1,vsize(c2) s2
                      from test
                      order by 1;
                      
                      
                      C1              C2           S1      S2
                      -- ---------- -- -------------------------
                      9          9          2     2
                      99          99          2     2
                      999          999          3     3
                      9999          9999          3     3
                      99999          99999          4     4
                      999999          999999          4     4
                      9999999          9999999          5     5
                      99999999     99999999     5     5
                      999999999     999999999     6     6
                      9999999999     9999999999     6     6
                      Edited by: jeneesh on Nov 6, 2012 1:55 PM
                      Digged out the Asktom page
                      http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:1856720300346322149
                      • 8. Re: Number vs Varchar2
                        Paul  Horth
                        >
                        The undocumented function dump shows you, that the number always uses 2 Bytes, regardless of the length of the number.
                        >

                        Not true:
                        select dump(1234) from dual;
                        Typ=2 Len=3: 194,13,35
                        
                        select dump(1234000) from dual;
                        Typ=2 Len=4: 196,2,24,41
                        
                        select dump(1234567) from dual;
                        Typ=2 Len=5: 196,2,24,46,68
                        • 9. Re: Number vs Varchar2
                          TPD-Opitz
                          Maahjoor wrote:
                          i want to know the difference between NUMBER and VARCHAR2 datatypes such that;
                          I wonder why you wan to know this difference.
                          Maybe it's just your curiosity. That's fine.

                          But I'm afraid you're going to make a design decision based on that knowledge.
                          Don't do that!
                          Your choice of datatypes sould depend on business needs only!
                          You will run into varous prolems when you base your design decisions on such micro optimization.

                          bye
                          TPD
                          • 10. Re: Number vs Varchar2
                            andrewmy
                            Disk is cheap and overtime is expensive.

                            It is good advice to define data in the natural way it should be stored, and not worry about things like this.

                            Otherwise some consultant will be getting paid a lot to re-engineer it 5 years from now when the company can no longer afford all the fire-fighting that has to be done to keep the system running.
                            • 11. Re: Number vs Varchar2
                              6363
                              Maahjoor wrote:

                              i want to know the difference between NUMBER and VARCHAR2 datatypes such that;
                              They are constraints not storage or performance options.

                              If you want to store only numeric data you should store it in NUMBER data type or else your data will get corrupted and you may as well throw it away and start again.
                              storage difference:
                              Probably so small it would hardly be measurable or noticeable.
                              Performance difference:
                              which one execute fast?
                              Storing numbers in the wrong data type will lead to slower queries and less efficient plans. In the example below the plan estimated three times as much CPU and twice the run time for numbers stored wrongly in VARCHAR2 column. The actual run times though both small were worse for numbers stored wrongly in VARCHAR2 columns, being at least 10 times slower.
                              SQL> create table t (n number, v varchar2(6));
                              
                              Table created.
                              
                              SQL> insert into t select level, level from dual
                                2  connect by level < 1000000;
                              
                              999999 rows created.
                              
                              SQL> create index tx1 on t (n);
                              
                              Index created.
                              
                              SQL> create index tx2 on t (v);
                              
                              Index created.
                              
                              SQL> exec dbms_stats.gather_table_stats(user, 't', cascade=>true)
                              
                              PL/SQL procedure successfully completed.
                              
                              SQL> explain plan for
                                2  select avg(n) from t where n between 600000 and 700000;
                              
                              Explained.
                              
                              SQL> select * from table(dbms_xplan.display);
                              
                              PLAN_TABLE_OUTPUT
                              -------------------------------------------------------------------------------
                              Plan hash value: 2355292904
                              
                              --------------------------------------------------------------------------
                              | Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
                              --------------------------------------------------------------------------
                              |   0 | SELECT STATEMENT  |      |     1 |     5 |   226   (1)| 00:00:03 |
                              |   1 |  SORT AGGREGATE   |      |     1 |     5 |            |          |
                              |*  2 |   INDEX RANGE SCAN| TX1  |   100K|   488K|   226   (1)| 00:00:03 |
                              --------------------------------------------------------------------------
                              
                              Predicate Information (identified by operation id):
                              
                              PLAN_TABLE_OUTPUT
                              -------------------------------------------------------------------------------
                              
                                 2 - access("N">=600000 AND "N"<=700000)
                              
                              14 rows selected.
                              
                              SQL> explain plan for
                                2  select avg(v) from t where v between 600000 and 700000;
                              
                              Explained.
                              
                              SQL> select * from table(dbms_xplan.display);
                              
                              PLAN_TABLE_OUTPUT
                              -------------------------------------------------------------------------------
                              
                              Plan hash value: 2966233522
                              
                              ---------------------------------------------------------------------------
                              | Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
                              ---------------------------------------------------------------------------
                              |   0 | SELECT STATEMENT   |      |     1 |     7 |   539   (3)| 00:00:07 |
                              |   1 |  SORT AGGREGATE    |      |     1 |     7 |            |          |
                              |*  2 |   TABLE ACCESS FULL| T    |  2500 | 17500 |   539   (3)| 00:00:07 |
                              ---------------------------------------------------------------------------
                              
                              Predicate Information (identified by operation id):
                              
                              PLAN_TABLE_OUTPUT
                              -------------------------------------------------------------------------------
                              
                                 2 - filter(TO_NUMBER("V")>=600000 AND TO_NUMBER("V")<=700000)
                              
                              14 rows selected.
                              
                              SQL> set timing on
                              SQL> select avg(n) from t where n between 600000 and 700000;
                              
                                  AVG(N)
                              ----------
                                  650000
                              
                              Elapsed: 00:00:00.03
                              SQL> select avg(v) from t where v between 600000 and 700000;
                              
                                  AVG(V)
                              ----------
                                  650000
                              
                              Elapsed: 00:00:00.20
                              SQL> select avg(n) from t where n between 600000 and 700000;
                              
                                  AVG(N)
                              ----------
                                  650000
                              
                              Elapsed: 00:00:00.01
                              SQL> select avg(v) from t where v between 600000 and 700000;
                              
                                  AVG(V)
                              ----------
                                  650000
                              
                              Elapsed: 00:00:00.21
                              • 12. Re: Number vs Varchar2
                                rp0428
                                >
                                i want to know the difference between NUMBER and VARCHAR2 datatypes such that;

                                storage difference:
                                if we store 'Alaska' in varcahar2(100) field, it will not occupy 100 byte of storage, just the length of 'alaska' will be occupy.

                                if we store 245 in number(10) field, will it be like varchar2? only space occupy by 245 will be taken or all the storage space for number(10) will be occupied?
                                >
                                The Oracle Concepts doc has the information about the datatypes and storage. For VARCHAR2
                                http://docs.oracle.com/cd/B28359_01/server.111/b28318/datatype.htm#i3253
                                >
                                VARCHAR2 and VARCHAR Datatypes

                                The VARCHAR2 datatype stores variable-length character strings. When you create a table with a VARCHAR2 column, you specify a maximum string length (in bytes or characters) between 1 and 4000 bytes for the VARCHAR2 column. For each row, Oracle Database stores each value in the column as a variable-length field unless a value exceeds the column's maximum length, in which case Oracle Database returns an error. Using VARCHAR2 and VARCHAR saves on space used by the table.

                                For example, assume you declare a column VARCHAR2 with a maximum size of 50 characters. In a single-byte character set, if only 10 characters are given for the VARCHAR2 column value in a particular row, the column in the row's row piece stores only the 10 characters (10 bytes), not 50.

                                Oracle Database compares VARCHAR2 values using nonpadded comparison semantics.
                                >
                                And for NUMBER Oracle only stores the significant digits. That is why Marwim's examples kept showing the same length for 1 or 1000000000.
                                >
                                Numeric Format

                                Oracle Database stores numeric data in variable-length format. Each value is stored in scientific notation, with 1 byte used to store the exponent and up to 20 bytes to store the mantissa. The resulting value is limited to 38 digits of precision. Oracle Database does not store leading and trailing zeros. For example, the number 412 is stored in a format similar to 4.12 x 102, with 1 byte used to store the exponent(2) and 2 bytes used to store the three significant digits of the mantissa(4,1,2). Negative numbers include the sign in their length.

                                Taking this into account, 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
                                where s equals zero if the number is positive, and s equals 1 if the number is negative.

                                Zero and positive and negative infinity (only generated on import from Oracle Database, Version 5) are stored using unique representations. Zero and negative infinity each require 1 byte; positive infinity requires 2 bytes.
                                >
                                Your other question
                                >
                                Performance difference:
                                which one execute fast?
                                >
                                They both execute fast because Oracle reads blocks. It doesn't read rows or columns - it reads blocks.

                                If you want one column from one row Oracle will read the entire block that contains that one row (assuming the row fits into one block and is not chained - then it would also read the blocks containing the chain).

                                If your table has 100 rows and they all fit in one block and you want anything from the table Oracle will still have to read the whole block.