Sybase 12.4.2 manual Automatic index creation for IQ tables, 121, Table placement

CHAPTER 3 Working with Database Objects

Dropping and altering global temporary tables

Placement of tables

Join virtual tables

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.

See “Versioning of temporary tables” for versioning information on local temporary tables.

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

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

Table 3-2: Table placement
Type of table	Permitted placement	Default placement
Permanent	Main IQ Store or Catalog	Main IQ Store
	Store
Global temporary	Temporary IQ Store or	Temporary IQ Store
	Catalog Store
Local temporary	Temporary IQ Store or	Temporary IQ Store
	Catalog Store; only visible
	to user who creates it

A Join Virtual Table is a denormalized table that looks like a regular table; it has a name, columns, rows, and indexes. Adaptive Server 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.

Automatic index creation for IQ tables

You can automate indexing for certain columns by creating a table with either PRIMARY KEY or UNIQUE as a single-column constraint. These options cause Adaptive Server IQ to create an HG index for the column that enforces uniqueness.

121