0228-0230

Previous Table of Contents Next

Page 228

table constraint is optional and is used to define an integrity constraint as part of the table, such as the primary key.

PCTFREE is optional but has a default of 10. This indicates that 10 percent of each data block is reserved for future updates to the table's rows. Integers from 1 to 99 are allowed.

PCTUSED is optional but has a default of 40. This indicates the minimum percentage of space that Oracle maintains before a data block becomes a candidate for row insertion. Integers from 1 to 99 are allowed. The sum of PCTFREE and PCTUSED must be less than 100.

INITRANS is optional but has a default of 1. Integers from 1 to 255 are allowed. It is recommended that you leave this alone. This is an allocation of the number of transaction entries assigned within the data block for the table.

MAXTRANS is optional but has a default that is a function of the data block size . This is used to identify the maximum number of concurrent transactions that can update a data block for your table. You should not change this parameter.

TABLESPACE is optional but has a default value of the tablespace name of the owner of the schema. You can use a tablespace name that is different from the default, and tablespace names are usually application-dependent. The DBA will give you recommendations.

STORAGE is optional and has default characteristics defined by the DBA.

CLUSTER is optional and specifies that a table is part of a cluster. You must identify the columns from the table that are clustered. Typically, the cluster columns are columns that comprise the primary key.

ENABLE is optional and turns on an integrity constraint.

DISABLE is optional and turns off an integrity constraint.

AS subquery is optional and inserts the rows returned by the subquery into the table upon creation.

Once the table is created, you can use the ALTER TABLE command to make alterations to the table. To modify an integrity constraint, drop the constraint first and then re-create it.

Presented now are two examples of creating tables:

 CREATE TABLE ADDRESSES (ADRS_ID          NUMBER(6),                         ACTIVE_DATE      DATE,                         BOX_NUMBER       NUMBER(6),                         ADDRS_1          VARCHAR2(40),                         ADDRS_2          VARCHAR2(40),                         CITY             VARCHAR2(40),                         STATE            VARCHAR2(2),                         ZIP              VARCHAR2(10)); 

Page 229

This is the simplest form of a table creation using all the default capabilities. The second example follows :

 CREATE TABLE ADDRESSES       (ADRS_ID       NUMBER(6)   CONSTRAINT PK_ADRS PRIMARY KEY, ACTIVE_DATE   DATE           DEFAULT SYSDATE,                               BOX_NUMBER    NUMBER(6)      DEFAULT NULL,                               ADDRS_1       VARCHAR2(40)   NOT NULL,                               ADDRS_2       VARCHAR2(40)   DEFAULT NULL,                               CITY          VARCHAR2(40)   DEFAULT NULL,                               STATE         VARCHAR2(2)    DEFAULT `NY',                               ZIP           VARCHAR2(10))    PCTFREE 5    PCTUSED 65    TABLESPACE adrs_data    STORAGE (INITIAL 5140             NEXT      5140             MINEXTENTS  1             MAXEXTENTS 10             PCTINCREASE 10); 

This example uses data constraints, and certain storage parameters are in effect. Using PCTFREE and PCTUSED is a good idea if your data is relatively static.

Partitioned Tables

Oracle8 allows the use of table partitions to increase the availability of large databases while making them easier to manage. With respect to availability, losing one table partition to disk problems does not crash the entire database. As for management, updates required in one partition may not affect other partitions. Using this table creation extension, you can create range-partitioned tables using the following syntax:

 CREATE TABLE schema.table ON schema.table PARTITION BY RANGE (partition_key) (PARTITION partition_name VALUES LESS THAN (column_value) 

PARTITION BY RANGE is the table's partition key.

PARTITION is the table's partition name.

VALUES LESS THAN defines the table partition range based on the values available for the partition key.

All parameters available for non-partitioned tables are available for partitioned table creation. In addition, partitions created have their own sets of characteristics, such as the tablespace in which they reside. Storage settings such as MAXTRANS are also set for each partition, independent of other partitions.

In creating partitioned tables, keep in mind the following guidelines. The upper bound of the previous partition is the implicit lower bound for all partitions subsequent to the first. Also, table partitions must have ranges that increase.

MAXVALUE is a reserved word that can be used as the highest value of the highest range of a set of partitions being created. Partition keys are not limited to not null columns, so in the selection

Page 230

process, nulls might be encountered . As nulls are processed , they are sorted as a value greater than all others except MAXVALUE.

Accessing Partitioned Tables

Creating partitioned tables requires a syntax for accessing information stored in the partitions. The following syntax is useful for querying data from a partitioned table:

 SELECT * FROM addresses PARTITION (a1) 

This statement selects all information from the addresses table created under partition A1.

Indexes

Indexes are used to increase performance of the database. An index is created on one or more columns of a table or cluster. Multiple indexes per table are allowed. You need the create index system privilege to execute this command. The DBA is responsible for administering these privileges.

Non-Partitioned Indexes

The syntax to create a non-partitioned index is as follows:

 CREATE INDEX schema.index ON schema.table (COLUMN  ASCDESC) CLUSTER schema.cluster  INITRANS x MAXTRANS x TABLESPACE name   STORAGE  clause PCTFREE x NOSORT 

In this example, schema is an optional parameter to identify the database schema where the table is placed. The default is your own schema.

index is mandatory and is the name of the index.

ON is a mandatory reserved word.

table is a mandatory table name upon which the index is built.

COLUMN is the column name to be indexed. If there is more than one column, make sure they are listed in order of priority.

ASCDESC are optional parameters. Indexes are built in ascending order by default. Use DESC for descending order.

CLUSTER is needed only if this index is for a cluster.

INITRANS is optional but has a default of 1. Integers from 1 to 255 are allowed. It is recommended that this parameter not be changed. This is an allocation of the number of transaction entries assigned within the data block for the index.

MAXTRANS is optional but has a default that is a function of the data block size. It is used to identify the maximum number of concurrent transactions that can update a data block for the index. It is recommended that this parameter not be changed.

Previous Table of Contents Next