Access to the tables in most cases is with the Structured Query Language (SQL). Unlike flat-file systems or simpler data management tools, you do not have direct access to the raw data storage formats. This language has a number of statements that you need to learn to work with Oracle tables properly. It is important to understand that when you issue the select command to retrieve a row from a table, you actually get one or more blocks of rows back from the data files. In many cases, if you want multiple rows from a table, they are located relatively close to one another (in that they were stored at about the same time). By retrieving a block of data into memory, Oracle avoids accessing the disk separately for each row.

Of course, you do not want every user in your instance creating tables all the time. Therefore, Oracle enables the DBA to control who can create or modify these objects via the privileges mechanism. Chapter 11, “Oracle Files,” discusses privileges in some detail, but for now, understand that there are two basic privileges related to table creation and modification:

•  CREATE TABLE

•  ALTER TABLE

One final topic before we leave this section: It is useful to be able to determine what tables are out there and how they are set up. Once again, there is a DBA view that provides the information that the DBA needs to manage tables. Figure 12.1 shows a screen with a sample of a query to the DBA view that verifies the table created earlier in this section is set up the way you intended. Note that the where clause is used to narrow the range of information that you want to see.

Figure 12.1.  A query against the DBA_TABLES view.

Indexes

The where clause is a commonly used construct in database programming. Very few users want to see all the data in a table displayed for them on every query. Instead, most queries are designed to return a series of rows or perhaps even a single row that matches the users’ criteria. Without indexes, Oracle would have to read all of the rows in the table and then filter out the ones that are not of interest. However, with indexes, you scan the small index to see which rows are of interest and then only retrieve those rows from the table. For smaller tables, it is not a big deal to read in all the rows and check them off one by one to see whether they match the selection criteria input by the users. However, for large tables (I once saw a table that was 17GB), this is extremely slow and places a large load on the system for every query. To help solve this problem, indexes were created.

An index is a sorted list of data from one or more columns in the table that are commonly used as selection criteria. Thus, rather than searching through a large number of long personnel records to find the record of an individual, you can sort through a relatively short index containing just the last name and first name (see Figure 12.2). When Oracle finds the name that matches your search values, it retrieves just the block of rows that contains the data you need. Because the data is already presorted, Oracle does not have to read every record in the index. Instead, it uses a more efficient search routine (modified B-TREE whose search pattern resembles the branches of a tree) to reduce the number of index records that it needs to analyze.

Figure 12.2.  Indexed searches.

Because indexes are extracted lists of values for tables, Oracle needs a place to store them. Therefore, indexes have storage clauses similar to tables. Here is a sample command that creates an index on the date_played field of the golf_scores table in the previous section:

create index golf_scores_dates

on golf_scores (date_played)

tablespace user_data

storage

(initial 2K

next 2K

minextents 1

maxextents 100

pctincrease 0); 

How do you get your queries to use these wonderful indexes that you have created? The answer is that Oracle automatically uses indexes if you issue a query that can use the index (that is, the values in your where clause are among the fields of the index). That makes life a whole lot easier for developers. Even better, indexes are maintained automatically by Oracle. When you add, delete, or modify rows in a table that has one or more indexes, the indexes are automatically updated to reflect the changes. This brings up an important design consideration. It takes Oracle time to update an index. If you have a table that has a large number of indexes, it takes longer to add data to this table. Therefore, tables in which the key to performance is the capability of adding data (for example, an order entry on a system that is heavily loaded during the day) benefit from fewer indexes. Conversely, systems that are used primarily for queries (such as decision support systems) benefit from having as many indexes as are useful based on the types of user queries.