You've already forked mariadb-columnstore-engine
mirror of
https://github.com/mariadb-corporation/mariadb-columnstore-engine.git
synced 2025-07-29 08:21:15 +03:00
343 lines
8.2 KiB
Plaintext
Executable File
343 lines
8.2 KiB
Plaintext
Executable File
Demo 6
|
|
Select nation,
|
|
o_year,
|
|
sum(amount) sum_profit
|
|
from
|
|
( select
|
|
n_name nation,
|
|
extract(year from o_orderdate) o_year,
|
|
l_extendedprice * (1 - l_discount) - ps_supplycost * l_quantity amount
|
|
from
|
|
part,
|
|
supplier,
|
|
lineitem,
|
|
partsupp,
|
|
orders,
|
|
nation
|
|
where
|
|
s_suppkey = l_suppkey
|
|
and ps_suppkey = l_suppkey
|
|
and ps_partkey = l_partkey
|
|
and p_partkey = l_partkey
|
|
and o_orderkey = l_orderkey
|
|
and s_nationkey = n_nationkey
|
|
and p_name like '%green%'
|
|
) profit
|
|
group by nation, o_year
|
|
order by nation, o_year desc;
|
|
|
|
Oracle Execution Plan
|
|
|
|
Operation: table access
|
|
Options: FULL
|
|
Object_type: TABLE
|
|
Other:
|
|
Object_owner: TPCH
|
|
Search_columns:
|
|
Projection: P_PARTKEY
|
|
Object_name: PART
|
|
Alias: PART@SEL$2
|
|
Extended_information: FP:P_NAME LIKE '%green%'
|
|
Access_predicates:
|
|
Filter_predicates: P_NAME LIKE '%green%'
|
|
Select_level: SEL$F5BB74E1
|
|
--------------------
|
|
Operation: table access
|
|
Options: FULL
|
|
Object_type: TABLE
|
|
Other:
|
|
Object_owner: TPCH
|
|
Search_columns:
|
|
Projection: L_ORDERKEY^L_PARTKEY^L_SUPPKEY^L_QUANTITY^L_EXTENDEDPRICE^L_DISCOUNT
|
|
Object_name: LINEITEM
|
|
Alias: LINEITEM@SEL$2
|
|
Extended_information:
|
|
Access_predicates:
|
|
Filter_predicates:
|
|
Select_level: SEL$F5BB74E1
|
|
--------------------
|
|
Operation: table access
|
|
Options: FULL
|
|
Object_type: TABLE
|
|
Other:
|
|
Object_owner: TPCH
|
|
Search_columns:
|
|
Projection: PS_PARTKEY^PS_SUPPKEY^PS_SUPPLYCOST
|
|
Object_name: PARTSUPP
|
|
Alias: PARTSUPP@SEL$2
|
|
Extended_information:
|
|
Access_predicates:
|
|
Filter_predicates:
|
|
Select_level: SEL$F5BB74E1
|
|
--------------------
|
|
Operation: hash join ( Temp Space: 52,978,000 )
|
|
Options:
|
|
Object_type:
|
|
Other:
|
|
Object_owner:
|
|
Search_columns:
|
|
Projection: L_PARTKEY^L_ORDERKEY^L_DISCOUNT^L_SUPPKEY^L_QUANTITY^L_EXTENDEDPRICE
|
|
Object_name:
|
|
Alias:
|
|
Extended_information: AP:P_PARTKEY=L_PARTKEY
|
|
Access_predicates: P_PARTKEY=L_PARTKEY
|
|
Filter_predicates:
|
|
Select_level:
|
|
--------------------
|
|
Operation: table access
|
|
Options: FULL
|
|
Object_type: TABLE
|
|
Other:
|
|
Object_owner: TPCH
|
|
Search_columns:
|
|
Projection: S_SUPPKEY^S_NATIONKEY
|
|
Object_name: SUPPLIER
|
|
Alias: SUPPLIER@SEL$2
|
|
Extended_information:
|
|
Access_predicates:
|
|
Filter_predicates:
|
|
Select_level: SEL$F5BB74E1
|
|
--------------------
|
|
Operation: hash join ( Temp Space: 2,238,907,000 )
|
|
Options:
|
|
Object_type:
|
|
Other:
|
|
Object_owner:
|
|
Search_columns:
|
|
Projection: L_SUPPKEY^PS_SUPPLYCOST^L_QUANTITY^L_ORDERKEY^L_DISCOUNT^L_EXTENDEDPRICE
|
|
Object_name:
|
|
Alias:
|
|
Extended_information: AP:PS_SUPPKEY=L_SUPPKEY AND PS_PARTKEY=L_PARTKEY
|
|
Access_predicates: PS_SUPPKEY=L_SUPPKEY&PS_PARTKEY=L_PARTKEY
|
|
Filter_predicates:
|
|
Select_level:
|
|
--------------------
|
|
Operation: hash join ( Temp Space: 20,030,000 )
|
|
Options:
|
|
Object_type:
|
|
Other:
|
|
Object_owner:
|
|
Search_columns:
|
|
Projection: S_NATIONKEY^L_EXTENDEDPRICE^PS_SUPPLYCOST^L_QUANTITY^L_ORDERKEY^L_DISCOUNT
|
|
Object_name:
|
|
Alias:
|
|
Extended_information: AP:S_SUPPKEY=L_SUPPKEY
|
|
Access_predicates: S_SUPPKEY=L_SUPPKEY
|
|
Filter_predicates:
|
|
Select_level:
|
|
--------------------
|
|
Operation: table access
|
|
Options: FULL
|
|
Object_type: TABLE
|
|
Other:
|
|
Object_owner: TPCH
|
|
Search_columns:
|
|
Projection: O_ORDERKEY^O_ORDERDATE
|
|
Object_name: ORDERS
|
|
Alias: ORDERS@SEL$2
|
|
Extended_information:
|
|
Access_predicates:
|
|
Filter_predicates:
|
|
Select_level: SEL$F5BB74E1
|
|
--------------------
|
|
Operation: table access
|
|
Options: FULL
|
|
Object_type: TABLE
|
|
Other:
|
|
Object_owner: TPCH
|
|
Search_columns:
|
|
Projection: N_NATIONKEY^N_NAME
|
|
Object_name: NATION
|
|
Alias: NATION@SEL$2
|
|
Extended_information:
|
|
Access_predicates:
|
|
Filter_predicates:
|
|
Select_level: SEL$F5BB74E1
|
|
--------------------
|
|
Operation: hash join ( Temp Space: 437,076,000 )
|
|
Options:
|
|
Object_type:
|
|
Other:
|
|
Object_owner:
|
|
Search_columns:
|
|
Projection: S_NATIONKEY^L_EXTENDEDPRICE^PS_SUPPLYCOST^L_QUANTITY^L_DISCOUNT^O_ORDERDATE
|
|
Object_name:
|
|
Alias:
|
|
Extended_information: AP:O_ORDERKEY=L_ORDERKEY
|
|
Access_predicates: O_ORDERKEY=L_ORDERKEY
|
|
Filter_predicates:
|
|
Select_level:
|
|
--------------------
|
|
Operation: hash join
|
|
Options:
|
|
Object_type:
|
|
Other:
|
|
Object_owner:
|
|
Search_columns:
|
|
Projection: N_NAME^O_ORDERDATE^L_EXTENDEDPRICE^PS_SUPPLYCOST^L_QUANTITY^L_DISCOUNT
|
|
Object_name:
|
|
Alias:
|
|
Extended_information: AP:S_NATIONKEY=N_NATIONKEY
|
|
Access_predicates: S_NATIONKEY=N_NATIONKEY
|
|
Filter_predicates:
|
|
Select_level:
|
|
--------------------
|
|
Operation: sort ( Temp Space: 614,802,000 )
|
|
Options: GROUP BY
|
|
Object_type:
|
|
Other:
|
|
Object_owner:
|
|
Search_columns:
|
|
Projection: N_NAME^EXTRACT(YEAR FROM INTERNAL_FUNCTION(O_ORDERDATE))^SUM(L_EXTENDEDPRICE*(1-L_DISCOUNT)-PS_SUPPLYCOST*L_QUANTITY)
|
|
Object_name:
|
|
Alias:
|
|
Extended_information:
|
|
Access_predicates:
|
|
Filter_predicates:
|
|
Select_level: SEL$F5BB74E1
|
|
--------------------
|
|
Operation: select statement
|
|
Options:
|
|
Object_type:
|
|
Other:
|
|
Object_owner:
|
|
Search_columns:
|
|
Projection:
|
|
Object_name:
|
|
Alias:
|
|
Extended_information:
|
|
Access_predicates:
|
|
Filter_predicates:
|
|
Select_level:
|
|
--------------------
|
|
|
|
Calpont Execution Plan
|
|
|
|
>SELECT MAIN
|
|
>>Returned Columns
|
|
SimpleColumn tpch.NATION.N_NAME
|
|
s/t/c/T/A/RA: tpch/NATION/N_NAME/5//0
|
|
|
|
ArithmeticColumn: FunctionColumn: extract(YEAR FROM INTERNAL_FUNCTION(O_ORDERDATE))
|
|
|
|
ArithmeticColumn: AggregateColumn sum(L_EXTENDEDPRICE*(1-L_DISCOUNT)-PS_SUPPLYCOST*L_QUANTITY)
|
|
ArithmeticColumn: SimpleColumn L_EXTENDEDPRICE
|
|
s/t/c/T/A/RA: //L_EXTENDEDPRICE/30//0
|
|
|
|
ConstantColumn: 1(n)
|
|
SimpleColumn L_DISCOUNT
|
|
s/t/c/T/A/RA: //L_DISCOUNT/31//0
|
|
|
|
Operator: -
|
|
Operator: *
|
|
SimpleColumn PS_SUPPLYCOST
|
|
s/t/c/T/A/RA: //PS_SUPPLYCOST/44//0
|
|
|
|
SimpleColumn L_QUANTITY
|
|
s/t/c/T/A/RA: //L_QUANTITY/29//0
|
|
|
|
Operator: *
|
|
Operator: -
|
|
|
|
|
|
>>Filters
|
|
ConstantFilter
|
|
SimpleColumn tpch.PART.P_NAME
|
|
s/t/c/T/A/RA: tpch/PART/P_NAME/47//0
|
|
|
|
SimpleFilter
|
|
SimpleColumn tpch.PART.P_NAME
|
|
s/t/c/T/A/RA: tpch/PART/P_NAME/47//0
|
|
Operator: LIKE ConstantColumn: %green%(l)
|
|
|
|
SimpleFilter
|
|
SimpleColumn tpch.PART.P_PARTKEY
|
|
s/t/c/T/A/RA: tpch/PART/P_PARTKEY/46//0
|
|
Operator: = SimpleColumn tpch.LINEITEM.L_PARTKEY
|
|
s/t/c/T/A/RA: tpch/LINEITEM/L_PARTKEY/26//0
|
|
|
|
Operator: and
|
|
SimpleFilter
|
|
SimpleColumn tpch.PARTSUPP.PS_SUPPKEY
|
|
s/t/c/T/A/RA: tpch/PARTSUPP/PS_SUPPKEY/42//0
|
|
Operator: = SimpleColumn tpch.LINEITEM.L_SUPPKEY
|
|
s/t/c/T/A/RA: tpch/LINEITEM/L_SUPPKEY/27//0
|
|
|
|
SimpleFilter
|
|
SimpleColumn tpch.PARTSUPP.PS_PARTKEY
|
|
s/t/c/T/A/RA: tpch/PARTSUPP/PS_PARTKEY/41//0
|
|
Operator: = SimpleColumn tpch.LINEITEM.L_PARTKEY
|
|
s/t/c/T/A/RA: tpch/LINEITEM/L_PARTKEY/26//0
|
|
|
|
Operator: AND
|
|
Operator: and
|
|
SimpleFilter
|
|
SimpleColumn tpch.SUPPLIER.S_SUPPKEY
|
|
s/t/c/T/A/RA: tpch/SUPPLIER/S_SUPPKEY/55//0
|
|
Operator: = SimpleColumn tpch.LINEITEM.L_SUPPKEY
|
|
s/t/c/T/A/RA: tpch/LINEITEM/L_SUPPKEY/27//0
|
|
|
|
Operator: and
|
|
SimpleFilter
|
|
SimpleColumn tpch.ORDERS.O_ORDERKEY
|
|
s/t/c/T/A/RA: tpch/ORDERS/O_ORDERKEY/16//0
|
|
Operator: = SimpleColumn tpch.LINEITEM.L_ORDERKEY
|
|
s/t/c/T/A/RA: tpch/LINEITEM/L_ORDERKEY/25//0
|
|
|
|
Operator: and
|
|
SimpleFilter
|
|
SimpleColumn tpch.SUPPLIER.S_NATIONKEY
|
|
s/t/c/T/A/RA: tpch/SUPPLIER/S_NATIONKEY/58//0
|
|
Operator: = SimpleColumn tpch.NATION.N_NATIONKEY
|
|
s/t/c/T/A/RA: tpch/NATION/N_NATIONKEY/4//0
|
|
|
|
Operator: and
|
|
>>Group By Columns
|
|
SimpleColumn tpch.NATION.N_NAME
|
|
s/t/c/T/A/RA: tpch/NATION/N_NAME/5//0
|
|
|
|
ArithmeticColumn: FunctionColumn: extract(YEAR FROM INTERNAL_FUNCTION(O_ORDERDATE))
|
|
|
|
ArithmeticColumn: AggregateColumn sum(L_EXTENDEDPRICE*(1-L_DISCOUNT)-PS_SUPPLYCOST*L_QUANTITY)
|
|
ArithmeticColumn: SimpleColumn L_EXTENDEDPRICE
|
|
s/t/c/T/A/RA: //L_EXTENDEDPRICE/30//0
|
|
|
|
ConstantColumn: 1(n)
|
|
SimpleColumn L_DISCOUNT
|
|
s/t/c/T/A/RA: //L_DISCOUNT/31//0
|
|
|
|
Operator: -
|
|
Operator: *
|
|
SimpleColumn PS_SUPPLYCOST
|
|
s/t/c/T/A/RA: //PS_SUPPLYCOST/44//0
|
|
|
|
SimpleColumn L_QUANTITY
|
|
s/t/c/T/A/RA: //L_QUANTITY/29//0
|
|
|
|
Operator: *
|
|
Operator: -
|
|
|
|
SessionID: 11856
|
|
TxnID: 168
|
|
VerID: 168
|
|
|
|
--- Column Map ---
|
|
L_DISCOUNT : 0x6d54b0
|
|
L_EXTENDEDPRICE : 0x6ce700
|
|
L_ORDERKEY : 0x6ce420
|
|
L_PARTKEY : 0x6d67f0
|
|
L_QUANTITY : 0x6d59c0
|
|
L_SUPPKEY : 0x6d5a20
|
|
N_NAME : 0x6d6f10
|
|
N_NATIONKEY : 0x6d6f70
|
|
O_ORDERDATE : 0x6d6fd0
|
|
O_ORDERKEY : 0x6d7070
|
|
PS_PARTKEY : 0x6d6560
|
|
PS_SUPPKEY : 0x6d6640
|
|
PS_SUPPLYCOST : 0x6d6720
|
|
P_NAME : 0x6d9410
|
|
P_PARTKEY : 0x6d94f0
|
|
S_NATIONKEY : 0x6d95d0
|
|
S_SUPPKEY : 0x6d96b0
|
|
|