Different types of tables exists per data storage in SingleStore namely Columnstore Tables and Rowstore Tables, Reference Tablesm Temporary tables & Derived Tables

SingleStore Tables

This tutorials will explain what are the types of table available in SingleStore. This will also explain how users can create a copy of the table and check the size of the table.


Columnstore Tables
  • Data for these tables are stored on disks (Flash / SSD / disks).
  • As name suggest, data is stored in columnar format.
  • Table definition is stored in data dictionary.
  • Data and structure can be shared across multiple sessions and users.
  • Indexes can be created.
  • COMPRESS column and DEFAULT clause are supported.
  • Should be used where DELETE and Updates are rare.
  • Ideal if table is used for Analytical purpose.
  • Aggregation is faster in this table since data is stored in columnar format.
  • Columnstore table is the default table storage format since SingleStore Version 7.3.
  • Columnstore table can be created specifically by mentioning Key with "USING CLUSTERED COLUMNSTORE".
  • Columnstore temporary table is not supported by SingleStore.
  • Columnstore reference table is supported by SingleStore.
    Example: Creating a columnstore table
    
    CREATE Reference TABLE tutorial_db.employee_columnstore(
      emp_no INTEGER,
      emp_name VARCHAR(50),
      job_title VARCHAR(30),
      manager_id INTEGER,
      hire_date Date,
      salary DECIMAL(18,2),
      commission DECIMAL(18,2),
      dept_no INTEGER,
      KEY (dept_no) USING CLUSTERED COLUMNSTORE
    );
    


Rowstore Tables
  • Data for these tables are stored in Memory.
  • Table(not temporary tables) definition is stored in data dictionary.
  • Data and structure(not temporary tables) can be shared across multiple sessions and users.
  • Indexes can be created.
  • COMPRESS column and DEFAULT clause are supported.
  • Should be used where DELETE and Updates are frequent.
  • Ideal if table is used for transactional purpose.
  • Rowstore table was the default table storage format prior to SingleStore Version 7.3.
  • Rowstore table can be created specifically by mentioning "CREATE ROWSTORE TABLE".
  • Rowstore temporary table is supported by SingleStore.
  • Rowstore reference table is supported by SingleStore.
    Example: Creating a rowstore table
    
    CREATE ROWSTORE TABLE tutorial_db.employee_rowstore(
      emp_no INTEGER,
      emp_name VARCHAR(50),
      job_title VARCHAR(30),
      manager_id INTEGER,
      hire_date Date,
      salary DECIMAL(18,2),
      commission DECIMAL(18,2),
      dept_no INTEGER,
      KEY (dept_no),
      SHARD(emp_no)
      );
    


Reference Tables
  • Entire table data is copied to each node of SingleStore cluster.
  • Ideal for small master or dimension tables.
  • This table can be either defined as Rowstore or Columnstore.
  • Table definition is stored in data dictionary.
  • Data and structure(not temporary tables) can be shared across multiple sessions and users.
  • Indexes can be created.
  • COMPRESS column and DEFAULT clause are supported.
  • Ideal if table is used for transactional purpose.
  • Rowstore Reference table can be created specifically by mentioning "CREATE ROWSTORE REFERENCE TABLE".
  • Columnstore Reference table can be created specifically by mentioning Key with "USING CLUSTERED COLUMNSTORE".
  • Sharded reference tables are not supported in SingleStore since each node store complete table.
  • Primary key must be defined(manadatory) for Rowstore Reference table.
  • Primary key is not manadatory for Columnstore Reference table.

    Example: Rowstore Reference Table
    
    CREATE ROWSTORE REFERENCE TABLE tutorial_db.employee_rowstore_reference(
      emp_no INTEGER PRIMARY KEY,  
      emp_name VARCHAR(50),
      job_title VARCHAR(30),
      manager_id INTEGER,
      hire_date Date,
      salary DECIMAL(18,2),
      commission DECIMAL(18,2),
      dept_no INTEGER,
      KEY (dept_no)
      );
    

    Example: Columnstore Reference Table
    
    CREATE REFERENCE TABLE tutorial_db.employee_columnstore_reference(
      emp_no INTEGER,
      emp_name VARCHAR(50),
      job_title VARCHAR(30),
      manager_id INTEGER,
      hire_date Date,
      salary DECIMAL(18,2),
      commission DECIMAL(18,2),
      dept_no INTEGER,
      KEY (emp_no) USING CLUSTERED COLUMNSTORE
      );
    


Global Temporary Tables(GTT)
  • This is one kind of temporary tables.
  • Its a subtype of Rowstore table.
  • Columnstore global temporary tables are not supported by SingleStore.
  • Table definition is stored in data dictionary.
  • Structure can be shared across multiple users but data will remain private to the session(and its user).
  • Data is stored in the memory.
  • Data will be purged for that session once session ends.
  • Indexes can be created.
  • COMPRESS column and DEFAULT clause are supported.
  • Global temporary tables cannot be ALTERED after creation.
  • Global Temporary tables cannot persist data after Node restart/failover.
    Example: Creating a temporary table.
    
    CREATE ROWSTORE GLOBAL TEMPORARY TABLE updatetable2 (
      id int(11) PRIMARY KEY DEFAULT 0,
      name varchar(100) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL,
      indicator_flag char(1) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL,
      KEY(id) USING BTREE
      , SHARD KEY (id) 
    )
    COMPRESSION=SPARSE;
    


Temporary Tables
  • This is a kind of temporary tables.
  • Its a subtype of Rowstore table.
  • Columnstore temporary tables are not supported by SingleStore.
  • Table definition is not stored in data dictionary.
  • Structure & data is private to the session and its user.
  • Data is stored in the Memory as in the case of Rowstore table.
  • Table gets dropped once session ends.
  • Indexes can be created at the time of table creation.
  • COMPRESS column and DEFAULT clause are supported.
  • Partition Primary Index(PPI) is supported
  • Either Primary key or SHARD Key can be defined but not both.
  • Temporary tables cannot be ALTERED after creation.
  • Temporary tables cannot persist data after Node restart/failover.
  • All the temporary tables in current session can be checked using below command:
    
    SHOW TEMPORARY tables;
    
    Example: Creating a temporary table.
    
    CREATE ROWSTORE TEMPORARY TABLE updatetable (
      id int(11) DEFAULT 0,
      name varchar(100) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL,
      indicator_flag char(1) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL,
      KEY(id) USING BTREE
      , SHARD KEY (id) 
    )
    COMPRESSION=SPARSE;
    


Derived Tables
  • This is one kind of temporary table that is derived from one or more other tables as the result of a sub-query.
  • Derived tables are local to the query and exists only for the duration of the query.
  • The table is automatically discarded once the query is done.
  • Data is stored in the Memory.
  • Table definition is not stored in data dictionary.
    Example: Sub query named 'derived_table' is the example of derived table
    
    SELECT employee.emp_name, employee.dept_no, employee.Salary, derived_table.Avgsal 
    FROM (SELECT dept_no, AVG(salary) Avgsal
          FROM employee GROUP BY dept_no)
       AS derived_table,
    employee  
    WHERE derived_table.dept_no = employee.dept_no
    AND employee.salary > derived_table.Avgsal;
    


Copy Table

    Example: Creating a table using other table with no data.
    
    CREATE TABLE tutorial_db.employee_copy LIKE tutorial_db.employee;
    

    Example: Creating a table using other table with data.
    
    CREATE TABLE tutorial_db.employee_copy_with_data AS SELECT * FROM tutorial_db.employee;
    


Table Size

    Example: Finding table size
    
    SELECT database_name,table_name, SUM(memory_use)/(1024*1024*1024) "TableSize in GB"
    FROM information_schema.table_statistics 
    WHERE database_name = '[databasename]' 
    AND table_name      = '[tablename]' 
    GROUP BY database_name,table_name ;