Database Reference
In-Depth Information
Indexing Only Some of the Rows
In addition to transparently helping out queries that use built-in functions like
UPPER
,
LOWER
, and so on, function-
based indexes can be used to selectively index only some of the rows in a table. As we'll discuss a little later, B*Tree
indexes do not contain entries for entirely
NULL k
eys. That is, if you have an index
I
on a table
T
(as follows) and you
have a row where
A
and
B
are both NULL, there will be no entry in the index structure.
Create index I on t(a,b);
This comes in handy when you are indexing just some of the rows in a table.
Consider a large table with a
NOT NULL
column called
PROCESSED_FLAG
that may take one of two values,
Y
or
N
,
with a default value of
N
. New rows are added with a value of
N
to signify not processed, and as they are processed, they
are updated to
Y
to signify processed. We would like to index this column to be able to retrieve the
N
records rapidly,
but there are millions of rows and almost all of them are going to have a value of
Y
. The resulting B*Tree index will
be large, and the cost of maintaining it as we update from
N
to
Y
will be high. This table sounds like a candidate for a
bitmap index (this is low cardinality, after all), but this is a transactional system and lots of people will be inserting
records at the same time with the processed column set to
N
and, as we discussed earlier, bitmaps are not good for
concurrent modifications. When we factor in the constant updating of
N
to
Y
in this table as well, then bitmaps would
be out of the question, as this process would serialize entirely.
So, what we would really like is to index only the records of interest (the
N
records). We'll see how to do this with
function-based indexes, but before we do, let's see what happens if we just use a regular B*Tree index. Using the
standard
BIG_TABLE
script described in the setup section at the beginning of the topic, we'll update the
TEMPORARY
column, flipping the
Y
s to
N
s and the
N
s to
Y
s:
EODA@ORA12CR1> update big_table set temporary = decode(temporary,'N','Y','N');
1000000 rows updated.
And we'll check out the ratio of
Y
s to
N
s:
EODA@ORA12CR1> select temporary, cnt,
2 round( (ratio_to_report(cnt) over ()) * 100, 2 ) rtr
3 from (
4 select temporary, count(*) cnt
5 from big_table
6 group by temporary
7 )
8 /
T CNT RTR
- ---------- ----------
Y 998728 99.87
N 1272 .13
As we can see, of the 1,000,000 records in the table, only about one-fifth of 1 percent of the data should be
indexed. If we use a conventional index on the
TEMPORARY
column (which is playing the role of the
PROCESSED_FLAG
column in this example), we would discover that the index has 1,000,000 entries, consumes almost 14MB of space,
and has a height of 3:
EODA@ORA12CR1> create index processed_flag_idx
2 on big_table(temporary);
Index created.
