Call (800) 766-1884 for Oracle support & training
Free Oracle Tips


Oracle Consulting Support
Oracle Upgrades
Use New Oracle Features
Oracle Replication Support
Oracle Training
Remote Oracle DBA
System Documentation
Oracle Tips
Oracle Performance
 

Free Oracle Tips


 

HTML Text

BC Oracle tuning

Oracle training

Oracle support

Remote Oracle

 

 

   
 

Oracle PL/SQL Indexing
Oracle Tips by Burleson
 

Think back to the last time you were in a library and needed to find a particular book. While it’s certainly feasible to start at the beginning of the shelves and read through each book title to look for that book, it would take hours (or, more likely, days) to find the right book.

Instead of looking through every book, you probably went to a card catalog (or to a computer terminal) and, based on a few keywords, were able to find your book fairly quickly. That card catalog (or the database that you accessed through the terminal) is a perfect example of an index.

When an index is created on a table, Oracle creates a data structure very similar to a card catalog. This structure is updated whenever an UPDATE, DELETE, or INSERT is performed on the table. Indexes are created in two ways.

  • A primary key for a table is associated with a unique index; this index is a composite set of all the columns that have the primary key constraint.

  • A CREATE INDEX statement is run. This is the case with all indexes except for the table’s primary key index.

An index provides Oracle with a way to quickly locate and retrieve data from its tables. When tables start to contain thousands of rows, it becomes crucial that indexes be used. In order to achieve high performance, SQL statements then have to be carefully tuned to use (and, in some cases, not to use) one or more indexes for a table.

As an application developer, you should keep an eye out for situations in which none of the indexes on a table is appropriate for the task that your code must accomplish. If this is the case, you should consult your DBA. It might be necessary for the DBA to add an index to a table if your code is to perform as well as possible.


TIP:  Finding The Indexes For A Table

Before writing code against a table, it’s a good idea to get a listing of all the indexes on the table. You can execute this query at the SQL prompt to retrieve a listing of the indexes for a specified table, as shown in Listing 2.3.

Listing 2.3 Finding the indexes for a table.

SELECT index_name, column_name
FROM   ALL_IND_COLUMNS
WHERE  table_name = '&1'
ORDER BY index_name, column_name;

When prompted for a value for &1, type in the name of the table for which you want to find the indexes. If your database has the same table in multiple schemas, modify this query to include the owner of the table.


This is an excerpt from the book "High Performance Oracle Database Automation" by Jonathan Ingram and Donald K. Burleson, Series Editor.

  
 

Oracle performance tuning software 
 
 
 
 

Oracle performance tuning book

 

 
 
 
Oracle performance Tuning 10g reference poster
 
 
 
Oracle training in Linux commands
 
Oracle training Excel
 
Oracle training & performance tuning books
 

 

   

Copyright © 1996 -  2014 by Burleson. All rights reserved.

Oracle® is the registered trademark of Oracle Corporation. 

Hit Counter