It is sometimes difficult to answer the question as to what indexes are useful in a table. Obviously, indexing every column in the table is probably not a good idea. Even though an index search is more efficient than a table scan, it does take some time. When you add the time of searching a large index to the time it takes to retrieve the rows you need from the table, you usually wind up taking longer than if you just scan the table. It also may not be justifiable to build and maintain indexes that support the selection criteria of infrequently used queries. In this case, it costs more time on the updates than it saves on these infrequent queries. Being trained as a scientist with a love for experimentation, I always try constructing an index that I am curious about and then see its effect on the performance of my queries and update routines. Based on this data, I decide on whether it was worth keeping.

The following are a few more notes about indexes:

•  Tables are not affected by the presence or lack of indexes. You can drop and re-create indexes at will without affecting any of the data in a table.

•  It is sometimes beneficial in systems where you perform large batch updates to drop the indexes, perform the updates, and then re-create the indexes.

•  One rule of performance tuning is to locate the indexes for a table on a separate disk from the table itself. This splits the input/output load for queries that access both the table and an index.

•  You have the option of creating unique indexes. If you create a unique index, it gives an error if you try to insert a row in the table that has values in the index columns that match those of another row in the table. This is a good way to enforce a primary key on the table and build an index in a single step.

•  If you have privileges to create or modify a table, you have the privileges you need to create or modify indexes associated with that table.

Indexes are very useful tools to improve the performance of queries. However, like all tools, you need to consider their design carefully to ensure that you get the performance gains that you desire. Figure 12.3 shows a screen with the query that you would issue to view the indexes associated with a particular user in the database.

Figure 12.3.  Determining the indexes owned by a user.

The following example shows the columns in the indexes associated with a given table name:

SQL> select index_owner,index_name,column_name

  2  from dba_ind_columns

  3  where table_name=’WORLD_CITIES’;



INDEX_OWNER                    INDEX_NAME                     COLUMN_NAME

------------------------------ ------------------------------ ------------

JGREENE                        SYS_C004325                    LOCATION_ID

Views

Next on your list of database objects to study is views. You have already seen a number of views in the examples where we queried the database for a list of tables, tablespaces, and indexes. The actual data retrieved is stored in one or more internally controlled Oracle tables, and I would have to do some research to figure out what they are. I honestly don’t care. I have these nice little views that I issue simple queries against to get my answers.

A view is merely a selection of one or more rows from one or more tables. The data is not duplicated. The view is merely a structure that references the data that is actually stored in the tables. You could think of it as a stored SQL query (with join and filtering logic) from which you can select data as if it were a table. Therefore, they do not require storage parameters and do not reside in tablespaces. Views can be used to achieve a number of objectives for designers:

•  Your users do not want to write complex queries that join two or more tables together. Therefore, you build the logic joining the two tables into the view and let the users access the data as if it were a single table.

•  You may have tables where certain users are permitted to access data only from certain columns. You can create views that contain the columns that they are allowed to access and give those users access to only the view and not the base table.

•  You have tables where the column names may not make sense to certain users. You can create a view that renames these columns to names that make sense.

•  You may want to deal with simpler structures than tables. For example, your table may have hundreds of columns of data. It may be easier to create a view and then have the users worry about only the simpler view.

One of the best features about views is that you access them the same way you access tables. You do not have to learn any separate language constructs. You cannot create indexes on views. To create a view, you need the CREATE VIEW privilege assigned to your Oracle user ID. You cannot alter a view. Instead you just drop it and re-create it. You can do this in a single command by using the CREATE OR REPLACE VIEW format of the CREATE VIEW command. The advantage of the CREATE OR REPLACE VIEW format is that you do not lose the privilege grants that may have been granted on the view. This is not a problem, because dropping or creating views has no effect on the data in the tables associated with that view.

Views are convenient database objects that do not store any data themselves. Instead, views reference data located in one or more tables in the instance. They have many uses, including making queries more convenient for the users and providing additional security for the database.