Changes the storage map definition for a relation. You can also
change which index Oracle Rdb uses when it stores new records
whether records will be stored in a compressed format, and
whether data will be reorganized.
1 – Format
(B)0[m[4mCHANGE[m [4mSTORAGE[m [4mMAP[m map-name qq>qqk
lqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqj
mwqqqqqqqqqqq>qqqqqqqqqqwqwqqqqqqqqqqqqqqq>qqqqqqqqqqqqqqwk
m> map-storage-clause qj m> change-relation-map-clause qjx
lqqqqqqqqqqqqqqqqqqqqqqqqqqq<qqqqqqqqqqqqqqqqqqqqqqqqqqqqqj
mq> [4mEND[m qqqqqwqqqqqqqq>qqqqqqwqqqqq> STORAGE MAP qqqq> .
mq> map-name qqqj
1.1 – map-name
The name of the storage map you want to modify.
1.2 – map-storage-clause
(B)0[mmap-storage-clause =
[4mSTORE[m qw> map-within-clause qqqqqqqqqqqqqqqqqqqqqqqqqqwqk
m> [4mUSING[m qw> field-name qw> map-within-clause qj x
mqqqqq , <qqqqqj x
lqqqqqqqqqqqqqqqqqqqqqqqq<qqqqqqqqqqqqqqqqqqqqqqqqqqqqqj
mqwqqqqqqqqqqqqq>qqqqqqqqqqqqqqwqq>
m> default-threshold-clause qj
Used to define a storage map. This clause lets you specify which
storage area files will be used to store rows from the relation:
o All rows of a relation can be associated with a single storage
area.
o Rows of a relation can be randomly distributed among several
storage areas.
o Rows of a relation 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.
If you omit the storage map definition, the default is to store
all the rows for a relation in the main RDB$SYSTEM storage area.
1.2.1 – map-within-clause
(B)0[mmap-within-clause =
[4mWITHIN[m qwq> area-name qwqqqqqqqqqqqqqq>qqqqqqqqqqqqqqqwqk
x m> [4mWITH[m [4mLIMIT[m OF qw> literal qwj x
x mqqqq , <qqqj x
xlqqqqqqqqqqqqqqqqqqqqqqq<qqqqqqqqqqqqqqqqqqqqqqj
xmwqqqqqqqqqqqqqqqqqqqqqq>qqqqqqqqqqqqqqqqqqwqwqk
x mq> [4mFOR[m qwqwqw> rel-name qwqqqqqqqqqqqqqqqu x x
x x x mqqqq , <qqqqj x x x
x x mw> relation-name.field-name qwj x x
x x mqqqqqqqqqqqq , <qqqqqqqqqqqqj x x
x mqqqqqqqqqqqq , <qqqqqqqqqqqqqqqqqqj x
x lqqqqqqqqqqqqqqqqqqqqqqqq<qqqqqqqqqqqqqqqqqqqqj
x mqwqqqqqqqqqq>qqqqqqqqqqwqqwqqqqqqqqq>
x mq> threshold-clause qj x
mqqqqqqqqqqqqq ; <qqqqqqqqqqqj
1.2.1.1 – area-name
The name of the storage area you want records stored in. You
must have defined this storage area with either the DEFINE
DATABASE statement or the DEFINE STORAGE AREA clause of the
CHANGE DATABASE statement before you refer to it in the store
clause.
1.2.1.2 – WITH_LIMIT_OF
The maximum value for the index key that will reside in the
specified storage area.
The number of literals in this clause must be the less than or
equal to the number of fields in the USING clause. Repeat this
clause to partition the index entries among multiple storage
areas.
When you define a multisegmented index using multiple keys and
use the STORE USING...WITH LIMITS clauses, if the values for the
first key are all the same, then set the limit for the first key
at that value. By doing this, you ensure that the value of the
second key determines the storage area in which each record will
be stored.
Note that the last storage area you specify CANNOT have a WITH
LIMIT OF clause associated with it.
If you change the limits for a storage area, records that were
already stored in the area are not moved according to the new
limit clause. However, new records will be stored into the
relation according to the new limits you specify.
1.2.1.3 – rel-name
The name of the relation whose segmented strings you want to
store in the specified storage area. If you want to store the
segmented strings of more than one relation in the storage area,
separate the names of the relations with commas.
1.2.1.4 – relation-name.field-name
The name of the relation and segmented string field that you want
to store in the specified storage area. If you want to store more
than one segmented string field in the storage area, separate the
list items with commas.
1.2.1.5 – threshold-clause
(B)0[mthreshold-clause=
qq> [4mTHRESHOLDS[m ARE qqqqqqqqqqk
lqqqqqqqqqqqqq<qqqqqqqqqqqqqj
mq> ( qq> val1 qwqqqqqqqqqqq>qqqqqqqqqqwq> ) qq>
mq> ,val2 qqwqqqqq>qqqqu
mq> ,val3 qj
Specifies associated threshold values for each storage area with
uniform format that is specified in the map-within-clause. By
setting threshold values, you can make sure that Oracle Rdb does not
overlook a page with sufficient space to store compressed data.
The threshold values specify when the page is marked as FULL
in the SPAM page free space inventory lists. For example,
if you set default values of 70, 85, and 95 percent, ranges
of guaranteed free space on each data page are 30, 15, and 5
percent, respectively. If you do not set default values, the
values are (0,0,0). With values of (0,0,0), Oracle Rdb will use the
record length when setting the SPAM fullness. Oracle Rdb will never
store a record on a page at threshold 3. The value you set for
the highest threshold can be used to reserve space on the page
for future record growth.
If you specify a value of 40 for the "val1" parameter, but do not
specify values for the "val2" or "val3" parameters, the threshold
values will be set at (40,100,100).
If you use data compression, you should use logical area
thresholds to obtain optimum storage performance.
1.2.2 – USING field-name
The names of the fields whose values will be used as limits for
partitioning the relation across multiple storage areas. Oracle Rdb
compares values in the fields to the values in the WITH LIMIT OF
clause to determine where to initially store the record.
1.2.3 – default-threshold-clause
(B)0[mdefault-threshold-clause =
qq> [4mDEFAULT[m [4mTHRESHOLDS[m ARE qqk
lqqqqqqqqqqqqq<qqqqqqqqqqqqqj
mq> ( qq> val1 qwqqqqqqqqqqq>qqqqqqqqqqwq> ) qq>
mq> ,val2 qqwqqqqq>qqqqu
mq> ,val3 qj
Specifies associated threshold values for each storage area with
uniform format that is NOT specified in the map-within-clause. By
setting threshold values, you can make sure that Oracle Rdb does not
overlook a page with sufficient space to store compressed data.
The threshold values specify when the page is marked as FULL
in the SPAM page free space inventory lists. For example,
if you set default values of 70, 85, and 95 percent, ranges
of guaranteed free space on each data page are 30, 15, and 5
percent, respectively. If you do not set default values, the
values are (0,0,0). With values of (0,0,0), Oracle Rdb will use the
record length when setting the SPAM fullness. Oracle Rdb will never
store a record on a page at threshold 3. The value you set for
the highest threshold can be used to reserve space on the page
for future record growth.
If you specify a value of 40 for the "val1" parameter, but do not
specify values for the "val2" or "val3" parameters, the threshold
values will be set at (40,100,100).
If you use data compression, you should use logical area
thresholds to obtain optimum storage performance.
1.3 – change-relation-map-clause
(B)0[mchange-relation-map-clause =
qqwwq> [4mNO[m [4mPLACEMENT[m [4mVIA[m [4mINDEX[m qqqqqqqqqqqqqqqwq>
xmq> [4mPLACEMENT[m [4mVIA[m [4mINDEX[m qq> index-name qqu
twq> [4mDISABLE[m qqwq> [4mCOMPRESSION[m qqqqqqqqqqqqu
xmq> [4mENABLE[m qqqj x
mqq> [4mREORGANIZE[m qqqqqwqqqqqq>qqqqwqqqqqqqqqj
tq> [4mAREAS[m qqu
mq> [4mPAGES[m qqj
1.3.1 – PLACEMENT_VIA_INDEX
Indicates that Oracle Rdb should attempt to store a record in a
way that optimizes access to that record via the indicated path.
Oracle Rdb chooses a target page for any record being stored by
rules that take into account the type of index defined (sorted or
hashed), the type of storage areas involved (uniform or mixed),
and how indexes and relations are assigned to each other.
The index named in the PLACEMENT VIA INDEX clause must be defined
for the same relation that the storage map is being defined for.
1.3.2 – NO_PLACEMENT_VIA_INDEX
Negates the PLACEMENT VIA INDEX clause, so that subsequent
records stored are not stored via the index named in the
PLACEMENT VIA INDEX clause. This option is only available on the
CHANGE STORAGE MAP statement. If you specify the CHANGE STORAGE
MAP statement without the PLACEMENT VIA INDEX option or the NO
PLACEMENT VIA INDEX option, the statement executes as if the
clause specified on the DEFINE STORAGE MAP statement or last
CHANGE STORAGE MAP statement had been used.
1.3.3 – COMPRESSION clause
Specifies whether data compression will be enabled or disabled
for the records when they are stored. ENABLE COMPRESSION is the
default.
1.3.4 – REORGANIZE clause
Causes rows previously stored in specified relations to be moved
according to the partitions specified in the CHANGE STORAGE MAP
statement.
2 – More
To change a storage map for a relation with the CHANGE STORAGE
MAP statement, you need the Oracle Rdb CHANGE privilege for the
relation.
If the database is created with the DICTIONARY IS REQUIRED
option, you must invoke the database by path name, rather than
file name, before you issue this statement.
You must specify either a store-clause, a [NO]PLACEMENT VIA INDEX
clause, a REORGANIZE clause, or a COMPRESSION clause in a CHANGE
STORAGE MAP statement.
In the change-relation-map-clause, you can select one or more of
the three clauses ([NO]PLACEMENT VIA INDEX clause, REORGANIZE
clause, or COMPRESSION clause) in any order, but you cannot
repeat a clause. Note that when the REORGANIZE clause is used,
records are moved and assigned to new dbkeys.
If you omit the store-clause in the CHANGE STORAGE MAP statement,
you can create a second index by mistake.
You must execute this statement in a read/write transaction.
If there is no active transaction and you issue this statement,
Oracle Rdb starts a read/write transaction implicitly.
Other users are allowed to be attached to the database when you
issue the CHANGE STORAGE MAP statement. However, they are not
allowed to be using the relation whose map is being changed.
3 – Examples
Example 1
The following example disables compression for the CANDIDATES_MAP
storage map:
RDO> CHANGE STORAGE MAP CANDIDATES_MAP
cont> DISABLE COMPRESSION
cont> END CANDIDATES_MAP STORAGE MAP.
Example 2
The following example assigns new limits for storage areas:
RDO> CHANGE STORAGE MAP EMPLOYEES_MAP
cont> STORE USING EMPLOYEE_ID
cont> WITHIN EMPIDS_LOW WITH LIMIT OF "00300";
cont> EMPIDS_MID WITH LIMIT OF "00600";
cont> EMPIDS_OVER
cont> END EMPLOYEES_MAP STORAGE MAP.
Current data will not be moved according to the new limits.
However, when new data is stored, it will be stored according
to the new limits in the storage map.
Example 3
The following example defines a new storage area EMPIDS_MID2 to
handle the employee IDs 600-900, and to reorganize the data from
one existing storage area, EMPIDS_OVER. The current data which
is stored within the limits of employee IDs 601-900 will be moved
according to the new limits. When the new data is stored, it
will be stored according to the new limits set in the storage map
definition.
RDO> CHANGE STORAGE MAP EMPLOYEES_MAP
cont> STORE USING EMPLOYEE_ID
cont> WITHIN EMPIDS_LOW WITH LIMIT of "00300";
cont> EMPIDS_MID WITH LIMIT OF "00600";
cont> EMPIDS_MID2 WITH LIMIT OF "00900";
cont> EMPIDS_OVER
cont> REORGANIZE
cont> END EMPLOYEES_MAP STORAGE MAP.