8 Replies Latest reply on Feb 14, 2020 3:05 PM by Robeen

    Field size

    Robeen

      Oracle DB 12.2.0

      RHEL 7.4

       

      Hello Team,

       

      I have a few queries regarding datatypes used for calculating field size

       

      https://docs.oracle.com/database/121/SQLQR/sqlqr06002.htm#SQLQR959

       

      As per doc above,

       

      1. NUMBER(10) - does this mean the field stores NUMBER of size 10 bytes?

      2. BINARY_DOUBLE - what size by default is binary_double? Is it 8 bytes?

      3. TIMESTAMP(6) - does this mean TIMESTAMP of size 6 bytes? As per doc, it says frational seconds precision.  Or does this  field takes by default 11 bytes irrespective of the fractional seconds precision - TIMESTAMP(6), TIMESTAMP(10) ...?

       

      Can anyone pls validate code below?

      set serveroutput on

      DECLARE

      ub NUMBER;

      ab NUMBER;

      cl sys.create_table_cost_columns;

      BEGIN

      cl := sys.create_table_cost_columns(

      sys.create_table_cost_colinfo('VARCHAR2',11 ) ,

      sys.create_table_cost_colinfo('VARCHAR2',2 ) ,

      sys.create_table_cost_colinfo('VARCHAR2',20 ) ,

      sys.create_table_cost_colinfo('NUMBER',20) ,

      sys.create_table_cost_colinfo('NUMBER',20) ,

      sys.create_table_cost_colinfo('TIMESTAMP', 11) ,

      sys.create_table_cost_colinfo('VARCHAR2',30 ),

      sys.create_table_cost_colinfo('VARCHAR2',20 ) ,

      sys.create_table_cost_colinfo('VARCHAR2',20 ) ,

      sys.create_table_cost_colinfo('VARCHAR2',10 ) ,

      sys.create_table_cost_colinfo('BINARY_DOUBLE' ,8),

      sys.create_table_cost_colinfo('VARCHAR2',255) ,

      sys.create_table_cost_colinfo('NUMBER',10) ,

      sys.create_table_cost_colinfo('TIMESTAMP',11),

      sys.create_table_cost_colinfo('VARCHAR2',100 ) ,

      sys.create_table_cost_colinfo('VARCHAR2',100 )

      );

       

       

      DBMS_SPACE.CREATE_TABLE_COST('TESTTBS',cl,16709155 ,10,ub,ab);

      DBMS_OUTPUT.PUT_LINE('Used s = ' || TO_CHAR(ub));

      DBMS_OUTPUT.PUT_LINE('Allocated s = ' || TO_CHAR(ab));

      END;

      /

       

      Regards,

       

      Joe

        • 1. Re: Field size
          mathguy

          I am not familiar with the procedure(s) in your PL/SQL code.


          With that said:

           

          NUMBER(10) is not ten bytes. Rather, it can hold any whole number (positive or negative) up to ten digits in base 10. How much space each value will take is variable; Oracle stores numbers in a quasi-"base 100" format. For example, the number 1000000 (one million) is stored in 2 bytes. 9999999999 (ten digits) is stored in 6 bytes, and -9999999999 in 7 bytes.

           

          BINARY_FLOAT is always 32 bits (4 bytes) and BINARY_DOUBLE is always 64 bits (8 bytes).

           

          With that said, Oracle also stores meta-data - a "length byte"; add 1 to the byte counts above.

           

          Dates and timestamps are also stored in a proprietary internal format; TIMESTAMP(6) means that the fractional second part is at most six decimal places. The number of bytes it occupies depends on the value: it's at most 11 bytes, but it may be as few as 7 bytes (when the fractional second part is .000000).

          1 person found this helpful
          • 2. Re: Field size
            Gaz in Oz

            Further to TIMESTAMP, the maximum precision definition allowed is 9 so declaring a column as TIMESTAMP(10) will fail.

            SQL> create table ts (ts10 timestamp(10));
            create table ts (ts10 timestamp(10))
                                            *
            ERROR at line 1:
            ORA-30088: datetime/interval precision is out of range
            
            SQL>
            

            ...and the calls you are making to the zining procedures etc, need to be prefixed with DBMS_SPACE...

            Take a look here for a usage example:

            https://asktom.oracle.com/pls/asktom/f?p=100:11:::::p11_question_id:231414051079

            • 3. Re: Field size
              BrunoVroman

              Hello Joe,

               

              you point to a doc explaining the various data types but not the way they are stored.  If you search more you will find interesting information (for example: it is very interesting to know how Oracle stores a "DATE" on seven bytes, it is also very interesting to know how Oracle stores a NUMBER, …). More than knowing how one column is stored it is important to know that Oracle stores only "what has to be stored": if you have a table defined with 60 columns ( col1, col2, …, col60 ) and that for a given row you have values for col1, col2, col3 and col 15 only (all other columns being NULL), note that at the end of the col15 value Oracle has a "end of record" marker so there is no need to store col16, col17, …, col60 even in a short format.

               

              Then you have to get informed about the way rows are stored in Oracle blocks... (search the documentation "Database Concepts", "12 Logical Storage Structures", "Data Block Format")… (and I don't mention the possibility of compression)

               

              Don't forget that a table has (usually) indexes, they have to be taken into account (and store their entries in a different way).

               

              All this is really good for the "general knowledge" but I doubt you need to compute precisely the size of a table based on this; your "naïve" approach is far from reality, refining it enough to be realistic is a really complex task and you will have more success by creating a sample table, populating it with some representative data (especially: average length of varchar2 columns) and from this derive an idea of your target...

               

              What is your goal?

              Note that usually people are quite bad at predicting the number of rows that a table will contain in 2 years... Illustration with two cases that I have seen:

              - a team asked for a database, requesting 2TB of size... 3 years later they use hardly a few GB, no idea how they had "found" the 2TB

              - another team told me that a DB would keep a stable size after a few months of initial growth. More than a year later the DB continues to grow by about 20GB per week, especially 1 table and its indexes... I had a talk with the application guys who told me "indeed, we changed our mind, we had decided to purge the old data and keep only a few months but now we prefer to keep all the history"

              With such guys I would have been fool to spend time computing the size of 1 row of 1 table to try to derive I don't know what ;-)

               

              Best regards,

               

              Bruno Vroman.

              • 4. Re: Field size
                Robeen

                You mean the procedure is not good? I tried it on another table and the estimation was near to dba segments.

                https://docs.oracle.com/database/121/ARPLS/d_space.htm

                • 5. Re: Field size
                  BrunoVroman

                  Hello,

                   

                  I don't know sys.create_table_cost_columns / sys.create_table_cost_colinfo (after a quick look: I tend to think that you have to provide the average size, not the "defined size" for example if a column "address" is defined as VARCHAR2(255 CHAR), its actually used size is probably closer to 50 than to 255...)

                   

                  I say that ideas like "NUMBER(10) occupies 10 bytes or TIMESTAMP(6) occupies 6 bytes is far from reality, and that you cannot tell the size of a NUMBER or VARCHAR2 "in general" as the size depends of the data actually stored (it takes less space to store 123 than 456.78901234567, or to store 'Joe' than 'Bruno'...) So you have to perform this work (determining reasonable average size of each column) before trying to size a table.

                   

                  So my suggestion if your need is to estimate the size of a table supposed to contain for example 100 million rows is to create such a table (and its indexes) with representative data and for example 200K rows, and to extrapolate, with the grain of salt given by my real-life examples that you might end up with completely wrong figures even if the size of 1 row is estimated carefully if for example the table ends up with 10 million rows only, or 10 billion...

                   

                  Anyway, beside this it is good to get interested in the details of Oracle hence my suggestion to have a look at the documentation about for example the structure of an Oracle block, the way a NUMBER or a DATE are stored, and so on.

                   

                  Best regards,

                   

                  Bruno

                  1 person found this helpful
                  • 6. Re: Field size
                    Robeen

                    I executed command below after loading data into the table. Why is the result null? Should I gather statistics?

                     

                    select column_name, data_type, avg_col_len from dba_tab_cols where table_name ='ARCHICOM_2020' order by column_id;

                     

                     

                    Regards,

                     

                    Joe

                    • 7. Re: Field size
                      mathguy

                      Robeen wrote:

                       

                      I executed command below after loading data into the table. Why is the result null? Should I gather statistics?

                       

                       

                       

                      So, you already thought of what you may have missed. Why don't you go ahead and try for yourself? Gather table statistics, then run your query again. What happens when you try?

                       

                      The idea is to ask questions here only when you can't answer them yourself. Here there's no reason why you can't.

                      • 8. Re: Field size
                        Robeen

                        Now it is good