Database Reference
In-Depth Information
Table 13-3.
Conditions That Can Lead to an Index Unique/Range Scan
Condition
B-tree
Bitmap
Equality (
=
)
✓
✓
IS NULL
✓
*
✓
Range (
BETWEEN
,
>
,
>=
,
<
and
<=
)
✓
✓
IN
✓
✓
LIKE
✓
✓
Inequality (
!=
,
<>
) and
IS NOT NULL
✓
†
*
Never applicable to single-column indexes; applicable to composite indexes only if
either another condition leading to an index range scan is specified or the NULL
values are guaranteed to be stored in the index.
†
Applicable only when several bitmaps are combined.
Many examples in the following sections are based on the
conditions.sql
script. The test table and its indexes
are created with the following SQL statements:
CREATE TABLE t (
id NUMBER,
d1 DATE,
n1 NUMBER,
n2 NUMBER,
n3 NUMBER,
n4 NUMBER,
n5 NUMBER,
n6 NUMBER,
c1 VARCHAR2(20),
c2 VARCHAR2(20),
pad VARCHAR2(4000),
CONSTRAINT t_pk PRIMARY KEY (id)
);
CREATE INDEX i_n1 ON t (n1);
CREATE INDEX i_n2 ON t (n2);
CREATE INDEX i_n3 ON t (n3);
CREATE INDEX i_n123 ON t (n1, n2, n3);
CREATE BITMAP INDEX i_n4 ON t (n4);
CREATE BITMAP INDEX i_n5 ON t (n5);
CREATE BITMAP INDEX i_n6 ON t (n6);
CREATE INDEX i_c1 ON t (c1);
