As described in Part 1 of this tutorial series, a table is similar to a spreadsheet that houses data in rows and columns. Each column describes a piece of data being held by this table, and each row contains those pieces of data being held about a particular instance of the subject of this table.
CREATE TABLE SQL statement is used to
create a database table. The syntax of the
CREATE TABLE statement can get quite
complicated with many different options, so this tutorial won't show
the syntax, but will instead show examples of using the different
clauses of the syntax as it progresses.
The simplest form of the
statement is creating a table with just basic columns.
Listing 1. Creating a table with basic columns
CREATE TABLE customer ( SSN char(11), LName char(50), Age integer, Birthday date );
This example created a table called 'customer' with four columns in it: 'SSN', 'LName', 'Age', and 'Birthday'. Notice how the column definitions are a comma-delimited list that is set inside a pair of parentheses.
The example in Listing 2 adds on the storage clause. As described in Part 3 of this series, tables are stored in dbspaces. So the storage clause lists the dbspace name in which the table should be created.
Listing 2. Storage clause
CREATE TABLE customer ( SSN char(11), LName char(50), Age integer, Birthday date ) IN dbspace1;
The next example (Listing 3) adds sizing attributes. The actual data inside a table is stored in pieces of a disk known as Informix 'pages'. Informix pages are not the same as O/S disk pages. Informix pages are the I/O mechanism that Informix uses when reading and writing from disk. Informix pages can be between 2K and 16K in size. Even though data is stored on pages, IDS doesn't want single pages for a table scattered all over the disk, so IDS uses a mechanism called an extent. An extent is a collection of contiguous pages. So the sizing of a table is to tell IDS how big, or how many pages, you want in an extent. Extents by default are eight pages. IDS allows a DBA to specify the size of the very first extent that is allocated to the table, as well as the size of any other extents that are allocated to the table. A second extent will be allocated to the table when the first extent's pages are full. A third extent will be allocated to the table when the first and second extents' pages are full. IDS adds more and more extents as pages are needed to hold data being added to the table.
Note: Extents on disk are in pages, but when defined, the DBA sizes them in kilobytes. So it is important to understand the Informix page size being used on your system. For most UNIX and Linux 32-bit systems, the default page size is 2K. For UNIX and Linux 64-bit systems and Windows systems, the default page size is 4K. You can also specify a non-default page size.
The example in Listing 3 creates a table that has a first extent size of 50 pages and a next extent size of 25 pages, using the assumption that you have a 4K Informix page size:
Listing 3. Sizing attributes
CREATE TABLE customer ( SSN char(11), LName char(50), Age integer, Birthday date ) EXTENT SIZE 200 NEXT SIZE 100;
The next example (Listing 4) brings in what is
clause. When a user attempts to update a row in a table, a lock
must be placed on the area being updated so that another user cannot
attempt to update the same area at the same time. By default a table
lock mode page put on it, which
means that the area being locked is the whole page. Since a page can
have multiple rows on it, even though the user is just trying to
update one row on the page, all the rows on the page are locked and
inaccessible to other updating users. The locking area can be changed
by the DBA to be of
row. When the
lock mode row set on it, the
updating user only locks the one row being updated on a page, allowing
all other rows on that page to be accessible to other updating users.
The example in Listing 4 adds the
lock mode row clause; the
lock mode page clause is the
lock mode rowclause
CREATE TABLE customer ( SSN char(11), LName char(50), Age integer, Birthday date ) LOCK MODE ROW;
Each way of locking has its advantages and disadvantages. To more fully understand this, read the "Locking" section of the Administration topics in the Information Center (see Resources).
When a table is created, by default, it has to completely exist within
a single dbspace. The dbspace it has to exist within is either given
to it by the server or specified in the
CREATE TABLE SQL statement with the
IN clause (see
Listing 2 above). This single dbspace
constraint puts several limitations on a table, including a size
limit. To allow for much larger tables, you can create fragmented
tables. These special tables break the single dbspace rule and allow
parts of the table to exist within two or more dbspaces. A table is
not fragmented until it is explicitly done so by the DBA.
A table can be fragmented in one of two ways:
- By round-robin – evenly distributes the rows across the fragments listed
- By expression - specifies an expression that locates the rows in the appropriate fragment
Note: Fragmented tables can only have one fragment per dbspace, so you have to specify a dbspace name for each fragment of the table.
Listing 5 shows how a DBA can create a new table that uses round-robin-based fragmentation to split the table among three dbspaces.
Listing 5. Round-robin-based fragmentation of new table
CREATE TABLE customer ( SSN char(11), LName char(50), Age integer, Birthday date ) FRAGMENT BY ROUND ROBIN IN dbs1, dbs2, dbs3;
Listing 6 shows how a DBA can create a new table that uses expression-based fragmentation. The expression is evaluated every time the table is queried to determine which fragments need to be accessed.
Listing 6. Expression-based fragmentation
CREATE TABLE customer ( SSN char(11), LName char(50), Age integer, Birthday date ) FRAGMENT BY EXPRESSION (Age>50) IN dbs1 (Age<51) in dbs2;
In the expression in Listing 6, the value of the Age column determines
which fragment the data resides in. If Age = 28, then the fragment in
dbs1 is used to store the row, because 28 > 50.
each dbspace can only have one fragment of the table, the
CREATE TABLE SQL statement uses the dbspace
name to specify the table fragment.
The examples in Listings 5 and 6 are just the beginning. A round-robin-based fragmented table can have as many fragments as the number of dbspaces defined in the instance.
An expression-based fragmented table can also have that many fragments, and can use as complicated an expression as needed. An example of a more complicated expression would be:
Listing 7. More complicated expression-based fragmentation
FRAGMENT BY EXPRESSION (Age < 50 AND (Birthday BETWEEN '01/01/1998' AND '4/15/2008') OR LName IN ("Smith","Zimmermann") ) IN dbs1, (Age > 51 OR Age = 17 AND LName NOT LIKE '%John%') IN dbs2;
Although the expressions can get complicated (and even more complicated than the one in Listing 7), the general rule of thumb is to keep them as simple as possible, while still maintaining good distribution of your data across the fragments.
The right fragmentation scheme can improve performance dramatically. To understand how, read more in the "Fragmentation Guidelines" section of the Performance Guide under the Administering topic in information center.
Partitioned tables are almost identical to fragmented tables, with the exception of two things:
- They can have more than one partition per dbspace
- Their CREATE TABLE syntax
Partitioned tables allow the table to have more than one partition per dbspace. The convenience here is that a table with 10 partitions could be spread across 10 dbspaces or less, where a table with 10 fragments requires 10 dbspaces.
Since partitioned tables allow more than one partition per dbspace, the syntax of the SQL statement has to change because the dbspace name can no longer be used by default to reference the partitions inside it.
Listing 8. Syntax for partitioned tables
CREATE TABLE customer ( SSN char(11), LName char(50), Age integer, Birthday date ) PARTITION BY EXPRESSION PARTITION part1 (Age>50) IN dbs1 PARTITION part2 (Age<51) in dbs1;
The example in Listing 8 shows how both partitions are created in the same dbspace (dbs1) and are both given names (part1 and part2).
The importance of the name of a fragment or partition is to allow the user to manipulate the fragment or partition at a later date. A quick example of this without going into much detail is:
Listing 9. Modifying fragment
ALTER FRAGMENT ON TABLE customer MODIFY dbs1 TO Age < 55 IN dbs1;
The example in Listing 9 modifies the expression that was created for
that fragment from Age < 50 to Age < 55. Notice how the
modify clause references exactly which
fragment's expression is going to be modified by specifying the
dbspace name, dbs1, after the
To change this example and instead use the partition example from above, it would look like:
Listing 10. Modifying partition
ALTER FRAGMENT ON TABLE customer MODIFY PARTITION part1 TO Age < 55 IN dbs1;
Notice how the example in Listing 10 uses the partition name, part1, to specify which partition expression is being modified.
Temporary tables act just like real tables with a few minor exceptions:
- Temporary tables are automatically dropped when the user that created them disconnects from the database
- Temporary tables can be unlogged in all types of databases
- Temporary tables are only visible to the user who created them
A temporary table can be created in one of two ways:
- Using the
SELECT … INTO TEMP…SQL statement
- Using the
CREATE TEMP TABLE… SQLstatement
After a temporary table has been created, it can be used just like any other visible table in the database.
Listing 11. Creating a temporary table
CREATE TEMP TABLE temp_cust (Name char(50), Old int, Bday date); SELECT * FROM customer, temp_tab WHERE customer.Age = temp_cust.Old;
Temporary tables don't have all the same capabilities as regular tables, but they do include fragmentation, storage, indexes, and some constraint options.
WITH NO LOG clause is used to turn
logging off on a temporary table:
Listing 12. Turning logging off on a temporary table
CREATE TEMP TABLE temp_cust (Name char(50), Old int, Bday date) WITH NO LOG;