0231-0233

Previous Table of Contents Next

Page 231

TABLESPACE is optional but has a default value of the tablespace name of the owner of the schema. You might need a tablespace name that is different from the default. The DBA can give you 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 of each data block is reserved for future updates to the index. Integers from 1 to 99 are allowed.

NOSORT is an optional parameter that saves 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 are created in the following example:

 CREATE INDEX x_adrs_id ON ADDRESSES (ADRS_ID); 

This creates 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. For queries to use an index, the column names must be part of the select statement. If a select statement includes STATE but not CITY, the index is not used. However, if the select statement contains a reference to CITY but not STATE, part of the index is used because CITY is the first column of the index.

Partitioned Indexes

The intent of Oracle8 in allowing the use of partitioned indexes is similar to the intent of using partitioned tables, to make large databases more manageable and available. As a result, you can create indexes as partitioned indexes, either prefixed or non-prefixed. Partitioned indexes can also be global or local. Partitioned indexes are only supported on non-clustered tables with index entries stored according to the partition key.

The syntax to create a partitioned index is as follows :

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

PARTITION BY RANGE is the partition key for the index.

PARTITION is the partition name for the index.

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

Creating prefixed instead of non-prefixed indexes is a decision based on the application. In a prefixed index, the partition key is based on the leftmost columns in the index. By default, all single-column partitioned indexes are prefixed. Non-prefixed indexes are allowed only on composite indexes. The deciding factor in prefixed versus non-prefixed is that the index is not used in SQL queries unless the WHERE clause uses the index prefix.

Page 232

NOTE

Using partitioned indexes in Oracle8 might cause full partition scans rather than full table scans , which enhance performance even if the index is not used.

Another decision in creating partitioned indexes is whether to use a global or a local partitioned index. Global indexes include keys from all table partitions, whereas local indexes use only corresponding partition keys. Global indexes enhance performance in large database applications because the number of index hits is reduced.

Sequences

Sequences are a great way to have the database automatically generate unique integer primary keys. The CREATE SEQUENCE system privilege is required to execute this command. The DBA is responsible for administering this privilege. The CREATE SEQUENCE syntax is shown in the following example:

 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 the database schema where the sequence is placed. The default is your own schema.

name is mandatory because it is the name of the sequence.

INCREMENT BY is optional. The default is 1. Zero is not allowed. If you specify a negative integer, the sequence descends in order. A positive integer makes 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 ²⁷ and _1 for descending sequences. This is the default.

MINVALUE is an optional integer that determines the minimum of a sequence.

NOMINVALUE is optional. It causes the minimum ascending limit to be 1 and _10 ²⁶ for descending sequences. This is the default.

CYCLE is an option that enables the sequence to continue even when the maximum is reached. If the maximum is reached, the next sequence number generated is the minimum value of the sequence.

Page 233

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 are stored in memory for faster access. The minimum value is 2.

NOCACHE is an option that does 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, your code might 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 must be preceded with your sequence name. For example, adrs_seq.nextval returns 100 for the first access and 105 for the second. If it is necessary to determine the current sequence number, use CURRVAL instead of NEXTVAL. For example, adrs_seq.currval returns the current value of the sequence.

Other Objects

The purpose of this chapter is not to elaborate on every variation of every SQL statement. Therefore, the examples provide an overview of the more common CREATE statements. The following is an alphabetical list of all objects that can be created with the CREATE statement.

CREATE xxx where xxx is one of the following:

 CLUSTER      CONTROLFILE      DATABASE      DATABASE LINK      DATAFILE      FUNCTION      INDEX      PACKAGE      PACKAGE BODY      PROCEDURE      PROFILE 

Previous Table of Contents Next