Indexes in Oracle - An Introduction

If an index has been established and the SQL statement is setup to take advantage of the index, the index will be searched first and the access time will probably be noticeably lower if you are dealing with a large database. Without an index Oracle does a full table search examining every row. Oracle is actively involved in using indexes to satisfy queries and examines the query to determine what indexes it will use.

In Oracle, indexes are grouped with the concept of constraints. There are two major categories of constraints: integrity constraints that refer to the key fields and value constraints that deal with data entered into a column. A constraint is used to protect the validity of data in one or multiple tables and prevent invalid entries. Specifically, constraints enforce certain rules dealing with a table or a column of that table and can be used to prevent the deletion of a table that has children or dependencies. Indexes as constraints are making sure that the primary key field is unique and that the connection through a foreign key is valid.

The constraints that we will look at are shown in the table below.

Constraint	Processing
CHECK	Allows the specification of a condition on the data
FOREIGN KEY	Key used in the relationship of two tables
PRIMARY KEY	Unique key to each row in the table - uniquely identifies row
NOT NULL	Column must not be null
UNIQUE	Column(s) that must be unique for each row in the table

When the programmer is using constraints, they have the option of naming them (then the name can be meaningful) or having the system generate a name with the SYS-Cn format. Constraints can be part of the process to create a table or they can be done as maintenance of the table. Since constrains can be on a column or on a table they can be defined at either level. If you want to see the constraints that have been assigned to a particular table do a SELECT from the USER_CONSTRAINTS data dictionary table.

To see all tables use:

SELECT * FROM USER_CONSTRAINTS;

To see a specific table, use:

SELECT * FROM USER_CONSTRAINTS WHERE TABLE_NAME = 'DONOR';

In the example below, I have defined one field as a primary key and put a check constraint on another.

SQL CODE:

1  CREATE TABLE TRYKEY1
2  (idno NUMBER(3) CONSTRAINT idno_pk PRIMARY KEY,
3  name VARCHAR2(20),
4* deptno NUMBER(2) CONSTRAINT valid_dept_ch CHECK (deptno > 0 AND deptno < 20))
SQL> /

Table created.

I am now inserting data into the table and making some errors to correspond to the constraints that I put in the table. The first row was inserted with no problems. In the second example, I tried to put in a row with a duplicate of the idno in the first row. An error occurred because of idno_pk. In the next example, I violated the deptno check and got an error on that constraint entitled valid_dept_ch. Notice that when I do a DESC on the table, the idno is described as NOT NULL. This is because a primary key can not contain a null value.

SQL CODE:

SQL> INSERT INTO TRYKEY1
  2  VALUES(111,'Susan Smith',12);

1 row created.

SQL> INSERT INTO TRYKEY1
  2  VALUES (111, 'David Johnson',10);
VALUES (111, 'David Johnson',10)
    *
ERROR at line 2:
ORA-00001: unique constraint (SCOTT.IDNO_PK) violated

SQL> INSERT INTO TRYKEY1
  2  VALUES (222, 'Josh Anderson',24);
VALUES (222, 'Josh Anderson',24)
    *
ERROR at line 2:
ORA-02290: check constraint (SCOTT.VALID_DEPT_CH) violated


SQL> SELECT * FROM trykey1;

     IDNO NAME                    DEPTNO
--------- -------------------- ---------
      111 Susan Smith                 12

SQL> DESC trykey1;
 Name                            Null?    Type
 ------------------------------- -------- ----
 IDNO                            NOT NULL NUMBER(3)
 NAME                                     VARCHAR2(20)
 DEPTNO                                   NUMBER(2)

In the examples below, I created a second table with the deptno as the primary key and the deptname as a field that must be unique. I successfully inserted the first row and then tried to violate the unique key principle in the second insert and got an error.

SQL CODE:

1  CREATE TABLE trykeysnc
  2  (deptno NUMBER(2) CONSTRAINT deptnosnd_pk PRIMARY KEY,
  3* deptname VARCHAR(20) CONSTRAINT deptname_uk UNIQUE)
SQL> /

Table created.

SQL> INSERT INTO trykeysnc
  2  VALUES(12,'Information Systems');

1 row created.

SQL> INSERT INTO trykeysnc
  2  VALUES(14,'Information Systems');
INSERT INTO trykeysnc
            *
ERROR at line 1:
ORA-00001: unique constraint (SCOTT.DEPTNAME_UK) violated

In the example below, I created a new table called trykey2 that has idno as the primary key and deptno as a foreign key that references into the deptno on the table trykeysnc. When I tried to put a record in the trykey2 table that did not match a deptno in the trykeysnc table I got a violation error because the parent key was not found. In otherwords, there was no prime key for a dept 15 in the trykeysnc table so I could not put in this foreign key.

SQL CODE:

1  CREATE TABLE trykey2
  2  (idno NUMBER(3) constraint idnotry2_pk PRIMARY KEY,
  3  name VARCHAR2(20),
  4* deptno NUMBER(2) constraint deptnotry2_fk REFERENCES trykeysnc(deptno))
SQL> /

Table created.

SQL> SELECT * FROM trykeysnc;

   DEPTNO DEPTNAME
--------- --------------------
       12 Information Systems

SQL> INSERT INTO trykey2
  2  VALUES(111, 'Gary Tyler', 15);
INSERT INTO trykey2
            *
ERROR at line 1:
ORA-02291: integrity constraint (SCOTT.DEPTNOTRY2_FK) violated - parent key not found


SQL> INSERT into trykey2
  2  VALUES (111, 'Gary Tyler', 12);

1 row created.

SQL> SELECT * FROM trykey2;

     IDNO NAME                    DEPTNO
--------- -------------------- ---------
      111 Gary Tyler                  12

In this example, I tried to alter the trykey2 table by adding a field called paycode that could not be null. I could not do this because the table already had rows in it and a table must be empty to add a mandatory NOT NULL column.

SQL CODE:

1  ALTER TABLE TRYKEY2
  2* ADD (paycode CHAR(2) CONSTRAINT paycode_nn NOT NULL)
SQL> /
ALTER TABLE TRYKEY2
            *
ERROR at line 1:
ORA-01758: table must be empty to add mandatory (NOT NULL) column

SQL> DELETE * FROM trykey2;
DELETE * FROM trykey2
       *
ERROR at line 1:
ORA-00903: invalid table name


SQL> DELETE FROM trykey2;

1 row deleted.

SQL> SELECT * FROM trykey2;

no rows selected

SQL> ALTER TABLE trykey2
  2  ADD (paycode CHAR(2) CONSTRAINT paycode2_nn NOT NULL);

Table altered.

SQL> DESC trykey2;
 Name                            Null?    Type
 ------------------------------- -------- ----
 IDNO                            NOT NULL NUMBER(3)
 NAME                                     VARCHAR2(20)
 DEPTNO                                   NUMBER(2)
 PAYCODE                         NOT NULL CHAR(2)

SQL> INSERT INTO trykey2 (idno, name, deptno)
  2  VALUES (111, 'John Doe', 12);
INSERT INTO trykey2 (idno, name, deptno)
            *
 
ERROR at line 1:
ORA-01400: mandatory (NOT NULL) column is missing or NULL during insert

I have created a table named nameadr1 that has no indexes. Now I want to index the table so I am creating three indexes. One on idno_na, one on last_na and first_na and another on salary_na descending. This is done with CREATE INDEX. If you decide you do not want an index, you can drop it with DROP INDEX.

SQL CODE:


SQL> CREATE TABLE nameadr1
  2  (idno_na NUMBER(3),
  3  last_na VARCHAR2(12),
  4  first_na VARCHAR2(10),
  5  salary_na NUMBER(8,2),
  6  npaycode NUMBER(2));

Table created.

SQL> DESC nameadr1;
 Name                            Null?    Type
 ------------------------------- -------- ----
 IDNO_NA                                  NUMBER(3)
 LAST_NA                                  VARCHAR2(12)
 FIRST_NA                                 VARCHAR2(10)
 SALARY_NA                                NUMBER(8,2)
 NPAYCODE                                 NUMBER(2)

SQL> CREATE INDEX idno_index ON nameadr1(idno_na);

Index created.

1* CREATE INDEX name_index ON nameadr1(last_na,first_na)
SQL> /

Index created.

SQL> CREATE INDEX sal_index ON nameadr1(salary_na desc);

Index created.

SQL> DROP INDEX idno_index;

Index dropped.

Now that I dropped the ordinary index on idno, I am going to make it the primary key. This is done with the ALTER and the command ADD PRIMARY KEY with the key in parenthesis. If you want to add a foreign key, it is also done with the ALTER. First, I tried to create the foreign key without creating the table that I needed to relate into. Because I must reference the secondary table in the creation of the foreign key, Oracle was aware that the secondary table did not exist and denyed the request. I then created the secondary table without giving it a primary key, used the ALTER to make the primary key and then went back to the main table and created the FOREIGN KEY.

SQL CODE:


SQL> ALTER TABLE nameadr1
  2  ADD PRIMARY KEY(idno_na);

Table altered.

SQL> DESC nameadr1;
 Name                            Null?    Type
 ------------------------------- -------- ----
 IDNO_NA                         NOT NULL NUMBER(3)
 LAST_NA                                  VARCHAR2(12)
 FIRST_NA                                 VARCHAR2(10)
 SALARY_NA                                NUMBER(8,2)
 NPAYCODE                                 NUMBER(2)

SQL> ALTER TABLE nameadr1
  2  ADD FOREIGN KEY (npaycode) REFERENCES pay1;
ADD FOREIGN KEY (npaycode) REFERENCES pay1
                                      *
ERROR at line 2:
ORA-00942: table or view does not exist


SQL> CREATE TABLE pay1
  2  (paycode NUMBER(2), jobname VARCHAR2(15));

Table created.

SQL> DESC pay1;
 Name                            Null?    Type
 ------------------------------- -------- ----
 PAYCODE                                  NUMBER(2)
 JOBNAME                                  VARCHAR2(15)

SQL> ALTER TABLE pay1
  2  ADD PRIMARY KEY (paycode);

Table altered.

SQL> ALTER TABLE nameadr1
  2  ADD FOREIGN KEY (npaycode) REFERENCES pay1;

Table altered.