A non-unique B-tree index is created on a column by default if no other keywords are
specified in a CREATE INDEX statement.
Reverse Key Indexes
A reverse key index is a special kind of index used typically in an OLTP (online transaction
processing) environment. In a reverse key index, all the bytes in each column??™s key value of
the index are reversed. The reverse keyword specifies a reverse key index in the create index
command. Here is an example of creating a reverse key index:
create index IE_LINE_ITEM_ORDER_NUMBER
on LINE_ITEM(Order_Number) REVERSE;
If an order number of 123459 is placed, the reverse key index stores the order number as
954321. Inserts into the table are distributed across all leaf keys in the index, reducing the
contention among several writers all doing inserts of new rows. A reverse key index also reduces
the potential for these ???hot spots??? in an OLTP environment if orders are queried or modified soon
after they are placed.
Function-Based Indexes
A function-based index is similar to a standard B-tree index, except that a transformation of a
column or columns, declared as an expression, is stored in the index instead of the columns
themselves.
Function-based indexes are useful in cases where names and addresses might be stored in the
database as mixed case. A regular index on a column containing the value ???SmiTh??™ would not
return any values if the search criterion was ???Smith??™.
Pages:
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83