Never, never try to make any sense out of this

Menu

Usually, I tend to publish only successful experiments. For the first time, however, I am seemingly not able to somehow emulate the soft- and hardware setup needed to verify my hypotheses. So I desperately need your help.

Today, we wander through the realm of “big” propositional rule matching. Suppose we’ve got on the one hand 10^7 formulas of the form precondition -> conclusion where the propositions are key-encoded and we assume for simplicity a fixed length for their conjunction.
On the other hand, we are given a set of 250.000 fact formulas which are represented quite similarly but with a variable conjunction size whose average is three times the length of the preconditions.

So here is the vertical DDL model, followed by a Teradata sample population code:

Under the given model, the task of matching rules to facts equals to determining, which fixed-length sets in the RULE table correspond are subsets of the variable-length sets in the FACT table. Hence, a quite traditional JOIN followed by a filtered aggregation:

The performance of this method, though at least partially covered by the primary keys, is not overwhelming (1.500 seconds in the standard TDExpress14 VMWare image on a Core i7). Which is not too surprising, given the obtained execution plan:

1) First, we lock a distinct PRODUCT_QUALITY."pseudo table" for read
on a RowHash to prevent global deadlock for PRODUCT_QUALITY.FACT.
2) Next, we lock a distinct PRODUCT_QUALITY."pseudo table" for read
on a RowHash to prevent global deadlock for PRODUCT_QUALITY.RULE.
3) We lock PRODUCT_QUALITY.FACT for read, and we lock
PRODUCT_QUALITY.RULE for read.
4) We execute the following steps in parallel.
1) We do an all-AMPs RETRIEVE step from PRODUCT_QUALITY.RULE
by way of an all-rows scan with no residual conditions into
Spool 4 (all_amps) fanned out into 5 hash join partitions,
which is duplicated on all AMPs. The result spool file will
not be cached in memory. The size of Spool 4 is estimated
with high confidence to be 206,708 rows (4,340,868 bytes).
The estimated time for this step is 0.65 seconds.
2) We do an all-AMPs RETRIEVE step from PRODUCT_QUALITY.FACT
by way of an all-rows scan with no residual conditions into
Spool 5 (all_amps) fanned out into 5 hash join partitions,
which is built locally on the AMPs. The input table will not
be cached in memory, but it is eligible for synchronized
scanning. The result spool file will not be cached in memory.
The size of Spool 5 is estimated with high confidence to be
350,001 rows (7,350,021 bytes). The estimated time for this
step is 1.32 seconds.
5) We do an all-AMPs JOIN step from Spool 4 (Last Use) by way of a
RowHash match scan, which is joined to Spool 5 (Last Use) by way
of a RowHash match scan. Spool 4 and Spool 5 are joined using a
merge join, with a join condition of ("propositionId =
propositionId"). The result goes into Spool 3 (all_amps), which
is built locally on the AMPs. The size of Spool 3 is estimated with no confidence to be
61,145,139 rows (1,406,338,197 bytes). The estimated time for
this step is 2 minutes and 42 seconds.
6) We do an all-AMPs SUM step to aggregate from Spool 3 (Last Use) by
way of an all-rows scan , grouping by field1 (
PRODUCT_QUALITY.FACT.factId ,PRODUCT_QUALITY.RULE.ruleId
,PRODUCT_QUALITY.RULE.conclusionId). Aggregate Intermediate Results
are computed globally, then placed
in Spool 6. The aggregate spool file will not be cached in memory.
The size of Spool 6 is estimated with no confidence to be
45,858,855 rows (1,696,777,635 bytes). The estimated time for
this step is 1 hour and 22 minutes.
7) We do an all-AMPs RETRIEVE step from Spool 6 (Last Use) by way of
an all-rows scan with a condition of ("(Field_5 (DECIMAL(15,0)))=
7.") into Spool 1 (group_amps), which is built locally on the AMPs.
The result spool file will not be cached in memory. The size of
Spool 1 is estimated with no confidence to be 45,858,855 rows (
1,696,777,635 bytes). The estimated time for this step is 3
minutes and 36 seconds.
-> The contents of Spool 1 are sent back to the user as the result of
statement 1. The total estimated time is 1 hour and 28 minutes.

The problem that we are faced with is that due to the nature of the primary key (leading key factId or ruleId) and the Teradata parallelization strategy, the rows cannot be locally joined in the AMPS (or as to put it in traditional SQL terms: we need to arrange a SORT-ORDER MERGE JOIN).

Hence we need to fiddle with the table layout itself, i.e., we manipulate the primary index to just point to the crucial propositionId column:

1) First, we lock a distinct PRODUCT_QUALITY."pseudo table" for read
on a RowHash to prevent global deadlock for PRODUCT_QUALITY.FACT.
2) Next, we lock a distinct PRODUCT_QUALITY."pseudo table" for read
on a RowHash to prevent global deadlock for PRODUCT_QUALITY.RULE.
3) We lock PRODUCT_QUALITY.FACT for read, and we lock
PRODUCT_QUALITY.RULE for read.
4) We do an all-AMPs JOIN step from PRODUCT_QUALITY.RULE by way of a
RowHash match scan with no residual conditions, which is joined to
PRODUCT_QUALITY.FACT by way of a RowHash match scan with no
residual conditions. PRODUCT_QUALITY.RULE and
PRODUCT_QUALITY.FACT are joined using a merge join, with a join
condition of ("PRODUCT_QUALITY.FACT.propositionId =
PRODUCT_QUALITY.RULE.propositionId"). The result goes into Spool
3 (all_amps), which is built locally on the AMPs.
The size of Spool 3 is estimated with low confidence to be
137,806 rows (3,169,538 bytes). The estimated time for this step
is 1.02 seconds.
5) We do an all-AMPs SUM step to aggregate from Spool 3 (Last Use) by
way of an all-rows scan , grouping by field1 (
PRODUCT_QUALITY.FACT.factId ,PRODUCT_QUALITY.RULE.ruleId
,PRODUCT_QUALITY.RULE.conclusionId). Aggregate Intermediate
Results are computed globally, then placed in Spool 4. The
aggregate spool file will not be cached in memory. The size of
Spool 4 is estimated with no confidence to be 103,355 rows (
3,824,135 bytes). The estimated time for this step is 1.42
seconds.
6) We do an all-AMPs RETRIEVE step from Spool 4 (Last Use) by way of
an all-rows scan with a condition of ("(Field_5 (DECIMAL(15,0)))=
7.") into Spool 1 (group_amps), which is built locally on the AMPs.
The size of Spool 1 is estimated with no confidence to be 103,355
rows (3,824,135 bytes). The estimated time for this step is 0.51
seconds.
-> The contents of Spool 1 are sent back to the user as the result of
statement 1. The total estimated time is 2.95 seconds.

That looks like a nice parallel plan and an vene better prediction. But the runtime is … tatarata … 1.500 seconds!

Wait a minute. How is that possible?

It is possible (hypothesis), because

a) the VMWare image is configured with 1 logical processor, mainly disabling any hyperthreading or multicore support by the hosting i7 and because

b) the TDExpress configuration inside the VMWare is only equippied with two AMPs, hereby efficiently disabling any noticable partitioning/distribution effect at all.

This is the fourth part of a posting series (I, II and III) which deals with pushing the declarative boundary of SQL engines in the domain of statistics as far as possible.

In the previous examples, we have gathered samples for (piecewise-linear) risk probabilities.

Now we would rather use these samples to regress a typical exponential risk probability disttibution. The appeal of this particular family of distributions is not only that it can adapt to the most reasonable shapes (increasing, decreasing, even a kind of normal distribution), but that it has most interesting numeric properties.

As announced in the second part of the posting series, the scheme to use aggregate User-Defined-Functions (UDF) for fusing probabilities will work in a similar fashion for all standard databases, such as Oracle and Teradata.

Similar to the Oracle formulation (in which the aggregation object itself keeps state and functional logic), here is the Teradata version consisting of a static logic class and a seperate state class that is serialized and passed):

Once you deployed the jar file under a unique id (e.g., “de.jasna.teradata.repair” for which your user needs “execute prodedure” grants on the ‘SQLJ’ database), you can define the aggregation UDF as follows (note that the storage class above needs some extra class aggregate space wrt the default 64 ?bytes?).

This time, we are interested in propagating the hence obtained individual probabities up a more complex material hierarchy. For which we start by the simplifying assumption, that our only variable (“age” could also be named or measured as general “wear and tear”) does already take into account all the influences to a given material’s span of life. And when combining two materials into some higher structure, we assume that they do not influence each other’s durability alltoomuch.

Unfortunately, even under these assumptions fusing the individual probabilities turns out to require a “progressive” calculation, i.e., a formula which cannot be stated in terms of an ANSI SQL aggregation.

Fortunately, the expressiveness of the typical Used-Defined-Function (UDF) frameworks, such as the one of the Oracle Database, is well able to handle that requirement even under parallel/distributed execution settings:

/* Oracle needs a type/object definition as the UDF-base */
create type ProbabilityUnionImpl as object
(
probability NUMBER, -- memory just keeps current probability
static function ODCIAggregateInitialize(sctx IN OUT ProbabilityUnionImpl) return number,
member function ODCIAggregateIterate(self IN OUT ProbabilityUnionImpl
, value IN number) return number,
member function ODCIAggregateTerminate(self IN ProbabilityUnionImpl
, returnValue OUT number
, flags IN number) return number,
member function ODCIAggregateMerge(self IN OUT ProbabilityUnionImpl
, ctx2 IN ProbabilityUnionImpl) return number
);
/
/* and here are the method implementations */
create or replace type body ProbabilityUnionImpl is
/* We start with an empty probability */
static function ODCIAggregateInitialize(sctx IN OUT ProbabilityUnionImpl) return number is
begin
sctx := ProbabilityUnionImpl(0);
return ODCIConst.Success;
end;
/* Local iteration implements fusion under independence */
member function ODCIAggregateIterate(self IN OUT ProbabilityUnionImpl
, value IN number) return number is
begin
self.probability:=self.probability+value-self.probability*value;
return ODCIConst.Success;
end;
/* At the end, just return the stored probability */
member function ODCIAggregateTerminate(self IN ProbabilityUnionImpl
, returnValue OUT number
, flags IN number) return number is
begin
returnValue := self.probability;
return ODCIConst.Success;
end;
/* Distributed/Parallel merge, same case as local iteration */
member function ODCIAggregateMerge(self IN OUT ProbabilityUnionImpl
, ctx2 IN ProbabilityUnionImpl) return number is
begin
self.probability:=self.probability+ctx2.probability-self.probability*ctx2.probability;
return ODCIConst.Success;
end;
end;
/
/* finally, the aggregation function is defined in terms of the type */
create function union_indep_prop (input number) return number
PARALLEL_ENABLE AGGREGATE USING ProbabilityUnionImpl;

With that backing installed, we are now able to complete our estimation table even for complex materials. Since we are Oracle-specific anyway, we also use the established recursion syntax when flattening the bill of material tree.

insert into REPAIR_ESTIMATIONS
with
/* we just need the root - leaf material relations */
DECOMPOSITION as (
select
CONNECT_BY_ROOT
composite.composite_material as root_material
, composite.component_material as component_material
, level as depth
from STRUCTURED_MATERIAL composite
connect by prior composite.component_material=composite.composite_material
)
/* for each age of an individual age, find all corresponding probabilities of
sibling materials for fusion */
select DECOMPOSITION.root_material
, REPAIR.age
, lead(REPAIR.age-1) over (partition by DECOMPOSITION.root_material order by REPAIR.age) as next_age
, union_indep_prop(ALL_REPAIRS.probability) as probability
, union_indep_prop(ALL_REPAIRS.lower_bound_95) as lower_bound_95
, union_indep_prop(ALL_REPAIRS.upper_bound_95) as upper_bound_95
, max(DECOMPOSITION.depth) as depth
from DECOMPOSITION
join REPAIR_ESTIMATIONS REPAIR on REPAIR.material=DECOMPOSITION.component_material and REPAIR.depth=0
join DECOMPOSITION ALL_PARTS on ALL_PARTS.root_material=DECOMPOSITION.root_material
join REPAIR_ESTIMATIONS ALL_REPAIRS on ALL_REPAIRS.material=ALL_PARTS.component_material and
REPAIR.age between ALL_REPAIRS.age and coalesce(ALL_REPAIRS.next_age, REPAIR.age)
group by DECOMPOSITION.root_material
, REPAIR.age
;

SQL can be a quite efficient tool to implement particular types of statistics. Since windowing became a part of the ANSI standard, this holds for more and more databases as well as mathematical/structural complexities.

Fortunately, we know (or at least Google knows 😉 that for positive P(i), it holds that ΠP(i) = EXP(Σln(P(i)))

This allows us to realize a (batch) product limit estimation including the determination of confidence intervals such as in the following example which has been generalized from Oracle to Teradata. Already included are some objects needed for the sequel post, just in case you wonder about the verbosity:

So last week, I found myself thinking about some kind of Ingress competition for my son’s 10th anniversary. With some intervention of my wife, fortunately, we came up with a more down-to-earth scheme: Two teams of five boys each (the “red knights” versus the “blue monks”) had to hunt down 20 “magic symbols” (really horrifying tongue tattoos only to be “resolved by a truthful mouth”) which the “white sorcerer” had hidden in a rural area of ~20 square kilometers between our house and a playground/barbecue place in the village nearby.

From the two tasks of the white sorcerer

Hiding the treasures only equipped with a bike and a single hour of sparetime

Printing two maps on A3 paper detailed enough such that the young lads stay on track

Finally, you need to capture the single-bitmap-web-page at once, independently of your screen resolution. This is the domain of Awesome screenshot. Believe me, I tested a whole lot of these extensions in Chrome, but that was the only “awesome” one that didn’t crash and produced a reasonable result (click on the image above).