Table Location
When you create a table, it is placed in the default location designated for the database’s data files, unless you specify otherwise.
Requirements for Using an Alternate Location for a Table
Before using an alternate location for a table, the following requirements must be met:
• The location must exist and must be designated to hold data files
• The area to which the location name points must exist with correct permissions and ownership
• The directory indicated by the area must have the appropriate subdirectory structure with correct permissions
• The database must be extended to the location
• You must be the table owner (or a user with the security privilege impersonating the owner).
Create a Table in an Alternate Location
To create a table in a location other than the default location, use the WITH LOCATION clause of the CREATE TABLE statement.
In Director, use the Options in the New Table tab.
If you specify only one location, the entire table is stored in that location.
Create a table with columns in different locations:
CREATE TABLE sales_fact AS
SELECT orders FROM sales
WITH LOCATION(col1)=(location1),
LOCATION(col2)=(location2),
LOCATION(col3)=(location3);
Create a table that spans three locations (horizontally partitioned):
CREATE TABLE sales_fact AS
SELECT orders FROM sales
WITH LOCATION = (location1, location2, location3)
Using Multiple Locations
Vector can store data in multiple physical locations on disk. This feature lets you add more locations to a database if disk space is low.
You can store different classes of data (tables) on separate physical drives. Different columns of a single table can be stored in different locations. You can also assign a temporary storage location for spilling to disk. Multiple locations also enable manual performance tuning by strategically storing tables in different locations.
A table can be stored in more than one location by specifying a location for each column (vertical partitioning). Only one location can be specified per column.
Horizontal Partitioning
A table or single column can be stored in multiple storage locations, a feature known as horizontal partitioning (in contrast to vertical partitioning in which different columns are stored in different locations).
Horizontal partitioning is useful if disk space in a single location is insufficient to contain the table, or if the database administrator wants to stripe table access across multiple physical drives, controllers, or arrays to optimize performance.
You can horizontally partition a table or column by using appropriate syntax on the following statements:
• CREATE TABLE...WITH LOCATION
• CREATE INDEX...WITH LOCATION
• ALTER TABLE...ADD COLUMN...SET LOCATION
• DECLARE GLOBAL TEMPORARY TABLE...WITH LOCATION
A table or column is horizontally partitioned across all listed locations. You cannot specify which records are stored in which partition; the system distributes the records in a round-robin fashion among the specified partitions and in the specified order. The order of locations and the granularity of partitioning may change after automatic update propagation or after issuing a MODIFY...TO COMBINE statement.
The output of the HELP TABLE statement shows the table Location as the set of locations used by the most columns when the table is created. The Column Location lists locations for columns that do not have the same locations as the table locations.
Restrictions:
• All the locations in which a table or column will be stored must be specified when the table (CREATE TABLE) or column (ALTER TABLE) is created. The locations for a column cannot change.
• If a specified location is full, appending data to the table will fail. "Switching off" the location is not possible.