Details
Major Description
Here are the plans for the two queries. The plans show the queries. The differences can be seen in lines 34-37. In the second plan, the select is below the assign.
|
|
|
Plan 1
|
|
|
|
|
select 7, count (1) from R, S where tobigint (R.u64) /*+ productivity R 65536.0 */ = (S.u64) and R.rand <= 1024 and S.rand <= 1024
|
{
|
"requestID": "591ffafb-e8e3-4f6c-96cf-4e8ddcd5b224",
|
"signature": {
|
"*": "*"
|
},
|
"results": [ {
|
"$1" : 7,
|
"$2" : 16529
|
} ]
|
,
|
"plans":{"optimizedLogicalPlan": "distribute result [$$53]
|
-- DISTRIBUTE_RESULT |UNPARTITIONED|
|
exchange
|
-- ONE_TO_ONE_EXCHANGE |UNPARTITIONED|
|
project ([$$53])
|
-- STREAM_PROJECT |UNPARTITIONED|
|
assign [$$53] <- [{\"$1\": 7, \"$2\": $$60}]
|
-- ASSIGN |UNPARTITIONED|
|
aggregate [$$60] <- [agg-sql-sum($$64)]
|
-- AGGREGATE |UNPARTITIONED|
|
exchange
|
-- RANDOM_MERGE_EXCHANGE |PARTITIONED|
|
aggregate [$$64] <- [agg-sql-count(1)]
|
-- AGGREGATE |PARTITIONED|
|
exchange
|
-- ONE_TO_ONE_EXCHANGE |PARTITIONED|
|
join (eq($$58, $$59))
|
-- HYBRID_HASH_JOIN [$$59][$$58] |PARTITIONED|
|
exchange
|
-- HASH_PARTITION_EXCHANGE [$$59] |PARTITIONED|
|
project ([$$59])
|
-- STREAM_PROJECT |PARTITIONED|
|
select (le($$S.getField(\"rand\"), 1024))
|
-- STREAM_SELECT |PARTITIONED|
|
assign [$$59] <- [$$S.getField(\"u64\")]
|
-- ASSIGN |PARTITIONED|
|
project ([$$S])
|
-- STREAM_PROJECT |PARTITIONED|
|
exchange
|
-- ONE_TO_ONE_EXCHANGE |PARTITIONED|
|
data-scan []<-[$$56, $$S, $$57] <- Default.S
|
-- DATASOURCE_SCAN |PARTITIONED|
|
exchange
|
-- ONE_TO_ONE_EXCHANGE |PARTITIONED|
|
empty-tuple-source
|
-- EMPTY_TUPLE_SOURCE |PARTITIONED|
|
exchange
|
-- HASH_PARTITION_EXCHANGE [$$58] |PARTITIONED|
|
project ([$$58])
|
-- STREAM_PROJECT |PARTITIONED|
|
assign [$$58] <- [to-bigint($$R.getField(\"u64\"))]
|
-- ASSIGN |PARTITIONED|
|
select (le($$R.getField(\"rand\"), 1024))
|
-- STREAM_SELECT |PARTITIONED|
|
project ([$$R])
|
-- STREAM_PROJECT |PARTITIONED|
|
exchange
|
-- ONE_TO_ONE_EXCHANGE |PARTITIONED|
|
data-scan []<-[$$54, $$R, $$55] <- Default.R
|
-- DATASOURCE_SCAN |PARTITIONED|
|
exchange
|
-- ONE_TO_ONE_EXCHANGE |PARTITIONED|
|
empty-tuple-source
|
-- EMPTY_TUPLE_SOURCE |PARTITIONED|
|
"},
|
"status": "success",
|
"metrics": {
|
"elapsedTime": "1.316047007s",
|
"executionTime": "1.311338763s",
|
"compileTime": "59.512719ms",
|
"resultCount": 1,
|
"resultSize": 31,
|
"processedObjects": 8388608
|
}
|
}
|
|
|
Plan 2
|
|
|
select 67, count (1) from R, S where tobigint (R.u64) /*+ productivity R 65536.0 */ /*+ hashjoin build (R) */ = tobigint (S.u64) and R.rand <= 1024 and S.rand <= 1024
|
{
|
"requestID": "e7925c8d-78a2-4ec3-bb95-90d8880aad23",
|
"signature": {
|
"*": "*"
|
},
|
"results": [ {
|
"$1" : 67,
|
"$2" : 16529
|
} ]
|
,
|
"plans":{"optimizedLogicalPlan": "distribute result [$$54]
|
-- DISTRIBUTE_RESULT |UNPARTITIONED|
|
exchange
|
-- ONE_TO_ONE_EXCHANGE |UNPARTITIONED|
|
project ([$$54])
|
-- STREAM_PROJECT |UNPARTITIONED|
|
assign [$$54] <- [{\"$1\": 67, \"$2\": $$61}]
|
-- ASSIGN |UNPARTITIONED|
|
aggregate [$$61] <- [agg-sql-sum($$66)]
|
-- AGGREGATE |UNPARTITIONED|
|
exchange
|
-- RANDOM_MERGE_EXCHANGE |PARTITIONED|
|
aggregate [$$66] <- [agg-sql-count(1)]
|
-- AGGREGATE |PARTITIONED|
|
exchange
|
-- ONE_TO_ONE_EXCHANGE |PARTITIONED|
|
join (eq($$59, $$60))
|
-- HYBRID_HASH_JOIN [$$60][$$59] |PARTITIONED|
|
exchange
|
-- HASH_PARTITION_EXCHANGE [$$60] |PARTITIONED|
|
project ([$$60])
|
-- STREAM_PROJECT |PARTITIONED|
|
assign [$$60] <- [to-bigint($$S.getField(\"u64\"))]
|
-- ASSIGN |PARTITIONED|
|
select (le($$S.getField(\"rand\"), 1024))
|
-- STREAM_SELECT |PARTITIONED|
|
project ([$$S])
|
-- STREAM_PROJECT |PARTITIONED|
|
exchange
|
-- ONE_TO_ONE_EXCHANGE |PARTITIONED|
|
data-scan []<-[$$57, $$S, $$58] <- Default.S
|
-- DATASOURCE_SCAN |PARTITIONED|
|
exchange
|
-- ONE_TO_ONE_EXCHANGE |PARTITIONED|
|
empty-tuple-source
|
-- EMPTY_TUPLE_SOURCE |PARTITIONED|
|
exchange
|
-- HASH_PARTITION_EXCHANGE [$$59] |PARTITIONED|
|
project ([$$59])
|
-- STREAM_PROJECT |PARTITIONED|
|
assign [$$59] <- [to-bigint($$R.getField(\"u64\"))]
|
-- ASSIGN |PARTITIONED|
|
select (le($$R.getField(\"rand\"), 1024))
|
-- STREAM_SELECT |PARTITIONED|
|
project ([$$R])
|
-- STREAM_PROJECT |PARTITIONED|
|
exchange
|
-- ONE_TO_ONE_EXCHANGE |PARTITIONED|
|
data-scan []<-[$$55, $$R, $$56] <- Default.R
|
-- DATASOURCE_SCAN |PARTITIONED|
|
exchange
|
-- ONE_TO_ONE_EXCHANGE |PARTITIONED|
|
empty-tuple-source
|
-- EMPTY_TUPLE_SOURCE |PARTITIONED|
|
"},
|
"status": "success",
|
"metrics": {
|
"elapsedTime": "740.649411ms",
|
"executionTime": "738.748432ms",
|
"compileTime": "24.568622ms",
|
"resultCount": 1,
|
"resultSize": 32,
|
"processedObjects": 8388608
|
}
|
}
|
|