Databases with well-behaved rowids
- Flat files, RMS fixed length sequential files, and RMS
relative files all have well-behaved rowids because the
TRR option is REQUIRED.
- TurboImage/XL children have well-behaved rowids.
- Any database that is using a Redefined Rowid, where the
redefined rowid is mostly sequentially ascending from 1,
has well-behaved rowids.
Databases with ill-behaved rowids
- Oracle uses a 13-byte rowid which must be mapped into
a $RECNO using a TIDMAP. Oracle 8i uses 16-byte rowids.
- Informix uses a 4-byte rowid but is not sequentially ascending.
Rowids may also be duplicated.
- C-ISAM uses a 4-byte rowid but is not sequentially ascending.
- ODBC standardized interface does not support rowids. This
includes Microsoft SQL Server.
- RMS Index Files do not have well-behaved rowids.
Well-Behaved vs Ill-Behaved IDs
The following comparison demonstrates bitmap indexing for
well-behaved ids versus ill-behaved ids.
The first example is "well-behaved" because the
ids are sequentially ascending from one.
Well-behaved ids |
Bitmap Index for "CA" |
Bitmap Index for "CO" |
|
Data |
ID |
ID |
Index |
ID |
Index |
|
CA |
2 |
1 |
0 |
1 |
1 |
|
CO |
1 |
2 |
1 |
2 |
0 |
|
CO |
3 |
3 |
0 |
3 |
1 |
Ill-behaved
The next example is "ill-behaved" because the maximum value
of the IDs (3,000,000) greatly exceeds the row count of the table. A
bitmap index will create a bit for every possible value between 1 and
the maximum value of the IDs in the table. In this example, there are
3,000,000 bits in the index for 3 records.
Ill-behaved ids |
Bitmap Index for "CA" |
Bitmap Index for "CO" |
|
Data |
ID |
ID |
Index |
ID |
Index |
|
CA |
2,000,000 |
1 |
0 |
1 |
1 |
|
CO |
1,000,000 |
2 |
0 |
2 |
0 |
|
CO |
3,000,000 |
3 |
0 |
3 |
1 |
|
|
|
4
...
1,999,999 |
0 |
4
...
999,999 |
0 |
|
|
2,000,000 |
1 |
1,000,000 |
1 |
|
|
2,000,001
...
3,000,000 |
0 |
1,000,001
...
2,999,999 |
0 |
|
|
|
|
3,000,000 |
1 |
top
|
