Re: Dynamic Partitioning using Segment Visibility Maps

hris Browne wrote:
> _On The Other Hand_, there will be attributes that are *NOT* set in a
> more-or-less chronological order, and Segment Exclusion will be pretty
> useless for these attributes.

Short summary:

With the appropriate clustering, ISTM Segment Exclusion
can be useful on all columns in a table.

Cluster the table by "one-bit-of-column1 || one-bit-of-column-2"...
That way any "col2=value" query could exclude at least about
half the table regardless of if it's monotonically increasing
or even totally random.

It seems one could make Segment Exclusion even useful for
multiple unrelated columns in a table. Consider a large
table of people where one might want segment exclusion to
help with both first name, and last name.

One could cluster the table by "first-letter-of-last-name ||
first-letter-of-first-name". Then a query for last name Smith
could exclude all but the consecutive segments of S's; while
the query for first name John would only have to look in the
26 runs of segments with AJ, BJ, CJ, ...

Perhaps better - hash each column and interleave the bits
col1bit1, col2bit1, col3bit1, col1bit2, col2bit2, col3bit3
If I understand segment exclusion right, that way on any
table bigger than 8 segments any query of col1=val,
or col2=val or col3=val would scan at most half the table;
on a table bigger than 64 segments any such query would
scan at most 1/4 of the table.

Obviously this only benefits the rarely changing parts of
tables; and new and updated values would be in a very hot
segment at the end of the table that wouldn't be segment
excluded much.