11. Managing Tables and Views : Table Management : Table Location
 
Share this page                  
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);