Changes an existing storage map. A storage map controls which
rows of a table are stored in which storage areas in a multifile
database.
In addition to changing storage maps, the ALTER STORAGE MAP
statement has options that change the following:
o Which index the database system uses when inserting rows in
the table
o Whether or not the rows of the table are stored in a
compressed format
o Whether or not the data is reorganized
o Whether partitioning keys can be modified
o Whether logging the transaction containing the ALTER statement
is journaled to the RUJ and AIJ files.
1 – Environment
You can use the ALTER STORAGE MAP statement:
o In interactive SQL
o Embedded in host language programs to be precompiled
o As part of a procedure in an SQL module
o In dynamic SQL as a statement to be dynamically executed
2 – Format
ALTER STORAGE MAP <map-name> -+
+-----------------------------+
++-+-++-> ENABLE --+-> COMPRESSION ---------------------------------+-+-+->
| | |+-> DISABLE -+ | | |
| | +-> COMPILE ---------------------------------------------------+ | |
| | +-> NO PLACEMENT VIA INDEX ------------------------------------+ | |
| | +-> PLACEMENT VIA INDEX <index-name> --------------------------+ | |
| | +-> RENAME PARTITION <partition-name> TO <new-partition-name> -+ | |
| | +-> REORGANIZE ---+-----------+--------------------------------+ | |
| | | +-> AREAS --+ | | |
| | | +-> PAGES --+ | | |
| | +-> NO REORGANIZE ---------------------------------------------+ | |
| | +-> store-clause ----------------------------------------------+ | |
| | +-> PARTITIONING IS UPDATABLE ---------------------------------+ | |
| | +-> PARTITIONING IS NOT UPDATABLE -----------------------------+ | |
| | +-> threshold-clause ------------------------------------------+ | |
| | +-> LOGGING ---------------------------------------------------+ | |
| | +-> NOLOGGING -------------------------------------------------+ | |
| | +-> COMMENT IS -+-> 'string' --+-------------------------------+ | |
| | +---- / <-----+ | |
| +------------------- <---------------------------------------------+ |
+-----------> store-list-clause ---------------------------------------+
store-clause =
--> STORE ---+-> IN area-spec ------+->
+-> across-clause -----+
+-> using-clause ------+
area-spec =
--> <area-name> -+---------------------------------------------------+->
+-> ( -++-> threshold-clause ---------------++-> ) -+
|+-> LOGGING ------------------------+|
|+-> NOLOGGING ----------------------+|
|+-> PARTITION <name> ---------------+|
|+-> COMMENT IS -+--> 'string' ---+--+|
| +------- / <-----+ |
+---------------- , -----------------+
across-clause =
---> RANDOMLY ACROSS ---+
+----------------------+
+-> ( -+> area-spec --+--> ) -->
+----- , <-----+
using-clause =
---> USING ---> ( -+-> <column-name> -+-> ) ------------------+
+-------- , <------+ |
+--------------------------------------------------------------+
++-> IN area-spec -> WITH LIMIT OF --> ( +-> <literal> -+> ) +-+
| +------ , <----+ | |
+--------------------------<--------------------------------+ |
+--------------------------<----------------------------------+
++------------------------------------------------+------------->
+> OTHERWISE IN area-spec -----------------------+
threshold-clause =
--+-> THRESHOLD -+-> IS -+-> ( --> <val1> --> ) ---------+->
| +-> OF -+ |
| |
+-> THRESHOLDS -+-> ARE -+-----------+ |
+-> OF -+ | |
+----------------------------------+ |
+-> ( --> <val1> -+---------------------------+-> ) -+
+-> , <val2> -+-------------+
+-> , <val3> -+
store-lists-clause =
---> STORE LISTS ---+
+------------------+
+-+-> IN -+-> area-spec ---------------+---+----------->
| +-> ( +> area-spec -> ) ----++ |
| +------ , <-----------+ |
| +---------------------------------------+
| +-+------------------------>-----------------------+
| +-> FOR -> ( -+-> <table-name> ---------+-> ) -+ |
| +-> <table-name.col-name> + | |
| +-------- , <-------------+ | |
| +--------------------------------------+ |
| +---+------------>----------+------------+
| +-> FILL RANDOMLY -----+ |
| +-> FILL SEQUENTIALLY --+ |
+----------------------------<-----------------------+
3 – Arguments
3.1 – AREAS
Specifies that the target of the data reorganization is storage
areas. All rows are checked to see if they are in the correct
storage area and if some are not, they are moved. This is the
default.
3.2 – COMMENT_IS
Adds or alters 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.3 – COMPILE
Creates a SQL mapping routine that matches the WITH LIMIT OF
clause for the storage map. The routine is automatically created
in the system module RDB$STORAGE_MAPS (use SHOW SYSTEM MODULES to
view). The storage map name is used to name the mapping routine
(use SHOW SYSTEM FUNCTIONS to view).
NOTE
If a routine already exists with the same name as the
storage map, then the mapping routine will not be created.
If the storage map includes a STORE COLUMNS clause, that is,
a vertically partitioned map, then several routines will be
created and uniquely named by adding the vertical partition
number as a suffix.
The mapping routine returns the following values:
o Zero (0) if the storage map is defined as RANDOMLY ACROSS.
This routine is just a descriptive place holder.
o Positive value representing the storage map number (the
same value as stored in RDB$ORDINAL_POSITION column of the
RDB$STORAGE_MAP_AREAS table). These values can be used with
the PARTITION clause of the SET TRANSACTION...RESERVING clause
to reserve a specific partition prior to inserting the row.
o A value of -1 if the storage map has no OTHERWISE clause. This
indicates that the row cannot be inserted because it does not
match any of the WITH LIMIT OF clauses.
3.4 – COMPRESSION
Syntax options:
ENABLE COMPRESSION | DISABLE COMPRESSION
Changes whether the rows for the table are compressed or
uncompressed when stored. Enabling compression conserves disk
space, but it incurs additional CPU overhead for inserting and
retrieving compressed rows.
Changing the COMPRESSION clause causes the database system
to read all the rows in the table and write them back to the
table in the changed format. If compression is enabled and you
subsequently disable it, records may become fragmented because
the space allowed for the record is no longer large enough.
3.5 – 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 benefits 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.
3.6 – FOR (table name)
Specifies the table or tables to which this 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 a , do not use this statement unless all areas
specified list of table names.
3.7 – 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.
3.8 – LOGGING
The LOGGING clause specifies that the ALTER STORAGE MAP statement
should be logged in the recovery-unit journal file (.ruj) and
after-image journal file (.aij). The LOGGING clause is the
default.
3.9 – NOLOGGING
The NOLOGGING clause specifies that the ALTER STORAGE MAP
statement should not be logged in the recovery-unit journal file
(.ruj) and after-image journal file (.aij).
3.10 – NO_PLACEMENT_VIA_INDEX
Negates the PLACEMENT VIA INDEX clause so that subsequent
records stored are not stored by means of the index named in
the PLACEMENT VIA INDEX clause. If you specify the ALTER STORAGE
MAP statement without the PLACEMENT VIA INDEX argument or the
NO PLACEMENT VIA INDEX argument, the statement executes as if
the clause specified on the CREATE STORAGE MAP statement or last
ALTER STORAGE MAP statement was used.
3.11 – NO_REORGANIZE
Disables the reorganize action for PARTITIONING IS NOT UPDATABLE.
3.12 – PAGES
Specifies that the target of the data reorganization is database
pages. All rows are checked to determine whether they are in the
correct storage area and if some are not, they are moved. Then,
all rows are checked if any should be moved within each storage
area, and these rows are moved if there is space on or closer to
the new target page.
3.13 – PARTITION name
Names the partition. The name can be a delimited identifier
if the dialect is set to 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.
3.14 – 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.
See the Oracle Rdb Guide to Database Design and Definition for
more information regarding partitioning.
3.15 – PLACEMENT_VIA_INDEX
See the CREATE STORAGE_MAP statement for details of the PLACEMENT
VIA INDEX argument.
3.16 – RENAME_PARTITION
Syntax options:
RENAME PARTITION partition-name TO new-partition-name
Specifies a new name for an existing storage map partition.
3.17 – REORGANIZE
Causes new rows and rows previously stored in specified tables
to be moved according to the partitions specified in the STORE
clause of the ALTER STORAGE MAP statement. The REORGANIZE clause
works for one or more areas in the storage maps.
For details of how rows are moved or not moved among storage
areas depending on whether or not the REORGANIZE argument is
specified, see the Oracle Rdb Guide to Database Design and
Definition.
3.18 – STORAGE MAP map name
Specifies the name of the storage map you want to alter.
3.19 – store-clause
A new storage map definition that replaces the existing storage
map. The store-clause allows you to specify which storage area
files will be used to store rows from the table. Note that:
o All rows of a table can be associated with a single storage
area.
o Rows of a table can be distributed among several storage
areas.
o Rows of a table can be systematically distributed
(horizontally partitioned) among several storage areas by
specifying upper limits on the values for a column in a
particular storage area.
The store-clause specifies only how you want to associate rows
with areas and not the manner in which rows are assigned to pages
within an area.
See the CREATE STORAGE_MAP statement for a description of the
syntax for the store-clause. However, the effect of the clause
in the ALTER STORAGE MAP statement depends on how you change the
existing storage map.
3.20 – STORE LISTS IN area name
Directs the database system to store the lists from tables in a
specified storage area. You can store lists from different tables
in the same area. You can create only one storage map for lists
within each database.
You must specify the default storage area for lists. This should
be the LIST STORAGE AREA specified on CREATE DATABASE, or if
none, the DEFAULT STORAGE AREA, or if none, then it will be
RDB$SYSTEM.
For more information, see the CREATE STORAGE_MAP statement.
3.21 – threshold-clause
Specifies SPAM thresholds for logical areas with uniform format
pages.
When you specify the THRESHOLD clause without enclosing it in
parentheses, you are specifying the default threshold values
for all areas specified in the ALTER STORAGE MAP statement.
You cannot alter the thresholds for any storage areas which are
part of the storage map. Only specify this clause for storage
areas being added to the storage area by the ALTER STORAGE MAP
statement.
To specify threshold values for a particular storage area,
specify the clause as part of the STORE clause and enclose the
THRESHOLD clause in parentheses. You can only specify threshold
values for new areas, not existing ones.
For examples of specifying the THRESHOLD clause, see the Oracle
Rdb Guide to Database Design and Definition. See the CREATE
STORAGE_MAP statement for a description of the THRESHOLDS clause.
4 – Examples
Example 1: Reorganizing storage area data using the ALTER STORAGE
MAP statement
The following example defines a new storage area, EMPIDS_MID2, to
handle the employee ID numbers from 601 to 900 and to reorganize
the data from an existing storage area, EMPIDS_OVER. The current
data that is stored for employees with employee ID numbers from
601 to 900 is moved according to the new limits. Because no AREA
or PAGE option is specified, the default method of reorganization
is by storage areas.
SQL> ALTER DATABASE FILENAME mf_personnel ADD STORAGE AREA
cont> EMPIDS_MID2 PAGE FORMAT IS MIXED;
SQL> ATTACH 'FILENAME mf_personneL';
SQL> ALTER STORAGE MAP EMPLOYEES_MAP
cont> STORE USING (EMPLOYEE_ID)
cont> IN EMPIDS_LOW WITH LIMIT OF ('00300')
cont> IN EMPIDS_MID WITH LIMIT OF ('00600')
cont> IN EMPIDS_MID2 WITH LIMIT OF ('00900')
cont> OTHERWISE IN EMPIDS_OVER
cont> REORGANIZE;
Example 2: Enabling compression with an ALTER STORAGE MAP
statement
The following example defines a new storage map, UNIFORM1_MAP,
and specifies thresholds for the logical area in the UNIFORM1
storage area. The ALTER STORAGE MAP statement is used to enable
row compression.
SQL> ALTER DATABASE FILENAME mf_personnel
cont> ADD STORAGE AREA UNIFORM1;
SQL> ATTACH 'FILENAME mf_personnel';
SQL> CREATE TABLE TEST (COL1 REAL);
SQL> CREATE STORAGE MAP UNIFORM1_MAP FOR TEST
cont> STORE IN UNIFORM1
cont> (THRESHOLDS ARE (80,90,95));
SQL> ALTER STORAGE MAP UNIFORM1_MAP
cont> STORE IN UNIFORM1
cont> ENABLE COMPRESSION;
Example 3: Changing an overflow partition to a WITH LIMIT OF
partition
To change the overflow partition to a partition defined with the
WITH LIMIT OF clause, you must use the REORGANIZE clause if you
want existing data that is stored in the overflow partition moved
to the appropriate storage area. For example, suppose the JOB_
HISTORY table contains a row with an EMPLOYEE_ID of 10001 and the
JH_MAP storage map is defined, as shown in the following example:
SQL> SHOW STORAGE MAP JH_MAP
JH_MAP
For Table: JOB_HISTORY
Compression is: ENABLED
Store clause: STORE USING (EMPLOYEE_ID)
IN PERSONNEL_1 WITH LIMIT OF ('00399')
IN PERSONNEL_2 WITH LIMIT OF ('00699')
OTHERWISE IN PERSONNEL_3
SQL>
If you want to change the PERSONNEL_3 storage area from an
overflow partition to a partition with a limit of 10,000 and
add the partition PERSONNEL_4, you must use the REORGANIZE clause
to ensure that Oracle Rdb moves existing rows to the new storage
area. The following example shows the ALTER STORAGE MAP statement
that accomplishes this change:
SQL> ALTER STORAGE MAP JH_MAP
cont> STORE USING (EMPLOYEE_ID)
cont> IN PERSONNEL_1 WITH LIMIT OF ('00399')
cont> IN PERSONNEL_2 WITH LIMIT OF ('00699')
cont> IN PERSONNEL_3 WITH LIMIT OF ('10000')
cont> IN PERSONNEL_4 WITH LIMIT OF ('10399')
cont> REORGANIZE;
SQL>
Example 4: Disabling Logging to the RUJ and AIJ files
SQL> ATTACH'FILENAME MF_PERSONNEL.RDB';
SQL> ALTER STORAGE MAP EMPLOYEES_MAP
cont> STORE
cont> USING (EMPLOYEE_ID)
cont> IN EMPIDS_LOW
cont> WITH LIMIT OF ('00200')
cont> IN JOBS
cont> (NOLOGGING)
cont> WITH LIMIT OF ('00400')
cont> OTHERWISE IN EMPIDS_OVER;
%RDB-W-META_WARN, metadata successfully updated with the reported warning
-RDMS-W-DATACMIT, unjournaled changes made; database may not be recoverable
Example 5: Disabled Area Scan for PARTITIONING IS NOT UPDATABLE
When a storage map is altered to be NOT UPDATABLE a REORGANIZE
scan is implicitly executed to check that all rows are in the
correct storage area according to the WITH LIMIT OF clauses in
the storage map. This scan can be time consuming, and an informed
database administrator may know that the data already conforms
fully to the storage map. The NO REORGANIZE clause is used
in the following example to avoid the extra I/O. The database
administrator must understand that use of this clause might lead
to incorrect query results (for sequential scans) if the storage
map does not reflect the correct row mapping.
SQL> SET FLAGS 'stomap_stats';
SQL> ALTER STORAGE MAP EMPLOYEES_MAP
cont> PARTITIONING IS NOT UPDATABLE
cont> NO REORGANIZE
cont> STORE
cont> USING (EMPLOYEE_ID)
cont> IN EMPIDS_LOW
cont> WITH LIMIT OF ('00200')
cont> IN EMPIDS_MID
cont> WITH LIMIT OF ('00400')
cont> OTHERWISE IN EMPIDS_OVER;
~As: starting map restructure...
~As: REORGANIZE needed to preserve strict partitioning
~As: NO REORGANIZE was used to override scan
~As: reads: async 0 synch 21, writes: async 7 synch 3
SQL>
SQL> SHOW STORAGE MAPS EMPLOYEES_MAP
EMPLOYEES_MAP
For Table: EMPLOYEES
Placement Via Index: EMPLOYEES_HASH
Partitioning is: NOT UPDATABLE
Strict partitioning was not validated for this table
Comment: employees partitioned by "00200" "00400"
Store clause: STORE
using (EMPLOYEE_ID)
in EMPIDS_LOW
with limit of ('00200')
in EMPIDS_MID
with limit of ('00400')
otherwise in EMPIDS_OVER
Compression is: ENABLED
SQL>
A subsequent ALTER STORAGE MAP . . . REORGANIZE statement will
validate the partitioning, as shown in the following example:
SQL> ALTER STORAGE MAP EMPLOYEES_MAP
cont> PARTITIONING IS NOT UPDATABLE
cont> REORGANIZE;
~As: starting map restructure...
~As: starting REORGANIZE...
~As: reorganize AREAS...
~As: processing rows from area 69
~As: processing rows from area 70
~As: processing rows from area 71
~As: reads: async 408 synch 22, writes: async 3 synch 0
SQL>
Example 6: Redefining a SQL routine that matches the WITH LIMIT
OF clause for the storage map
The ALTER STORAGE MAP command removes any old mapping routine
and redefines it when either the STORE clause is used, or if the
COMPILE option is used.
SQL> alter storage map EMPLOYEES_MAP
cont> store
cont> using (EMPLOYEE_ID)
cont> in EMPIDS_LOW
cont> with limit of ('00200')
cont> in EMPIDS_MID
cont> with limit of ('00400')
cont> in EMPIDS_OVER
cont> with limit of ('00800');
SQL>
SQL> show system function (source) EMPLOYEES_MAP;
Information for function EMPLOYEES_MAP
Source:
return
case
when (:EMPLOYEE_ID <= '00200') then 1
when (:EMPLOYEE_ID <= '00400') then 2
when (:EMPLOYEE_ID <= '00800') then 3
else -1
end case;