NOTE
You cannot issue CREATE STORAGE AREA as an independent
statement. It is a clause allowed only as part of a CREATE
DATABASE or IMPORT statement.
You can also create a storage area using the ADD STORAGE
AREA clause of the ALTER DATABASE statement.
Creates additional storage areas in a multifile database. Storage
areas are data and snapshot files that are associated with
particular tables in a multifile database.
A CREATE STORAGE AREA clause specifies the names for the storage
area files and determines their physical characteristics.
Subsequent CREATE STORAGE MAP statements associate the storage
area with particular tables in the database.
1 – Environment
You can use the CREATE STORAGE AREA clause only within a CREATE
DATABASE or IMPORT statement.
2 – Format
CREATE STORAGE AREA -+-> <area-name> -+-+-------------------------++
+-> RDB$SYSTEM --+ +-> FILENAME <file-spec> -+|
+-------------------------------------------------------------+
++------------------------------+-+->
+-+-+-> storage-area-params-1 -+ |
| +-> storage-area-params-2 -+ |
+--------------- <-------------+
storage-area-params-1 =
--+-> ALLOCATION IS ---> <number-pages> --> PAGES -----+->
+-> CACHE USING <row-cache-name> --------------------+
+-> NO ROW CACHE ------------------------------------+
+-> extent-params -----------------------------------+
+-> INTERVAL IS --> <number-data-pages> -------------+
+-> LOCKING IS --+-> ROW --+--> LEVEL ---------------+
| +-> PAGE -+ |
+-> PAGE FORMAT IS +-> UNIFORM -+--------------------+
| +-> MIXED ---+ |
+-> PAGE SIZE IS ----> <page-blocks> --> BLOCKS -----+
extent-params =
--+-> EXTENT IS -+-> ENABLED -------------------++-->
| +-> DISABLED ------------------+|
| +-> <extent-pages> --> PAGES --+|
| +-> (extension-options) -------+|
+---------------------<------------------------+
extension-options =
---> MINIMUM OF <min-pages> PAGES, --+
+------------------------------------+
+--> MAXIMUM OF <max-pages> PAGES, --+
+------------------------------------+
+--> PERCENT GROWTH IS <growth> ------->
storage-area-params-2 =
--+-> CHECKSUM CALCULATION IS -----------+--+-> ENABLED --+-+->
+-> SNAPSHOT CHECKSUM CALCULATION IS --+ +-> DISABLED -+ |
+-> SNAPSHOT ALLOCATION IS --> <snp-pages> ----> PAGES ---+
+-> SNAPSHOT EXTENT IS -+-> <extent-pages> ----> PAGES --++
| +-> (extension-options) ---------+|
+-> SNAPSHOT FILENAME --> <file-spec> --------------------+
+-> THRESHOLDS ARE ( <val1> +-----------------------+> ) -+
| +> ,<val2> -+----------++ |
| +> ,<val3> + |
+------------------------------<--------------------------+
3 – Arguments
3.1 – ALLOCATION_PAGES
Syntax option:
ALLOCATION IS number-pages PAGES
The number of database pages initially allocated to the storage
area. Rdb will automatically extend this allocation to account
for internal structure pages, such as SPAM (spage management)
pages. For example, an allocation of 25 will be increased to 27
as shown in this example:
SQL> alter database filename MF_PERSONNEL
cont> add storage area DOC_EXAMPLE
cont> page format is uniform
cont> allocation 25;
SQL> attach 'filename MF_PERSONNEL';
SQL> show storage area DOC_EXAMPLE
DOC_EXAMPLE
Access is: Read write
Page Format: Uniform
Page Size: 2 blocks
Area File: USER_DISK:[DOC.DATABASES]DOC_EXAMPLE.RDA;1
Area Allocation: 27 pages
Extent: Enabled
Area Extent Minimum: 99 pages
Area Extent Maximum: 9999 pages
Area Extent Percent: 20 percent
Snapshot File: USER_DISK:[DOC.DATABASES]DOC_EXAMPLE.SNP;1
Snapshot Allocation: 100 pages
Snapshot Extent Minimum: 99 pages
Snapshot Extent Maximum: 9999 pages
Snapshot Extent Percent: 20 percent
Locking is Row Level
No Cache Associated with Storage Area
No database objects use Storage Area DOC_EXAMPLE
3.2 – CACHE USING row cache name
Assigns the named row cache to the specified storage area. All
rows stored in this area, whether they consist of table data,
segmented string data, or special rows such as index nodes, are
cached if those rows fit in the cache.
If the row cache does not exist, you must create the row cache
before terminating the CREATE DATABASE statement. For example:
SQL> CREATE DATABASE FILENAME test_db
cont> ROW CACHE IS ENABLED
cont> CREATE STORAGE AREA area1
cont> CACHE USING test1
cont> CREATE CACHE test1
cont> CACHE SIZE IS 100 ROWS
cont> ROW LENGTH IS 200 BYTES;
Only one row cache is allowed for each storage area.
NO ROW CACHE is the default for a storage area.
3.3 – CHECKSUM_CALCULATION
Syntax options:
CHECKSUM CALCULATION | SNAPSHOT CHECKSUM CALCULATION
This option allows you to enable or disable calculations of page
checksums when pages are read from or written to the storage or
snapshot area files.
The default is ENABLED.
NOTE
Oracle Corporation recommends that you leave checksum
calculations enabled; which is the default.
With current technology, it is possible that errors may occur
that the checksum calculation can detect but that may not
be detected by either the hardware, firmware, or software.
Unexpected application results and database corruption may occur
if corrupt pages exist in memory or on disk but are not detected.
Oracle Corporation recommends performing checksum calculations,
except in the following specific circumstances:
o Your application is stable and has run without errors on the
current hardware and software configuration for an extended
period of time.
o You have reached maximum CPU utilization in your current
configuration. Actual CPU utilization by the checksum
calculation depends primarily on the size of the database
pages in your database. The larger the database page, the
more noticeable the CPU usage by the checksum calculation may
become.
NOTE
Oracle Corporation recommends that you carefully evaluate
the trade-off between reducing CPU usage by the checksum
calculation and the potential for loss of database integrity
if checksum calculations are disabled.
Oracle Rdb allows you to disable and, subsequently, re-enable
checksum calculation without error. However, once checksum
calculations have been disabled, corrupt pages may not be
detected even if checksum calculations are subsequently re-
enabled.
3.4 – EXTENT
Syntax options:
EXTENT ENABLED | EXTENT DISABLED
Enables or disables extents. Extents are ENABLED by default.
You may encounter performance problems when creating hashed
indexes in storage areas with the mixed page format if the
storage area was created specifying the incorrect size for
the area and if extents are enabled. By disabling extents,
this problem can be diagnosed early and corrected to improve
performance.
3.5 – EXTENT_IS
Syntax options:
EXTENT IS extent-pages PAGES | EXTENT IS (extension-options)
Specifies the number of pages of each storage area file extent.
See also the description under the SNAPSHOT EXTENT argument.
3.6 – FILENAME file spec
Provides an explicit file specification for storage area files.
The CREATE STORAGE AREA clause creates two files: a storage area
file with a file extension of .rda, and a snapshot file with a
file extension of .snp. If you omit the FILENAME argument, the
file specification takes the following defaults:
o Device: the current device for the process
o Directory: the current directory for the process
o File name: the name specified for the storage area
Neither the file specification for the storage area nor the
snapshot file may contain a node specification.
The file specification is used for both the storage area and
snapshot files that comprise the storage area (unless you use the
SNAPSHOT FILENAME argument to specify a different file for the
snapshot file). Because the CREATE STORAGE AREA clause can create
two files with different file extensions, do not specify a file
extension with the file specification.
You may use a logical name for all or part of a file
specification.
One benefit of a multifile database is that its files can reside
on more than one disk. If you want storage area files to reside
on another disk, you must specify the FILENAME argument with a
full file specification.
However, you may choose to create a multifile database even
if your main purpose in creating the storage area is not to
distribute storage area files across more than one disk. For
instance, a multifile database enables you to:
o Take advantage of hashed indexes. Hashed indexes require a
storage area with mixed page format and cannot be stored in
the RDB$SYSTEM storage area.
o Set attributes such as page size to better correspond with
tables that will be stored in the storage area.
3.7 – INTERVAL
Syntax options:
INTERVAL IS number-data-pages
Specifies the number of data pages between SPAM pages in the
storage area file, and thus the maximum number of data pages each
SPAM page manages. The default, and also the minimum interval,
is 216 data pages. The first page of each storage area is a SPAM
page. The interval you specify determines where subsequent SPAM
pages are to be inserted if there are enough data pages in the
storage file to require more SPAM pages.
You cannot specify the INTERVAL storage area parameter unless you
also explicitly specify PAGE FORMAT IS MIXED.
Oracle Rdb calculates the maximum INTERVAL size based on the
number of blocks per page, and returns an error message if you
exceed this value. For example, when the page size is 2 blocks,
the maximum INTERVAL is 4008 pages. If you try to create a
storage area with the INTERVAL set to 4009, Oracle Rdb returns
the following error message:
%RDB-E-BAD_DPB_CONTENT, invalid database parameters in the database parameter
block (DPB)
-RDMS-F-SPIMAX, spam interval of 4009 is more than the Rdb maximum of 4008
-RDMS-F-AREA_NAME, area NEW
For more information about setting space area management
parameters, see the Oracle Rdb Guide to Database Maintenance.
3.8 – LOCKING level
Syntax options:
LOCKING IS ROW LEVEL | LOCKING IS PAGE LEVEL
Specifies if locking is at the page or row level for the storage
area. This clause provides an alternative to requesting locks on
records. Specifying a lock level when you create a storage area
overrides the database default lock level. The default is ROW
LEVEL.
When many records are accessed in the same area and on the same
page, the LOCKING IS PAGE LEVEL clause reduces the number of lock
operations performed to process a transaction; however, this is
at the expense of reduced concurrency because these pages' locks
are held until COMMIT/ROLLBACK. Transactions that benefit most
with page-level locking are of short duration and also access
several database records on the same page. However, to guarantee
consistency of the data in the absence of row locking these page
level locks must be held until the transaction ends with COMMIT
or ROLLBACK.
Use the LOCKING IS ROW LEVEL if transactions are long in duration
and lock many rows.
The LOCKING IS PAGE LEVEL clause causes fewer blocking ASTs
and provides better response time and utilization of system
resources. However, there is a higher contention for pages and
increased potential for deadlocks and long transactions may use
excessive locks.
Page-level locking is never applied to RDB$SYSTEM or the DEFAULT
storage-area, either implicitly or explicitly, because the
locking protocol can stall metadata users.
You cannot specify page-level locking on single-file databases.
3.9 – MAXIMUM_PAGES
Syntax options:
MAXIMUM OF max-pages PAGES
Specifies the maximum number of pages of each extent. The default
is 9,999 pages.
3.10 – MINIMUM_PAGES
Syntax options:
MINIMUM OF min-pages PAGES
Specifies the minimum number of pages of each extent. The default
is 99 pages.
3.11 – NO_ROW_CACHE
Specifies that a row cache is not assigned to the specified
storage area in the database. You cannot specify the NO ROW CACHE
clause if you specify the CACHE USING clause.
Alter the storage area and name a row cache with the CACHE USING
clause to assign a row cache to the storage area or to override
the database default. Only one row cache is allowed for each
storage area.
3.12 – PAGE_FORMAT
Syntax options:
PAGE FORMAT IS UNIFORM | PAGE FORMAT IS MIXED
Specifies the on-disk structure for the storage area.
o The default is PAGE FORMAT IS UNIFORM. A storage area with
uniform page format is a file that is divided into groups of n
pages, called clumps, where n equals the buffer size divided
by the page size. Both buffer size and page size are user
specified values. By default, the buffer size is 6 blocks,
and the page size is 1024 bytes or 2 blocks long, resulting
in clumps of three pages. The PAGE FORMAT IS UNIFORM argument
creates a storage area file that is divided into clumps. A set
of clumps forms a logical area that can contain rows from a
single table or index only.
Uniform page format storage areas generally give the best
performance if the tables in the storage area are likely to be
subject to a wide range of queries.
o The PAGE FORMAT IS MIXED argument creates a storage area with
a format that allows rows from more than one table to reside
on or near a particular page of the storage area file. This
is useful for storing related rows from different tables on
the same page of the data file. For storage areas subject to
repeated queries that retrieve those related rows, a mixed
page format can greatly reduce input/output overhead if the
mix of rows on the page is carefully controlled. However,
mixed page format storage areas degrade performance if the mix
of rows on the page is not suited for the queries made against
the storage area.
For more information on the relative advantages and disadvantages
of uniform and mixed storage areas, see the Oracle Rdb Guide to
Database Maintenance.
3.13 – PAGE SIZE blocks
Syntax options:
PAGE SIZE IS page-blocks BLOCKS
The size in blocks of each data page in the storage area. Page
size is allocated in 512-byte blocks. The default is 2 blocks
(1024 bytes). If your largest row is larger than approximately
950 bytes, allocate more blocks per page to prevent fragmented
rows. If you specify a page size larger than the buffer size, an
error message is returned.
3.14 – PERCENT_GROWTH
Syntax options:
PERCENT GROWTH IS growth
Specifies the percent growth of each extent. The default is 20
percent growth.
3.15 – SNAPSHOT_ALLOCATION
Syntax options:
SNAPSHOT ALLOCATION IS snp-pages PAGES
Specifies the number of pages allocated for the snapshot file.
The default is 100 pages.
3.16 – SNAPSHOT_EXTENT
Syntax options:
SNAPSHOT EXTENT IS extent-pages | SNAPSHOT EXTENT IS (extension-
options)
Specifies the number of pages of each snapshot or storage area
file extent. The default extent for storage area files is 100
pages.
Specify a number of pages for simple control over the extension.
For greater control, and particularly for multivolume databases,
use the MIN, MAX, and PERCENT GROWTH extension options instead.
If you use the MIN, MAX, and PERCENT GROWTH parameters, you must
enclose them in parentheses.
3.17 – SNAPSHOT_FILENAME
Syntax options:
SNAPSHOT FILENAME file-spec
Provides a separate file specification for the snapshot file. The
SNAPSHOT FILENAME argument can only be specified with multifile
databases.
This argument lets you specify a different file name, device,
or directory for the snapshot file created by the CREATE STORAGE
AREA clause. Do not specify a file extension other than .snp to
the file specification. Oracle Rdb assigns the extension .snp
to the file specification, even if you specify an alternate
extension.
If you omit the SNAPSHOT FILENAME argument, the snapshot file
gets the same device, directory, and file name as the storage
area file.
3.18 – STORAGE_AREA
Specifies the name of the storage area you want to create. The
name cannot be the same as any other storage area definition in
the database.
3.19 – STORAGE_AREA_RDB$SYSTEM
Specifies that you want the CREATE STORAGE AREA clause to
override the default characteristics for the main storage area,
RDB$SYSTEM, in a new database.
The RDB$SYSTEM storage area contains database system tables and
indices. If an alternate DEFAULT STORAGE AREA is not assigned
then this area may also contain unmapped user tables and indices.
3.20 – THRESHOLDS values
Syntax options:
THRESHOLDS ARE ( val1 [,val2 [,val3] ] )
Specifies one, two, or three threshold values for mixed format
pages. The threshold values 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.
The default threshold values for mixed areas, if not specified,
are (70,85,95), which indicates that the nominal record size
should be used for SPAM threshold calculations. Oracle Rdb never
stores a record on a page 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).
You cannot specify the THRESHOLDS storage area parameter unless
you also explicitly specify PAGE FORMAT IS MIXED.
For more information about setting space area management
parameters, see the Oracle Rdb Guide to Database Maintenance.
4 – Examples
Example 1: Defining a multifile database
This example shows the definition of a database and storage areas
for a multifile database.
SQL> -- Note that there is no semicolon before
SQL> -- the first CREATE STORAGE AREA clause.
SQL> CREATE DATABASE ALIAS MULTIFILE_EXAMPLE
cont> FILENAME 'DB_DATA01:[DB.DATA]MULTIFILE_EXAMPLE'
cont> CREATE STORAGE AREA EMPID_LOW
cont> FILENAME 'DB_DATA02:[DB.DATA]EMPID_LOW'
cont> ALLOCATION IS 10 PAGES
cont> -- Notice that the snapshot file resides on a
cont> -- different disk than the storage area file. This
cont> -- strategy reduces disk input/output bottlenecks:
cont> SNAPSHOT FILENAME 'DB_SNAP03:[DB.SNAP]EMPID_LOW'
cont> SNAPSHOT ALLOCATION IS 10 PAGES
cont> --
cont> CREATE STORAGE AREA EMPID_MID
cont> FILENAME 'DB_DATA04:EMPID_MID'
cont> ALLOCATION IS 10 PAGES
cont> SNAPSHOT FILENAME 'DB_SNAP05:[DB.SNAP]EMPID_MID'
cont> SNAPSHOT ALLOCATION IS 10 PAGES
cont> --
cont> CREATE STORAGE AREA EMPID_OVER
cont> FILENAME 'DB_DATA06:[DB.DATA]EMPID_OVER'
cont> ALLOCATION IS 10 PAGES
cont> SNAPSHOT FILENAME 'DB_SNAP07:[DB.SNAP]EMPID_OVER'
cont> SNAPSHOT ALLOCATION IS 10 PAGES
cont> --
cont> CREATE STORAGE AREA HISTORIES
cont> FILENAME 'DB_DATA02:[DB.DATA]HISTORIES'
cont> ALLOCATION IS 10 PAGES
cont> SNAPSHOT FILENAME 'DB_SNAP03:[DB.SNAP]HISTORIES'
cont> SNAPSHOT ALLOCATION IS 10 PAGES
cont> --
cont> CREATE STORAGE AREA CODES
cont> FILENAME 'DB_DATA04:[DB.DATA]CODES'
cont> ALLOCATION IS 10 PAGES
cont> SNAPSHOT FILENAME 'DB_SNAP05:[DB.SNAP]CODES'
cont> SNAPSHOT ALLOCATION IS 10 PAGES
cont> --
cont> CREATE STORAGE AREA EMP_INFO
cont> FILENAME 'DB_DATA08:[DB.DATA]EMP_INFO'
cont> ALLOCATION IS 10 PAGES
cont> SNAPSHOT FILENAME 'DB_SNAP09:[DB.SNAP]EMP_INFO'
cont> SNAPSHOT ALLOCATION IS 10 PAGES
cont> --
cont> -- End the CREATE DATABASE statement:
cont> ;
Example 2:
This example shows how to set page-level and row-level locking on
storage areas from both the database level and from the storage
area level.
SQL> CREATE DATABASE FILENAME sample
cont> LOCKING IS PAGE LEVEL
cont> --
cont> -- All storage areas will default to page-level locking unless
cont> -- explicitly set to row-level locking.
cont> --
cont> CREATE STORAGE AREA RDB$SYSTEM
cont> FILENAME sample_system
cont> --
cont> -- You cannot specify page-level locking on RDB$SYSTEM. RDB$SYSTEM
cont> -- always defaults to row-level locking.
cont> --
cont> CREATE STORAGE AREA HASH_AREA
cont> FILENAME sample_hash
cont> PAGE FORMAT IS MIXED
cont> --
cont> -- HASH_AREA defaultS to page-level locking.
cont> --
cont> CREATE STORAGE AREA DATA_AREA
cont> FILENAME sample_data
cont> LOCKING IS ROW LEVEL
cont> --
cont> -- DATA_AREA is explicitly set to row-level locking.
cont> --
cont> ;
SQL> SHOW STORAGE AREAS (ATTRIBUTES) *
Storage Areas in database with filename sample
RDB$SYSTEM
List storage area.
Access is: Read write
Page Format: Uniform
Page Size: 2 blocks
.
.
.
Extent : Enabled
Locking is Row Level
HASH_AREA
Access is: Read write
Page Format: Mixed
Page Size: 2 blocks
.
.
.
Extent : Enabled
Locking is Page Level
DATA_AREA
Access is: Read write
Page Format: Uniform
Page Size: 2 blocks
.
.
.
Extent : Enabled
Locking is Row Level
See the SHOW statement for information on the SHOW STORAGE AREAS
statement.
Example 3: Creating and assigning a row cache to a storage area
SQL> create database
cont> filename SAMPLE_DB
cont> reserve 2 cache slots
cont> row cache is enabled
cont> default storage area is AREA1
cont> create cache CACHE1
cont> cache size is 1000 rows
cont> row length is 1000 bytes
cont> create storage area AREA1
cont> cache using CACHE1
cont> ;
SQL> show cache CACHE1
CACHE1
Cache Size: 1000 rows
Row Length: 1000 bytes
Row Replacement: Enabled
Shared Memory: Process
Large Memory: Disabled
Window Count: 100
Working Set Count: 10
Reserved Rows: 20
Allocation: 100 blocks
Extent: 100 blocks
SQL> show storage area AREA1
AREA1
Access is: Read write
Page Format: Uniform
Page Size: 2 blocks
Area File: USER_DISK:[DOC.DATABASES]AREA1.RDA;1
Area Allocation: 702 pages
Extent: Enabled
Area Extent Minimum: 99 pages
Area Extent Maximum: 9999 pages
Area Extent Percent: 20 percent
Snapshot File: USER_DISK:[DOC.DATABASES]AREA1.SNP;1
Snapshot Allocation: 100 pages
Snapshot Extent Minimum: 99 pages
Snapshot Extent Maximum: 9999 pages
Snapshot Extent Percent: 20 percent
Locking is Row Level
Using Cache CACHE1
Database objects using Storage Area AREA1:
Usage Object Name Map / Partition
---------------- ------------------------------- -------------------------------
Default Area