5 Replies Latest reply on Feb 14, 2020 6:10 PM by Mark D Powell

    DBMS_SPACE.CREATE_TABLE_COST - binary_double field

    Robeen
    Robeen Feb 13, 2020 8:55 AM

      Oracle DB 12.1.0.2

      RHEL 7.4

       

      Hello Team,

       

      can anyone pls advise what value should I use with BINARY_DOUBLE?

       

      Reference:

      https://docs.oracle.com/cd/B19306_01/appdev.102/b14258/d_space.htm#i1002261

       

      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 (6) , <<<<<<<<<<<<<<<<<<------------------- should i replace this with DATE,NULL ?

      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 , <<<<<<<----------------------------

      sys.create_table_cost_colinfo('VARCHAR2',255) ,

      sys.create_table_cost_colinfo('NUMBER',10) ,

      sys.create_table_cost_colinfo('TIMESTAMP (6), <<<<<<<<<<<<<<<<<<------------------- should i replace this with DATE,NULL ?

      sys.create_table_cost_colinfo('VARCHAR2',100 ) ,

      sys.create_table_cost_colinfo('VARCHAR2',100 )

      );

       

       

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

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

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

      END;

       

      Thanks,

       

      Joe

       

      /

      • 75 Views
      • Tags:
        • 1. Re: DBMS_SPACE.CREATE_TABLE_COST - binary_double field
          Mark D Powell
          Mark D Powell Feb 13, 2020 2:02 PM (in response to Robeen)

          Joe, what errors do you net when you run your code?  A binary double takes 8 bytes of storage.  A Timestamp(6) should be 11 bytes internal.

          - -

          HTH -- Mark D Powell --1

          • 2. Re: DBMS_SPACE.CREATE_TABLE_COST - binary_double field
            Robeen
            Robeen Feb 14, 2020 2:57 AM (in response to Mark D Powell)

            What size should I BINARY_DOUBLE,8 in the procedure?

             

            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', 6) ,

            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',6),

            sys.create_table_cost_colinfo('VARCHAR2',100 ) ,

            sys.create_table_cost_colinfo('VARCHAR2',100 )

            );

             

             

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

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

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

            END;

            /

            • 3. Re: DBMS_SPACE.CREATE_TABLE_COST - binary_double field
              Mark D Powell
              Mark D Powell Feb 14, 2020 2:57 PM (in response to Robeen)

              Joe, I have never tried to use binary_double in the routine but what happens if you just try it.  If it fails does integer take a length,  If yes, try substituting integer,8 since that would be the same size.  Sometimes when the manual are not specific enough and you cannot find an example you need to try a little trial and error.

              - -

              HTH -- Mark D Powell --

              • 4. Re: DBMS_SPACE.CREATE_TABLE_COST - binary_double field
                cormaco
                cormaco Feb 14, 2020 3:16 PM (in response to Robeen)

                can anyone pls advise what value should I use with BINARY_DOUBLE?

                You can use the DUMP function to determine how Oracle stores a value internally:

                select dump(1d) from dual

DUMP(1D)                          
----------------------------------
Typ=101 Len=8: 191,240,0,0,0,0,0,0

                So binary_double 1d is stored in 8 bytes and since binary_double is a fixed length datatype this is true for all values.

                1 person found this helpful
                • 5. Re: DBMS_SPACE.CREATE_TABLE_COST - binary_double field
                  Mark D Powell
                  Mark D Powell Feb 14, 2020 6:10 PM (in response to cormaco)

                  All, see also OP's other thread Field size