Associates a table with one or more storage areas in a multifile
database. The CREATE STORAGE MAP statement specifies a storage
map that controls which lists or rows of a table are stored in
which storage areas.
In addition to creating storage maps, the CREATE STORAGE MAP
statement has options that control:
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 partitioning keys can be modified.
o Whether the table is partitioned vertically, horizontally, or
both.
o Whether logging is enabled or disabled for the duration of
this operation
1 – Environment
You can use the CREATE 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
CREATE STORAGE MAP <map-name> --+
+------------------------------+
++-----------------------------------+-+
+-> STORED NAME IS <stored-name> ---+ |
+--------------------------------------+
+> FOR <table-name> ++-------------------------------------++-+
| |++> ENABLE -+> COMPRESSION -----------+| |
| ||+> DISABLE + || |
| |+-> PLACEMENT VIA INDEX <index-name> -+| |
| |+-> partition-updatable-clause -------+| |
| |+-> threshold-clause -----------------+| |
| |+-> LOGGING ---------------------------+ |
| |+-> NOLOGGING -------------------------+ |
| |+-> COMMENT IS -+-> '<string>' ---+----+ |
| | +-------- / <-----+ | |
| +-----------------<---------------------+ |
| +---------------------------------------------------------+
| +-+----------------------+-+------------------------------->
| +-> partition-clause --+ |
+> store-lists-clause ---------+
partition-updatable-clause =
---> PARTITIONING IS -+-> NOT UPDATABLE -+-->
+-> UPDATABLE -----+
threshold-clause =
--+-> THRESHOLD -+-> IS -+-> ( --> <val1> --> ) ---------+->
| +-> OF -+ |
| |
+-> THRESHOLDS -+-> ARE -+-----------+ |
+-> OF -+ | |
+----------------------------------+ |
+-> ( --> <val1> -+---------------------------+-> ) -+
+-> , <val2> -+-------------+
+-> , <val3> -+
partition-clause =
-+> STORE -+------------------++--------------------+> store-clause -+>
| +> columns-clause -++> store-attributes -+ |
+------------------------------- <----------------------------------+
columns-clause =
-> COLUMNS --> ( -+-> <column-name> -+-> ) -->
+-------- , <------+
store-attributes =
--+-+-+-> ENABLE --+-> COMPRESSION -+-+-->
| | +-> DISABLE -+ | |
| +-> thresholds-clause ----------+ |
| +-> VERTICAL PARTITION <name> --+ |
+--------------- <------------------+
store-clause =
---+-> 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 -----------------------+
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 – across-clause
Associates the table with two or more storage areas.
3.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.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.
3.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.
3.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.
3.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.
3.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.
3.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.
3.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.
3.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.
3.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.
3.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.
3.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.
3.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.
3.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.
3.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.
3.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.
3.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;
3.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.
3.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.
3.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.
3.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.
3.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.
3.24 – using-clause
Specifies columns whose values are used as limits for
partitioning the table horizontally across multiple storage
areas.
3.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.
4 – Examples
Example 1: Defining storage maps for a multifile database
This example shows the definition of storage maps for a multifile
database. The tables named in the CREATE STORAGE MAP statements
have the same definitions as those in the sample database.
See the CREATE STORAGE_AREA clause for an example of a CREATE
DATABASE statement with CREATE STORAGE AREA clauses that create
the storage areas referred to in this example.
SQL> -- Declare the database as the default:
SQL> ATTACH 'FILENAME multifile_example';
SQL> --
SQL> CREATE STORAGE MAP EMPLOYEE_MAP FOR EMPLOYEES
cont> STORE USING (EMPLOYEE_ID)
cont> IN EMPID_LOW WITH LIMIT OF ('00200')
cont> IN EMPID_MID WITH LIMIT OF ('00500')
cont> OTHERWISE IN EMPID_OVER;
SQL> --
SQL> CREATE STORAGE MAP RESUME_MAP
cont> STORE LISTS IN EMP_INFO FOR (TABLE1, TABLE2, TABLE3)
cont> IN RDB$SYSTEM;
SQL> --
SQL> CREATE STORAGE MAP JOB_HISTORY_MAP FOR JOB_HISTORY
cont> STORE IN HISTORIES;
SQL> --
SQL> CREATE STORAGE MAP SALARY_HISTORY_MAP FOR SALARY_HISTORY
cont> STORE IN HISTORIES;
SQL> --
SQL> CREATE STORAGE MAP JOBS_MAP FOR JOBS
cont> STORE IN CODES;
SQL> --
SQL> CREATE STORAGE MAP DEPARTMENTS_MAP FOR DEPARTMENTS
cont> STORE IN CODES;
SQL> --
SQL> CREATE STORAGE MAP COLLEGES_MAP FOR COLLEGES
cont> STORE IN CODES;
SQL> --
SQL> CREATE STORAGE MAP DEGREES_MAP FOR DEGREES
cont> STORE IN EMP_INFO;
SQL> --
SQL> CREATE STORAGE MAP WORK_STATUS_MAP FOR WORK_STATUS
cont> STORE IN HISTORIES;
SQL> --
SQL> --
SQL> -COMMIT;
SQL> --
Example 2: Defining storage maps that place and override
thresholds on uniform storage areas
SQL> CREATE DATABASE FILENAME birdlist
cont> CREATE STORAGE AREA AREA1
cont> CREATE STORAGE AREA AREA2
cont> CREATE STORAGE AREA AREA3
cont> CREATE STORAGE AREA AREA4
cont> CREATE TABLE SPECIES
cont> ( GENUS CHAR (30),
cont> SPECIES CHAR (30),
cont> COMMON_NAME CHAR (40),
cont> FAMILY_NUMBER INT (3),
cont> SPECIES_NUMBER INT (3)
cont> )
cont> CREATE INDEX I1 ON SPECIES (FAMILY_NUMBER)
cont> CREATE TABLE SIGHTING
cont> ( SPECIES_NUMBER INT (3),
cont> COMMON_NAME CHAR (40),
cont> CITY CHAR (20),
cont> STATE CHAR (20),
cont> SIGHTING_DATE DATE ANSI,
cont> NOTES_NUMBER INT (5))
cont> CREATE INDEX I2 ON SIGHTING (SPECIES_NUMBER)
cont> CREATE TABLE FIELD_NOTES
cont> ( WEATHER CHAR (30),
cont> TIDE CHAR (15),
cont> SPECIES_NUMBER INT (3),
cont> SIGHTING_TIME TIMESTAMP(2),
cont> NOTES CHAR (500),
cont> NOTES_NUMBER INT (5))
cont> CREATE INDEX I3 ON FIELD_NOTES (NOTES_NUMBER)
cont> ;
SQL> --
SQL> -- The following CREATE STORAGE MAP statements place and
SQL> -- override thresholds on uniform storage area.
SQL> --
SQL> -- Note that the default threshold clause for the
SQL> -- storage map is not enclosed in parentheses, but each
SQL> -- threshold clause associated with a particular area is.
SQL> --
SQL> CREATE STORAGE MAP M1 FOR SPECIES
cont> THRESHOLDS ARE (30, 50, 80)
cont> ENABLE COMPRESSION
cont> PLACEMENT VIA INDEX I1
cont> STORE
cont> IN AREA1
cont> (THRESHOLD (10) );
SQL> --
SQL> CREATE STORAGE MAP M2 FOR SIGHTING
cont> THRESHOLD IS (40)
cont> STORE
cont> RANDOMLY ACROSS (
cont> AREA1 (THRESHOLD OF (10) ),
cont> AREA2 (THRESHOLDS ARE (30, 50, 98) ),
cont> AREA3
cont> );
SQL> --
SQL> CREATE STORAGE MAP M3 FOR FIELD_NOTES
cont> THRESHOLDS OF (50,70,90)
cont> STORE
cont> USING (SPECIES_NUMBER, NOTES_NUMBER)
cont> IN AREA1
cont> (THRESHOLDS OF (20, 80, 90) )
cont> WITH LIMIT OF (30, 88)
cont> IN AREA2
cont> WITH LIMIT OF (40, 89)
cont> IN AREA3
cont> WITH LIMIT OF (50, 90)
cont> OTHERWISE IN AREA4
cont> (THRESHOLDS ARE (20, 30, 40));
SQL> --
SQL> SHOW STORAGE MAP *;
User Storage Maps in database with filename birdlist
M1
For Table: SPECIES
Placement Via Index: I1
Partitioning is: UPDATABLE
Store clause: STORE
IN AREA1
(THRESHOLD (10) )
Partition information for storage map:
Compression is: ENABLED
Partition: (1) SYS_P00062
Storage Area: AREA1
M2
For Table: SIGHTING
Partitioning is: UPDATABLE
Store clause: STORE
RANDOMLY ACROSS (
AREA1 (THRESHOLD OF (10) ),
AREA2 (THRESHOLDS ARE (30, 50, 98) ),
AREA3
)
Partition information for storage map:
Compression is: ENABLED
Partition: (1) SYS_P00063
Storage Area: AREA1
Partition: (2) SYS_P00064
Storage Area: AREA2
Partition: (3) SYS_P00065
Storage Area: AREA3
M3
For Table: FIELD_NOTES
Partitioning is: UPDATABLE
Store clause: STORE
USING (SPECIES_NUMBER, NOTES_NUMBER)
IN AREA1
(THRESHOLDS OF (20, 80, 90) )
WITH LIMIT OF (30, 88)
IN AREA2
WITH LIMIT OF (40, 89)
IN AREA3
WITH LIMIT OF (50, 90)
OTHERWISE IN AREA4
(THRESHOLDS ARE (20, 30, 40))
Partition information for storage map:
Compression is: ENABLED
Partition: (1) SYS_P00066
Storage Area: AREA1
Partition: (2) SYS_P00067
Storage Area: AREA2
Partition: (3) SYS_P00068
Storage Area: AREA3
Partition: (4) SYS_P00069
Storage Area: AREA4
SQL> --
SQL> ROLLBACK;
Example 3: Creating a storage map that stores lists
This example creates a storage map that stores lists on specific
storage areas.
SQL> CREATE DATABASE FILENAME test
cont> CREATE STORAGE AREA LISTS1 PAGE FORMAT IS MIXED
cont> CREATE STORAGE AREA LISTS2 PAGE FORMAT IS MIXED
cont>
cont> CREATE TABLE EMPLOYEES
cont> (EMP_ID CHAR(5),
cont> RESUME LIST OF BYTE VARYING);
SQL> --
SQL> CREATE STORAGE MAP LISTS_MAP
cont> STORE LISTS IN
cont> (LISTS1,LISTS2) FOR (EMPLOYEES.RESUME)
cont> FILL SEQUENTIALLY
cont> IN RDB$SYSTEM;
Example 4: Creating an alternate map
This example following storage map shows an alternate mapping for
the EMPLOYEES table in the same MF_PERSONNEL database.
SQL> create storage map EMPLOYEES_MAP
cont> for EMPLOYEES
cont> placement via index EMPLOYEES_HASH
cont> -- store the primary information horizontally partitioned
cont> -- across the areas EMPIDS_LOW, EMPIDS_MID and EMPIDS_OVER
cont> -- disable compress because these columns are accessed often
cont> store
cont> columns (EMPLOYEE_ID, LAST_NAME,
cont> FIRST_NAME, MIDDLE_INITIAL)
cont> disable compression
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
cont>
cont> -- place all the address information in EMP_INFO
cont> -- make sure these character columns are compressed
cont> -- to remove the trailing spaces
cont> store
cont> columns (ADDRESS_DATA_1, ADDRESS_DATA_2, CITY, STATE,
cont> POSTAL_CODE)
cont> enable compression
cont> in EMP_INFO
cont>
cont> -- the remaining columns get
cont> -- written randomly over these areas
cont> store
cont> enable compression
cont> randomly across (SALARY_HISTORY, JOBS);
Example 5: Disabling logging and naming horizontal and vertical
partitions
SQL> CREATE DATABASE FILENAME birdlist
cont> CREATE STORAGE AREA AREA1
cont> CREATE STORAGE AREA AREA2
cont> CREATE STORAGE AREA AREA3
cont> CREATE STORAGE AREA AREA4
cont> CREATE STORAGE AREA AREA5
cont> CREATE STORAGE AREA AREA6
cont> CREATE STORAGE AREA AREA7
cont> CREATE STORAGE AREA AREA8
cont> CREATE TABLE SPECIES
cont> ( GENUS CHAR (30),
cont> SPECIES CHAR (30),
cont> COMMON_NAME CHAR (40),
cont> FAMILY_NUMBER INT (3),
cont> SPECIES_NUMBER INT (3)
cont> )
cont> CREATE INDEX I1 ON SPECIES (FAMILY_NUMBER)
cont> CREATE TABLE SIGHTING
cont> ( SPECIES_NUMBER INT (3),
cont> COMMON_NAME CHAR (40),
cont> CITY CHAR (20),
cont> STATE CHAR (20),
cont> SIGHTING_DATE DATE ANSI,
cont> NOTES_NUMBER INT (5))
cont> CREATE INDEX I2 ON SIGHTING (SPECIES_NUMBER)
cont> CREATE TABLE FIELD_NOTES
cont> ( WEATHER CHAR (30),
cont> TIDE CHAR (15),
cont> SIGHTING_TIME TIMESTAMP(2),
cont> NOTES CHAR (500),
cont> NOTES_NUMBER INT (5),
cont> SPECIES_NUMBER INT (3))
cont> CREATE INDEX I3 ON FIELD_NOTES (NOTES_NUMBER);
SQL> --
SQL> -- Note that the default threshold clause for the
SQL> -- storage map is not enclosed in parentheses, but each
SQL> -- threshold clause associated with a particular area is enclosed
SQL> -- in parentheses.
SQL> --
SQL> CREATE STORAGE MAP M1 FOR SPECIES
cont> THRESHOLDS ARE (30, 50, 80)
cont> ENABLE COMPRESSION
cont> PLACEMENT VIA INDEX I1
cont> NOLOGGING
cont> COMMENT IS 'Storage Map for Species'
cont> STORE
cont> IN AREA1
cont> (THRESHOLD (10),
cont> PARTITION AREA1,
cont> COMMENT IS 'Partition is AREA1');
SQL> --
SQL> CREATE STORAGE MAP M2 FOR SIGHTING
cont> THRESHOLD IS (40)
cont> STORE
cont> RANDOMLY ACROSS (
cont> AREA1 (THRESHOLD OF (10),
cont> PARTITION AREA1),
cont> AREA2 (THRESHOLDS ARE (30, 50, 98),
cont> PARTITION AREA2),
cont> AREA3 (PARTITION AREA3)
cont> );
SQL> --
SQL> CREATE STORAGE MAP M3 FOR FIELD_NOTES
cont> THRESHOLDS OF (50,70,90)
cont> STORE COLUMNS (WEATHER, TIDE, SIGHTING_TIME)
cont> VERTICAL PARTITION WEATHER_TIDE_SIGHTINGTIME
cont> USING (SPECIES_NUMBER, NOTES_NUMBER)
cont> IN AREA1
cont> (THRESHOLDS OF (20, 80, 90) )
cont> WITH LIMIT OF (30, 88)
cont> IN AREA2
cont> WITH LIMIT OF (40, 89)
cont> IN AREA3
cont> WITH LIMIT OF (50, 90)
cont> OTHERWISE IN AREA4
cont> (THRESHOLDS ARE (20, 30, 40))
cont> STORE COLUMNS (NOTES, NOTES_NUMBER, SPECIES_NUMBER)
cont> VERTICAL PARTITION NOTES_NOTESNUM_SPECIESNUM
cont> USING (SPECIES_NUMBER)
cont> IN AREA5
cont> (THRESHOLDS OF (20, 80, 90) )
cont> WITH LIMIT OF (30)
cont> IN AREA6
cont> WITH LIMIT OF (40)
cont> IN AREA7
cont> WITH LIMIT OF (50)
cont> OTHERWISE IN AREA8
cont> (THRESHOLDS ARE (20, 30, 40));
Example 6: Creating a storage map for a table containing data
SQL> -- Create table, insert data, and then create a storage map.
SQL> --
SQL> CREATE TABLE MAP_TEST2 (a INTEGER, b CHAR(10));
SQL> INSERT INTO MAP_TEST2 (a, b) VALUES (2, 'Second');
1 row inserted
SQL> CREATE STORAGE MAP MAP_TEST2_MAP FOR MAP_TEST2
cont> STORE IN RDB$SYSTEM;
SQL> INSERT INTO MAP_TEST2 (a, b) VALUES (22, 'Second2');
1 row inserted
SQL> COMMIT;
SQL> SELECT *,DBKEY FROM MAP_TEST2;
A B DBKEY
2 Second 90:809:0
22 Second2 90:809:1
2 rows selected
SQL>
SQL> -- Now alter the storage map and
SQL> -- place it in a different storage area.
SQL>
SQL> ALTER STORAGE MAP MAP_TEST2_MAP
cont> STORE IN TEST_AREA2;
SQL> COMMIT;
SQL> SELECT *,DBKEY FROM MAP_TEST2;
A B DBKEY
2 Second 91:11:0
22 Second2 91:11:1
2 rows selected
SQL>
Example 7: Invalid attempts to create a storage map
SQL> -- Create table, insert data, and then
SQL> -- create a storage map with invalid attributes.
SQL>
SQL> CREATE TABLE MAP_TEST3 (a INTEGER, b CHAR(10));
SQL> CREATE INDEX MAP_TEST3_INDEX ON MAP_TEST3 (a);
SQL> INSERT INTO MAP_TEST3 (a, b) VALUES (3, 'Third');
1 row inserted
SQL>
SQL> CREATE STORAGE MAP MAP_TEST3_MAP FOR MAP_TEST3
cont> STORE IN TEST_AREA1; -- Must be the default area.
%RDB-E-NO_META_UPDATE, metadata update failed
-RDMS-F-RELNOTEMPTY, table "MAP_TEST3" has data in it
-RDMS-E-NOCMPLXMAP, can not use complex map for non-empty table
SQL>
SQL> CREATE STORAGE MAP MAP_TEST3_MAP for MAP_TEST3
cont> PLACEMENT VIA INDEX MAP_TEST3_INDEX -- Can't use placement.
cont> STORE IN RDB$SYSTEM;
%RDB-E-NO_META_UPDATE, metadata update failed
-RDMS-F-RELNOTEMPTY, table "MAP_TEST3" has data in it
-RDMS-E-NOCMPLXMAP, can not use complex map for non-empty table
SQL>
SQL> CREATE STORAGE MAP MAP_TEST3_MAP FOR MAP_TEST3
cont> DISABLE COMPRESSION -- Can't change compression.
cont> STORE IN RDB$SYSTEM;
%RDB-E-NO_META_UPDATE, metadata update failed
-RDMS-F-RELNOTEMPTY, table "MAP_TEST3" has data in it
-RDMS-E-NOCMPLXMAP, can not use complex map for non-empty table
SQL>
SQL> CREATE STORAGE MAP MAP_TEST3_MAP for MAP_TEST3
cont> THRESHOLDS ARE (50, 60, 70) -- Can't change thresholds.
cont> STORE IN RDB$SYSTEM;
%RDB-E-NO_META_UPDATE, metadata update failed
-RDMS-F-RELNOTEMPTY, table "MAP_TEST3" has data in it
-RDMS-E-NOCMPLXMAP, can not use complex map for non-empty table
SQL>
SQL> CREATE STORAGE MAP MAP_TEST3_MAP FOR MAP_TEST3
cont> STORE ACROSS (RDB$SYSTEM, TEST_AREA2);-- Can't use more than one area.
%RDB-E-NO_META_UPDATE, metadata update failed
-RDMS-F-RELNOTEMPTY, table "MAP_TEST3" has data in it
-RDMS-E-NOCMPLXMAP, can not use complex map for non-empty table
SQL>
SQL> CREATE STORAGE MAP MAP_TEST3_MAP for MAP_TEST3
cont> STORE COLUMNS (a) in RDB$SYSTEM -- Can't vertically partition.
cont> STORE COLUMNS (b) in TEST_AREA2;
%RDB-E-NO_META_UPDATE, metadata update failed
-RDMS-F-RELNOTEMPTY, table "MAP_TEST3" has data in it
-RDMS-E-NOCMPLXMAP, can not use complex map for non-empty table
Example 8: Using the RMU Extract command to display WITH LIMIT OF
expressions
The WITH LIMIT OF clauses of the STORE clause are converted
to Boolean expressions that are used by Oracle Rdb to direct
inserted data to the correct storage area. You can use the RMU
Extract command to display these Boolean expressions. Use the
Item=STORAGE_MAP and Option=FULL qualifiers as shown in the
following example.
$ RMU/EXTRACT-
_$ /ITEM=STORAGE_MAP-
_$ /OPTION=(MATCH:EMPLOYEES_MAP%,NOHEADER,FULL,FILENAME_ONLY) -
_$ DB$:MF_PERSONNEL
set verify;
set language ENGLISH;
set default date format 'SQL92';
set quoting rules 'SQL92';
set date format DATE 001, TIME 001;
attach 'filename MF_PERSONNEL.RDB';
create storage map EMPLOYEES_MAP
for EMPLOYEES
comment is
' employees partitioned by "00200" "00400"'
placement via index EMPLOYEES_HASH
store
using (EMPLOYEE_ID)
-- Partition:
-- (EMPLOYEE_ID <= '00200')
in EMPIDS_LOW
with limit of ('00200')
-- Partition:
-- (EMPLOYEE_ID <= '00400')
in EMPIDS_MID
with limit of ('00400')
otherwise in EMPIDS_OVER;
commit work;
Example 9: SQL Mapping Routine
This example shows the SQL mapping routine created by the CREATE
STORAGE MAP statement that matches the WITH LIMIT OF clause for
the storage map.
SQL> create table EMPLOYEES (
cont> EMPLOYEE_ID CHAR (5),
cont> LAST_NAME CHAR (14),
cont> FIRST_NAME CHAR (10),
cont> MIDDLE_INITIAL CHAR (1),
cont> ADDRESS_DATA_1 CHAR (25),
cont> ADDRESS_DATA_2 CHAR (25),
cont> CITY CHAR (20),
cont> STATE CHAR (2),
cont> POSTAL_CODE CHAR (5),
cont> SEX CHAR (1),
cont> BIRTHDAY DATE VMS,
cont> STATUS_CODE CHAR (1));
SQL>
SQL> create storage map EMPLOYEES_MAP
cont> for EMPLOYEES
cont> comment is
cont> ' employees partitioned by "00200" "00400"'
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;
SQL>
SQL> commit work;
SQL>
SQL> show system modules;
Modules in database with filename MF_PERSONNEL
RDB$STORAGE_MAPS
SQL>
SQL> show system functions;
Functions in database with filename MF_PERSONNEL
EMPLOYEES_MAP
SQL>
SQL> show system function EMPLOYEES_MAP;
Information for function EMPLOYEES_MAP
Function ID is: -2
Source:
return
case
when (:EMPLOYEE_ID <= '00200') then 1
when (:EMPLOYEE_ID <= '00400') then 2
else 3
end case;
Comment: Return value for select partition - range 1 .. 3
Module name is: RDB$STORAGE_MAPS
Module ID is: -1
Number of parameters is: 1
Parameter Name Data Type Domain or Type
-------------- --------- --------------
INTEGER
Function result datatype
Return value is passed by value
EMPLOYEE_ID CHAR(5)
Parameter position is 1
Parameter is IN (read)
Parameter is passed by reference
Example 10: Using Storage Area Attributes in a LIST Storage Map
The following example shows the use of storage area attributes in
a LIST storage map. The storage area attributes must immediately
follow the storage area name (as in table storage maps).
SQL> create database
cont> filename 'DB$:MULTIMEDIA'
cont>
cont> create storage area PHOTO_AREA1
cont> filename 'DB$:PHOTO_AREA1'
cont> page format UNIFORM
cont>
cont> create storage area PHOTO_AREA2
cont> filename 'DB$:PHOTO_AREA2'
cont> page format UNIFORM
cont>
cont> create storage area TEXT_AREA
cont> filename 'DB$:TEXT_AREA'
cont> page format UNIFORM
cont>
cont> create storage area AUDIO_AREA
cont> filename 'DB$:AUDIO_AREA'
cont> page format UNIFORM
cont>
cont> create storage area DATA_AREA
cont> filename 'DB$:DATA_AREA'
cont> page format UNIFORM
cont> ;
SQL>
SQL> create table EMPLOYEES
cont> (name char(30),
cont> dob date,
cont> ident integer,
cont> photograph list of byte varying (4096) as binary,
cont> resume list of byte varying (132) as text,
cont> review list of byte varying (80) as text,
cont> voiceprint list of byte varying (4096) as binary
cont> );
SQL>
SQL> create storage map EMPLOYEES_MAP
cont> for EMPLOYEES
cont> enable compression
cont> store in DATA_AREA;
SQL>
SQL> create storage map LISTS_MAP
cont> store lists
cont> in AUDIO_AREA
cont> (thresholds are (89, 99, 100)
cont> ,comment is 'The voice clips'
cont> ,partition AUDIO_STUFF)
cont> for (employees.voiceprint)
cont> in TEXT_AREA
cont> (thresholds is (99)
cont> ,partition TEXT_DOCUMENTS)
cont> for (employees.resume, employees.review)
cont> in (PHOTO_AREA1
cont> (comment is 'Happy Smiling Faces?'
cont> ,threshold is (99)
cont> ,partition PHOTOGRAPHIC_IMAGES_1)
cont> ,PHOTO_AREA2
cont> (comment is 'Happy Smiling Faces?'
cont> ,threshold is (99)
cont> ,partition PHOTOGRAPHIC_IMAGES_2)
cont> )
cont> for (employees.photograph)
cont> fill randomly
cont> in RDB$SYSTEM
cont> (partition SYSTEM_LARGE_OBJECTS);
SQL>
SQL> show storage map LISTS_MAP;
LISTS_MAP
For Lists
Store clause: STORE lists
in AUDIO_AREA
(thresholds are (89, 99, 100)
,comment is 'The voice clips'
,partition AUDIO_STUFF)
for (employees.voiceprint)
in TEXT_AREA
(thresholds is (99)
,partition TEXT_DOCUMENTS)
for (employees.resume, employees.review)
in (PHOTO_AREA1
(comment is 'Happy Smiling Faces?'
,threshold is (99)
,partition PHOTOGRAPHIC_IMAGES_1)
,PHOTO_AREA2
(comment is 'Happy Smiling Faces?'
,threshold is (99)
,partition PHOTOGRAPHIC_IMAGES_2)
)
for (employees.photograph)
fill randomly
in RDB$SYSTEM
(partition SYSTEM_LARGE_OBJECTS)
Partition information for lists map:
Vertical Partition: VRP_P000
Partition: (1) AUDIO_STUFF
Fill Randomly
Storage Area: AUDIO_AREA
Thresholds are (89, 99, 100)
Comment: The voice clips
Partition: (2) TEXT_DOCUMENTS
Fill Randomly
Storage Area: TEXT_AREA
Thresholds are (99, 100, 100)
Partition: (3) PHOTOGRAPHIC_IMAGES_1
Fill Randomly
Storage Area: PHOTO_AREA1
Thresholds are (99, 100, 100)
Comment: Happy Smiling Faces?
Partition: (3) PHOTOGRAPHIC_IMAGES_2
Storage Area: PHOTO_AREA2
Thresholds are (99, 100, 100)
Comment: Happy Smiling Faces?
Partition: (4) SYSTEM_LARGE_OBJECTS
Fill Randomly
Storage Area: RDB$SYSTEM
SQL>
SQL> commit;