This chapter discusses the management of different types of schema objects in DBMaker, including: tables, views, synonyms, indexes, serial numbers and domains. The role of data integrity will be discussed as well.

In addition to the above fields, this chapter includes topics on browsing the system catalogs to get information about schema objects, and how to estimate the disk storage sizes required for tables and indexes.

7.1 Managing Tables

Tables are the logical unit of storage used by DBMaker to store data. A table consists of several columns and rows. A column can also be called a field or attribute, and a row can also be called a record or tuple.

In DBMaker, each table is identified by a unique owner name and table name. For example, two users called Jeff and Kevin can each create a table named friend, and the table names Jeff.friend and Kevin.friend denote two different tables.

Creating Tables

Every table is defined with a table name and a set of columns. The number of columns in a table can range from 1 to 252. Each column contains:

a column name and a data type (or a domain, which is described in a later section)

a length (the length might be predetermined by the data type, such as INTEGER), a precision and scale (for columns of the DECIMAL data type only) or a starting number (for columns of SERIAL data type only).

DBMaker supports a large number of data types which can be used to define columns. There are numerical types (SMALLINT, INTEGER, LONG, FLOAT, DOUBLE, DECIMAL and SERIAL), binary types (BINARY, VARBINARY, CHAR, and VARCHAR), BLOB types (LONG VARCHAR, LONG VARBINARY and FILE), and time types (DATE, TIME and TIMESTAMP). See the SQL Reference Manual for more information about data types.

To create a table, you must provide the table name, column definitions and the name of the associated tablespace. If a table is not explicitly associated with a tablespace, the table is placed in the system tablespace by default.

The following SQL command creates a table employee in tablespace ts1.

dmSQL> CREATE TABLE employee (nation CHAR(20),
                                  ID INTEGER,
                                name CHAR(30),
                            joinDate DATE,
                              height FLOAT,
                              degree VARCHAR(200),
                             picture LONG VARCHAR) IN ts1;

DBMaker provides a lot of useful features that can be applied when creating tables, such as:

defining a default value for a column

specifying that a column is not nullable

specifying the LOCK MODE, FILLFACTOR, or NOCACHE options to improve database efficiency

specifying the table as temporary.

The following sections give you more detailed descriptions about these features.

Default Values for Columns

A column in a table can be assigned a default value so that when a new row is inserted and a value for the column is omitted, a default value is automatically supplied.

You can optionally specify default values for each column of a table. If a default value is not explicitly defined for a column, the default value for the column is implicitly set to NULL.

Legal default values can be constants or built-in functions. For more information about built-in functions, refer to the SQL Reference Manual.

For example, you can use the following SQL command to specify the default value of the column nation in the table employee is a constant-`R.O.C.' and the default value of the column joinDate is the value of the built-in function CURDATE().

dmSQL> CREATE TABLE employee (nation CHAR(20) DEFAULT `R.O.C',
                                  ID INTEGER,
                                name CHAR(30),
                            joinDate DATE DEFAULT CURDATE(),
                              height FLOAT,
                              degree VARCHAR(200),
                             picture LONG VARCHAR) IN ts1;

Not Null

You can optionally specify rules for columns or tables. These rules are called integrity constraints. One example is the NOT NULL integrity constraint defined on a column of a table. It enforces the rule that the column can not contain a NULL value. For example, the table employee might always need an ID and a name for a new employee, thus the SQL command to create table employee might be refined as follows:

dmSQL> CREATE TABLE employee (nation CHAR(20) DEFAULT `R.O.C',
                                  ID INTEGER NOT NULL,
                                name CHAR(30) NOT NULL,
                            joinDate DATE DEFAULT CURDATE(),
                              height FLOAT,
                              degree VARCHAR(200)) IN ts1;

See Section 7.5 for more information about additional integrity constraints.

Lock Mode

The lock mode of a table identifies the type of lock that DBMaker automatically places on objects when accessing the database.

DBMaker supports three levels of lock mode: TABLE, PAGE, and ROW. The PAGE lock mode is used by default if the lock mode is not explicitly specified when a table is created.

If the lock mode is set to a higher level (such as TABLE), the level of concurrency on database accesses will be lower, but the required lock resources (shared memory) will also be smaller. If the lock mode is set to a lower level (such as ROW), the level of concurrency on database accesses will be higher, but the required lock resources (shared memory) will be larger. In other words, if you insert or modify rows in a table with the lock mode set to TABLE, no one else can access this table. The reason for this is that an exclusive lock is taken on the entire table. For more information about lock modes, see the chapter "Concurrency Control".

The following SQL command shows you how to specify the lock mode on a table.

dmSQL> CREATE TABLE employee (nation CHAR(20) DEFAULT `R.O.C',
                                  ID INTEGER NOT NULL,
                                name CHAR(30) NOT NULL,
                            joinDate DATE DEFAULT CURDATE(),
                              height FLOAT,
                              degree VARCHAR(200)) IN ts1
                    LOCK MODE ROW;

FILLFACTOR

The FILLFACTOR feature is used to optimize the utilization of space on data pages by reserving space on a data page to allow for the expansion of individual records that already exist on a page. It does this by specifying the percentage of a page that can be filled before it stops allowing new records to be inserted. By allowing space for a record to grow on the same data page, the records can be accessed more efficiently by avoiding the need to retrieve information for one record from multiple pages. The following SQL example specify the FILLFACTOR of table employee to be 80%.

dmSQL> CREATE TABLE employee (nation CHAR(20) DEFAULT `R.O.C',
                                  ID INTEGER NOT NULL,
                                name CHAR(30) NOT NULL,
                            joinDate DATE DEFAULT CURDATE(),
                              height FLOAT,
                              degree VARCHAR(200)) IN ts1
                    LOCK MODE ROW
                    FILLFACTOR 80;

In this case, no new row can be inserted into the data page when the used space is larger than 80% of the page space.

The legal values for the FILLFACTOR can be from 50 to 100, and the default value is 100.

NOCACHE

The NOCACHE feature is useful when accessing large tables by table scan. Although DBMaker uses page buffers in shared memory to cache retrieved data and avoid frequent disk I/O when accessing a database, table scans on large tables can still cause frequent disk I/O activity. This happens during a table scan on a table with a larger number of data pages than the number of page buffers, which causes all page buffers to be exhausted.

Once the NOCACHE option is specified when creating a table, DBMaker only uses one page buffer to cache the data retrieved from a table during a table scan. This prevents the page buffers from being exhausted by only one large table scan.

The following SQL command gives an example of how to specify the NOCACHE option:

dmSQL> CREATE TABLE employee (nation CHAR(20) DEFAULT `R.O.C',
                                  ID INTEGER NOT NULL,
                                name CHAR(30) NOT NULL,
                            joinDate DATE DEFAULT CURDATE(),
                              height FLOAT,
                              degree VARCHAR(200)) IN ts1
                    LOCK MODE ROW
                    FILLFACTOR 80
                    NOCACHE;

Temporary Tables

You can create a temporary table for storing data on a temporary basis. Temporary tables only exist during a single session, and DBMaker will automatically drop the temporary table when you disconnect from the database. Temporary tables support fast data operations and can be used only by the creator. If you execute an operation improperly on a temporary table, the data in this table may be incorrect.

The following SQL command gives an example of creating a temporary table named student:

dmSQL> CREATE TEMPORARY TABLE student (name CHAR(25) NOT NULL,
                                       birthday DATE,
                                       score INTEGER);

Altering Tables

After a table is created in DBMaker, you can alter the table for the following purposes:

Adding Columns

You can add a column to a table whether it is empty or not. Adding a new column to an empty table is the same as expanding the table schema and placing the new column in the last position. When you add a new column to a non-empty table, DBMaker not only expands the table schema but also fills all rows of the new column with NULL values. Consequently, you cannot add a column with the NOT NULL integrity constraint to a non-empty table.

The following SQL command gives an example of adding a column named photo to the table employee.

dmSQL> ALTER TABLE employee ADD photo LONG VARCHAR;

Changing the Lock Mode

If you would like to get higher level of concurrency on simultaneous multiple connections to a database, you can set the lock mode to a lower level (such as a ROW lock). However, doing this causes DBMaker to expend more resources handling the lower level of lock mode. Thus deciding which lock mode to use on a table always involves a trade-off.

The following SQL command gives an example of changing the lock mode of the table employee.

dmSQL> ALTER TABLE employee SET LOCK MODE ROW;

Changing the FILLFACTOR Value

You can change the FILLFACTOR value of a table using the following SQL command:

dmSQL> ALTER TABLE employee SET FILLFACTOR 90;

Turning On/Off the NOCACHE Option

You can turn the NOCACHE option for a table on or off at any time. This can be done by using an SQL command like the example shown below:

dmSQL> ALTER TABLE employee SET NOCACHE OFF;

This example turns off the NOCACHE option for the table employee.

Locking Tables

Although DBMaker automatically handles the lock mechanism whenever you access DBMaker databases, you can manually lock a table for later SELECT or UPDATE statements. In general, you need to lock tables when you wish to prevent updates by other people when you are viewing or modifying them.

DBMaker supports some options for locking tables, such as shared locks for viewing data or exclusive locks for modifying data, and the WAIT or NO WAIT option which is used when obtaining a lock. For more information about these features, see the SQL Reference Manual.

The following SQL command locks the table employee for later selections and doesn't wait if it can not get the table lock right away.

dmSQL> LOCK TABLE employee IN SHARE MODE NO WAIT;

Dropping Tables

You can drop a table when the table is not being used any more. When a table is dropped, all data and indexes for this table are dropped, and pages allocated by the dropped table are also released.

The following SQL command gives an example of how to drop the table employee.

dmSQL> DROP TABLE employee;

7.2 Managing Views

DBMaker provides the ability to define a virtual table, called a view, which is based on existing tables and is stored in the database as a definition and a user defined view name. The view definition is stored persistently in the database, but the actual data that you will see in the view is not physically stored anywhere. Rather, the data is stored in the base tables from which the view's rows are derived. A view is defined by a query which references one or more tables (or other views).

Views are a very helpful mechanism for using a database. For example, you can define complex queries once and use them repeatedly without having to re-invent them over and over. Furthermore, views can be used to enhance the security of your database by restricting access to a predetermined set of rows and/or columns of a table.

Since views are derived from querying tables, you can not determine the rows of the tables to update. Due to this limitation views can only be queried. Users can not update, insert into, or delete from views.

The following sections introduce how to create views and drop views.

Creating Views

Each view is defined by a name together with a query that references tables or other views. The query that defines a view cannot contain the ORDER BY clause or UNION operator.

You can specify a list of column names for a view. If you do not specify column names, the view inherits the column names of the underlying tables.

For example, if you want users to see only two columns of the table employee, you can create a view with the SQL command shown below. Users can then view only two columns, name and ID, of the table employee through the view empView.

dmSQL> CREATE VIEW empView (empName, empId) AS
                    SELECT name, ID FROM employee;

Dropping Views

You can drop a view when it is no longer required. When you drop a view, only the definition stored in the system catalog is removed. There is no effect on the base tables that the view was derived from.

The following SQL command is an example of how to drop a view:

dmSQL> DROP VIEW empView;

7.3 Managing Synonyms

A synonym is an alias for any table or view. Since a synonym is simply an alias, it requires no storage other than its definition in the system catalog.

Synonyms are useful for simplifying a fully qualified table or view name. DBMaker normally identifies tables and views with fully qualified names that are composites of the owner and object names. By using a synonym anyone can access a table or view through the corresponding synonym without having to make use of the fully qualified name. Because a synonym has no owner name, all synonyms in the database must be unique so DBMaker can identify them.

Creating Synonyms

You can create a synonym with the following SQL command:

dmSQL> CREATE SYNONYM employee FOR employee;

Assume that the owner of the table employee is TOM. This command creates an alias named employee for the table TOM.employee. All database users can directly reference the table TOM.employee through the synonym employee.

Dropping Synonyms

You can drop a synonym that is no longer required. When you drop a synonym, only its definition is removed from the system catalog.

The following SQL command drops the synonym employee:

dmSQL> DROP SYNONYM employee;

7.4 Managing Indexes

An index provides support for fast random access to a row. You can build indexes on a table to speed up searching. For example, when you execute the query SELECT NAME FROM EMPLOYEE WHERE ID=306004, it is possible to retrieve the data in a much shorter time if there is an index created on the ID column.

An index can be composed of more than one column, up to a maximum of 16 columns. Although a table can have up to 252 columns, only the first 127 columns can be used in an index.

An index can be unique or non-unique. In a unique index, no more than one row can have the same key value, with the exception that any number of rows may have NULL values. If you create a unique index on a non-empty table, DBMaker will check whether all existing keys are distinct or not. If there are duplicate keys, DBMaker will return an error message. After creating a unique index on a table, you can insert a row in this table and DBMaker will ensure that there is no existing row that already has the same key as the new row.

When creating an index, you can specify the sort order of each index column as ascending or descending. For example, suppose there are five keys in a table with the values 1, 3, 9, 2, and 6. In ascending order the sequence of keys in the index is 1, 2, 3, 6, and 9, and in descending order the sequence of keys in the index is 9, 6, 3, 2, and 1.

When you implement a query, the index order will occasionally affect the order of the data output. For example, when you execute a query SELECT NAME, AGE FROM FRIEND_TABLE WHERE AGE > 20 by using an index with a descending order at the column age, the output appear as below:

Name	Age
Jeff	49
Kevin	40
Jerry	38
Hughes	30
Cathy	22

As for tables, when you create an index you can specify the fill factor. The fill factor denotes how dense the keys will be in the index pages. The legal fill factor values are in the range from 1% to 100%, and the default is 100%. If you often update data after creating the index, you can set a loose fill factor in the index, for example 60%. If you never update the data in this table, you can leave the fill factor at the default value of 100%.

Before creating indexes on a table, it is recommended that you load all your data first, especially if you have a large amount of data for that table. If you create an index before loading the data into a table, the indexes will be updated each time you load a new row. As you can see, it is far more efficient to create an index after loading a large amount of data than to create an index before loading the data.

Creating Indexes

To create an index on a table, you must specify the index name and index columns. You can specify the sort order of each column as ascending or descending. The default sort order is ascending.

For example, the following SQL command creates an index idx1 on the column ID of table salary in descending order.

dmSQL> CREATE INDEX idx1 ON salary (ID DESC);

Also, you have to explicitly specify if you want to create a unique index. Otherwise DBMaker implicitly creates non-unique indexes.

The following example shows you how to create a unique index idx2 on the column ID of the table salary:

dmSQL> CREATE UNIQUE INDEX idx2 ON salary (ID);

The next example shows you how to create an index with a specified fill factor:

dmSQL> CREATE INDEX idx3 ON salary(name, age DESC) FILLFACTOR 60;

Dropping Indexes

You can drop indexes by using the DROP INDEX statement. In general, you might need to drop an index if it becomes fragmented, which reduces its efficiency. Rebuilding the index will create a denser, unfragmented index.

If the index is a primary key (primary keys will be discussed later in this chapter) and is referred to by other tables, it cannot be dropped.

The following SQL command drops the index idx1 from the table salary.

dmSQL> DROP INDEX idx1 FROM salary;

7.5 Managing Data Integrity

The integrity of data can be ensured by applying constraints, or rules, to ensure the data meets certain criteria. For example, verifying that an input value for a particular data item is within the correct range of values (e.g. a new employee's age must be between 16 and 90) is an example of data integrity.

In general, the different types of data integrity applicable to tables include those described in the following subsections.

Not Null

By default, all columns in a table allow NULL values. NOT NULL indicates that NULL values are not permissible in a column defined with the NOT NULL keyword. (NOT NULL was discussed earlier in the section on managing tables.)

Unique Indexes

Unique indexes, mentioned in the section on managing indexes, can be used to ensure no two rows of a table have duplicate values (except NULL values) in a specified column or set of columns.

Check Constraints

A CHECK constraint on a column or set of columns requires that a specified condition be true for every row of the table. If an INSERT or UPDATE statement is issued and the condition of the CHECK constraint is evaluated as false, the statement will fail.

In general, a CHECK constraint can be defined on a column (column constraint) or set of columns (table constraint).

Column Constraints

A column constraint is defined on a specific column and does not affect the other columns of the same table. When inserting a new row or updating an existing row, each column constraint is evaluated.

Table Constraints

A table constraint is defined on a set of columns. When inserting a new row or updating an existing row, the table constraint is evaluated after all column constraints are evaluated as true. Only after the table constraint is also evaluated as true will the statement be processed. The following SQL command gives an example of creating a table with column and table constraints:

dmSQL> CREATE TABLE student (mathematics SMALLINT
                                      CHECK VALUE >= 0 AND VALUE <= 100,
                             chemistry SMALLINT
                                      CHECK VALUE >= 0 AND VALUE <= 100)
              CHECK mathematics + chemistry <= 200;

The keyword VALUE is used to represent the value of the column in column constraints, but the columns names are used to represent the values of the columns in a table constraint.

Primary Keys

A table can have one primary key, including a column or a group of columns, whose value identifies each row uniquely. A primary key is similar to a unique index whose columns cannot contain NULL values. When you create a primary key, DBMaker will create a unique index called PrimaryKey on this table. A table can have at most one primary key.

Creating Primary Keys

The following SQL command is an example of how to create a primary key for the table employee.

dmSQL> ALTER TABLE employee PRIMARY KEY (ID , name);

Dropping Primary Keys

You can drop a primary key when it is not necessary. Before dropping the primary key, all foreign keys that refer to this primary key should be dropped.

The following SQL command is an example of how to drop the primary key of table employee.

dmSQL> ALTER TABLE employee DROP PRIMARY KEY;

Foreign Keys (Referential Integrity)

A column in one table whose values contain the same values as the primary key in another table is known as a foreign key. A foreign key denotes the relationship between the two tables. You can create a foreign key on a column or a group of columns in a table, and use it to reference a column or group of columns in another table. The referenced columns should be a primary key or a unique index, and cannot contain NULL values.

Because the columns in the table with the foreign key reference the columns in another table, before you insert a new row in the table with the foreign key, the columns the foreign key references must already contain the key values that are being inserted in the table with the foreign key. If they are not present, you will not be allowed to insert that row.

Similarly, before deleting a row in a table with a foreign key, all key values in the table with the foreign key must be deleted before you can delete the same key value in the referenced table.

You can create or drop a primary key or foreign key whenever it is necessary. If you create a primary key on a non-empty table, DBMaker will check the uniqueness of the key. If you create a foreign key on a non-empty table, DBMaker will check whether all the key values already exist in the referenced table.

Creating Foreign Keys

A foreign key is used to refer to another table by specifying the referencing and referenced columns. Both the referencing and referenced columns should be mapped to each other. The mapping columns should be the same type and length. The referenced columns should be NOT NULL, but the referencing columns can be NOT NULL or nullable. If you do not denote the referenced columns, the primary key on the referenced table is regarded as the referenced column(s).

The following SQL command shows how to create a foreign key on table salary which references the table employee:

dmSQL> ALTER TABLE salary FOREIGN KEY f1(ID, name) REFERENCES employee;

When you create a primary key on the table named employee, you can create a foreign key on another table to refer to this table without specifying the referenced columns.

Dropping Foreign Keys

If the relationship defined by a foreign key is not necessary, you can drop a foreign key using the ALTER TABLE command. The following SQL command gives an example of how to drop a foreign key from the table salary.

dmSQL> ALTER TABLE salary DROP FOREIGN KEY f1;

7.6 Managing Serial Numbers

DBMaker provides a feature to generate serial numbers automatically. This feature is especially useful in multi-user environments for generating and returning unique sequential numbers without the overhead of disk I/O or transaction locking.

Serial numbers are signed 32-bit integers in DBMaker. A table can only have one column containing the SERIAL data type for generating serial numbers.

You can specify the starting number for the SERIAL column in any table when you create the table. If you do not explicitly specify the starting number of a SERIAL column, its starting number is implicitly set to 1.

To trigger DBMaker to generate a serial number, you insert a new row and supply a NULL value for the serial column. If you insert a new row and supply an integer value instead of a NULL value, DBMaker does not generate a serial number for that serial column. If the supplied integer value is greater than the last serial number generated, DBMaker will reset the sequence of generated serial numbers to start with the supplied integer value.

The SERIAL columns cannot be defined with default values or constraints.

Creating Serial Columns

A serial column must be defined with the SERIAL type keyword and an optional starting number.

The following SQL command example creates the column ID of the table employee with the SERIAL type instead of the INTEGER type, and specifies its starting number as 1001.

dmSQL> CREATE TABLE employee (nation CHAR(20) DEFAULT `R.O.C',
                                  ID SERIAL(1001),
                                name CHAR(30) NOT NULL,
                            joinDate DATE DEFAULT CURDATE(),
                              height FLOAT,
                              degree VARCHAR(200)) IN ts1;

Generating Serial Numbers

The serial numbers are generated by DBMaker automatically, but must be triggered by inserting a NULL value into the serial column.

For example, to insert a new row into the table employee and make DBMaker generate a serial number for the column ID, you can use the following SQL command:

dmSQL> INSERT INTO employee VALUES
              (`U.S.A', NULL, `Jeff', , 6.6, `Director', NULL);

7.7 Managing Domains

A domain is a type of integrity constraint you can use when defining a column. Domains specify the data type of the column, and may also specify a default value and a value constraint. When you define a column using a domain, the column inherits the properties of the domain (data type, default value, and value constraint) without requiring you to explicitly specify them.

Specifying the default value and value constraint using domains achieves the same results as specifying them in a standard column definition. If you specify a default value for a column, it will override the default value specified in a domain. If you specify any value constraints in the column definition, they will be used in addition to the value constraints specified in the domain. If you do define a column using a domain and specify additional value constraints, you should ensure the additional value constraints do not conflict with those defined in the domain. DBMaker does not check for conflicting value constraints, so it may be possible to define value constraints that would not allow you to enter any values at all.

Any data types supported by DBMaker except the SERIAL type can be used in domains.

Creating Domains

A domain is defined by a domain name, an optional default value and an optional constraint. For example, you might want to ensure that all columns that deal with some form of titles (movie, CD, or videotape) have a data type of VARCHAR, are no more than 35 characters in length, and do not permit insertion of NULL values. The CREATE DOMAIN statement can be used to create a specific domain that is used in subsequent CREATE TABLE statements.

dmSQL> CREATE DOMAIN title_type VARCHAR(35) CHECK VALUE IS NOT NULL;

The keyword VALUE is used to represent the value of the column defined on the domain.

After you create the domain title_type, you can use it to define columns as in the following CREATE TABLE statement.

dmSQL> CREATE TABLE movie_titles (title title_type, ..., ...)

Dropping Domains

A domain can be dropped only when there is no column based on it. To drop a domain, you can use the DROP DOMAIN statement as in the following SQL command.

dmSQL> DROP DOMAIN title_type;

7.8 Browsing System Catalogs

DBMaker keeps detailed information of all schema objects in the system catalog tables. You can browse information about these objects from the corresponding system catalog tables, shown in the list below. For more information about these system catalog tables, see Appendix B.

schema object information

system catalog table name

Tables

SYSTABLE

Columns

SYSCOLUMN

Views

SYSVIEWDATA

Synonyms

SYSSYNONYM

Indexes

SYSINDEX

Domains

SYSDOMAIN

Serial numbers

SYSCOLUMN

Table constraints

SYSTABLE

Column constraints

SYSCOLUMN

Domain constraints

SYSDOMAIN

Table 7-1: Schema information in the System Catalog tables

7.9 Calculating the Space Required

As previous sections stated, of all schema objects only tables and indexes occupy physical disk space. For disk space management, you should decide their size and which tablespaces they will belong to before creating them. In the estimating phase, you must have a clear picture of how to construct tablespaces using tables and how much hardware will be required to support the database in the future. Generally, if you split the tables in a database between several tablespaces, you will get higher performance than if you put all of them in a single large tablespace. In contrast, many small tablespaces are harder to manage. Therefore, you should clearly define the required storage size when designing tablespaces.

How to Estimate the Size of a Table

Now we will examine how to estimate the size of tables and indexes. The following formulas show you how to estimate the size of a table and the size of an index:

table size = row size x number of rows x 1.05

index size = key size x number of rows x 1.20

Having these two formulas, we can estimate the size of a tablespace needed by adding the size of all tables and indexes in it. In the above formulas, 1.05 and 1.20 are estimates of the resource overhead used for calculating the system resources required. The row size and key size contain the internal record header size. The following subsections show you how to calculate the size of a row and a key.

Row Size

In DBMaker, the storage size of a row excluding any BLOB data cannot exceed 3996 bytes. The storage size of a row is made up of the space required for data storage and an internal record header. The size of the internal record header is equal to:

internal record header size = (number of columns + 1) x 2

Each data type has its own space requirements as shown in the following table.

type

column length

BINARY(n)

n

CHAR(n)

n

SMALLINT

2

INTEGER

4

SERIAL

4

DOUBLE

8

DECIMAL(p,s)

[(p+1)/2]+2

TIME

4

DATE

4

TIMESTAMP

12

OID

8

VARCHAR(n)

0..n

FILE

20

LONG VARBINARY

20+X

LONG VARCHAR

20+X

Table 7-2: Data Types and their sizes

Note: The size of a VARCHAR type column depends on the size of the actual data, but can not exceed the maximum size defined.

Note: A BLOB type column (LONG VARCHAR or LONG VARBINARY) will occupy 20 bytes in the data file at least and the actual data will be stored in the BLOB file or data file. For more detailed information, refer to Chapter 7-Large Object Management.

Note: If the value in a column is NULL, it does not occupy any space.

For example, if you create a table with five columns defined as shown below:

dmSQL> CREATE TABLE employee (ID INTEGER NOT NULL,
                            name CHAR(30) NOT NULL,
                          height FLOAT,
                          degree VARCHAR(200),
                         picture LONG VARCHAR);

After issuing this command, you insert rows into this table and calculate the size of the record as shown below:

(3001, "Jeff Yang", 175.5, "Stanford PhD.", [pic1]) where pic1 is an image.

Data Item

Type

Size

ID

integer

4 bytes

name

char

30 bytes

height

float

4 bytes

degree

varchar

13 bytes

picture

long varchar

20 bytes

row header

-

12 bytes

Total

83 bytes

=(4 + 30 + 4 + 13 + 20) + (5 + 1) x 2
= 83 bytes

(3002, "George Wang", 180.0, "NCTU Ms.", NULL)

Data Item

Type

Size

ID

integer

4 bytes

name

char

30 bytes

height

float

4 bytes

degree

varchar

8 bytes

picture

long varchar

0 bytes

row header

-

12 bytes

Total

58 bytes

=(4 + 30 + 4 + 8 + 0) + (5 + 1) x 2
= 58 bytes

When inserting or updating rows, DBMaker will verify that the row size does not exceed 3996 bytes. When creating a table, DBMaker also verifies that the smallest possible row size does not exceed 3996 bytes.

The smallest row size in the above example can be calculated as follows:

minimum row size	= (4 + 30 + 0 + 0 + 0) + (5 + 1) x 2
	= 46 bytes

The minimum row size does not exceed 3996 bytes, so DBMaker will allow
you to create this table.

Key Size

A key is similar to a row in that the storage size for a key is made up of the space required for data storage of the index columns and an internal record header, but it also requires an extra 8 bytes for an internal row identifier. The internal row identifier also requires 1 byte in the record header. Thus the size of the internal record header is equal to:

internal record header size = (no. of columns + 1 + 1) x 2

For example, if an index is created on a single column with the SMALLINT type, the size of each key would be:

key size	= 2 + 8 + (1 + 1 + 1) x 2
	= 16 bytes

In this case two bytes are used by the data in the key column, eight bytes are used for the internal row ID for each key, and six bytes are used for the record header.

Estimating the Size of Tablespaces and Tables

The following example demonstrates how to estimate the size of a tablespace and its tables. Assume there is a tablespace that contains three tables A, B, and C, and one index D created on table A. Columns in table A are defined as INTEGER, INTEGER, and CHAR(10). Columns in table B are defined as SMALLINT, CHAR(10), FLOAT, and VARCHAR(200). Columns in table C are defined as SMALLINT, INTEGER, and LONG VARCHAR. Index D is created on the first column in table A. Table A, table B and table C consist of 1500, 3000, and 250 rows respectively.

The row and key sizes for this database can be calculated as shown below. Suppose the average length of the VARCHAR column in table B is 80 bytes, and the size of each BLOB column in the data file in table C is 20 bytes:

In table A:

row size = (4 + 4 + 10) + 8

= 26 bytes

In table B:

row size = (2 + 10 + 4 +80) + 10

= 106 bytes

In table C:

row size = (2 + 4 + 20) + 8

= 34 bytes

If the average size of each BLOB item in table C is 9000 bytes, then you might specify the frame size of BLOB file to be 11KB. See "Large Object Management" for more information about BLOB data.

For index D:

key size = 4 + 4 + 8

= 16 bytes

The table sizes for this database can be calculated as shown below. Note that the size of table A also includes the size of index D.

For table A:

table size = (26 x 1500 x 1.05) + (16 x 1500 x 1.2)

= 40950 + 24000

= 10 + 6 pages

= 16 pages

For table B:

table size = 106 x 3000 x 1.05

= 333900 bytes

= 82 pages

For table C:

table size = 34 x 250 x 1.05

= 34 x 250 x 1.05

= 3 pages

In the BLOB file, the size of table C is 250 frames (every row needs a frame).

After examining the above figures, you might create a tablespace with at least one data file (16+82+3=101 pages) and one BLOB file (250 frames with a frame size of 11KB) to store the above tables and index.

You can estimate the size of a tablespace when creating to avoid the trouble of adding or enlarging files at a later time.