Databases Reference
In-Depth Information
Some shops use prefixes when naming indexes. For example, a primary key index would be named
PK_CUST
(instead of
CUST_PK
). All of these various naming standards are valid.
Tip
It doesn't matter what the standard is, as long as everybody follows the same standard.
Implementing B-tree Indexes
This section describes typical tasks you'll encounter when working with B-tree indexes. Typical tasks
include:
•
Creating indexes.
•
Reporting on indexes.
•
Displaying code required to re-create an index.
Dropping indexes.
These tasks are detailed in the following subsections.
•
Creating a B-tree Index
Listed next is a sample script that creates a table and its associated indexes in separate tablespaces. The
tables and indexes inherit their storage attributes from the tablespace; this is because no storage
parameters are specified in either the
CREATE TABLE
or
CREATE INDEX
statements.
Also, you want the primary key and unique key constraints to automatically create B-tree indexes
(for more details on this topic, see the subsequent section “Managing B-tree Indexes with Constraints”).
CREATE TABLE cust(
cust_id NUMBER
,last_name VARCHAR2(30)
,first_name VARCHAR2(30))
TABLESPACE reporting_data;
--
ALTER TABLE cust ADD CONSTRAINT cust_pk PRIMARY KEY (cust_id)
USING INDEX TABLESPACE reporting_index;
--
ALTER TABLE cust ADD CONSTRAINT cust_uk1 UNIQUE (last_name, first_name)
USING INDEX TABLESPACE reporting_index;
--
CREATE TABLE address(
address_id NUMBER
,cust_id NUMBER
,street VARCHAR2(30)
,city VARCHAR2(30)
,state VARCHAR2(30))
TABLESPACE reporting_data;
