Types of tables

Sybase IQ recognizes four types of tables:

• Base tables

• Local temporary tables

• Global temporary tables

• Join virtual tables

Base tables are sometimes called main, persistent, or permanent tables because they are a permanent part of the database until you drop them explicitly. They remain in the database over user disconnects, server restart, and recovery. Base tables and the data in them are accessible to all users who have the appropriate permissions. The CREATE TABLE statement shown in the previous example creates a base table.

Certain client tools need to create and populate persistent tables. In a multiplex database, persistent tables have two locations, depending upon the server to which the user connects:

• On the write server, users create persistent tables in the shared IQ Main Store.

• On the query server, users create persistent tables in a local IQ Main Store (Local Store). If the user has not first created a dbspace to serve as the local IQ Main Store, IQ issues an error when a user tries to create a persistent object.

There are two types of temporary tables, global and local.

You create a global temporary table, using the GLOBAL TEMPORARY option of CREATE TABLE, or by using the Global Temporary Table Creation wizard in Sybase Central. When you create a global temporary table, it exists in the database until it is explicitly removed by a DROP TABLE statement.

A database contains only one definition of a global temporary table, just as it does for a base table. However, each user has a separate instance of the data in a global temporary table. Those rows are visible only to the connection that inserts them. They are deleted when the connection ends. A given connection inherits the schema of a global temporary table as it exists when the user first refers to the table. Global temporary tables created on the write server are also created on all query servers. Global temporary tables created on the query server are local to that query server.

To select into a temporary table, use syntax like the following:

SELECT * INTO #TableTemp FROM lineitem 
WHERE l_discount < 0.5

You declare a local temporary table for your connection only, using the DECLARE LOCAL TEMPORARY TABLE statement. A local temporary table exists until the connection ends, or within a compound statement in which it is declared. The table and its data are completely inaccessible to other users.

You drop a global temporary table just as you would a base table, with the DROP TABLE statement, or with Sybase Central. You cannot drop or alter a global temporary table while other connections are using the table.

Sybase IQ creates tables in your current database. If you are connected to an IQ database, tables are placed as follows:

A Join Virtual Table is a denormalized table that looks like a regular table; it has a name, columns, rows, and indexes. Sybase IQ creates Join Virtual Tables as a result of a Create Join Index for internal processing purposes and deletes them when you do a Drop Join Index. You cannot create, modify, or delete Join Virtual Tables, but you may see error messages related to them if you try to use or modify them. Sybase suggests that you ignore all Join Virtual Tables. Servers running in a multiplex cannot create or drop Join Indexes. (Write servers must be in single-node mode to do so, and query servers must be synchronized.)