XMLWordPrintable

    Details

    • Type: Explanation
    • Status: Published
    • Affects Version/s: EXASolution 4.2.0, EXASolution 5.0, EXASOL 6.0.0, Exasol 6.1.0
    • Fix Version/s: None
    • Component/s: EXASolution
    • Labels:
      None
    • Explanation:
      Hide

      Database size

      Database size can be calculated on the basis of the following system dictionaries: EXA_ALL_OBJECT_SIZES or EXA_DBA_OBJECT_SIZES.

      The dictionary lists all tables and schemas. Please note, the size of an object of the type 'SCHEMA' will be calculated as a sum of the sizes of all the objects in this schema. For views, functions, etc., the size represents the corresponding text size.

      RAW_OBJECT_SIZE specifies the logical object size based on both data types and content. This size is comparable with the size of a CSV-file containing the same data. The value is calculated as a sum of sizes of stored data:

      • NULL –› 1 byte
      • fixed size type: depending on the type –› 1-80000 bytes
      • variable size type (varchar) –› number of bytes the value really uses.

      MEM_OBJECT_SIZE specifies the real size of the database object. The value is calculated as a sum of the following:

      • a sum of all stored values after compression
      • structural overhead, e.g. length information for a VARCHAR value
      • overhead for replication
        Replication: Table content will be held in RAM on each node for better performance. This applies only small tables (< 100.000 rows).

      Please note, that for a new table some data blocks will be reserved. Therefore, MEM_OBJECT_SIZE of empty or very small tables can be bigger than RAW_OBJECT_SIZE. This does not imply a bad data compression ratio.

      These system dictionaries provide you the total size of database objects in the cluster.

      Example: Objects in the schema 'EXAMPLES'

      
              OBJECT_NAME              OBJECT_TYPE RAW_OBJE MEM_OBJE
              ------------------------ ----------- -------- --------
              TESTADR                  TABLE         492836  2615072
              ITEMS                    TABLE           1120    23489
              PAYMENTS                 TABLE            208    13956
              PRODUCTS                 TABLE           1191   230942
              NEW_CITIES               TABLE            132    80336
              V_PRODUCT_ORDERING       VIEW             578      578
              V_CUSTOMERS              VIEW             431      431
              V_PRODUCT_RATING_MONTHLY VIEW             755      755
              CUSTOMER_MOVES           TABLE              0    13920
              MYMAX                    FUNCTION         290      290
              DAYS_BETWEEN             FUNCTION         402      402
              COUNTRIES                TABLE             64    74655
              NEW_CUSTOMERS            TABLE            117   217756
              CITIES                   TABLE            167    80336
              ORDERS                   TABLE            399    13996
              TESTADR_CLEANSED         TABLE          41770  1822724
              CUSTOMERS                TABLE            365   222986
              RETURNED_ITEMS           TABLE            178    92179
              V_ORDERS                 VIEW             648      648
              V_RETURNS                VIEW             713      713
              V_PAYMENTS               VIEW             472      472
              V_CUSTOMER_RAITING       VIEW             861      861
              V_TRANSACTIONS           VIEW             615      615
              LAG                      TABLE             45     9299
              MYFUNCS                  PACKAGE          208      208
      
      

      Data distribution

      You can check the data distribution of a table by using an iproc()-function:

      Example: Using of iproc()-function

      SELECT
      count(*), iproc()
      FROM mytable
      GROUP BY iproc()
      ORDER BY 2;
      
      
      
              COUNT(*)            IPROC
              ------------------- -----
                          5327099     0
                          5325780     1
                          5333799     2
                          5319445     3
               
      
      Show
      Database size Database size can be calculated on the basis of the following system dictionaries: EXA_ALL_OBJECT_SIZES or EXA_DBA_OBJECT_SIZES. The dictionary lists all tables and schemas. Please note, the size of an object of the type 'SCHEMA' will be calculated as a sum of the sizes of all the objects in this schema. For views, functions, etc., the size represents the corresponding text size. RAW_OBJECT_SIZE specifies the logical object size based on both data types and content. This size is comparable with the size of a CSV-file containing the same data. The value is calculated as a sum of sizes of stored data: NULL –› 1 byte fixed size type: depending on the type –› 1-80000 bytes variable size type (varchar) –› number of bytes the value really uses. MEM_OBJECT_SIZE specifies the real size of the database object. The value is calculated as a sum of the following: a sum of all stored values after compression structural overhead, e.g. length information for a VARCHAR value overhead for replication Replication: Table content will be held in RAM on each node for better performance. This applies only small tables (< 100.000 rows). Please note, that for a new table some data blocks will be reserved. Therefore, MEM_OBJECT_SIZE of empty or very small tables can be bigger than RAW_OBJECT_SIZE. This does not imply a bad data compression ratio. These system dictionaries provide you the total size of database objects in the cluster. Example: Objects in the schema 'EXAMPLES' OBJECT_NAME OBJECT_TYPE RAW_OBJE MEM_OBJE ------------------------ ----------- -------- -------- TESTADR TABLE 492836 2615072 ITEMS TABLE 1120 23489 PAYMENTS TABLE 208 13956 PRODUCTS TABLE 1191 230942 NEW_CITIES TABLE 132 80336 V_PRODUCT_ORDERING VIEW 578 578 V_CUSTOMERS VIEW 431 431 V_PRODUCT_RATING_MONTHLY VIEW 755 755 CUSTOMER_MOVES TABLE 0 13920 MYMAX FUNCTION 290 290 DAYS_BETWEEN FUNCTION 402 402 COUNTRIES TABLE 64 74655 NEW_CUSTOMERS TABLE 117 217756 CITIES TABLE 167 80336 ORDERS TABLE 399 13996 TESTADR_CLEANSED TABLE 41770 1822724 CUSTOMERS TABLE 365 222986 RETURNED_ITEMS TABLE 178 92179 V_ORDERS VIEW 648 648 V_RETURNS VIEW 713 713 V_PAYMENTS VIEW 472 472 V_CUSTOMER_RAITING VIEW 861 861 V_TRANSACTIONS VIEW 615 615 LAG TABLE 45 9299 MYFUNCS PACKAGE 208 208 Data distribution You can check the data distribution of a table by using an iproc()-function: Example: Using of iproc()-function SELECT count (*), iproc() FROM mytable GROUP BY iproc() ORDER BY 2; COUNT (*) IPROC ------------------- ----- 5327099 0 5325780 1 5333799 2 5319445 3
    • Category 1:
      Database Administration

      Attachments

        Issue Links

          Activity

            People

            • Assignee:
              CaptainEXA Captain EXASOL
              Reporter:
              CaptainEXA Captain EXASOL
            • Votes:
              0 Vote for this issue
              Watchers:
              0 Start watching this issue

              Dates

              • Created:
                Updated: