Database Reference
In-Depth Information
Fortunately, once this issue was discovered, correcting the problem was easy. We did need an index on the
processed-flag column, just not a bitmap index. We needed a conventional B*Tree index. It took a bit of convincing to
get one created. No one wanted to believe that conventionally indexing a column with two distinct values was a good
idea. But after setting up a simulation (I am very much into simulations, testing, and experimenting), we were able to
prove it was not only the correct approach but also that it would work very nicely.
We create indexes, indexes of any type, typically to find a small number of rows in a large set of data. in this
case, the number of rows we wanted to find via an index was one. We needed to find one unprocessed record. one is
a very small number of rows, therefore an index is appropriate. an index of any type would be appropriate. the B*tree
index was very useful in finding a single record out of a large set of records.
Note
When we created the index, we had to choose between the following approaches:
Just create an index on the processed-flag column.
Create an index only on the processed-flag column when the processed flag is N, that is, only
index the values of interest. We typically don't want to use an index when the processed flag is
Y since the vast majority of the records in the table have the value Y. Notice that I did not say
“We never want to use...” You might want to very frequently count the number of processed
records for some reason, and then an index on the processed records might well come in
very handy.
In the chapter on indexing, we'll go into more detail on both types. In the end, we created a very small
index on just the records where the processed flag was N. Access to those records was extremely fast and the
vast majority of Y records did not contribute to this index at all. We used a function-based index on a function
decode( processed_flag, ' N ' , ' N ' ) to return either N or NULL —since an entirely NULL key is not placed into a
conventional B*Tree index, we ended up only indexing the N records.
Note
there is more information on NULL s and indexing in Chapter 11.
Was that the end of the story? No, not at all. My client still had a less than optimal solution on its hands. They still had
to serialize on the “dequeue” of an unprocessed record. We could easily find the first unprocessed record—quickly—using
select * from queue_table where decode( processed_flag, ' N ' , ' N ' ) = ' N ' FOR UPDATE , but only one session at a time
could perform that operation. The project was using Oracle 10 g and therefore could not yet make use of the relatively new
SKIP LOCKED feature added in Oracle 11 g Release 1. SKIP LOCKED would permit many sessions to concurrently find the first
unlocked, unprocessed record, lock that record, and process it. Instead, we had to implement code to find the first unlocked
record and lock it manually. Such code would generally look like the following in Oracle 10 g and before. We begin by creating
a table with the requisite index described earlier and populate it with some data, as follows:
EODA@ORA12CR1> create table t
2 ( id number primary key,
3 processed_flag varchar2(1),
4 payload varchar2(20)
5 );
Table created.
 
 
Search WWH ::




Custom Search