Creates a storage map for a relation. A storage map associates
a relation with a particular storage area or areas. The DEFINE
STORAGE MAP statement allows you to specify:
o Which storage areas the records in a relation will be stored
in
o Whether an index will be used to choose a target location for
storing the record
o Whether data compression will be enabled when the records are
stored
o Which storage areas segmented strings will be stored in
Example:
RDO> DEFINE STORAGE MAP JOBS_MAP FOR JOBS
cont> STORE WITHIN JOBS
cont> END JOBS_MAP STORAGE MAP.
1 – Format
(B)0[m[4mDEFINE[m [4mSTORAGE[m [4mMAP[m qqqqqqqqqqq> map-name qqqqqqqqqk
lqqqqqqqqqqqqqqqqqqqqqqqq<qqqqqqqqqqqqqqqqqqqqqqqqj
mqqqqqqwqqqqqqqqqqqqqqqqq>qqqqqqqqqqqqqqqwqqqqqqqqk
mq> [4mDESCRIPTION[m IS /* text */ qqj x
lqqqqqqqqqqqqqqqqqqqqqqqq<qqqqqqqqqqqqqqqqqqqqqqqqj
mq> [4mFOR[m qqqqq> relation-map-clause qqqqqqqqqqqqqqqk
lqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqq<qqqqqqqqqqqqqqqqj
mq> [4mEND[m qqwqqqqqqq>qqqqqqwqqwqqqqqqqq>qqqqqqqwq> .
mq> map-name qqj mq> [4mSTORAGE[m [4mMAP[m qj
1.1 – map-name
Specifies the name for the storage map being defined.
1.2 – text
A text string that adds a comment to the storage map definition.
1.3 – relation-map-clause
(B)0[mrelation-map-clause =
qqwq> relation-name qqqq> RELATION qqwqqqqqqqk
mq> [4mSEGMENTED[m [4mSTRINGS[m qqqqqqqqqqqqqj x
lqqqqqqqqqqqqqqqqqqqq<qqqqqqqqqqqqqqqqqqqqqqj
mqqwqwqq> map-storage-clause qqqqqqqqqqqqqqqqqqwqwq>
x tqq> [4mPLACEMENT[m [4mVIA[m [4mINDEX[m qq> index-name qqu x
x mwqq> [4mDISABLE[m qqwq> [4mCOMPRESSION[m qqqqqqqqqqj x
x mqq> [4mENABLE[m qqqj x
mqqqqqqqqqqqqqqqqqqqqqqqqq<qqqqqqqqqqqqqqqqqqqj
1.3.1 – relation-name
Specifies the relation to which the storage map will apply. The
relation must already be defined and cannot have a storage map
associated with it.
1.3.2 – SEGMENTED_STRINGS
Specified when you want to store segmented strings in multiple
storage areas. Use the store-clause to specify the storage areas
in which you wish to store the segmented strings.
1.3.3 – 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
1.3.3.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.3.3.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 map-
within-clause.
1.3.3.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.
1.3.3.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.3.3.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.3.3.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.3.3.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.3.3.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.4 – 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.
If the index named is a hashed index, the storage area named must
have a MIXED page format. If the hashed index definition and the
storage map for the relation designate the same storage area,
then the record is stored on the same page as the hashed index
node. Otherwise, Oracle Rdb uses the same relative page within the
data storage area as the target page.
If the index named is a sorted index, Oracle Rdb finds the dbkey of
the next lowest record to the one being stored and uses the page
number in the dbkey as the target page.
1.3.5 – COMPRESSION clause
Specifies whether data compression will be enabled or disabled
when the records are stored. ENABLE COMPRESSION is the default.
2 – More
To define a storage map for a relation, you need the Oracle Rdb
DEFINE privilege to the relation.
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 DEFINE STORAGE MAP statement. However, they are
not allowed to be using the relation whose storage map is being
defined.
3 – Examples
Example 1
The following statement defines a storage map for the employees
relation in MF_PERSONNEL.
DEFINE STORAGE MAP EMPLOYEES_MAP
DESCRIPTION IS /* EMPLOYEES partitioned by EMPLOYEE_ID */
FOR EMPLOYEES RELATION
STORE USING EMPLOYEE_ID
WITHIN
EMPIDS_LOW WITH LIMIT OF "00200";
EMPIDS_MID WITH LIMIT OF "00400";
EMPIDS_OVER
END EMPLOYEES_MAP STORAGE MAP.
Example 2
The following example shows how to define a storage map that
stores all records from a relation in the named storage area:
RDO> DEFINE STORAGE MAP SALARY_HISTORY_MAP
cont> DESCRIPTION IS /* Map for salary history records */
cont> FOR SALARY_HISTORY RELATION
cont> STORE WITHIN SALARY_HISTORY
cont> END SALARY_HISTORY_MAP STORAGE MAP.
This statement stores all the records from the SALARY_HISTORY
relation into the storage area, SALARY_HISTORY. SALARY_HISTORY
is the name of a storage area that was created with the DEFINE
DATABASE statement.
Example 3
The following example uses the PLACEMENT VIA INDEX clause to
store records in a storage area according to a hashed index:
RDO> DEFINE STORAGE MAP DEPARTMENTS_MAP
cont> FOR DEPARTMENTS RELATION
cont> STORE WITHIN DEPARTMENTS
cont> PLACEMENT VIA INDEX DEPARTMENTS_INDEX
cont> END DEPARTMENTS_MAP STORAGE MAP.
Example 4
The following example defines a database and two relations that
contain segmented strings. The two relations are mapped along
with their segmented strings to separate storage areas.
DEFINE DATABASE SEGSTR_0
DESCRIPTION IS
/*
This database is used to show the Oracle Rdb
segmented string support. Two relations that contain
segmented strings are mapped along with their segmented
string data to separate storage areas.
*/
DICTIONARY IS NOT USED
DEFINE STORAGE AREA RDB$SYSTEM
FILENAME 'SEGSTR_0'
END
DEFINE STORAGE AREA SEGSTR_1
FILENAME 'SEGSTR_1'
END
DEFINE STORAGE AREA SEGSTR_2
FILENAME 'SEGSTR_2'
END
DEFINE STORAGE AREA SEGSTR_SS1
FILENAME 'SEGSTR_SS1'
END
DEFINE STORAGE AREA SEGSTR_SS2
FILENAME 'SEGSTR_SS2'
END
SEGMENTED STRING STORAGE AREA IS SEGSTR_SS2.
DEFINE FIELD STANDARD_DATE
DATATYPE DATE.
DEFINE FIELD TITLE
DATATYPE TEXT SIZE 50.
DEFINE FIELD DIARY_ENTRY
DATATYPE SEGMENTED STRING
SUB_TYPE TEXT
SEGMENT_LENGTH 200.
DEFINE RELATION DAILY_DIARY
DESCRIPTION /* Simple diary */.
ENTRY_DATE BASED ON STANDARD_DATE.
TITLE.
DIARY_ENTRY.
END.
DEFINE RELATION SPECIAL_EVENTS
DESCRIPTION IS
/* Special events - birthdays, anniversaries, etc. */.
EVENT_DATE BASED ON STANDARD_DATE.
EVENT_NAME BASED ON TITLE.
EVENT_DESCRIPTION BASED ON DIARY_ENTRY.
SPECIAL_INSTRUCTIONS BASED ON DIARY_ENTRY.
END.
DEFINE STORAGE MAP DAILY_DIARY_MAP
DESCRIPTION IS
/* Diary entries are randomly partitioned over two areas */
FOR DAILY_DIARY
STORE WITHIN SEGSTR_1; SEGSTR_2
END.
DEFINE STORAGE MAP SPECIAL_EVENTS_MAP
DESCRIPTION IS
/* Special events entries are stored in one area */
FOR SPECIAL_EVENTS
STORE WITHIN SEGSTR_1
END.
DEFINE STORAGE MAP DIARY_TEXT
DESCRIPTION IS
/* Keep segmented strings in separate areas */
FOR SEGMENTED STRINGS
STORE WITHIN
SEGSTR_SS1 FOR DAILY_DIARY, SPECIAL_EVENTS.EVENT_DESCRIPTION;
SEGSTR_SS2 FOR DAILY_DIARY;
SEGSTR_1 FOR SPECIAL_EVENTS.SPECIAL_INSTRUCTIONS;
RDB$SYSTEM ! RDB$SYSTEM stores other segmented strings
END.
Example 5
In the following example, three new storage areas (EMPIDS_LOW_
UNIFORM, EMPIDS_MID_UNIFORM, and EMPIDS_OVER_UNIFORM) and a new
relation (EMPLOYEES2) are defined in the MF_PERSONNEL database.
Then a storage map is defined so that new EMPLOYEES2 records are
stored in either the EMPIDS_LOW_UNIFORM, EMPIDS_MID_UNIFORM, or
EMPIDS_OVER_UNIFORM storage areas, depending on the value stored
in the LAST_NAME field.
RDO> CHANGE DATABASE FILENAME MF_PERSONNEL
cont> DEFINE STORAGE AREA EMPIDS_LOW_UNIFORM
cont> FILENAME EMPIDS_LOW_UNIFORM.RDA
cont> END EMPIDS_LOW_UNIFORM STORAGE AREA
cont> DEFINE STORAGE AREA EMPIDS_MID_UNIFORM
cont> FILENAME EMPIDS_MID_UNIFORM.RDA
cont> END EMPIDS_MID_UNIFORM STORAGE AREA
cont> DEFINE STORAGE AREA EMPIDS_OVER_UNIFORM
cont> FILENAME EMPIDS_OVER_UNIFORM.RDA
cont> END EMPIDS_OVER_UNIFORM STORAGE AREA.
RDO> !
RDO> INVOKE DATABASE FILENAME MF_PERSONNEL
RDO> !
RDO> DEFINE RELATION EMPLOYEES2
cont> DESCRIPTION IS /* new relation for testing */.
cont> EMPLOYEE_ID BASED ON ID_NUMBER.
cont> /* Generic last name */
cont> LAST_NAME DATATYPE IS TEXT SIZE IS 14.
cont> /* Generic first name */
cont> FIRST_NAME DATATYPE IS TEXT SIZE IS 10.
cont> /* Generic middle initial */
cont> MIDDLE_INITIAL DATATYPE IS TEXT SIZE IS 1.
cont> /* Street name */
cont> ADDRESS_DATA_1 DATATYPE IS TEXT SIZE IS 25.
cont> /* Mail stops, suite addresses, street numbers, etc. */
cont> ADDRESS_DATA_2 DATATYPE IS TEXT SIZE IS 25.
cont> /* City name */
cont> CITY DATATYPE IS TEXT SIZE IS 20.
cont> /* State abbreviation (or DISTRICT) */
cont> STATE DATATYPE IS TEXT SIZE IS 2.
cont> /* Postal code (in US = ZIP) */
cont> POSTAL_CODE DATATYPE IS TEXT SIZE IS 5.
cont> /* M, F */
cont> SEX DATATYPE IS TEXT SIZE IS 1.
cont> BIRTHDAY BASED ON STANDARD_DATE.
cont> /* A number */
cont> STATUS_CODE DATATYPE IS TEXT SIZE IS 1.
cont> END EMPLOYEES2 RELATION.
RDO> !
RDO> DEFINE STORAGE MAP EMP2_LAST_NAME_MAP
cont> FOR EMPLOYEES2 RELATION
cont> STORE USING LAST_NAME
cont> WITHIN EMPIDS_LOW_UNIFORM WITH LIMIT OF "IRONS"
cont> THRESHOLDS ARE (70,80,95);
cont> EMPIDS_MID_UNIFORM WITH LIMIT OF "QUIST";
cont> EMPIDS_OVER_UNIFORM
cont> DEFAULT THRESHOLDS ARE (60,70,80)
cont> END EMP2_LAST_NAME_MAP STORAGE MAP.
Note that the EMPIDS_LOW_UNIFORM storage area receives the
threshold values of (70,80,95) specified with the THRESHOLDS
clause, and the EMPIDS_MID_UNIFORM and EMPIDS_OVER_UNIFORM
storage areas receive the default threshold values of (60,70,80)
specified with the DEFAULT THRESHOLDS clause.