EX.NO: 4.d     CREATION OF INDEXES

Aim:

To create the indexes on the attributes for fast retrieval of records.

Description

Indexes are special lookup tables that the database search engine can use to speed up data retrieval.

An index helps speed up SELECT queries and WHERE clauses, but it slows down data input, with UPDATE and INSERT statements. Indexes can be created or dropped with no effect on the data.

The CREATE INDEX Command:

The basic syntax of CREATE INDEX is as follows:

CREATE INDEX index_name ON table_name;

Single-Column Indexes:

A single-column index is one that is created based on only one table column. The basic syntax is as follows:

CREATE INDEX index_name

ON table_name (column_name);

Unique Indexes:

Unique indexes are used not only for performance, but also for data integrity. A unique index does not allow any duplicate values to be inserted into the table. The basic syntax is as follows:

CREATE UNIQUE INDEX index_name

on table_name (column_name);

Composite Indexes:

A composite index is an index on two or more columns of a table. The basic syntax is as follows:

CREATE INDEX index_name

on table_name (column1, column2);

The following guidelines indicate when the use of an index should be reconsidered:

  • Indexes should not be used on small tables.
  • Tables that have frequent, large batch update or insert operations.
  • Indexes should not be used on columns that contain a high number of NULL values.
  • Columns that are frequently manipulated should not be indexed.

Example:

SQL> create table indexing(pno int, pname varchar(20), qty int, cost int);

Table created.

SQL> create index in1 on indexing(pno);

Index created.

SQL> insert into indexing values(&pno, ‘& pname’, &qty, &cost);

Enter value for pno: 1

Enter value for pname: pencil

Enter value for qty: 2

Enter value for cost: 3

old   1: insert into indexing values(&pno, ‘& pname’, &qty, &cost)

new   1: insert into indexing values(1, ‘pencil’, 2, 3)

1 row created.

SQL> /

Enter value for pno: 2

Enter value for pname: pen

Enter value for qty: 6

Enter value for cost: 5

old   1: insert into indexing values(&pno, ‘& pname’, &qty, &cost)

new   1: insert into indexing values(2, ‘pen’, 6, 5)

1 row created.

SQL> /

Enter value for pno: 3

Enter value for pname: whitener

Enter value for qty: 5

Enter value for cost: 20

old   1: insert into indexing values(&pno, ‘& pname’, &qty, &cost)

new   1: insert into indexing values(3, ‘whitener’, 5, 20)

1 row created.

SQL> /

Enter value for pno: 4

Enter value for pname: sheet

Enter value for qty: 100

Enter value for cost: 100

old   1: insert into indexing values(&pno, ‘& pname’, &qty, &cost)

new   1: insert into indexing values(4, ‘sheet’, 100, 100)

1 row created.

SQL> /

Enter value for pno: 5

Enter value for pname: eraser

Enter value for qty: 5

Enter value for cost: 4

old   1: insert into indexing values(&pno, ‘& pname’, &qty, &cost)

new   1: insert into indexing values(5, ‘eraser’, 5, 4)

1 row created.

SQL> select * from indexing;

PNO         PNAME       QTY       COST

———-   ————-   ——-     ———-

  1             pencil             2           3

  2             pen                 6           5

  3             whitener          5          20

  4             sheet              100       100

  5             eraser              5           4

SQL> select pname from indexing where pno=2;

PNAME

————

pen

SQL> update indexing set qty=5 where pno=1;

1 row updated.

SQL> select * from indexing;

PNO         PNAME       QTY       COST

———-   ————-   ——-     ———-

  1             pencil             5           3

  2             pen                 6           5

  3             whitener          5          20

  4             sheet              100       100

  5             eraser              5           4

Creation of unique indexes

SQL> create unique index in2 on indexing(pname);

Index created.

SQL> insert into indexing values(&pno, ‘&pname’, &qty, &cost);

Enter value for pno: 6

Enter value for pname: eraser

Enter value for qty: 3

Enter value for cost: 5

old   1: insert into indexing values(&pno, ‘&pname’, &qty, &cost)

new   1: insert into indexing values(6, ‘eraser’, 3, 5)

insert into indexing values(6, ‘eraser’, 3, 5)

*

ERROR at line 1:

ORA-00001: unique constraint (SCOTT.IN2) violated

SQL> /

Enter value for pno: 6

Enter value for pname: ink eraser

Enter value for qty: 3

Enter value for cost: 5

old   1: insert into indexing values(&pno, ‘&pname’, &qty, &cost)

new   1: insert into indexing values(6, ‘ink eraser’, 3, 5)

1 row created.

SQL> select * from indexing;

PNO         PNAME       QTY       COST

———-   ————-   ——-     ———-

  1             pencil             5           3

  2             pen                 6           5

  3             whitener          5          20

  4             sheet              100       100

  5             eraser              5           4

  6             ink eraser         3          5

6 rows selected.

The DROP INDEX Command:

An index can be dropped using SQL DROP command. Care should be taken when dropping an index because performance may be slowed or improved.

The basic syntax is as follows:

DROP INDEX index_name; 

Result:

Thus the index is created on the tables and the information is retrieved.

Leave a Reply

Your email address will not be published. Required fields are marked *