Tables
Every database designer will have to create a table sometime. The CREATE TABLE system privilege is needed to execute this command. The DBA is responsible for administering these privileges. The syntax to create a table is
CREATE TABLE schema.TABLE (COLUMN DATATYPE default expression column constraint) table constraint
PCTFREE x PCTUSED x INITRANS x MAXTRANS x TABLESPACE name STORAGE clause CLUSTER cluster clause
ENABLE clause DISABLE clause AS subquery
In this syntax, SCHEMA is an optional parameter to identify which database schema to place this table in. The default is your own.
TABLE is mandatory and is the name of your table.
COLUMN DATATYPE are required to identify each column in the table. Separate the columns with commas. There is a maximum of 254 columns per table.
The DEFAULT expression is optional and is used to assign a default value to a column when a subsequent insert statement fails to assign a value.
COLUMN CONSTRAINT is optional. It is used to define an integrity constraint such as not null.
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 for each data block will be 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 used 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. It is recommended that this parameter not be changed.
TABLESPACE is optional but has a default value as the tablespace name of the owner of the schema. A different tablespace name than the default can be used. Tablespace names are usually application-dependent. The DBA will be able to give proper recommendations.
STORAGE is optional and has default characteristics defined by the DBA.
CLUSTER is optional and specifies that a table is to be part of a cluster. You must identify the columns from the table that need to be 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.
Let's look at two examples on 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));
This is the simplest form of a table create using all of 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);
In this example, data constraints are being utilized and certain storage parameters will be in effect. Using PCTFREE and PCTUSED is a good idea if your data is relatively static.
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. The CREATE INDEX system privilege is needed to execute this command. The DBA is responsible for administering these privileges. The syntax to create an index is
CREATE INDEX schema.index ON schema.table (COLUMN ASC/DESC)
CLUSTER schema.cluster INITRANS x MAXTRANS x TABLESPACE name STORAGE clause PCTFREE x NOSORT
In this syntax, SCHEMA is an optional parameter to identify which database schema to place this table in. The default is your own.
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 will be built.
COLUMN is the column name to be indexed. If there is more than one column, make sure they are in order of priority.
ASC/DESC 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.
TABLESPACE is optional but has a default value as the tablespace name of the owner of the schema. A different tablespace name than the default might be needed. The DBA will be able to give some recommendations.
STORAGE is optional and has default characteristics defined by the DBA.
PCTFREE is optional but has a default of 10. This indicates that 10 percent for each data block will be reserved for future updates to the index. Integers from 1 to 99 are allowed.
NOSORT is an optional parameter that will save time when creating the index if the table data is already stored in ascending order. This cannot be used if a clustered index is being created.
Using the addresses table defined from the create table example, two indexes will be created in the next example.
CREATE INDEX x_adrs_id ON ADDRESSES (ADRS_ID);
This will create an index on the adrs_id column only.
CREATE INDEX x_city_state ON ADDRESSES (CITY,STATE)
TABLESPACE application_indexes;
This index has two columns; CITY is the primary column. In order for queries to use an index, the column names must be part of the select statement. If a select statement included STATE but not CITY, the index would not be used. However, if the select statement contained a reference to CITY but not STATE, part of the index would be used because CITY is the first column of the index.
Sequences
Sequences are a great way to have the database automatically generate unique integer primary keys. The CREATE SEQUENCE system privilege is needed to execute this command. The DBA is responsible for administering these privileges. The syntax to create a sequence is
CREATE SEQUENCE schema.name
INCREMENT BY x
START WITH x
MAXVALUE x NOMAXVALUE
MINVALUE x NOMINVALUE
CYCLE NOCYCLE
CACHE x NOCACHE
ORDER NOORDER
In this syntax, SCHEMA is an optional parameter that identifies which database schema to place this sequence in. The default is your own.
NAME is mandatory because it is the name of the sequence.
INCREMENT BY is optional. The default is one. Zero is not allowed. If a negative integer is specified, the sequence will descend in order. A positive integer will make the sequence ascend (the default).
START WITH is an optional integer that enables the sequence to begin anywhere.
MAXVALUE is an optional integer that places a limit on the sequence.
NOMAXVALUE is optional. It causes the maximum ascending limit to be 10 27 and -1 for descending sequences. This is the default.
MINVALUE is an optional integer that determines the minimum a sequence can be.
NOMINVALUE is optional. It causes the minimum ascending limit to be 1 and -(10 26) for descending sequences. This is the default.
CYCLE is an option that enables the sequence to continue even when the maximum has been reached. If the maximum is reached, the next sequence that will be generated is whatever the minimum value is.
NOCYCLE is an option that does not enable the sequence to generate values beyond the defined maximum or minimum. This is the default.
CACHE is an option that enables sequence numbers to be preallocated that will be stored in memory for faster access. The minimum value is 2.
NOCACHE is an option that will not enable the preallocation of sequence numbers.
ORDER is an option that ensures the sequence numbers are generated in order of request.
NOORDER is an option that does not ensure that sequence numbers are generated in the order they are requested.
If you want to create a sequence for your adrs_id column in the ADDRESSES table, it could look like the following example:
CREATE SEQUENCE adrs_seq
INCREMENT BY 5
START WITH 100;
To generate a new sequence number, use the pseudocolumn NEXTVAL. This needs to be preceded with your sequence name. For example, adrs_seq.nextval would return 100 for the first access and 105 for the second. If determining the current sequence number is necessary, use CURRVAL. Therefore, adrs_seq.currval will return the current value of the sequence.