1 – across-clause
Associates the table with two or more storage areas.
2 – COMMENT IS 'string'
Adds a comment about the storage map. SQL displays the text of
the comment when it executes a SHOW STORAGE MAPS statement.
Enclose the comment in single quotation marks (') and separate
multiple lines in a comment with a slash mark (/).
3 – COMPRESSION option
Syntax options:
ENABLE COMPRESSION | DISABLE COMPRESSION
Specifies whether the rows for the partition are compressed or
uncompressed when stored. You can enable or disable compression
on each vertical partition. You enable compression to conserve
disk space, but there is a small CPU overhead for inserting and
retrieving compressed rows.
If you omit this clause, the default compression is that which
was specified for the storage map before the first STORE COLUMNS
clause. The default is ENABLE COMPRESSION.
4 – FILL
Syntax options:
FILL RANDOMLY | FILL SEQUENTIALLY
Specifies whether to fill the area set randomly or sequentially.
Specifying FILL RANDOMLY or FILL SEQUENTIALLY requires a FOR
clause. When a storage area is filled, it is removed from the
list of available areas. Oracle Rdb does not attempt to store
any more lists in that area during the current database attach.
Instead, Oracle Rdb starts filling the next specified area.
When a set of areas is filled sequentially, Oracle Rdb stores
lists in the first specified area until that area is filled.
If the set of areas is filled randomly, lists are stored
across multiple areas. This is the default. Random filling is
intended for read/write media, which will benefit from the I/O
distribution across the storage areas.
The keywords FILL RANDOMLY and FILL SEQUENTIALLY can only be
applied to areas contained within an area list.
5 – FOR (table name)
Specifies the table or tables to which this list storage map
applies. The named table must already be defined. If you want to
store lists of more than one table in the storage area, separate
the names of the tables with commas. For each area, you can
specify one FOR clause and list of table names.
6 – FOR (table name.col name)
Specifies the name of the table and column containing the list
to which this storage map applies. Separate the table name and
the column name with a period (.). The named table and column
must already be defined. If you want to store multiple lists
in the storage area, separate the table name and column name
combinations with commas. For each area, you can specify one FOR
clause and a list of column names.
7 – LOGGING
Syntax options:
LOGGING | NOLOGGING
The LOGGING clause specifies that rows written to the table
during the current transaction (the transaction in which this
table was created) be logged when written to the database.
Logging includes writing data and management records to the
recovery-unit journal file (.ruj) and after-image journal files
(.aij). When the NOLOGGING clause is specified then only a small
number of management records are logged in the recovery-unit
journal file (.ruj) and after-image journal files (.aij). See the
Usage Notes below for more information.
LOGGING and NOLOGGING can be specified per storage area
(partition) or as a default for the CREATE STORAGE MAP statement.
The LOGGING and NOLOGGING clauses are mutually exclusive; specify
only one. The LOGGING clause is the default.
8 – OTHERWISE_IN
For partitioned storage maps only, specifies the storage area
that is used as the overflow partition. An overflow partition is
a storage area that holds any values that are higher than those
specified in the WITH LIMIT OF clause. An overflow partition
holds those values that exceed the highest specified limits.
9 – partition-clause
Defines vertical partitioning, horizontal partitioning, or both
for the specified table.
Horizontal partitioning means that you divide the rows of the
table among storage areas according to data values in one or more
columns. Vertical partitioning means that you divide the columns
of the table among storage areas. A given storage area will then
contain only some of the columns of a table. You can combine both
horizontal and vertical partitions in a single map.
Vertical partitioning reduces disk I/O operations by placing
frequently used data in one area, so that you can read and update
those portions of the table in a single disk I/O operation.
See the Oracle Rdb Guide to Database Design and Definition for
more information regarding partitioning.
10 – PARTITION name
Names the partition. The name can be a delimited identifier if
the dialect or quoting rules are set to SQL92 or SQL99. Partition
names must be unique within the storage map. If you do not
specify this clause, Oracle Rdb generates a default name for
the partition.
11 – PARTITIONING_IS_NOT_UPDATABLE
Specifies that the value of the partitioning key cannot be
modified and that the row is always stored in the storage area
based on the partitioning criteria in the STORE USING clause. The
partitioning key is the column or list of columns specified in
the STORE USING clause.
Specifying the PARTITIONING IS NOT UPDATABLE clause allows Oracle
Rdb to quickly retrieve data because the partitioning criteria
can be used when optimizing the query.
To update columns that are partitioning keys in a NOT UPDATABLE
storage map, you must delete the rows and then reinsert the rows
to ensure that they are placed in the correct location.
If you specify the PARTITIONING clause, you must also specify the
STORE USING clause when defining a storage map.
If the PARTITIONING clause is not specified, UPDATABLE is the
default.
See the Oracle Rdb Guide to Database Design and Definition for
more information regarding partitioning.
12 – PARTITIONING_IS_UPDATABLE
Specifies that the partitioning key can be modified. The
partitioning key is the column or list of columns specified in
the STORE USING clause.
If you modify a row in an UPDATABLE storage map, the row is not
moved to a different storage area even if the new value of the
partitioning key is not within the limits of original storage
area. As a result, Oracle Rdb must consider all storage areas
specified in the STORE USING clause when retrieving a row.
If you specify the PARTITIONING clause, you must also specify the
STORE USING clause when defining a storage map.
If the PARTITIONING clause is not specified, UPDATABLE is the
default.
See the Oracle Rdb Guide to Database Design and Definition for
more information regarding partitioning.
13 – PLACEMENT_VIA_INDEX
Directs the database system to store a column in a way that
optimizes access to that column by the indicated path. Oracle Rdb
chooses a target page for any columns being stored by rules that
take into account the type of index named (sorted or hashed),
the type of storage areas involved (uniform or mixed), and how
indexes and tables are assigned to storage areas.
For a hashed index, Oracle Rdb calculates the page containing
the hashed index node that points to the column. If that page is
within the same storage area in which the column will be stored,
it is used as the target page for storing the column. If that
page is not within the same storage area in which the column
is to be stored, Oracle Rdb chooses a target page in the same
relative position within the appropriate storage area (if it is a
mixed storage area) or a page in a clump reserved for that table
(if it is a uniform storage area).
For a sorted index, Oracle Rdb finds the database key of the next
lowest row to the one being stored and uses the page number in
the database key as the target page.
14 – STORAGE_MAP
Specifies the name of the storage map you want to create. The
name cannot be the same as any other definition in the database.
15 – store-clause
The storage map definition. The store-clause in a CREATE STORAGE
MAP statement lets you specify which storage area files are used
to store rows from the table.
o All rows of a table can be associated with a single storage
area.
o Rows of a table can be randomly distributed among several
storage areas.
o Rows of a table can be systematically distributed, or
partitioned, among several storage areas by specifying upper
limits on the values for a column in a particular storage
area. This is called horizontal partitioning.
o Columns of a table can be partitioned among storage areas.
This is called vertical partitioning.
If you omit the storage map definition, the default is to store
all the rows for a table in the default storage area. See the
CREATE and IMPORT DATABASE statements for information on the
default storage area.
16 – STORE_COLUMNS
Syntax option:
STORE COLUMNS (column-name)
Lists the columns which will be stored in the subsequent map.
Multiple STORE COLUMNS clauses may appear in a map to spread
across multiple storage areas. A column name may only appear
in one STORE COLUMNS clause. A final STORE clause can appear to
provide a location for all remaining unspecified columns.
17 – STORE IN area name
Associates the table directly with a single storage area. All
rows in the table are stored in the area you specify.
18 – STORE_LISTS_IN
Directs the database system to store the lists from tables in a
specified storage area or in a set of areas. You can create only
one storage map for lists within each database.
You must specify the default storage area for lists in the STORE
LISTS clause. The default list storage area contains lists from
system tables as well as lists not directed elsewhere by the
STORE LISTS clause. You can also use the LIST STORAGE AREA clause
of the CREATE DATABASE statement to specify a default storage
area for lists. If you do not use the STORE LISTS clause and do
not specify a list storage area in the CREATE DATABASE statement,
Oracle Rdb uses the default storage area as the default list
storage area. The following example directs Oracle Rdb to place
all lists in the LISTS storage area unless otherwise specified in
a storage map:
SQL> CREATE DATABASE FILENAME mf_personnel
SQL> LIST STORAGE AREA IS LISTS
SQL> CREATE STORAGE AREA LISTS;
The accompanying storage map statement must also specify the
LISTS storage area as the default storage area.
SQL> CREATE STORAGE MAP LISTS_MAP
cont> STORE LISTS IN LISTS1 FOR (EMPLOYEES.RESUME)
cont> IN LISTS;
You can use an area set to specify that data is to be distributed
across several areas. The following example shows how you can
store data in three storage areas (LISTS1, LISTS2, and LISTS3)
for two different columns in TABLE1. The default list storage
area is LISTS1.
CREATE STORAGE MAP LISTS_MAP
STORE LISTS IN (LISTS1,LISTS2,LISTS3) FOR (TABLE1.COL1,TABLE1.COL2)
IN LISTS1;
You can store lists from different tables in the same area. The
following example shows how you can store data from TABLE1,
TABLE2, and TABLE3 in the LISTS storage area. The default list
storage area is RDB$SYSTEM.
SQL> CREATE STORAGE MAP LISTS_MAP -- to direct the list data to area LISTS
cont> STORE LISTS IN LISTS FOR (TABLE1, TABLE2, TABLE3)
cont> IN RDB$SYSTEM;
Alternatively, you can store lists from each table in unique
areas. The following example shows list data from TABLE1 being
stored in the LISTS1 storage area and list data from TABLE2 being
stored in the LISTS2 storage area. The default list storage area
is RDB$SYSTEM.
CREATE STORAGE MAP LISTS_MAP
STORE LISTS IN LIST1 FOR (TABLE1)
IN LIST2 FOR (TABLE2)
IN RDB$SYSTEM;
You can also specify that different columns from the same table
go into different areas. The following example shows data from
different columns in TABLE1 being stored in either LISTS1 or
LISTS2. The default list storage area is RDB$SYSTEM.
CREATE STORAGE MAP LISTS_MAP
STORE LISTS IN LISTS1 FOR (TABLE1.COL1)
IN LISTS2 FOR (TABLE1.COL2)
IN RDB$SYSTEM;
19 – STORE_RANDOMLY_ACROSS
Syntax option:
STORE RANDOMLY ACROSS (area-name)
As rows are inserted in the table, they are distributed randomly
across the storage areas named in the list. You must name at
least two storage areas in this clause.
20 – STORE_USING
Syntax option:
STORE USING (column-name) IN area-name
The database system compares values in the columns to the values
in the WITH LIMIT OF clause to determine placement of rows
inserted into the table. See the Oracle Rdb SQL Reference Manual
for further information.
Use RMU EXTRACT to have the store using expression expanded. See
Example 9.
21 – STORED_NAME
Specifies a name that Oracle Rdb uses to access a storage map
created in a multischema database. The stored name allows you
to access multischema definitions using interfaces that do not
recognize multiple schemas in one database. You cannot specify a
stored name for a storage map in a database that does not allow
multiple schemas. For more information on stored names, see the
User_Supplied_Names HELP topic.
22 – THRESHOLD clause
Specifies one, two, or three default threshold values for logical
areas in storage areas with uniform format pages. The threshold
values (val1, val2, and val3) represent a fullness percentage on
a data page and establish three possible ranges of guaranteed
free space on the data pages. When a data page reaches the
percentage defined by a given threshold value, the space area
management (SPAM) entry for the data page is updated to reflect
the new fullness percentage and its remaining free space.
Oracle Rdb never stores a record at the third threshold. The
value you set for the highest threshold can be used to reserve
space on the page for future record growth.
When only val1 is specified, this is equivalent to (val1, 100,
100). When val1 and val2 are specified, this is equivalent to
(val1, val2, 100). The trailing, unspecified thresholds default
to 100 percent. For example, THRESHOLDS ARE (40) would appear as
(40, 100, 100).
If no thresholds are specified for the area, the default is
(0,0,0). This causes the SPAM algorithm to set thresholds based
on the nominal record length for the logical area; for example,
the node size for the index or the uncompressed length of the row
for a table.
You cannot specify the thresholds for the storage map attribute
for any area that is a mixed page format. If you have a mixed
page format, set the thresholds for the storage area using the
ADD STORAGE AREA or CREATE STORAGE AREA clause of the ALTER
DATABASE, CREATE DATABASE, or IMPORT statements.
23 – VERTICAL PARTITION name
Names a vertical partition. The name can be a delimited
identifier if the dialect or quoting rules are set to SQL92 or
SQL99. Partition names must be unique within the storage map. If
you do not specify this clause, Oracle Rdb generates a default
name for the partition.
24 – using-clause
Specifies columns whose values are used as limits for
partitioning the table horizontally across multiple storage
areas.
25 – WITH_LIMIT_OF
Specifies the maximum values that the columns named in the USING
clause can have when rows are initially stored in the specified
storage area. Repeat this clause to partition the rows of a table
among multiple storage areas.
The number of literals listed must be the same as the number of
columns in the USING clause. The data type of the literals must
agree with the data type of the column. For character columns,
enclose the literals in single quotation marks.
The values in the WITH LIMIT OF clause only affect placement of
rows when they are initially stored. If UPDATE statements change
data in a row so that values in columns named in the USING clause
exceed values specified in the WITH LIMIT OF clause, the row is
not moved into a different storage area.