On the other hand, if the index stored the last
Chapter 1: Getting Started with the Oracle Architecture 19
names in all uppercase, all searches on last names could use uppercase. Here is an example of
creating a function-based index on the Last_Name column of the EMPLOYEE table:
create index up_name on employee(upper(Last_Name));
As a result, searches using queries such as the following will use the index we just created
instead of doing a full table scan:
select Employee_Number, Last_Name, First_Name, from employee
where upper(Last_Name) = 'SMITH';
Bitmap Indexes
A bitmap index has a significantly different structure from a B-tree index in the leaf node of the index.
It stores one string of bits for each possible value (the cardinality) of the column being indexed. The
length of the string of bits is the same as the number of rows in the table being indexed.
In addition to saving a tremendous amount of space compared to traditional indexes, a
bitmap index can provide dramatic improvements in response time because Oracle can quickly
remove potential rows from a query containing multiple where clauses long before the table itself
needs to be accessed. Multiple bitmaps can use logical and and or operations to determine which
rows to access from the table.
Although you can use a bitmap index on any column in a table, it is most efficient when the
column being indexed has a low cardinality, or number of distinct values.
Pages:
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84