Previous Table of Contents Next


Indexing

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.


Privileges And Roles

The Oracle database incorporates a number of security features, two of which are privileges and roles. Both of these features are explained in some detail in the next few sections of this chapter. The most important thing that you need to know about roles and privileges is that all stored PL/SQL objects execute with the rights of the user who created the object.

For instance, let’s say there is a table, EMPLOYEE_COMPENSATIONS, which contains the salary and other benefits for each employee. While an individual user should never be able to modify the salaries of employees, occasionally employees get raises and benefits change. If a stored procedure owned by the system references this table, the stored procedure has the same rights as the system, despite the rights of the user who executes the stored procedure.

What Is A Privilege?

Oracle7 has two levels of privileges:

  System level privileges give users the ability to create, modify, and drop objects (tables, indexes, views, synonyms, and so on), as well as the ability to execute stored PL/SQL objects.
  Table level privileges give users the ability to perform SELECT, INSERT, UPDATE, and DELETE operations on tables.

These privileges can be granted to all system users or to individual users as needed. There is no list of privileges included in this book, because that knowledge is rarely needed by application developers.

What Is A Role?

Oracle7 introduced roles as a way to make user maintenance less of a chore for DBAs. Privileges can be granted to roles, and roles can be granted to other roles. Roles are then assigned to system users and application developers, and even to DBAs. Rather than grant privileges on tens or hundreds of tables to each individual user, these privileges can be granted to a role, which is then granted to each user.


TIP:  Looking Up Existing Roles In Your Database

You can get a listing of all the roles that exist in your system by executing the query shown in Listing 2.4.

Listing 2.4 Finding the existing roles in your database.

SELECT AR.role, ARP.granted_role
FROM   ALL_ROLES AR, ALL_ROLE_PRIVS ARP;

Understanding which roles exist and what each role does within the system will help you gain a better understanding of system design and, consequently, improve your ability to contribute to the success of your project.



Previous Table of Contents Next