Pro Oracle Database 11g Administration (37 page)
CHAPTER 8 ■ INDEXES
Making Indexes Unusable
If you’ve identified an index that is no longer being used, you can mark it as UNUSABLE. From that point forward, Oracle won’t maintain the index, nor will the optimizer consider the index for use in SELECT
statements. The advantage of marking the index as UNUSABLE (rather than dropping it) is that if you later determine that the index is being used, you can alter to a USABLE state and rebuild the index without needing the DDL on hand to re-create the index.
Here’s an example of marking an index as UNUSABLE:
SQL> alter index inv_idx1 unusable;
You can verify that it’s unusable via this query:
SQL> select index_name, status from user_indexes;
The index has an UNUSABLE status:
INDEX_NAME STATUS
------------------------------ --------
INV_IDX1 UNUSABLE
If you determine that the index is needed (before you drop it), then it must be rebuilt to become usable again:
SQL> alter index inv_idx1 rebuild;
Another common scenario for marking indexes as UNUSABLE is if you’re performing a large data load.
When you want to maximize table-loading performance, you can mark the indexes as UNUSABLE before performing the load. After you’ve loaded the table, you must rebuild the indexes to make them usable again.
■
Note
The alternative to setting an index as UNUSABLE is to drop and re-create the index. This approach requires the CREATE INDEX DDL.
Monitoring Index Usage
You may have inherited a database, and as part of getting to know the database and application, you want to determine which indexes are being used (or not). The idea is that you can identify indexes that aren’t being used and drop them, thus eliminating the extra overhead and storage required.
Use the ALTER INDEX...MONITORING USAGE statement to enable basic index monitoring. The following example enables index monitoring on an index named F_DOWN_DOM_FK9: SQL> alter index F_DOWN_DOM_FK9 monitoring usage;
The first time the index is accessed, Oracle records this; you can view whether an index has been accessed via the V$OBJECT_USAGE view. To report which indexes are being monitored and have ever been used, run this query:
SQL> select * from v$object_usage;
Most likely, you won’t monitor only one index. Rather, you’ll want to monitor all indexes for a user.
In this situation, use SQL to generate SQL to create a script you can run to turn on monitoring for all indexes. Here’s such a script:
190
CHAPTER 8 ■ INDEXES
select
'alter index ' || index_name || ' monitoring usage;'
from user_indexes;
The V$OBJECT_USAGE view only shows information for the currently connected user. If you inspect the TEXT column of DBA_VIEWS, notice the following line:
where io.owner# = userenv('SCHEMAID')
If you’re logged in as a DBA privileged user and want to view the status of all indexes that have monitoring enabled (regardless of the user), execute this query:
select io.name, t.name,
decode(bitand(i.flags, 65536), 0, 'NO', 'YES'),
decode(bitand(ou.flags, 1), 0, 'NO', 'YES'),
ou.start_monitoring,
ou.end_monitoring
from sys.obj$ io
,sys.obj$ t
,sys.ind$ i
,sys.object_usage ou
where i.obj# = ou.obj#
and io.obj# = ou.obj#
and t.obj# = i.bo#;
The prior query removes the line from the query that restricts the currently logged-in user. This provides you with a convenient way to view all monitored indexes.
Dropping an Index
If you’ve determined that an index isn’t being used, then it’s a good idea to drop it. Unused indexes take up space and can potentially slow down DML statements (because the index must be maintained as part of those DML operations). Use the DROP INDEX statement to drop an index: SQL> drop index inv_idx1;
Dropping an index is a permanent DDL operation; there is no way to undo an index drop other than to rebuild the index. Before you drop an index, it doesn’t hurt to quickly capture the DDL required to rebuild the index. Doing so will allow you to re-create the index in the event you subsequently discover that you did need it after all.
Summary
Indexes are critical objects separate from tables; they vastly increase the performance of a database application. Your index architecture should be well planned, implemented, and maintained. Carefully choose which tables and columns are indexed. Although indexes dramatically increase the speed of queries, indexes can slow down DML statement because the index has to be maintained as the table data changes. Indexes also consume disk space and thus should be created only when required.
Oracle’s B-tree index is the default index type and is sufficient for most applications. However, you should be aware of other index types and their uses. Specific features such as bitmap and function-based indexes should be implemented where applicable. You (the DBA) are expected to be an expert on index types and when to use them. Competent implementation of these features provides large performance benefits.
191
CHAPTER 8 ■ INDEXES
Indexes should be monitored to determine whether they’re being used. When you detect unused indexes, consider making them invisible or marking them as unused. After you’re certain an index isn’t being accessed, drop it. Doing so eliminates the overhead associated with maintaining the index and frees up storage.
After you build a database and users and configure the database with tables and indexes, the next step is to create additional objects needed by the application and users. In addition to tables and indexes, other typical objects include views, synonyms, and sequences. Building these database objects is detailed in the next chapter.
192
CHAPTER 9 ■ VIEWS, SYNONYMS, AND SEQUENCES
To create a view, your user account must have the CREATE VIEW system privilege. If you want to create a view in another user’s schema, then you must have the CREATE ANY VIEW privilege.
Use the CREATE VIEW statement to create a view. The following code creates a view (or replaces it if the view already exists) that selects a subset of columns and rows from the SALES table: create or replace view sales_rockies as
select
sales_id
,amnt
,state
from sales
where state in ('CO','UT','WY','ID','AZ');
Now you can treat the SALES_ROCKIES view as if it were a table. The schema that has access to the view can perform any SELECT, INSERT, UPDATE, or DELETE operation for which it has object grants, and the Data Manipulation Language (DML) operation will result in the underlying table data being changed.
For example, if a schema separate from the owner of the base table has INSERT privileges on the SALES
table, then you can use the view in the INSERT statement:
insert into sales_rockies(
sales_id, amnt, state)
values
(1,100,'CO');
Notice that you can insert a value into the view that results in a row in the underlying table that isn’t selectable by the view:
insert into sales_rockies(
sales_id, amnt, state)
values (2,123,'CA');
SQL> select * from sales_rockies;
SALES_ID AMNT ST
---------- ---------- --
1 100 CO
If you only want the view to allow DML statements that result in data modifications that are selectable by the view statement, then use WITH CHECK OPTION (see the next section).
Checking Updates
If you want to allow only the underlying table data within the scope of the view to be changed, specify WITH CHECK OPTION:
create or replace view sales_rockies as
select
sales_id
,amnt
,state
from sales
where state in ('CO','UT','WY','ID','AZ')
with check option;
194
CHAPTER 9 ■ VIEWS, SYNONYMS, AND SEQUENCES
Using WITH CHECK OPTION means that you can only insert or update rows that would be returned by the view query. For example, this UPDATE statement works because the statement isn’t changing the underlying data in a way would result in the row not being returned by the view query: SQL> update sales_rockies set state='ID' where sales_id=1;
However, this next update statement fails because it attempts to update the STATE column to a state that isn’t selectable by the query on which the view is based:
SQL> update sales_rockies set state='CA' where sales_id=1;
In this example, the following error is thrown:
ERROR at line 1:
ORA-01402: view WITH CHECK OPTION where-clause violation
Creating Read-Only Views
If you don’t want a user to be able to perform INSERT, UPDATE, or DELETE operations on a view, then don’t grant those object privileges on the underlying table(s) to that user. You should also create a view with the WITH READ ONLY clause for any views for which you don’t want the underlying tables to be modified (keep in mind that by default, the view is updatable, and the underlying objects can be modified if the object grants exist). This example creates a view with the WITH READ ONLY clause: create or replace view sales_rockies as
select
sales_id
,amnt
,state
from sales
where state in ('CO','UT','WY','ID','AZ')
with read only;
If you use views for reporting, and you never intend for the views to be used as a mechanism to update a table, then you should always create the views with the WITH READ ONLY clause. Doing so prevent accidental modifications to the underlying tables through a view that was never intended to be used for changing data.
Updatable Join Views
If you have multiple tables defined in the FROM clause of the SQL query on which the view is based, it’s still possible to update the underlying tables. This is known as an
updatable join view
.
For reference purposes, here are the CREATE TABLE statements for the two tables used in the examples in this section:
create table emp(
emp_id number primary key
,emp_name varchar2(15)
,dept_id number);
--
create table dept(
dept_id number primary key
,dept_name varchar2(15),
constraint emp_dept_fk
foreign key(dept_id) references dept(dept_id));
195
CHAPTER 9 ■ VIEWS, SYNONYMS, AND SEQUENCES
For this example, here’s some seed data for the two tables:
insert into dept values(1,'HR');
insert into dept values(2,'IT');
insert into dept values(3,'SALES');
insert into emp values(10,'John',2);
insert into emp values(20,'Bob',1);
insert into emp values(30,'Craig',2);
insert into emp values(40,'Joe',3);
insert into emp values(50,'Jane',1);
insert into emp values(60,'Mark',2);
Here’s an example of an updatable join view based on the two prior base tables: create or replace view emp_dept_v
as
select
a.emp_id
,a.emp_name
,b.dept_name
,b.dept_id
from emp a, dept b
where a.dept_id = b.dept_id;
There are some restrictions regarding the columns on which DML operations are permitted. For example, columns in the underlying tables can be updated only if the following conditions are true:
•
•
•
An underlying table in a view is
key-preserved
if the table’s primary key can also be used to uniquely identify rows returned by the view. An example with data will help illustrate whether an underlying table is key-preserved. In this example, the primary key of the EMP table is the EMP_ID column. And the primary key of the DEPT table is the DEPT_ID column. Here’s some sample data returned by the view listed previously listed in this section:
EMP_ID EMP_NAME DEPT_NAME DEPT_ID
---------- --------------- --------------- ----------
10 John IT 2
20 Bob HR 1
30 Craig IT 2
40 Joe SALES 3
50 Jane HR 1
60 Mark IT 2
As you can see from the output of the view, the EMP_ID column is always unique. Therefore, the EMP table is key-preserved (and its columns can be updated). On the other hand, the view’s output shows that it’s possible for the DEPT_ID column to be not unique. Therefore, the DEPT table isn’t key-preserved (its columns can’t be updated).
When you update the view, any modifications that result in columns that map to the underlying EMP table should be allowed because the EMP table is key-preserved in this view. For example, this UPDATE statement is successful: