Oracle9i SQL Reference Release 1 (9.0.1) Part Number A90125-01 |
|
SQL Statements:
CREATE SYNONYM to CREATE TRIGGER, 3 of 6
Use the CREATE
TABLE
statement to create one of the following types of tables:
You can also create an object type and then use it in a column when creating a relational table.
See also:
Oracle9i Application Developer's Guide - Fundamentals, Oracle9i Database Administrator's Guide, and CREATE TYPE for more information about creating objects |
Tables are created with no data unless a query is specified. You can add rows to a table with the INSERT
statement. After creating a table, you can define additional columns, partitions, and integrity constraints with the ADD
clause of the ALTER
TABLE
statement. You can change the definition of an existing column or partition with the MODIFY
clause of the ALTER
TABLE
statement.
To create a relational table in your own schema, you must have the CREATE
TABLE
system privilege. To create a table in another user's schema, you must have CREATE
ANY
TABLE
system privilege. Also, the owner of the schema to contain the table must have either space quota on the tablespace to contain the table or UNLIMITED
TABLESPACE
system privilege.
In addition to the table privileges above, to create an object table (or a relational table with an object type column), the owner of the table must have the EXECUTE
object privilege in order to access all types referenced by the table, or you must have the EXECUTE
ANY
TYPE
system privilege. These privileges must be granted explicitly and not acquired through a role.
Additionally, if the table owner intends to grant access to the table to other users, the owner must have been granted the EXECUTE
privileges on the referenced types with the GRANT
OPTION
, or have the EXECUTE
ANY
TYPE
system privilege with the ADMIN
OPTION
. Without these privileges, the table owner has insufficient privileges to grant access to the table to other users.
To enable a UNIQUE
or PRIMARY
KEY
constraint, you must have the privileges necessary to create an index on the table. You need these privileges because Oracle creates an index on the columns of the unique or primary key in the schema containing the table.
To create an external table, you must have the READ
object privilege on the directory in which the external data resides.
See Also:
|
create_table::=
create_table
relational_table::=
relational_table
relational_properties
object_table
object_table_substitution
object_properties
oid_clause
oid_index_clause
physical_properties
table_properties
column_properties
object_type_col_properties
substitutable_column_clause::=
substitutable_column_clause
nested_table_col_properties::=
nested_table_col_properties
varray_col_properties
lob_storage_clause
lob_parameters
xmltype_storage_clause
table_or_view_constraint, column_constraint, table_ref_constraint, column_ref_constraint, constraint_state: See the constraint_clause
segment_attributes_clause
row_movement_clause
physical_attributes_clause
storage_clause: See the storage_clause.
index_org_table_clause
mapping_table_clauses::=
mapping_table_clauses
compression_clauses
index_org_overflow_clause
supplemental_logging_props
external_table_clause
external_data_properties::=
external_data_properties
opaque_format_spec: See Oracle9i Database Utilities for information on how to specify values for the opaque_format_spec
.
table_partitioning_clauses
range_partitioning
composite_partitioning
partition_values_clause::=
partition_values_clause
list_partitioning
table_partition_description::=
table_partition_description
subpartition_clause
partition_level_subpartition::=
partition_level_subpartition
hash_partitioning
individual_hash_partitions::=
individual_hash_partitions
hash_partitions_by_quantity::=
hash_partitions_by_quantity
hash_partitioning_storage::=
hash_partitioning_storage
parallel_clause
enable_disable_clause
using_index_clause
exceptions_clause
global_partitioned_index::=
global_partitioned_index
global_partitioning_clause::=
global_partitioning_clause
Specify GLOBAL
TEMPORARY
to indicate that the table is temporary and that its definition is visible to all sessions. The data in a temporary table is visible only to the session that inserts the data into the table.
A temporary table has a definition that persists the same as the definitions of regular tables, but it contains either session-specific or transaction-specific data. You specify whether the data is session- or transaction-specific with the ON
COMMIT
keywords (described below).
Restrictions:
TABLESPACE
, storage_clause, LOGGING
or NOLOGGING
, MONITORING
or NOMONITORING
, or LOB_index_clause.
Specify the schema to contain the table. If you omit schema, Oracle creates the table in your own schema.
Specify the name of the table (or object table) to be created.
The OF
clause lets you explicitly create an object table of type object_type. The columns of an object table correspond to the top-level attributes of type object_type. Each row will contain an object instance, and each instance will be assigned a unique, system-generated object identifier (OID) when a row is inserted. If you omit schema, Oracle creates the object table in your own schema.
You can reference objects residing in an object table.
Use the object_table_substitution clause to specify whether row objects corresponding to subtypes can be inserted into this object table.
NOT
SUBSTITUTABLE
AT
ALL
LEVELS
indicates that the object table being created is not substitutable. In addition, substitution is disabled for all embedded object attributes and elements of embedded nested tables and arrays. The default is SUBSTITUTABLE
AT
ALL
LEVELS
.
The relational properties describe the components of a relational table.
Specify the name of a column of the table.
If you also specify AS
subquery, you can omit column and datatype unless you are creating an index-organized table. If you specify AS
subquery when creating an index-organized table, you must specify column, and you must omit datatype.
The absolute maximum number of columns in a table is 1000. However, when you create an object table (or a relational table with columns of object, nested table, varray, or REF
type), Oracle maps the columns of the user-defined types to relational columns, creating in effect "hidden columns" that count toward the 1000-column limit.
Specify the datatype of a column.
Restrictions:
VARRAY
for a hash-partitioned index-organized table. The datatypes for nonpartitioned and range-partitioned index-organized tables are not restricted.
ROWID
, but Oracle does not guarantee that the values in such columns are valid rowids.
The DEFAULT
clause lets you specify a value to be assigned to the column if a subsequent INSERT
statement omits a value for the column. The datatype of the expression must match the datatype of the column. The column must also be long enough to hold this expression.
The DEFAULT
expression can include any SQL function as long as the function does not return a literal argument, a column reference, or a nested function invocation.
Restrictions: A DEFAULT
expression cannot contain references to PL/SQL functions or to other columns, the pseudocolumns LEVEL
, PRIOR
, and ROWNUM
, or date constants that are not fully specified.
These clauses let you further describe a column of type REF
. The only difference between these clauses is that you specify table_ref from the table level, so you must identify the REF
column or attribute you are defining. You specify column_ref after you have already identified the REF
column or attribute.
Use the column_constraint to define an integrity constraint as part of the column definition.
You can create UNIQUE
, PRIMARY
KEY
, and REFERENCES
constraints on scalar attributes of object type columns. You can also create NOT
NULL
constraints on object type columns, and CHECK
constraints that reference object type columns or any attribute of an object type column.
Use the table_or_view_constraint to define an integrity constraint as part of the table definition.
The properties of object tables are essentially the same as those of relational tables. However, instead of specifying columns, you specify attributes of the object.
For attribute, specify the qualified column name of an item in an object.
The ON
COMMIT
clause is relevant only if you are creating a temporary table. This clause specifies whether the data in the temporary table persists for the duration of a transaction or a session.
Specify DELETE
ROWS
for a transaction-specific temporary table (this is the default). Oracle will truncate the table (delete all its rows) after each commit.
Specify PRESERVE
ROWS
for a session-specific temporary table. Oracle will truncate the table (delete all its rows) when you terminate the session.
The OID_clause lets you specify whether the object identifier (OID) of the object table should be system generated or should be based on the primary key of the table. The default is SYSTEM
GENERATED
.
Restrictions:
OBJECT
IDENTIFIER
IS
PRIMARY
KEY
unless you have already specified a PRIMARY
KEY
constraint for the table.
This clause is relevant only if you have specified the OID_clause as SYSTEM
GENERATED
. It specifies an index, and optionally its storage characteristics, on the hidden object identifier column.
For index
, specify the name of the index on the hidden system-generated object identifier column. If you omit index
, Oracle generates a name.
The physical properties relate to the treatment of extents and segments and to the storage characteristics of the table.
The physical_attributes_clause lets you specify the value of the PCTFREE
, PCTUSED
, INITRANS
, and MAXTRANS
parameters and the storage characteristics of the table.
CREATE
statement (and in subsequent ALTER
TABLE
... ADD
PARTITION
statements), unless you explicitly override that value in the PARTITION
clause of the statement that creates the partition.
Specify the percentage of space in each data block of the table, object table OID index, or partition reserved for future updates to the table's rows. The value of PCTFREE
must be a value from 0 to 99. A value of 0 means that the entire block can be filled by inserts of new rows. The default value is 10. This value reserves 10% of each block for updates to existing rows and allows inserts of new rows to fill a maximum of 90% of each block.
PCTFREE
has the same function in the PARTITION
description and in the statements that create and alter clusters, indexes, materialized views, and materialized view logs. The combination of PCTFREE
and PCTUSED
determines whether new rows will be inserted into existing data blocks or into new blocks.
Specify the minimum percentage of used space that Oracle maintains for each data block of the table, object table OID index, or index-organized table overflow data segment. A block becomes a candidate for row insertion when its used space falls below PCTUSED
. PCTUSED
is specified as a positive integer from 0 to 99 and defaults to 40.
PCTUSED
has the same function in the PARTITION
description and in the statements that create and alter clusters, materialized views, and materialized view logs.
PCTUSED
is not a valid table storage characteristic for an index-organized table (ORGANIZATION
INDEX
).
The sum of PCTFREE
and PCTUSED
must be equal to or less than 100. You can use PCTFREE
and PCTUSED
together to utilize space within a table more efficiently.
See Also:
Oracle9i Database Performance Guide and Reference for information on the performance effects of different values |
Specify the initial number of transaction entries allocated within each data block allocated to the table, object table OID index, partition, LOB index segment, or overflow data segment. This value can range from 1 to 255 and defaults to 1. In general, you should not change the INITRANS
value from its default.
Each transaction that updates a block requires a transaction entry in the block. The size of a transaction entry depends on your operating system.
This parameter ensures that a minimum number of concurrent transactions can update the block and helps avoid the overhead of dynamically allocating a transaction entry.
The INITRANS
parameter serves the same purpose in the PARTITION
description, clusters, indexes, materialized views, and materialized view logs as in tables. The minimum and default INITRANS
value for a cluster or index is 2, rather than 1.
Specify the maximum number of concurrent transactions that can update a data block allocated to the table, object table OID index, partition, LOB index segment, or index-organized overflow data segment. This limit does not apply to queries. This value can range from 1 to 255 and the default is a function of the data block size. You should not change the MAXTRANS
value from its default.
If the number of concurrent transactions updating a block exceeds the INITRANS
value, Oracle dynamically allocates transaction entries in the block until either the MAXTRANS
value is exceeded or the block has no more free space.
The MAXTRANS
parameter serves the same purpose in the PARTITION
description, clusters, materialized views, and materialized view logs as in tables.
The storage_clause lets you specify storage characteristics for the table, object table OID index, partition, LOB data segment, LOB index segment, or index-organized table overflow data segment. This clause has performance ramifications for large tables. Storage should be allocated to minimize dynamic allocation of additional space.
Specify the tablespace in which Oracle creates the table, object table OID index, partition, LOB data segment, LOB index segment, or index-organized table overflow data segment. If you omit TABLESPACE
, then Oracle creates that item in the default tablespace of the owner of the schema containing the table.
For heap-organized tables with one or more LOB columns, if you omit the TABLESPACE
clause for LOB storage, Oracle creates the LOB data and index segments in the tablespace where the table is created.
However, for an index-organized table with one or more LOB columns, if you omit TABLESPACE
, the LOB data and index segments are created in the tablespace in which the primary key index segment of the index-organized table is created.
For nonpartitioned tables, the value specified for TABLESPACE
is the actual physical attribute of the segment associated with the table. For partitioned tables, the value specified for TABLESPACE
is the default physical attribute of the segments associated with all partitions specified in the CREATE
statement (and on subsequent ALTER
TABLE
... ADD
PARTITION
statements), unless you specify TABLESPACE
in the PARTITION
description.
Restrictions: You cannot specify a tablespace with automatic segment-space management if table
contains any LOB columns.
Specify whether the creation of the table (and any indexes required because of constraints), partition, or LOB storage characteristics will be logged in the redo log file (LOGGING
) or not (NOLOGGING
).The logging attribute of the table is independent of that of its indexes.
This attribute also specifies whether subsequent Direct Loader (SQL*Loader) and direct-path INSERT
operations against the table, partition, or LOB storage are logged (LOGGING
) or not logged (NOLOGGING
).
For a table or table partition, if you omit this clause, the logging attribute of the table or table partition defaults to the logging attribute of the tablespace in which it resides.
For LOBs, if you omit this clause,
CACHE
, then LOGGING
is used (because you cannot have CACHE
NOLOGGING
).
NOCACHE
or CACHE
READS
, the logging attribute defaults to the logging attribute of the tablespace in which it resides.
NOLOGGING
does not apply to LOBs that are stored inline with row data. That is, if you specify NOLOGGING
for LOBs with values less than 4000 bytes and you have not disabled STORAGE
IN
ROW
, Oracle ignores the NOLOGGING
specification and treats the LOB data the same as other table data.
For nonpartitioned tables, the value specified for LOGGING
is the actual physical attribute of the segment associated with the table. For partitioned tables, the logging attribute value specified is the default physical attribute of the segments associated with all partitions specified in the CREATE
statement (and in subsequent ALTER
TABLE
... ADD
PARTITION
statements), unless you specify the logging attribute in the PARTITION
description.
In NOLOGGING
mode, data is modified with minimal logging (to mark new extents INVALID
and to record dictionary changes). When applied during media recovery, the extent invalidation records mark a range of blocks as logically corrupt, because the redo data is not fully logged. Therefore, if you cannot afford to lose this table, you should take a backup after the NOLOGGING
operation.
The size of a redo log generated for an operation in NOLOGGING
mode is significantly smaller than the log generated in LOGGING
mode.
If the database is run in ARCHIVELOG
mode, media recovery from a backup made before the LOGGING
operation restores the table. However, media recovery from a backup made before the NOLOGGING
operation does not restore the table.
See Also:
Oracle9i Database Concepts and Oracle9i Database Administrator's Guide for more information about logging and parallel DML |
These keywords are deprecated and have been replaced with LOGGING
and NOLOGGING
, respectively. Although RECOVERABLE
and UNRECOVERABLE
are supported for backward compatibility, Oracle Corporation strongly recommends that you use the LOGGING
and NOLOGGING
keywords.
Restrictions:
RECOVERABLE
for partitioned tables or LOB storage characteristics.
UNRECOVERABLE
for a partitioned or index-organized tables.
UNRECOVERABLE
only with AS
subquery.
The ORGANIZATION
clause lets you specify the order in which the data rows of the table are stored.
HEAP
indicates that the data rows of table are stored in no particular order. This is the default.
INDEX
indicates that table is created as an index-organized table. In an index-organized table, the data rows are held in an index defined on the primary key for the table.
EXTERNAL
indicates that table is a read-only table located outside the database.
Use the index_org_table_clause to create an index-organized table. Oracle maintains the table rows (both primary key column values and nonkey column values) in an index built on the primary key. Index-organized tables are therefore best suited for primary key-based access and manipulation. An index-organized table is an alternative to:
CREATE
INDEX
statement
CREATE
CLUSTER
statement that maps the primary key for the table to the cluster key
If an index-organized table is partitioned and contains LOB columns, you should specify the index_org_table_clause first, then the LOB_storage_clause, and then the appropriate table_partitioning_clauses.
Restrictions:
ROWID
for an index-organized table.
composite_partitioning_clause
for an index-organized table.
Specify the percentage of space reserved in the index block for an index-organized table row. PCTTHRESHOLD
must be large enough to hold the primary key. All trailing columns of a row, starting with the column that causes the specified threshold to be exceeded, are stored in the overflow segment. PCTTHRESHOLD
must be a value from 1 to 50. If you do not specify PCTTHRESHOLD
, the default is 50.
Restriction: You cannot specify PCTTHRESHOLD
for individual partitions of an index-organized table.
Specify MAPPING
TABLE
to instruct Oracle to create a mapping of local to physical ROWID
s and store them in a heap-organized table. This mapping is needed in order to create a bitmap index on the index-organized table.
Oracle creates the mapping table in the same tablespace as its parent index-organized table. You cannot query, perform DML operations on, or modify the storage characteristics of the mapping table.
Restriction: You cannot specify this clause for a partitioned index-organized table.
The compression_clauses lets you enable or disable key compression for index-organized tables.
Specify COMPRESS
to enable key compression, which eliminates repeated occurrence of primary key column values in index-organized tables. Use integer to specify the prefix length (number of prefix columns to compress).
The valid range of prefix length values is from 1 to the number of primary key columns minus 1. The default prefix length is the number of primary key columns minus 1.
Restriction: At the partition level, you can specify COMPRESS
, but you cannot specify the prefix length with integer.
Specify NOCOMPRESS
to disable key compression in index-organized tables. This is the default.
The index_org_overflow_clause lets you instruct Oracle that index-organized table data rows exceeding the specified threshold are placed in the data segment specified in this clause.
OVERFLOW
, Oracle raises an error and does not execute the CREATE
TABLE
statement. This checking function guarantees that subsequent DML operations on the index-organized table will not fail because an overflow segment is lacking.
OVERFLOW
keyword apply only to the overflow segment of the table. Physical attributes and storage characteristics for the index-organized table itself, default values for all its partitions, and values for individual partitions must be specified before this keyword.
OVERFLOW
, even if they would otherwise be small enough be to stored inline.
Specify a column at which to divide an index-organized table row into index and overflow portions. The primary key columns are always stored in the index. column_name
can be either the last primary-key column or any non-primary-key column. All non-primary-key columns that follow column_name
are stored in the overflow data segment.
Restriction: You cannot specify this clause for individual partitions of an index-organized table.
The supplemental_logging_props clause lets you instruct Oracle to put additional data into the log stream to support log-based tools.
Use the external_table_clause to create an external table, which is a read-only table whose metadata is stored in the database but whose data in stored outside database. External tables let you query data without first loading it into the database, among other capabilities.
See Also:
Oracle9i Data Warehousing Guide, Oracle9i Database Administrator's Guide, and Oracle9i Database Utilities for information on the uses for external tables |
Because external tables have no data in the database, you define them with a small subset of the clauses normally available when creating tables.
NULL
, NOT
NULL
, and CHECK
constraints.
ORGANIZATION
EXTERNAL
external_table_clause).
Restrictions on External Tables:
CREATE
TABLE
statement if you specify the external_table_clause.
TYPE
access_driver_type indicates the access driver of the external table. The access driver is the API that interprets the external data for the database. If you do not specify TYPE
, Oracle uses the default access driver, ORACLE_LOADER
.
See Also:
Oracle9i Database Utilities for information about the |
DEFAULT
DIRECTORY
lets you specify one or more default directory objects corresponding to directories on the file system where the external data sources may reside. Default directories can also be used by the access driver to store auxiliary files such as error logs. Multiple default directories are permitted to facilitate load balancing on multiple disk drives.
The optional ACCESS
PARAMETERS
clause lets you assign values to the parameters of the specific access driver for this external table:
opaque_format_spec
.
USING
CLOB
subquery lets you derive the parameters and their values through a subquery. The subquery cannot contain any set operators or an ORDER
BY
clause. It must return one row containing a single item of datatype CLOB
.
Whether you specify the parameters in an opaque_format_spec or derive them using a subquery, Oracle does not interpret anything in this clause. It is up to the access driver to interpret this information in the context of the external data.
The LOCATION
clause lets you specify one external locator for each external data source. Usually the location_specifier is a file, but it need not be. Oracle does not interpret this clause. It is up to the access driver to interpret this information in the context of the external data.
The REJECT
LIMIT
clause lets you specify how many conversion errors can occur during a query of the external data before an Oracle error is returned and the query is aborted. The default value is 0.
The CLUSTER
clause indicates that the table is to be part of cluster. The columns listed in this clause are the table columns that correspond to the cluster's columns. Generally, the cluster columns of a table are the column or columns that make up its primary key or a portion of its primary key.
Specify one column from the table for each column in the cluster key. The columns are matched by position, not by name.
A clustered table uses the cluster's space allocation. Therefore, do not use the PCTFREE
, PCTUSED
, INITRANS
, or MAXTRANS
parameters, the TABLESPACE
clause, or the storage_clause with the CLUSTER
clause.
Restrictions:
CLUSTER
with either ROWDEPENDENCIES
or NOROWDEPENDENCIES
unless the cluster has been created with the same ROWDEPENDENCIES
or NOROWDEPENDENCIES
setting.
Use the column_properties clauses to specify the storage attributes of a column.
The object_type_col_properties determine storage characteristics of an object column or attribute or an element of a collection column or attribute.
For column
, specify an object column or attribute.
The substitutable_column_clause
indicates whether object columns or attributes in the same hierarchy are substitutable for each other. You can specify that a column is of a particular type, or whether it can contain instances of its subtypes, or both.
ELEMENT
, you constrain the element type of a collection column or attribute to a subtype of its declared type.
IS
OF
[TYPE]
(ONLY
type
)
clause constrains the type of the object column to a subtype of its declared type.
NOT
SUBSTITUTABLE
AT
ALL
LEVELS
indicates that the object column cannot hold instances corresponding to any of its subtypes. Also, substitution is disabled for any embedded object attributes and elements of embedded nested tables and varrays. The default is SUBSTITUTABLE
AT
ALL
LEVELS
.
Restrictions on the substitutable_column_clause:
The LOB_storage_clause lets you specify the storage attributes of LOB data segments.
Restriction: You cannot specify the LOB_index_clause if table is partitioned.
Specify the LOB column name or LOB object attribute for which you are explicitly defining tablespace and storage characteristics that are different from those of the table. Oracle automatically creates a system-managed index for each LOB_item you create.
Specify the name of the LOB data segment. You cannot use LOB_segname if you specify more than one LOB_item.
The LOB_parameters clause lets you specify various elements of LOB storage.
ENABLE
STORAGE
IN
ROW
: If you enable storage in row, the LOB value is stored in the row (inline) if its length is less than approximately 4000 bytes minus system control information. This is the default.
Restriction: For an index-organized table, you cannot specify this parameter unless you have specified an OVERFLOW
segment in the index_org_table_clause.
DISABLE
STORAGE
IN
ROW
: If you disable storage in row, the LOB value is stored out of line (outside of the row) regardless of the length of the LOB value.
CHUNK
integer: Specify the number of bytes to be allocated for LOB manipulation. If integer is not a multiple of the database block size, Oracle rounds up (in bytes) to the next multiple. For example, if the database block size is 2048 and integer is 2050, Oracle allocates 4096 bytes (2 blocks). The maximum value is 32768 (32K), which is the largest Oracle block size allowed. The default CHUNK
size is one Oracle database block.
You cannot change the value of CHUNK
once it is set.
PCTVERSION
integer: Specify the maximum percentage of overall LOB storage space used for creating new versions of the LOB. The default value is 10, meaning that older versions of the LOB data are not overwritten until 10% of the overall LOB storage space is used.
This clause has been deprecated. Oracle automatically generates an index for each LOB column and names and manages the LOB indexes internally.
Although it is still possible for you to specify this clause, Oracle Corporation strongly recommends that you no longer do so. In any event, do not put the LOB index in a different tablespace from the LOB data.
See Also:
|
The varray_col_properties let you specify separate storage characteristics for the LOB in which a varray will be stored. In addition, if you specify this clause, Oracle will always store the varray in a LOB, even if it is small enough to be stored inline.
Restriction: You cannot specify the TABLESPACE
parameter of LOB_parameters as part of this clause. The LOB tablespace for a varray defaults to the containing table's tablespace.
The nested_table_col_properties let you specify separate storage characteristics for a nested table, which in turn enables you to define the nested table as an index-organized table. The storage table is created in the same tablespace as its parent table (using the default storage characteristics) and stores the nested table values of the column for which it was created.
You must include this clause when creating a table with columns or column attributes whose type is a nested table. Clauses within nested_table_col_properties that function the same way they function for parent object tables are not repeated here.
Specify the name of a column (or a top-level attribute of the table's object type) whose type is a nested table.
Specify the name of the table where the rows of nested_item reside. For a nonpartitioned table, the storage table is created in the same schema and the same tablespace as the parent table. For a partitioned table, the storage table is created in the default tablespace of the schema.
Restrictions on storage_table:
ALTER TABLE
statement.
Specify what Oracle returns as the result of a query.
If you do not specify the segment_attributes_clause or the LOB_storage_clause, the nested table is heap organized and is created with default storage characteristics.
Restrictions on nested_table_col_properties
TABLESPACE
(as part of the segment_attributes_clause) for a nested table. The tablespace is always that of the parent table.
ALTER
TABLE
.
ALTER
TABLE
statement.
The xmltype_storage_clauses let you specify storage attributes for an XMLType
column. XMLType
columns are always stored as character LOBs. The LOB_segname and LOB_parameters are as described for the LOB_storage_clause.
Use the table_partitioning_clauses to create a partitioned table.
Restriction: You cannot specify a TIMESTAMP
WITH
TIME
ZONE
column as part of the partitioning key.
Note: The storage of partitioned database entities in tablespaces of different block sizes is subject to several restrictions. Please refer to Oracle9i Database Administrator's Guide for a discussion of these restrictions. |
Use the range_partitioning clause to partition the table on ranges of values from column_list. For an index-organized table, column_list must be a subset of the primary key columns of the table.
Specify an ordered list of columns used to determine into which partition a row belongs (the partitioning key).
Restriction: The columns in column_list can be of any built-in datatype except ROWID
, LONG
, or LOB
.
Specify the physical partition attributes. If partition is omitted, Oracle generates a name with the form SYS_P
n for the partition. The partition must conform to the rules for naming schema objects and their part as described in "Schema Object Naming Rules".
Notes:
|
Specify the noninclusive upper bound for the current partition. value_list is an ordered list of literal values corresponding to column_list in the partition_by_range_clause. You can substitute the keyword MAXVALUE
for any literal in value_list. MAXVALUE
specifies a maximum value that will always sort higher than any other value, including NULL
.
Specifying a value other than MAXVALUE
for the highest partition bound imposes an implicit integrity constraint on the table.
See Also:
|
Use the list_partitioning clause to partition the table on lists of literal values from column. List partitioning is useful for controlling how individual rows map to specific partitions.
Each partition_value list must have at least one value. The MAXVALUE
keyword is not valid because it is meaningless for list partitions. List partitions are not ordered.
The string comprising the list of values for each partition can be up to 4K bytes. The total number of partition_values for all partitions cannot exceed 64K-1.
Restrictions on List Partitioning:
MAXVALUE
in the VALUES
clause.
VALUES
clause must be unique among all partitions of table.
Use the hash_partitioning clause to specify that the table is to be partitioned using the hash method. Oracle assigns rows to partitions using a hash function on values found in columns designated as the partitioning key. You can specify hash partitioning in one of two ways:
You can specify individual partitions by name. The TABLESPACE
clause specifies where the partition should be stored.
Alternatively, you can specify the number of partitions. In this case, Oracle assigns partition names of the form SYS_P
nnn. The STORE
IN
clause specifies one or more tablespaces where the hash partitions are to be stored. The number of tablespaces does not have to equal the number of partitions. If the number of partitions is greater than the number of tablespaces, Oracle cycles through the names of the tablespaces.
For both methods of hash partitioning, the only attribute you can specify for hash partitions (or subpartitions) is TABLESPACE
. Hash partitions inherit all other attributes from table-level defaults. Hash subpartitions inherit any attributes specified at the partition level, and inherit all other attributes from the table-level defaults.
Tablespace storage specified at the table level is overridden by tablespace storage specified at the partition level, which in turn is overridden by tablespace storage specified at the subpartition level.
If you specify tablespace storage in both the STORE
IN
clause of the hash_partitioning clause and the TABLESPACE
clause of the hash_partitioning_storage clause, the STORE
IN
clause determines placement of partitions as the table is being created. The TABLESPACE
clause determines the default tablespace at the table level for subsequent operations.
Specify an ordered list of columns used to determine into which partition a row belongs (the partitioning key).
Restrictions on Hash Partitioning
ROWID
or UROWID
pseudocolumns.
ROWID
, LONG
, or LOB.
Use the composite_partitioning
clause to first partition table by range, and then partition the partitions further into hash subpartitions. This combination of range partitioning and hash subpartitioning is called composite partitioning.
Restriction: You cannot specify composite partitioning for an index-organized table.
Use the subpartition_clause to indicate that Oracle should subpartition by hash each partition in table. The subpartitioning column_list is unrelated to the partitioning key, but is subject to the same restrictions.
Specify the default number of subpartitions in each partition of table, and optionally one or more tablespaces in which they are to be stored.
The default value is 1. If you do not specify the subpartition_clause here, Oracle will create each partition with one hash subpartition unless you subsequently specify the partition_level_subpartition clause.
The LOB_storage_clause lets you specify LOB storage characteristics for one or more LOB items in this partition. If you do not specify the LOB_storage_clause for a LOB item, Oracle generates a name for each LOB data partition. The system-generated names for LOB data and LOB index partitions take the form SYS_LOB_P
n and SYS_IL_P
n, respectively, where P stands for "partition" and n is a system-generated number.
The varray_col_properties lets you specify storage characteristics for one or more varray items in this partition.
The partition_level_subpartition clause lets you specify hash subpartitions for partition. This clause overrides the default settings established in the subpartition_clause.
Restriction: You can specify this clause only for a composite-partitioned table.
The row_movement_clause lets you specify whether a row can be moved to a different partition or subpartition because of a change to one or more of its key values during an update operation.
Specify ENABLE
to allow Oracle to move a row to a different partition or subpartition as the result of an update to the partitioning or subpartitioning key.
Specify DISABLE
if you want Oracle to return an error if an update to a partitioning or subpartitioning key would result in a row moving to a different partition or subpartition. This is the default.
Restriction: You can specify this clause only for a partitioned table.
For data that is accessed frequently, this clause indicates that the blocks retrieved for this table are placed at the most recently used end of the least recently used (LRU) list in the buffer cache when a full table scan is performed. This attribute is useful for small lookup tables.
As a parameter in the LOB_storage_clause, CACHE
specifies that Oracle places LOB data values in the buffer cache for faster access.
Restriction: You cannot specify CACHE
for an index-organized table. However, index-organized tables implicitly provide CACHE
behavior.
For data that is not accessed frequently, this clause indicates that the blocks retrieved for this table are placed at the least recently used end of the LRU list in the buffer cache when a full table scan is performed.
As a parameter in the LOB_storage_clause, NOCACHE
specifies that the LOB value is either not brought into the buffer cache or brought into the buffer cache and placed at the least recently used end of the LRU list. (The latter is the default behavior.) NOCACHE
is the default for LOB storage.
Restriction: You cannot specify NOCACHE
for index-organized tables.
CACHE
READS
applies only to LOB storage. It specifies that LOB values are brought into the buffer cache only during read operations, but not during write operations.
This clause lets you specify whether table will use row-level dependency tracking. With this feature, each row in the table has a system change number (SCN) that represents a time greater than or equal to the commit time of the last transaction that modified the row. You cannot change this setting after table is created.
Specify ROWDEPENDENCIES
if you want to enable row-level dependency tracking. This setting is useful primarily to allow for parallel propagation in replication environments. It increases the size of each row by 6 bytes.
Specify NOROWDEPENDENCIES if you do not want table to use the row level dependency tracking feature. This is the default.
See Also:
Oracle9i Replication for information about the use of row-level dependency tracking in replication environments |
Specify MONITORING
if you want modification statistics to be collected on this table. These statistics are estimates of the number of rows affected by DML statements over a particular period of time. They are available for use by the optimizer or for analysis by the user.
Restriction: You cannot specify MONITORING
for a temporary table.
Specify NOMONITORING
if you do not want Oracle to collect modification statistics on the table. This is the default.
Restriction: You cannot specify NOMONITORING
for a temporary table.
The parallel_clause lets you parallelize creation of the table and set the default degree of parallelism for queries and DML on the table after creation.
Specify NOPARALLEL
for serial execution. This is the default.
Specify PARALLEL
if you want Oracle to select a degree of parallelism equal to the number of CPUs available on all participating instances times the value of the PARALLEL_THREADS_PER_CPU
initialization parameter.
Specification of integer
indicates the degree of parallelism, which is the number of parallel threads used in the parallel operation. Each parallel thread may use one or two parallel execution servers. Normally Oracle calculates the optimum degree of parallelism, so it is not necessary for you to specify integer
.
Notes on the parallel_clause
INSERT
, UPDATE
, or DELETE
operations on table are executed serially without notification. Subsequent queries, however, will be executed in parallel.
CREATE
TABLE
... AS
SELECT
statements that reference remote objects can run in parallel. However, the "remote object" must really be on a remote database. The reference cannot loop back to an object on the local database (for example, by way of a synonym on the remote database pointing back to an object on the local database).
Oracle9i Database Performance Guide and Reference, Oracle9i Database Concepts, and Oracle9i Data Warehousing Guide for more information on parallelized operations
See Also:
The enable_disable_clause lets you specify whether Oracle should apply a constraint. By default, constraints are created in ENABLE
VALIDATE
state.
Restrictions:
Specify ENABLE
if you want the constraint to be applied to all new data in the table.
ENABLE VALIDATE
specifies that all old data also complies with the constraint. An enabled validated constraint guarantees that all data is and will continue to be valid.
If any row in the table violates the integrity constraint, the constraint remains disabled and Oracle returns an error. If all rows comply with the constraint, Oracle enables the constraint. Subsequently, if new data violates the constraint, Oracle does not execute the statement and returns an error indicating the integrity constraint violation.
If you place a primary key constraint in ENABLE
VALIDATE
mode, the validation process will verify that the primary key columns contain no nulls. To avoid this overhead, mark each column in the primary key NOT
NULL
before enabling the table's primary key constraint. (For optimal results, do this before entering data into the column.)
ENABLE NOVALIDATE
ensures that all new DML operations on the constrained data comply with the constraint. This clause does not ensure that existing data in the table complies with the constraint and therefore does not require a table lock.
If you specify neither VALIDATE
nor NOVALIDATE
, the default is VALIDATE
.
If you enable a unique or primary key constraint, and if no index exists on the key, Oracle creates a unique index. This index is dropped if the constraint is subsequently disabled, so Oracle rebuilds the index every time the constraint is enabled.
To avoid rebuilding the index and eliminate redundant indexes, create new primary key and unique constraints initially disabled. Then create (or use existing) nonunique indexes to enforce the constraint. Oracle does not drop a nonunique index when the constraint is disabled, so subsequent ENABLE
operations are facilitated.
If you change the state of any single constraint from ENABLE
NOVALIDATE
to ENABLE
VALIDATE
, the operation can be performed in parallel, and does not block reads, writes, or other DDL operations.
Restriction on ENABLE: You cannot enable a foreign key that references a unique or primary key that is disabled.
Specify DISABLE
to disable the integrity constraint. Disabled integrity constraints appear in the data dictionary along with enabled constraints. If you do not specify this clause when creating a constraint, Oracle automatically enables the constraint.
DISABLE
VALIDATE
disables the constraint and drops the index on the constraint, but keeps the constraint valid. This feature is most useful in data warehousing situations, where the need arises to load into a range-partitioned table a quantity of data with a distinct range of values in the unique key. In such situations, the disable validate state enables you to save space by not having an index. You can then load data from a nonpartitioned table into a partitioned table using the exchange_partition_clause
of the ALTER
TABLE
statement or using SQL*Loader. All other modifications to the table (inserts, updates, and deletes) by other SQL statements are disallowed.
If the unique key coincides with the partitioning key of the partitioned table, disabling the constraint saves overhead and has no detrimental effects. If the unique key does not coincide with the partitioning key, Oracle performs automatic table scans during the exchange to validate the constraint, which might offset the benefit of loading without an index.
DISABLE
NOVALIDATE
signifies that Oracle makes no effort to maintain the constraint (because it is disabled) and cannot guarantee that the constraint is true (because it is not being validated).
You cannot drop a table whose primary key is being referenced by a foreign key even if the foreign key constraint is in DISABLE
NOVALIDATE
state. Further, the optimizer can use constraints in DISABLE
NOVALIDATE
state.
See Also:
Oracle9i Database Performance Guide and Reference for information on when to use this setting |
If you specify neither VALIDATE
nor NOVALIDATE
, the default is NOVALIDATE
.
If you disable a unique or primary key constraint that is using a unique index, Oracle drops the unique index.
The UNIQUE
clause lets you enable or disable the unique constraint defined on the specified column or combination of columns.
The PRIMARY
KEY
clause lets you enable or disable the table's primary key constraint.
The CONSTRAINT
clause lets you enable or disable the integrity constraint named constraint.
This clause lets you either preserve or drop the index Oracle has been using to enforce a UNIQUE
or PRIMARY
KEY
constraint.
Restriction: You can specify this clause only when disabling a UNIQUE
or PRIMARY
KEY
constraint.
The using_index_clause
lets you specify an index for Oracle to use to enforce a UNIQUE
and PRIMARY
KEY
constraint, or lets you instruct Oracle to create the index used to enforce the constraint.
schema
.index
, Oracle will attempt to enforce the constraint using the specified index. If Oracle cannot find the index or cannot use the index to enforce the constraint, Oracle returns an error.
create_index_statement
, Oracle will attempt to create the index and use it to enforce the constraint. If Oracle cannot create the index or cannot use the index to enforce the constraint, Oracle returns an error.
INITRANS
, MAXTRANS
, TABLESPACE
, STORAGE
, and PCTFREE
parameters for the index.
table
is partitioned, you can specify a locally or globally partitioned index for the unique or primary key constraint.
"Explicit Index Control Example" for an example of how you can create an index for Oracle to use in enforcing a constraint
See Also:
Restrictions on the using_index_clause
using_index_clause
only when enabling UNIQUE
and PRIMARY
KEY
constraints.
schema.index
) or create an index (create_index_statement
) when enabling the primary key of an index-organized table.
See Also:
LOCAL
and the global_index_clause, and for a description of NOSORT
and LOGGING
|NOLOGGING
in relation to indexes
INITRANS
, MAXTRANS
, TABLESPACE
, STORAGE
, and PCTFREE
parameters
PRIMARY
KEY
and UNIQUE
constraints
The global_partitioned_index
clause lets you specify that the partitioning of the index is user defined and is not equipartitioned with the underlying table. By default, nonpartitioned indexes are global indexes.
Specify PARTITION
BY
RANGE
to indicate that the global index is partitioned on the ranges of values from the columns specified in column_list
. You cannot specify this clause for a local index.
For column_list
, specify the name of the column(s) of a table on which the index is partitioned. The column_list
must specify a left prefix of the index column list.
You cannot specify more than 32 columns in column_list,
and the columns cannot contain the ROWID
pseudocolumn or a column of type ROWID
.
The PARTITION
clause lets you describe the individual index partitions. The number of clauses determines the number of partitions. If you omit partition
, Oracle generates a name with the form SYS_P
n
.
For VALUES
LESS
THAN
(value_list
), specify the (noninclusive) upper bound for the current partition in a global index. The value_list
is a comma-separated, ordered list of literal values corresponding to column_list
in the partition_by_range_clause
. Always specify MAXVALUE
as the value_list
of the last partition.
Specify a table into which Oracle places the rowids of all rows violating the constraint. If you omit schema, Oracle assumes the exceptions table is in your own schema. If you omit this clause altogether, Oracle assumes that the table is named EXCEPTIONS
. The exceptions table must be on your local database.
You can create the EXCEPTIONS
table using one of these scripts:
UTLEXCPT.SQL
uses physical rowids. Therefore it can accommodate rows from conventional tables but not from index-organized tables. (See the Note that follows.)
UTLEXPT1.SQL
uses universal rowids, so it can accommodate rows from both conventional and index-organized tables.
If you create your own exceptions table, it must follow the format prescribed by one of these two scripts.
See Also:
|
Note: If you are collecting exceptions from index-organized tables based on primary keys (rather than universal rowids), you must create a separate exceptions table for each index-organized table to accommodate its primary-key storage. You create multiple exceptions tables with different names by modifying and resubmitting the script. See Also:
|
Specify CASCADE
to disable any integrity constraints that depend on the specified integrity constraint. To disable a primary or unique key that is part of a referential integrity constraint, you must specify this clause.
Restriction: You can specify CASCADE
only if you have specified DISABLE
.
AS subquerySpecify a subquery to determine the contents of the table. The rows returned by the subquery are inserted into the table upon its creation.
If you specify the parallel_clause in this statement, Oracle will ignore any value you specify for the INITIAL
storage parameter, and will instead use the value of the NEXT
parameter.
The ORDER
BY
clause lets you order rows returned by the statements.
For object tables, subquery can contain either one expression corresponding to the table type, or the number of top-level attributes of the table type.
If subquery returns (in part or totally) the equivalent of an existing materialized view, Oracle may use the materialized view (for query rewrite) in place of one or more tables specified in subquery.
See Also:
Oracle9i Data Warehousing Guide for more information on materialized views and query rewrite |
Oracle derives datatypes and lengths from the subquery. Oracle also follows the following rules for integrity constraints:
NOT
NULL
constraints on columns in the new table that existed on the corresponding columns of the selected table if the subquery selects the column rather than an expression containing the column.
CREATE
TABLE
statement contains both AS
subquery and a CONSTRAINT
clause or an ENABLE
clause with the EXCEPTIONS
INTO
clause, Oracle ignores AS
subquery. If any rows violate the constraint, Oracle does not create the table and returns an error.
If all expressions in subquery are columns, rather than expressions, you can omit the columns from the table definition entirely. In this case, the names of the columns of table are the same as the columns in subquery.
You can use subquery in combination with the TO_LOB
function to convert the values in a LONG
column in another table to LOB values in a column of the table you are creating.
See Also:
|
Restrictions:
CREATE
TABLE
statement that contains AS
subquery. Instead, you must create the table without the constraint and then add it later with an ALTER
TABLE
statement.
To define the employees
table owned by the demo Human Resources (hr
) schema, you would issue the following statement:
CREATE TABLE employees ( employee_id NUMBER(6) , first_name VARCHAR2(20) , last_name VARCHAR2(25) CONSTRAINT emp_last_name_nn NOT NULL , email VARCHAR2(25) CONSTRAINT emp_email_nn NOT NULL , phone_number VARCHAR2(20) , hire_date DATE DEFAULT SYSDATE CONSTRAINT emp_hire_date_nn NOT NULL , job_id VARCHAR2(10) CONSTRAINT emp_job_nn NOT NULL , salary NUMBER(8,2) CONSTRAINT emp_salary_nn NOT NULL , commission_pct NUMBER(2,2) , manager_id NUMBER(6) , department_id NUMBER(4) , dn VARCHAR2(300) , CONSTRAINT emp_salary_min CHECK (salary > 0) , CONSTRAINT emp_email_uk UNIQUE (email) ) ;
This table contains twelve columns. The employee_id
column is of datatype NUMBER
. The hire_date
column is of datatype DATE
and has a default value of SYSDATE
. The last_name
column is of type VARCHAR2
and has a NOT
NULL
constraint, and so on.
The following statement creates a temporary table today_sales
for use by sales representatives in the sample database. Each sales representative session can store its own sales data for the day in the table. The temporary data is deleted at the end of the session.
CREATE GLOBAL TEMPORARY TABLE today_sales ON COMMIT PRESERVE ROWS AS SELECT * FROM orders WHERE order_date = SYSDATE;
The following statement creates a substitutable table from the person_t
type, which was created in "Type Hierarchy Example":
CREATE TABLE persons OF person_t;
The following statement creates a table with a substitutable column of type person_t
:
CREATE TABLE books (title VARCHAR2(100), author person_t);
When you insert into persons
or books
, you can specify values for the attributes of person_t
or any of its subtypes. Example insert statements appear in "Inserting into a Substitutable Tables and Columns Examples".
You can extract data from such tables using built-in functions and conditions. For examples, see the functions TREAT and SYS_TYPEID, and "IS OF type Conditions".
To define the sample table salgrade
in the human_resource
tablespace with a small storage capacity and limited allocation potential, issue the following statement:
CREATE TABLE salgrade ( grade NUMBER CONSTRAINT pk_salgrade PRIMARY KEY USING INDEX TABLESPACE users_a, losal NUMBER, hisal NUMBER ) TABLESPACE human_resource STORAGE (INITIAL 6144 NEXT 6144 MINEXTENTS 1 MAXEXTENTS 5 );
The above statement also defines a primary key constraint on the grade
column and specifies that the index Oracle creates to enforce this constraint is created in the users_a
tablespace.
PARALLEL
Example
The following statement creates a table using an optimum number of parallel execution servers to scan employees
and to populate dept_80
:
CREATE TABLE dept_80 PARALLEL AS SELECT * FROM employees WHERE department_id = 80;
Using parallelism speeds up the creation of the table because Oracle uses parallel execution servers to create the table. After the table is created, querying the table is also faster, because the same degree of parallelism is used to access the table.
NOPARALLEL
ExampleThe following statement creates the same table serially. Subsequent DML and queries on the table will also be serially executed.
CREATE TABLE dept_80 AS SELECT * FROM employees WHERE department_id = 80;
ENABLE
VALIDATE
Example
The following statement creates the departments
table, defines a NOT
NULL
constraint, and places it in ENABLE
VALIDATE
state:
CREATE TABLE departments ( department_id NUMBER(4) , department_name VARCHAR2(30) CONSTRAINT dept_name_nn NOT NULL , manager_id NUMBER(6) , location_id NUMBER(4) , dn VARCHAR2(300) ) ;
DISABLE
Example
The following statement creates the same departments
table but also defines a disabled primary key constraint:
CREATE TABLE departments ( department_id NUMBER(4) PRIMARY KEY DISABLE , department_name VARCHAR2(30) CONSTRAINT dept_name_nn NOT NULL , manager_id NUMBER(6) , location_id NUMBER(4) , dn VARCHAR2(300) ) ;
The following statement creates the sample table pm.print_media with a nested table column ad_textdocs_ntab
:
CREATE TABLE print_media ( product_id NUMBER(6) , ad_id NUMBER(6) , ad_composite BLOB , ad_sourcetext CLOB , ad_finaltext CLOB , ad_fltextn NCLOB , ad_textdocs_ntab textdoc_tab , ad_photo BLOB , ad_graphic BFILE , ad_header adheader_typ , press_release LONG ) NESTED TABLE ad_textdocs_ntab STORE AS textdocs_nestedtab;
The following statement shows the same print_media
table with some hypothetical LOB storage characteristics:
CREATE TABLE print_media ( product_id NUMBER(6) , ad_id NUMBER(6) , ad_composite BLOB , ad_sourcetext CLOB , ad_finaltext CLOB , ad_fltextn NCLOB , ad_textdocs_ntab textdoc_tab , ad_photo BLOB , ad_graphic BFILE , ad_header adheader_typ , press_release LONG ) NESTED TABLE ad_textdocs_ntab STORE AS textdocs_nestedtab LOB (ad_sourcetext, ad_finaltext), STORE AS (TABLESPACE lob_seg_ts STORAGE (INITIAL 6144 NEXT 6144) CHUNK 4000 NOCACHE LOGGING);
In the example, Oracle rounds the value of CHUNK
up to 4096 (the nearest multiple of the block size of 2048).
The following statement creates the hr.countries table, which is index organized:
CREATE TABLE countries ( country_id CHAR(2) CONSTRAINT country_id_nn NOT NULL , country_name VARCHAR2(40) , currency_name VARCHAR2(25) , currency_symbol VARCHAR2(3) , region VARCHAR2(15) , CONSTRAINT country_c_id_pk PRIMARY KEY (country_id) ) ORGANIZATION INDEX INCLUDING country_name PCTTHRESHOLD 2 STORAGE ( INITIAL 4K NEXT 2K PCTINCREASE 0 MINEXTENTS 1 MAXEXTENTS 1 ) OVERFLOW STORAGE ( INITIAL 4K NEXT 2K PCTINCREASE 0 MINEXTENTS 1 MAXEXTENTS 1 );
The following statement creates an external table that represents a subset of the sample hr.employees
table. The opaque_format_spec
is shown in italics. Please refer to Oracle9i Database Utilities for information on the ORACLE_LOADER
access driver and how to specify values for the opaque_format_spec
.
CREATE TABLE emp_external ( employee_id NUMBER(6), last_name VARCHAR2(20), email VARCHAR2(25), hire_date DATE, job_id VARCHAR2(10), salary NUMBER(8,2) ) ORGANIZATION EXTERNAL (TYPE oracle_loader DEFAULT DIRECTORY admin ACCESS PARAMETERS ( RECORDS DELIMITED BY newline BADFILE 'ulcase1.bad' DISCARDFILE 'ulcase1.dis' LOGFILE 'ulcase1.log' SKIP 20 FIELDS TERMINATED BY "," OPTIONALLY ENCLOSED BY '"' ( deptno INTEGER EXTERNAL, dname CHAR, loc CHAR ) ) LOCATION ('ulcase1.dat') ) REJECT LIMIT UNLIMITED;
The sample table oe.orders
is not partitioned. However, you might want to partition such a large table by range so that order information could be handled by time period, as shown in this example:
CREATE TABLE product_information ( product_id NUMBER(6) , product_name VARCHAR2(50) , product_description VARCHAR2(2000) , category_id NUMBER(2) , weight_class NUMBER(1) , warranty_period INTERVAL YEAR TO MONTH , supplier_id NUMBER(6) , product_status VARCHAR2(20) , list_price NUMBER(8,2) , min_price NUMBER(8,2) , catalog_url VARCHAR2(50) , CONSTRAINT product_status_lov CHECK (product_status in ('orderable' ,'planned' ,'under development' ,'obsolete') ) ) PARTITION BY HASH (product_id) PARTITIONS 5 STORE IN (prod_ts1, prod_ts2, prod_ts3, prod_ts4, prod_ts5);
The sales table in the sample schema sh is partitioned by range. The following example shows the creation of the sales table (constraints and storage elements have been omitted from the example):
CREATE TABLE range_sales ( prod_id NUMBER(6) , cust_id NUMBER , time_id DATE , channel_id CHAR(1) , promo_id NUMBER(6) , quantity_sold NUMBER(3) , amount_sold NUMBER(10,2) ) PARTITION BY RANGE (time_id) (PARTITION SALES_Q1_1998 VALUES LESS THAN (TO_DATE('01-APR-1998','DD-MON-YYYY')), PARTITION SALES_Q2_1998 VALUES LESS THAN (TO_DATE('01-JUL-1998','DD-MON-YYYY')), PARTITION SALES_Q3_1998 VALUES LESS THAN (TO_DATE('01-OCT-1998','DD-MON-YYYY')), PARTITION SALES_Q4_1998 VALUES LESS THAN (TO_DATE('01-JAN-1999','DD-MON-YYYY')), PARTITION SALES_Q1_1999 VALUES LESS THAN (TO_DATE('01-APR-1999','DD-MON-YYYY')), PARTITION SALES_Q2_1999 VALUES LESS THAN (TO_DATE('01-JUL-1999','DD-MON-YYYY')), PARTITION SALES_Q3_1999 VALUES LESS THAN (TO_DATE('01-OCT-1999','DD-MON-YYYY')), PARTITION SALES_Q4_1999 VALUES LESS THAN (TO_DATE('01-JAN-2000','DD-MON-YYYY')), PARTITION SALES_Q1_2000 VALUES LESS THAN (TO_DATE('01-APR-2000','DD-MON-YYYY')), PARTITION SALES_Q2_2000 VALUES LESS THAN (TO_DATE('01-JUL-2000','DD-MON-YYYY')), PARTITION SALES_Q3_2000 VALUES LESS THAN (TO_DATE('01-OCT-2000','DD-MON-YYYY')), PARTITION SALES_Q4_2000 VALUES LESS THAN (MAXVALUE)) ;
For information about partitioned table maintenance operations, see the Oracle9i Database Administrator's Guide.
The following statement shows how the demo table oe.customers
might have been created as a list-partitioned table (some columns and all constraints of the demo table have been omitted in this example):
CREATE TABLE list_customers ( customer_id NUMBER(6) , cust_first_name VARCHAR2(20) , cust_last_name VARCHAR2(20) , cust_address CUST_ADDRESS_TYP , nls_territory VARCHAR2(30) , cust_email VARCHAR2(30)) PARTITION BY LIST (nls_territory) ( PARTITION asia VALUES ('CHINA', 'THAILAND'), PARTITION europe VALUES ('GERMANY', 'ITALY', 'SWITZERLAND'), PARTITION west VALUES ('AMERICA'), PARTITION east VALUES ('INDIA'));
This statement creates a partitioned table pt
with two partitions p1 and p2
, and three LOB columns, b
, c
, and d
:
CREATE TABLE print_media ( product_id NUMBER(6) , ad_id NUMBER(6) , ad_composite BLOB , ad_sourcetext CLOB , ad_finaltext CLOB , ad_fltextn NCLOB , ad_textdocs_ntab textdoc_tab , ad_photo BLOB , ad_graphic BFILE , ad_header adheader_typ , press_release LONG ) NESTED TABLE ad_textdocs_ntab STORE AS textdocs_nestedtab LOB (ad_composite, ad_photo, ad_finaltext) STORE AS(STORAGE (NEXT 20M)) PARTITION BY RANGE (product_id) (PARTITION P1 VALUES LESS THAN (3000) TABLESPACE ts1 LOB (ad_composite, ad_photo) STORE AS (TABLESPACE tsa STORAGE (INITIAL 20M)), PARTITION P2 VALUES LESS THAN (MAXVALUE) LOB (ad_composite, ad_finaltext) STORE AS (TABLESPACE tsb) TABLESPACE tsx; CREATE TABLE PT (A NUMBER, B BLOB, C CLOB, D CLOB) LOB (B,C,D) STORE AS (STORAGE (NEXT 20M)) PARTITION BY RANGE (A) (PARTITION P1 VALUES LESS THAN (10) TABLESPACE TS1 LOB (B,D) STORE AS (TABLESPACE TSA STORAGE (INITIAL 20M)), PARTITION P2 VALUES LESS THAN (20) LOB (B,C) STORE AS (TABLESPACE TSB) TABLESPACE TSX;
Partition p1 will be in tablespace ts1
. The LOB data partitions for ad_composite
and ad_photo
will be in tablespace tsa
. The LOB data partition for ad_finaltext
will be in tablespace ts1
. The storage attribute INITIAL
is specified for LOB columns ad_composite
and ad_photo
. Other attributes will be inherited from the default table-level specification. The default LOB storage attributes not specified at the table level will be inherited from the tablespace tsa
for columns ad_composite
and ad_photo
and tablespace ts1
for column ad_finaltext
. LOB index partitions will be in the same tablespaces as the corresponding LOB data partitions. Other storage attributes will be based on values of the corresponding attributes of the LOB data partitions and default attributes of the tablespace where the index partitions reside.
Partition p2
will be in the default tablespace tsx
. The LOB data for ad_composite
and ad_finaltext
will be in tablespace tsb
. The LOB data for ad_photo
will be in tablespace tsx
. The LOB index for columns ad_composite
and ad_finaltext
will be in tablespace tsb
. The LOB index for column ad_photo
will be in tablespace tsx
.
The sample table oe.product_information
is not partitioned. However, you might want to partition such a large table by hash for performance reasons, as shown in this example. (The tablespace names are hypothetical in this example.)
CREATE TABLE hash_products ( product_id NUMBER(6) , product_name VARCHAR2(50) , product_description VARCHAR2(2000) , category_id NUMBER(2) , weight_class NUMBER(1) , warranty_period INTERVAL YEAR TO MONTH , supplier_id NUMBER(6) , product_status VARCHAR2(20) , list_price NUMBER(8,2) , min_price NUMBER(8,2) , catalog_url VARCHAR2(50) , CONSTRAINT product_status_lov CHECK (product_status in ('orderable' ,'planned' ,'under development' ,'obsolete') ) ) PARTITION BY HASH (product_id) PARTITIONS 5 STORE IN (prod_ts1, prod_ts2, prod_ts3, prod_ts4, prod_ts5);
The table created in the "Range Partitioning Example" divides data by time of sale. If you plan to access recent data according to distribution channel as well as time, composite partitioning might be more appropriate. The following example creates the same sales
table, but with composite partitioning. The partitions with the most recent data are subpartitioned with both Oracle-defined and user-defined subpartition names. (Constraints and storage attributes have been omitted from the example).
CREATE TABLE composite_sales ( prod_id NUMBER(6) , cust_id NUMBER , time_id DATE , channel_id CHAR(1) , promo_id NUMBER(6) , quantity_sold NUMBER(3) , amount_sold NUMBER(10,2) ) PARTITION BY RANGE (time_id) SUBPARTITION BY HASH (channel_id) (PARTITION SALES_Q1_1998 VALUES LESS THAN (TO_DATE('01-APR-1998','DD-MON-YYYY')), PARTITION SALES_Q2_1998 VALUES LESS THAN (TO_DATE('01-JUL-1998','DD-MON-YYYY')), PARTITION SALES_Q3_1998 VALUES LESS THAN (TO_DATE('01-OCT-1998','DD-MON-YYYY')), PARTITION SALES_Q4_1998 VALUES LESS THAN (TO_DATE('01-JAN-1999','DD-MON-YYYY')), PARTITION SALES_Q1_1999 VALUES LESS THAN (TO_DATE('01-APR-1999','DD-MON-YYYY')), PARTITION SALES_Q2_1999 VALUES LESS THAN (TO_DATE('01-JUL-1999','DD-MON-YYYY')), PARTITION SALES_Q3_1999 VALUES LESS THAN (TO_DATE('01-OCT-1999','DD-MON-YYYY')), PARTITION SALES_Q4_1999 VALUES LESS THAN (TO_DATE('01-JAN-2000','DD-MON-YYYY')), PARTITION SALES_Q1_2000 VALUES LESS THAN (TO_DATE('01-APR-2000','DD-MON-YYYY')), PARTITION SALES_Q2_2000 VALUES LESS THAN (TO_DATE('01-JUL-2000','DD-MON-YYYY')) SUBPARTITIONS 8, PARTITION SALES_Q3_2000 VALUES LESS THAN (TO_DATE('01-OCT-2000','DD-MON-YYYY')) (SUBPARTITION ch_c, SUBPARTITION ch_i, SUBPARTITION ch_p, SUBPARTITION ch_s, SUBPARTITION ch_t), PARTITION SALES_Q4_2000 VALUES LESS THAN (MAXVALUE) SUBPARTITIONS 4) ;
Consider object type dept_t
:
CREATE TYPE dept_t AS OBJECT ( dname VARCHAR2(100), address VARCHAR2(200) );
Object table dept holds department objects of type dept_t
:
CREATE TABLE dept OF dept_t;
The following statement creates object table salesreps
with a user-defined object type, salesrep_t
:
CREATE OR REPLACE TYPE salesrep_t AS OBJECT ( repId NUMBER, repName VARCHAR2(64)); CREATE TABLE salesreps OF salesrep_t;
REF
Example
The following example uses the type dept_t and the table dept (created in "Object Table Examples") . A table with a scoped REF
is then created.
CREATE TABLE emp ( ename VARCHAR2(100), enumber NUMBER, edept REF dept_t SCOPE IS dept );
The following statement creates a table with a REF
column which has a referential constraint defined on it:
CREATE TABLE emp ( ename VARCHAR2(100), enumber NUMBER, edept REF dept_t REFERENCES dept);
This example creates an object type and a corresponding object table whose OID is primary key based:
CREATE TYPE emp_t AS OBJECT (empno NUMBER, address CHAR(30)); CREATE TABLE emp OF emp_t (empno PRIMARY KEY) OBJECT IDENTIFIER IS PRIMARY KEY;
You can subsequently reference the emp
object table in either of the following two ways:
CREATE TABLE dept (dno NUMBER, mgr_ref REF emp_t SCOPE IS emp); CREATE TABLE dept ( dno NUMBER, mgr_ref REF emp_t CONSTRAINT mgr_in_emp REFERENCES emp);
CREATE TYPE address_t AS OBJECT ( hno NUMBER, street VARCHAR2(40), city VARCHAR2(20), zip VARCHAR2(5), phone VARCHAR2(10) ); CREATE TYPE person AS OBJECT ( name VARCHAR2(40), dateofbirth DATE, homeaddress address, manager REF person ); CREATE TABLE persons OF person ( homeaddress NOT NULL, UNIQUE (homeaddress.phone), CHECK (homeaddress.zip IS NOT NULL), CHECK (homeaddress.city <> 'San Francisco') );
|
Copyright © 1996-2001, Oracle Corporation. All Rights Reserved. |
|