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;