Home >> FAQs/Tutorials >> Oracle DBA FAQ >> Index

Oracle DBA FAQ - Managing Oracle Table Indexes

By: FYIcenter.com

Part:   1   2  3  4 

This is a collection of 14 FAQs for Oracle DBA on creating indexes and managing indexes. The clear answers and sample scripts provided can be used as learning tutorials or interview preparation guides. Topics included in this FAQ are:

  1. What Is a Table Index?
  2. How To Run SQL Statements through the Web Interface?
  3. How To Create a Table Index?
  4. How To List All Indexes in Your Schema?
  5. What Is an Index Associated with a Constraint?
  6. How To Rename an Index?
  7. How To Drop an Index?
  8. Can You Drop an Index Associated with a Unique or Primary Key Constraint?
  9. What Happens to Indexes If You Drop a Table?
  10. How To Recover a Dropped Index?
  11. What Happens to the Indexes If a Table Is Recovered?
  12. How To Rebuild an Index?
  13. How To See the Table Columns Used in an Index?
  14. How To Create a Single Index for Multiple Columns?

Sample scripts used in this FAQ collection assumes that you are connected to the server with the default user account HR on the default instance XE. All scripts were executed through the Oracle Web interface. See other FAQ collections on how to connect to the server.

It is also assumed that user account HR has the CREATE ANY INDEX privilege needed to manage indexes.

What Is a Table Index?

Index is an optional structure associated with a table that allow SQL statements to execute more quickly against a table. Just as the index in this manual helps you locate information faster than if there were no index, an Oracle Database index provides a faster access path to table data. You can use indexes without rewriting any queries. Your results are the same, but you see them more quickly.

How To Run SQL Statements through the Web Interface?

If you don't like the command line interface offered by SQL*Plus, you can use the Web interface to run SQL statements. Here is how:

  • Open your Web browser to http://localhost:8080/apex/
  • Log in to the server with the predefined sample user account: HR/fyicenter
  • Click the SQL icon
  • Click the SQL Commands icon
  • Enter any SQL statement like: "SELECT COUNT(*) FROM USER_TABLES;", in the text area and click Run button
  • Your Oracle will execute the statement, and display the result in the result area.

How To Create a Table Index?

If you have a table with a lots of rows, and you know that one of the columns will be used often a search criteria, you can add an index for that column to in improve the search performance. To add an index, you can use the CREATE INDEX statement as shown in the following script:

CREATE TABLE tip (id NUMBER(5) PRIMARY KEY,
  subject VARCHAR(80) NOT NULL,
  description VARCHAR(256) NOT NULL,
  create_date DATE DEFAULT (sysdate));
Table created.
  
CREATE INDEX tip_subject ON tip(subject);
Index created.

How To List All Indexes in Your Schema?

If you log in with your Oracle account, and you want to get a list of all indexes in your schema, you can get it through the USER_INDEXES view with a SELECT statement, as shown in the following SQL script:

SELECT index_name, table_name, uniqueness 
  FROM USER_INDEXES WHERE table_name = 'EMPLOYEES';
INDEX_NAME              TABLE_NAME            UNIQUENES
----------------------- --------------------- ---------
EMP_EMAIL_UK            EMPLOYEES             UNIQUE
EMP_EMP_ID_PK           EMPLOYEES             UNIQUE
EMP_DEPARTMENT_IX       EMPLOYEES             NONUNIQUE
EMP_JOB_IX              EMPLOYEES             NONUNIQUE
EMP_MANAGER_IX          EMPLOYEES             NONUNIQUE
EMP_NAME_IX             EMPLOYEES             NONUNIQUE

As you can see, the pre-defined table EMPLOYEES has 6 indexes defined in the default sample database.

What Is an Index Associated with a Constraint?

An index associated with a constraint because this constraint is required to have an index. There are two types of constraints are required to have indexes: UNIQUE and PRIMARY KEY. When you defines a UNIQUE or PRIMARY KEY constraint in a table, Oracle will automatically create an index for that constraint. The following script shows you an example:

CREATE TABLE student (id NUMBER(5) PRIMARY KEY,
  first_name VARCHAR(80) NOT NULL,
  last_name VARCHAR(80) NOT NULL,
  birth_date DATE NOT NULL,
  social_number VARCHAR(80) UNIQUE NOT NULL);
Table created.
  
SELECT index_name, table_name, uniqueness 
  FROM USER_INDEXES WHERE table_name = 'STUDENT';
INDEX_NAME              TABLE_NAME            UNIQUENES
----------------------- --------------------- ---------
SYS_C004123             STUDENT               UNIQUE
SYS_C004124             STUDENT               UNIQUE

The result confirms that Oracle automatically created two indexes for you.

(Continued on next part...)

Part:   1   2  3  4 


Selected Developer Jobs:

More...