Hi,

I hope that everyone can follow this, as I am not trying to
obfuscate this, but to present a clear mathematically
concise formal definition of what I am proposing.

Your comments are welcome. I have been pondering a new type
of index, I call in an extent managed index. Curious about
its viability.

Assume:
TABLE001 (PCOL1 NUMERIC(20, 0) IDENTITY, COL2 CHAR(6), COL3
INT, COL4 CHAR(3), ...)

COUNT(*) OF TABLE001 = 900,000,000,000

In addition, COL2 by itself qualifies some 20% set of the
table and COL3 qualifies some other partial set of the
table. Now, I would like to query based upon COL2 and COL3
and retrieve the intersection set of the two sets. However,
a B-Tree index is absolutely enormous and unmanageable on a
table of this extreme size.

Hence, some other sort of automaton must arrange the data in
some sort of fashion for retrieval and/or storage.

KNOW:
A distinctive record is located via its page number P an
element of all page numbers of a database, not a segment,
because a table can have its growth started on a new
segment. In addition, its relative offset in bytes on the
page, B an element of the possible byte offsets of a page.

Lets represent all possible page numbers of our table as P0,
P1, ..., Pi and all possible byte offsets in Pi as B0, B1,
.., Bk

Our table can then be represented as:
P0:B0, P0:B1, ..., P0:Bk, P1:Bk, ..., Pi:Bk or simply, we
just call our table Pi:Bk

Now, I do not want to list all elements of COL2 and COL3 so
I will represent all elements of COL2 and COL3 as 2j and 3m
where 2j is an element of COL2 and 3m is and element of
COL3.

Create a extent managed index as such:
EXTENT TABLE MAP
K1 K2 EXT_STRT EXT_FRE_SPACE (0 or 1)
-------------------------------------------------------------
2j 3m x 0 or 1

Extents as pointed to by condition 2j:3m would be extent x
where x is element of 0 or the natural numbers and x = n*8 -
1 or x = 0, where n is an element of the natural numbers.

Extents only contain the RIDS for the condition 2j:3m or
Pi:Bk. So specifying a condition WHERE COL2 = AA AND COL3 =
1 would process as:

LOOP all records matching condition in the EXTENT TABLE
Locate Extent Start Of Qualifying Record
GoTo Extent Start
Traverse RIDS of extent to data pages and get data

Any thoughts, this type of index would be for DSS.

For even more compression, the byte offset Bk can be removed
and just search the entire page for records matching the
condition.

Thanks,
Ryan