In this second part of a three-part series, you will learn how to create, drop, and maintain indexes for Oracle Database XE. This article is excerpted from chapter 38 of the book Beginning PHP and Oracle: From Novice to Professional, written by W. Jason Gilmore and Bob Bryla (Apress; ISBN: 1590597702).
Creating, Dropping, and Maintaining Indexes
You use the CREATE INDEX statement to create a B-tree or bitmap index. The basic syntax looks like this:
CREATE [BITMAP | UNIQUE] INDEX indexname ON tablename (column1, column2, ...) [REVERSE];
If you do not specifyBITMAP, Oracle assumes a B-tree index. TheUNIQUEkeyword ensures that the index will not contain duplicate values. TheREVERSEkeyword creates a reverse key index, discussed in the previous section. The name of the index must be unique among all indexes within a schema (user). However, the namespace for indexes is different from the namespace for table names. This means you could create an index namedEMPLOYEESon theLAST_NAMEcolumn of theEMPLOYEEStable. This may lead to confusion, though, especially if you want to have more than one index on theEMPLOYEES table. One possible naming convention is to include the table name, the column name, and the index type in the index name, as in this example:
CREATE INDEX employees_last_name_ix ON employees(last_name);
Dropping an index is quite intuitive if you are familiar with other Oracle Database XE statements. Use theDROP INDEXstatement like this:
DROP INDEX employees_last_name_ix;
Using the Oracle Database XE GUI makes it even easier to create an index; no knowledge of syntax is required. However, it is good to know the syntax when you are (infrequently) stuck with only a SQL command-line interface. Start at the Oracle Database XE home page, click Object Browser, and select Indexes in the drop-down box at the top of the left navigation pane. Logged in as the userHR, you will see the indexes owned byHR. Clicking theEMP_EMP_ID_PKindex name in the left navigation area shows you the details for the primary key (unique) index on theEMPLOYEE_IDcolumn of theEMPLOYEEStable in Figure 38-2.
Figure 38-2.Browsing indexes owned by HR
In the following scenario, your queries against theEMPLOYEEStable seem to be slow (or at least your users tell you they are slow). You suspect it is because you might not have a column indexed. Here is a typical management query against theEMPLOYEEStable:
select * from employees where salary > 5000 order by salary desc;
Entering this query in the SQL Commands window and clicking the Explain tab shows you how Oracle Database XE accesses each of the tables in the query, along with a list of the indexed columns and the table columns, as you can see in Figure 38-3.
Figure 38-3. Using the Explain function on the SQL Commands page
It seems clear from the Explain tab on the SQL Commands window that Oracle Database XE reads the entireEMPLOYEES table when you filter by theSALARYcolumn. This line in the Query Plan section of Figure 38-3 spells it out for you:
TABLE ACCESS FULL EMPLOYEES
Therefore, you decide to create a nonunique B-tree index on theSALARYcolumn. From the Create drop-down box shown earlier in Figure 38-2, select Index. Enter the EMPLOYEES table name in the Table Name box, or select it from the drop-down button to the right of the box. You will see the page shown in Figure 38-4. For Type of Index, be sure that the Normal radio button is selected. We talk about text-based indexes in the “Using Oracle Text” section later in this chapter. Click the Next button.
Figure 38-4.Specifying the table name
You decide to keep Oracle’s suggested name for the index asEMPLOYEES_IDX1, as shown in Figure 38-5. The index will not be unique (many employees will have the same salary), and you selectSALARYas the indexed column in the Index Column 1 drop-down box.
Click the Next button and you see a confirmation page. Click Finish to create the index. The new index appears in the list on the Object Browser page shown in Figure 38-6.
Figure 38-5. Specifying indexed columns
Figure 38-6.Reviewing the details of the new index, EMPLOYEES_IDX1.
Please check back for the conclusion to this series.
DISCLAIMER: The content provided in this article is not warranted or guaranteed by Developer Shed, Inc. The content provided is intended for entertainment and/or educational purposes in order to introduce to the reader key ideas, concepts, and/or product reviews. As such it is incumbent upon the reader to employ real-world tactics for security and implementation of best practices. We are not liable for any negative consequences that may result from implementing any information covered in our articles or tutorials. If this is a hardware review, it is not recommended to open and/or modify your hardware.
