Performance Optimization - 6.4 [Foreign key association] Inner join syntax


We know that the foreign key of the fact table does not always have a corresponding dimension table record, and there may be invalid value. In this situation, we will take a very common action: delete this fact table record if no corresponding dimension table record is found for the foreign key; on the contrary, do the addressization association. This action is called inner join.

SPL provides the inner join syntax for cursors:

1 =file(“product.btx”).import@b().keys@i(id)
2 =file(“orders.btx”).cursor@b(p_id,quantity)
3 =A2.switch@i(p_id,A1)
4 =A3.groups(p_id.vendor;sum(p_id.price*quantity))

The switch@i() will delete the fact table record that cannot be associated.

The join() also has @i option for the same purpose.

The switch@i() will first fetch the record from the cursor and then judge the association. Even if it fails to associate, the record has already been generated. After reviewing the pre-cursor filtering discussed in Chapter 4, we will find that to get better performance, we need to finish the association judgement before generating the record.

SPL provides this mechanism for the cursor of composite table:

1 =file(“product.btx”).import@b().keys@i(id)
2 =file(“orders.ctx”).open().cursor@b(quantity;p_id:A1)
3 =A2.groups(p_id.vendor;sum(p_id.price*quantity))

The association relationship can be written in the filter condition parameters of composite table cursor, in this way, SPL will judge whether the association can be done before generating the records. If it can, the addressization conversion will be performed at the same time.

If we only need to judge whether there is association, without performing the addressization for the foreign key, the conventional conditional syntax will do:

1 =file(“product.btx”).import@b().keys@i(id)
2 =file(“orders.ctx”).open().cursor@b(quantity;A1.find(p_id))

SPL does not provide the corresponding syntax for the join() function. We can only read the records before processing in case of multi-field foreign key.