TABLE

This chapter explains how to create, modify, and drop tables.

There are 3 types tables in Machbase.

  • A LOG table is to store log data in the disk.
  • A VOLATILE table is to store master data in the memory and all data will be deleted after restarting server.
  • A LOOKUP table is to store master data in the memory b Types of Tableut it's also stored in the disk permanently.

If not specified type in CREATE TABLE, it's a log table by default.

Table 1. Tables & Modifications

CREATE TABLE ALTER TABLE DROP TABLE
Types of Table ALTER TABLE SET TRUNCATE TABLE
Column Property ALTER TABLE ADD COLUMN
NOT NULL Constraint ALTER TABLE DROP COLUMN
Table Property ALTER TABLE RENAME COLUMN
Predefined System Columns ALTER TABLE MODIFY COLUMN
Concept of MINMAX Cache ALTER TABLE FLUSH
MINMAX Cache Column ALTER TABLE FLUSH INDEX
Primary Key ALTER TABLE CHECK FORGERY RESULT FILE

CREATE TABLE

Syntax:

CREATE
 [VOLATILE | LOOKUP] 
TABLE
 table_name 
    (column_list) [tablespace tablespace_name]
    [table_property_list];


    column_list: 
        column_name (column_type) 
        [column_property_list] 
        [primary_key] 
        [not_null_constraint]

        column_type : {short | int | long | float | double | datetime | varchar | ipv4 | ipv6 | text | binary}

        column_property_list: property( value_pair, value_pair, ... )
            value_pair : (PROPERTY_NAME = PROPERTY_VALUE)

        primary_key = (PRIMARY KEY)

        not_null_constraint = (NOT NULL) 

    table_property_list : value_pair, value_pair,...
        value_pair: CHECK_FORGERY= value_pair (Default:0)

Types of Table

Log tables will be created if there is no keyword in between CREATE TABLE. If volatile keywords are inserted in CREATE TABLE, a table will be created, named after its keyword.

Column Property

It specifies the property for a column.

Name of property Available table
PART_PAGE_COUNT LOG TABLE
PAGE_VALUE_COUNT LOG TABLE
MAX_CACHE_PART_COUNT LOG TABLE
MINMAX_CACHE_SIZE LOG TABLE
  • PART_PAGE_COUNT

This property shows the number of pages that a single partition can have. With "PART_PAGE_COUNT * PAGE_VALUE_COUNT", you can get the number of values that a single partition can have. Refer to the Setup Data Partition for detailed information.

  • PAGE_VALUE_COUNT

It shows the number of values that a single page can have. Refer to the Setup Data Partition for detailed information.

  • MAX_CACHE_PART_COUNT (Default: 0)

This property sets the cache area in order to improve the performance. When Machbase accesses to the partitions, it tries to find a structure that contains the meta information about the partition and determines how many partitions that this structure can have. The larger the number of partition, the better performance, but, memory usage increases as well. The minimum value is 1 and maximum is 65535.

  • MINMAX_CACHE_SIZE (Default: 0)

It specifies the size of cache memory for MINMAX of the column. It specifies 100MB for a _ARRIVAL_TIME that is 0th hidden column by default. However, other columns are set to 0. After creating a table, this value can be changed through "ALTER TABLE MODIFY" statement. If this value is 0, don't use it.

NOT NULL Constraint

The columns of c1 and c2 are created with and without not null constraint respectively.

If NULL is not permitted to column values, it sets to NOT NULL. Otherwise, it sets to default. If you place a NOT NULL constraint on a column and create LSM indexes, it is able to use MIN_MAX values, thus it increases search performance. After creating the tables, you can delete or add the predefined constraints by using ALTER TABLE MODIFY COLUMN command.

Syntax;

CREATE TABLE table_name 
(
column_name column_type(size) NOT NULL,
column_name column_type(size), 
...
);

Example;

CREATE TABLE notnull_table
(
    c1 INTEGER NOT NULL,
    c2 VARCHAR(200)
);

CREATE TABLE log_data
(
    c1 INTEGER NOT NULL,
    c2 VARCHAR(200)
) TABLESPACE tbs_data;

Table Property

It sets properties of a table.

Name of property Available table
CHECK_FORGERY LOG TABLE
CHECK\_FORGERY \(Default:0\)

It is only supported for column tables in the disk. It allows a user to check whether data is modified when there is malicious data manipulation, once data is appended. Refer to ALTER TABLE CHECK FORGERY RESULT FILE statement for detailed information.

Predefined System Columns

If you use CREATE TABLE statement to create a table, the system additionally creates two predefined system columns: _ARRIVAL_TIME and _RID column.
_ARRIVAL_TIME column is a datetime type and is used based on the system time when data are inserted by INSERT or AppendData statement. The value of _ARRIVAL_TIME can be used as a unique key for a newly generated record. The value of this column can be specified and inserted by using a machloader or INSERT statement on the condition that columns are in sequential order. When data are searched by using DURATION conditional clause, the criteria are the value of _ARRIVAL_TIME column. The system generates _RID column that has a unique value. The data type of this column is a 64-bit integer and the user cannot specify the value to the column and cannot generate the index either. It is generated automatically when the data is inserted. Also, records can be searched by the value of _RID column.

Example;

create volatile table PredefinedTBL(c1 integer);
Created successfully.
mach>desc PredefinedTBL;

----------------------------------------------------------------
NAME                          TYPE                LENGTH        
----------------------------------------------------------------
_ARRIVAL_TIME                 datetime            8               
c1                            integer             4               

mach>insert into PredefinedTBL values (1);
1 row(s) inserted.
mach>select _rid from PredefinedTBL;
_rid                 
-----------------------
0                    
[1] row(s) selected.

mach>select c1 from PredefinedTBL where _rid = 0;
c1          
--------------
1           
[1] row(s) selected.

Concept of MINMAX Cache

In general, when search for a specific value by using an index for On Disk DBMS, it is designed to access the disk area that has the index, and then access the final disk page that has the value. On the other hand, Machbase has the partition structure to maintain time series data in chronological order. In other words, the information of the index is divided into files based on time. Therefore, Machbase searches these partitioned index files in chronological order when it uses indexes.

Syntax:

CREATE TABLE table_name 
    (column_list) 
     column_list: column_name (column_type) 
     [column_property_list] 
          column_property_list: property( value_pair, value_pair, ... )
            value_pair : (PROPERTY_NAME = PROPERTY_VALUE);

Example:

​mach> CREATE TABLE predefined_table1 (id INTEGER, name VARCHAR(100) PROPERTY(MINMAX_CACHE_SIZE = 0));
Created successfully.

mach> CREATE TABLE predefined_table2 (id INTEGER PROPERTY(MINMAX_CACHE_SIZE = 10240), name VARCHAR(100) PROPERTY(MINMAX_CACHE_SIZE = 0));
Created successfully.

mach> CREATE TABLE predefinedd_table3 (id1 INTEGER PROPERTY(MINMAX_CACHE_SIZE = 10240), name VARCHAR(100) PROPERTY(MINMAX_CACHE_SIZE = 0), 
id2 LONG PROPERTY(MINMAX_CACHE_SIZE = 1024), id3 IPV4 PROPERTY(MINMAX_CACHE_SIZE = 1024), id4 SHORT);
Created successfully.

mach> CREATE TABLE predefinedd_table4 (id1 INTEGER PROPERTY(MINMAX_CACHE_SIZE=10240), name VARCHAR(100) PROPERTY(MINMAX_CACHE_SIZE=0), 
id2 LONG PROPERTY(MINMAX_CACHE_SIZE=10240),id3 IPV4 PROPERTY(MINMAX_CACHE_SIZE=0), id4 SHORT);
Created successfully.

If the range of the data to be searched is divided into 1000 partitions, it needs to open and look what is in the partitions 1000 times to complete the search. Machbase has designed the efficient columnar database, however, it is natural that the costs of I/O and the number of partitioned index are in direct proportion. Thus, it is better to use MINMAX cache in order to improve the performance

The MINMAX_CACHE is a structure to hold the information of index file of the partitions, and contains the minimum and maximum values of the column in memory. When search for a certain value, it skips the search of the partitions that the value is smaller or larger than the minimum or maximum value of the index. With MINMAX cache feature, Machbase provides high-performance data analysis.

As you can see above, to find the value 85, it only searches Partition-1 and Partition-5 that are in the range of minimum and maximum values from the 5 partitions, and the rest of them was skipped from the search.

MINMAX Cache Column

When creating a table, you can determine whether to use the MINMAX cache for a specified column. MINMAX cache is off by default. If this column is set to use, it will be operated when minmax_cache_size is set to any number but 0. If minmax_cache_size = 0, it doesn't work. When you use MINMAX cache, pay attention to the following points:

 1. MINMAX_CACHE is applied even when an index is not created explicitly in the column.
 2. MINMAX CACHE_SIZE is set to zero in all columns by default. Therefore, if necessary, the memory has to be reset to the proper size by using ALTER TABLE statement.
 3. The hidden column "_arrival_time" is set to 100MB by default and automatically uses the memory of MINMAX CACHE.
 4. For VARCHAR type, MINMAX_CACHE cannot be used. Therefore, if you set MINMAX cache for VARCHAR type explicitly, an error occurs.
 5. When a table is created, it uses additional maximum memory size of MINMAX_CACHE_SIZE which was set in the property. When the number of partitions is increased, the amount of memory will be gradually increased to the maximum memory of MINMAX_CACHE_SIZE.
 6. If the specified table does not have any record, MINMAX_CACHE memory will not be allocated at all.

Primary Key

Primary key can be used as a constraint that can be assigned to volatile table columns in order to prevent duplicating the value of the column. Volatile table is not required to have primary keys all the time, but without the primary key, it cannot use "INSERT ON DUPLICATE KEY UPDATE" statement. When the primary key is allocated, RED-BLACK tree index which is corresponding to the primary key is created.

Create a table for the created tablespace. You can also specify the tablespace created when creating the table.
The data files of the tables created in this tablespace are stored in a round-robin manner on a disk in a tablespace so that data can be written to the disk by performing maximum I / O.
When data is loaded into a table, the data is distributed to the disks specified in the tablespace to which the table belongs.
It is only available if you have been issued a full license. You can only use one disk if you do not have any license.

Syntax:

CREATE TABLE table\_name \(column\_list\) TABLESPACE tablespace\_name;

Example:

CREATE TABLE sample\_table \(c1 INTEGER, c2 INTEGER\) TABLESPACE tbs\_data;

ALTER TABLE

It is used for changing the schema of the specified table.

ALTER TABLE SET

It changes the property of a table. Currently no property can be changed dynamically.

ALTER TABLE ADD COLUMN

It adds specific columns to a table in real-time. Add name and type of the column and default values can be set by DEFAULT statement.

Syntax;

ALTER TABLE table\_name ADD COLUMN \(column\_name column\_type DEFAULT value\);

Example;

mach> CREATE TABLE sample_table (sid int);
Created successfully.
mach> ALTER TABLE sample_table ADD COLUMN (number FLOAT);
Altered successfully.
mach> ALTER TABLE sample_table ADD COLUMN (value DOUBLE DEFAULT 5);
Altered successfully.
mach> ALTER TABLE sample_table ADD COLUMN (sip ipv4  DEFAULT '192.168.0.1');
Altered successfully.
mach> ALTER TABLE sample_table ADD COLUMN (name varchar(4) DEFAULT 'hello');
Altered successfully.
mach>

ALTER TABLE DROP COLUMN

It drops a specific column of the table in real-time.

Syntax:

ALTER TABLE table\_name DROP COLUMN \(column\_name\);

Example;

mach> ALTER TABLE sample_table DROP COLUMN (name);
Altered successfully.

mach> ALTER TABLE sample_table DROP COLUMN (value);
Altered successfully.

ALTER TABLE RENAME COLUMN

It changes the name of a specific column in the table.

Syntax:

ALTER TABLE table\_name RENAME COLUMN old\_column\_name TO new\_column\_name

Example:

mach> ALTER TABLE sample\_table RENAME COLUMN sip TO dip  
Altered successfully.

ALTER TABLE MODIFY COLUMN

It changes the property of a particular column in the table. Currently, it is possible to modify column length of varchar type, property of MINMAX CACHE and NOT NULL constraints.

Size of VARCHAR

​It supports only changing the column length of the VARCHAR type. In order to preserve the existing data, the length can only be increased, not reduced.

Syntax;

ALTER TABLE table\_name MODIFY COLUMN \(column\_name VARCHAR\(new\_size\)\);

Example;

mach> CREATE TABLE sample_table(id1 integer, name VARCHAR(5), id3 double, id4 float);
Created successfully.

mach> ALTER TABLE sample_table MODIFY COLUMN (id1 VARCHAR(10));
[ERR-02090 : Error in altering table. Only VARCHAR type can be modified.]

mach> ALTER TABLE sample_table MODIFY COLUMN (name VARCHAR(3));
[ERR-02091 : Error in altering table. VARCHAR length should be greater than previous value length]

mach> ALTER TABLE sample_table MODIFY COLUMN (name VARCHAR(32768));
[ERR-02077 : Invalid VARCHAR size. VARCHAR type size cannot be more than 32768.]

mach> ALTER TABLE sample_table MODIFY COLUMN (name VARCHAR(128));
Altered successfully.

MINMAX CACHE_SIZE

It changes the MINMAX CACHE_SIZE for the specified column.

Syntax :

ALTER TABLE table\_name MODIFY COLUMN column\_name SET MINMAX\_CACHE\_SIZE=value;

Example:

mach> CREATE TABLE sample_table (id integer, name varchar(100));
Created successfully.

mach> ALTER TABLE sample_table MODIFY COLUMN name SET MINMAX_CACHE_SIZE=0;
[ERR-02139 : MINMAX CACHE is not allowed for VARCHAR column(NAME).]

mach> ALTER TABLE sample_table MODIFY COLUMN id SET MINMAX_CACHE_SIZE=104857600;
Altered successfully.

NOT NULL

It adds NOT NULL constraints on a column. When NOT NULL constraints are added, it fails to operate DDL operations on a column with NULL values. When NOT NULL constraints are added to a column with LSM index, it enables fast search by using MIN_MAX cache within an index. If you want to permit NULL value on a column, you can use MODIFY COLUMN NULL command.

Syntax;

ALTER TABLE table\_name MODIFY COLUMN column\_name NOT NULL;

Example:

    mach>ALTER TABLE modify\_clmn MODIFY COLUMN name NOT NULL;  
    Altered successfully.

NULL

It deactivate NOT NULL constraints. Thus, no search performance can be expected by using MIN_MAX cache. However, it is now able to add NULL value.

Syntax:

ALTER TABLE table\_name MODIFY COLUMN column\_name NULL;

Example:

mach>ALTER TABLE modify\_clmn MODIFY COLUMN name NULL;  
Altered successfully.

ALTER TABLE FLUSH

It is used to instruct the current session to wait while it updates the changed data of the specified table into a data file completely.

Syntax:

ALTER TABLE table\_name FLUSH;

Example:

​mach>ALTER TABLE sample\_table FLUSH;  
Altered successfully.

ALTER TABLE FLUSH INDEX

It is in a waiting status when the index data of the specified table are fully reflected in the index file.

1) It should be in a waiting status until index data of c1_idx of bulktable reflected into the index file.
2) It should be in a waiting status until all the index data of bulktable reflected into the index file.

Syntax:

ALTER TABLE table\_name FLUSH INDEX index\_name;  
ALTER TABLE table\_name FLUSH INDEX ALL;

Example:

1)mach> ALTER TABLE sample\_table FLUSH INDEX c1\_idx;  
Altered successfully.  
2)mach> ALTER TABLE sample\_table FLUSH INDEX ALL;  
Altered successfully.

ALTER TABLE CHECK FORGERY RESULT FILE

It checks whether data are forged once the data were appended to a specified table. The forgery check is supported only for the disk column table. In addition, the disk column table should be created by setting the property of FORGERY_CHECK to 1. When forgery is detected, a "result_filename" will be created under $MACHBASE_HOME/trc directory and modified information of the table will be recorded in the file.

Syntax:

ALTER TABLE table\_name CHECK FORGERY RESULTFILE 'result\_filename';

Example:

mach>ALTER TABLE forgery\_table CHECK FORGERY RESULTFILE 'forgery\_table\_check';  
Altered successfully.

DROP TABLE

It drops the specified table, indexTBL. If another session is searching for the table, an error occurs and fails to drop the table.

Syntax:

DROP TABLE table_name;

Example;

DROP TABLE sample_table;  
Dropped successfully.

TRUNCATE TABLE

It is used to delete all the records from the specified table. However, an error occurs if another session is searching the table.

Syntax:

TRUNCATE TABLE table\_name;

Example:

mach>TRUNCATE TABLE sample\_table;  
Truncated successfully.

It is in a waiting status when the index data of the specified table are fully reflected in the index file.

1) It should be in a waiting status until index data of c1_idx of bulktable reflected into the index file.
2) It should be in a waiting status until all the index data of bulktable reflected into the index file.

results matching ""

    No results matching ""