Performance will be significantly worse with range queries,
queries that force a full table scan, or for hash clusters that are frequently updated.
Reverse indexes provide another tuning solution for equivalence queries. In a reverse index,
the bytes of the index are stored in reverse order. In a traditional index, two consecutive values
are stored next to each other. In a reverse index, consecutive values are not stored next to each
other. For example, the values 2004 and 2005 are stored as 4002 and 5002, respectively, in a
reverse index. Although not appropriate for range scans, reverse indexes may reduce contention
for index blocks if many equivalence queries are performed. Reverse key indexes may need to be
rebuilt quite often to perform well. They should also include a large value for PCTFREE to allow
for inserts.
NOTE
You cannot reverse a bitmap index.
You can create function-based indexes on expressions involving columns. This query could
not use a B-tree index on the Name column:
Select * from EMPLOYEE
where UPPER(Name) = 'JONES';
However, the query
select * from EMPLOYEE
where Name = 'JONES';
250 Oracle Database 11g DBA Handbook
could, because the second query does not perform a function on the Name column. Instead of
creating an index on the column Name, you can create an index on the column expression
UPPER(Name), as shown in the following example:
create index EMP_UPPER_NAME on
EMPLOYEE(UPPER(Name));
Although function-based indexes can be useful, be sure to consider the following points when
creating them:
Can you restrict the functions that will be used on the column? If so, can you restrict all
functions from being performed on the column?
Do you have adequate storage space for the additional indexes?
When you drop the table, you will be dropping more indexes (and therefore more
extents) than before.
Pages:
396
397
398
399
400
401
402
403
404
405
406
407
408
409
410
411
412
413
414
415
416
417
418
419
420