zigzag join

A zigzag join is a join method in which a fact table and two or more dimension tables in a star schema are joined, such that the fact table is accessed using an index. It requires equality predicates between each dimension table and the fact table. This join method calculates the Cartesian product of rows from the dimension tables without actually materializing the Cartesian product and probes the fact table using a multicolumn index, so that the fact table is filtered along two or more dimension tables simultaneously. The probe into the fact table finds matching rows. The zigzag join then returns the next combination of values that is available from the fact table index. This next combination of values, known as feedback, is used to skip over probe values provided by the Cartesian product of dimension tables that will not find a match in the fact table. Filtering the fact table on two or more dimension tables simultaneously, and skipping probes that are known to be unproductive, together makes the zigzag join an efficient method for querying large fact tables.
Zigzag 连接是一种连接方法，其中事实表与星型模式中的两个或多个维度表相连接，以便使用索引访问事实表。它要求每个维度表和事实表之间的相等谓词。这种连接方法计算维度表中的行笛卡儿积，而不实际具体化笛卡儿积，并使用多列索引探测事实表，以便事实表同时沿着两个或多个维度表过滤。对事实表的探测会找到匹配的行。然后，zigzag 连接返回事实表索引中可用的下一组值。接下来的值组合称为 feedback，用于跳过维度表笛卡儿积提供的探测值，这些探测值在事实表中找不到匹配。同时在两个或多个维度表上过滤事实表，并且跳过已知为非生产性的探测，这使得 z 字形连接成为查询大型事实表的有效方法。
总的来说就是，拿维度表的关联字段的笛卡尔组合去事实表找（通过联合索引），找到的结果用来过滤笛卡尔组合，因为很多组合在事实表里是没有的。这样两层过滤同时进行，可以有效提高join的效率。