The follwing is not clear to me: We have value ordering for Join Indexes, but at the same time the Join index has a Primary Index and is ROWHASH distributed. For me this means, Master Index and Cylinder Index carry the information about on which Data Blocks a searched ROWHASH can be found. This is done with a binary search on MI/CI.
But where takes the value ordering place? Does this mean, Only inside a data block, the row pointer array stores the ordered values (instead of ROWIDs in case of ROWHASH ordering)?
If this is the case, wouldn't this mean we always have to to a FTS on the join index as the AMP carrying searched VALUES is not known? I would assume in case of value ordering, where the primary index of the Join Index cannot be used for row qualification, we always have an All-AMP access and each AMP can probe ALL its Join Index data blocks with abinary search for the indexed value?
Maybe somebody has more insight on this topic? All I could find in the documentations is "value ordering is useful for range queries"...but not a single explanation WHY.
Thanks in advance
Rows are still distributed to an AMP based on the high order portion of the RowHash, but within an AMP the ordering value is substituted in place of the RowHash (in the MI/CI as well as the data block). This provides essentially the same benefits to range queries as a single-level PPI defined on the ordering value, but without the added overhead. You have an all-AMP access, but each AMP only has to scan its data blocks holding values within the indicated range.