The Art of Indexing

An index is a simple concept which you are already familiar with. A database index is quite like an index
to a book. If you want to look up every reference to the ALTER command that appears in this book, you would turn to the alphabetically arranged index at the back of this book and look up the term along with the page numbers on which the command is referred to. If however, there were no index, or if vour chosen term was not indexed, you would have to scan the entire book to find the piece of information you want.

Akin to the book index, the database index is a listing of keywords accompanied by the location of information on a subject. The concept of indexing is related to the idea of primary keys in ORACLE, which was described in the initial chapters of this book.

While indexes are not strictly necessary to running ORACLE, they do speed up the process. Indexing enables ORACLE to locate rows in a large table more quickly than it otherwise would, In this chapter we will learn how to create an index, use an index to speed up queries, how to use an index to ensure that a column of a table contains unique values, and finally how to drop an index.

Note that indexing is a feature of ORACLE itself, not of SQL*Plus Thus the indexes you create using SQL*Plus will be applicable to all the products of the ORACLE family.

The examples of indexing that will be shown in this chapter are no smaller tables, it should be noted that the real power of indexing is apparent only when the table is large. It will do little to speed up the search of a smaller table, however. In the case of a very small table, indexing might even slow you down. The computer must first search the index file for the address of the term you seek, then find that address in the ,main table.

As your tables grow, however, the time needed for this two step search will become mush less than the time needed to scan the entire table without benefit of an index.

To create an index, we use the CREATE INDEX command:

CREATE INDEX index name ON table (column, column );

AS shown, the name of the index that has to be created follows the CREATE INDEX command, after which the table on which the index is to be created is identified and (in parentheses) the column(s) that contains the information to go in the index. e.g., to create an index on column EMPNO of the table EMP, enter :

SQL > CREATE INDEX EMP NO NO EMP (EMPNO);

Index created.

You can create indexes on any number of columns in a table, as is apparent from the standard format. e.g., to create an index on ENAME and EMPNO columns in the EMP table, enter:

SQL > CREATE INDEX EMP NO NAME ON EMP(EMPNO, ENAME);

Index created.

You may also create any number of indexes on different columns for a table. Care should of course be taken that each index has a unique name which follows the naming conventions of ORACLE columns.

When you enter a WHERE clause that refers to am indexed column, SQL*Plus uses the index to locate rows that satisfy the clause. If the table in question is large, the index enables SQL*Plus to process the command containing the WHERE clause more efficiently that it otherwise would.

If an index is applicable to any query made to the database SQL*Plus applies it automatically you don't need to identify the index the SQL*Plus should use or even remember that an index exists.
The following query uses the index EMP NO:

SQL > SELECT EMPNO, ENAME, JOB, JOIN DT
2 FROM EMP

3 WHERE EMPNO = 842 1;

As mentioned earlier, since EMP is a small tables(less than two dozen rows), the overhead involved in the index will probably exceed the time saved. Thus the index will cause SQL*Plus to process a query less efficiently. You will get a feel for when indexes are useful in your applications through practice.

SQL *Plus can take advantage of an index only when you use an indexed column directly, not when you use an expression involving an indexed column. e.g., if the WHERE clause in the above example said, WHERE UPPER (ENAME) = 'NOEL', SQL*Plus would not be able to use the index even though there was an index EMP NO NAME involving the column ENAME.

In the ENT table, the primary key is the employee number, EMPNO. Simidarly in the DEPTS table, the ideal primary keys is DEPTNO. In other tables, a primary key may be a social security number, a client ID, or in a bank, a combination or branch number and account number.

Indexes can guarantee that a column of a table contains unique values.

For example, we can enforce that the EMP table shouldn't have any duplicate employee number(EMPNO) values. To do this, create a UNIQUE index on the EMPNO column:

SQL > CREATE UNIQUE INDEX EMP EMPNO ON EMP (EMPNO); Index created.

Now that you have created a unique index on EMPNO, if you try to insert a row which has the same EMPNO as an existing one, then SQL*Plus will generate an error message.

SQL > INSERT INTO EMP

2 VALUES (1638,'GOVER', CLERK', 6622,
3 '16 MAY 94,'2750, NULL 2);

INSERT INTO EMP

ERiOl.~ INTO EMP

ERROR at line 1 : ORA 000 I : duplicate value in index

SQL*Plus will also enforce uniqueness when you update a field in the EMPNO column.
Using indexes on joins

An index can be a major time saver when you use a Join command, since SQL *Plus must search through one table for matches to each row selected from the other. If you often Join two tables, create an index on the column involved in the Join condition in one or both the tables.

For example, if your application involves Joining the EMP and DEPTS tables, often, you could create an index on the DEPT NO column of the EMP tables.

SQL > CREATE INDEX EMP DEPT
2 ON EMP (DEPTNO);

Index created.

SQL > SELECT ENAME, DEPTNO, SITE
2 FROM EMP, DEPTS
3 WHERE EMP. DEPTNO DEPTS.DEPTNO
4 AND DEPTNO IN 93,4);

The index on DEPTNO is used to find the rows of EMP to Join to the selected rows of DEPTS. The index could well have been made on the DEPTNO column of the DEPTS table to improve the system performance, however this isn't always the case. Suppose you wanted to find employee no.9596's name and the department in which he worked then an index on EMPDEPTNO would'not help since this query does not look for department numbers in EMP, Create an index on DEPTS. DEPTNO; then Join EMP to DEPTS.

SQL > CREATE INDEX DEPTS DEPT ON DEPTS (DEPTNO);

Index created.

SQL > SELECT ENAME, DNAME
2 FROM EMP, DEPTS
3 WHERE EMP, DEPTNO = DEPTS. DEPTNO
4 AND EMPNO = 9596;

Two indexes were used to process the above query: EMP DEPT was used to find the correct row in the
EMP table and DEPTS DEPTNO was used to find the row in DEPTS to Join to the b>Guidelines for index usage

The following are the empirical guidelines in brief for creating and using indexes in your database:
If an index has more than a few hundred rows, index it.

Indexes are most useful on larger tables. The larger a table, the more an index will improve response time. Additionally, the more indexes available, the better are ORACLE's chances of finding "short cut" to the requested information.

Index only simply columns

If an index is modified by a function, such as SUBSTR or II, ORACLE will not use the index. Both MIN and MAX, if used alone with a column, will take advantage of the index.

Try not to create more than two or three indexes on a tables

Creating too many indexes on a table is not a good idea. This is because first, indexes take up disk storage space; Second, although indexes speed up queries, they may slow down data manipulation operations.

This is because when you insert or delete a row in an indexed table, or change the value of an indexed column, ORACLE must update the affected index(es). The more indexes you have, the more work it takes to keep them up to date.

Index frequently used columns

Index the columns you use most often in the WHERE clauses. There's no need to index the columns you use only to display values. Moreover, indexes are most useful on columns with a significant amount of variety in their data.

e.g., a column that 'indicates whether a company is a current client with a Y or N would be poor choice for an index. A telephone number column would be a good candidate, whereas an area code column would be quite marginal

Indexes can be removed from the database exactly like tables and views. To do so, use the command DROP INDEX followed by the name of the index. e.g., to drop the index EMP NO NAME, enter:

SQL > DROP INDEX EMP NO NAME;

Index dropped

Domain Name Search