You've already forked mariadb-columnstore-engine
mirror of
https://github.com/mariadb-corporation/mariadb-columnstore-engine.git
synced 2025-10-31 18:30:33 +03:00
379 lines
9.3 KiB
Plaintext
Executable File
379 lines
9.3 KiB
Plaintext
Executable File
Q7:
|
|
select
|
|
supp_nation,
|
|
cust_nation,
|
|
l_year,
|
|
sum(volume) as revenue
|
|
from
|
|
(
|
|
select
|
|
n1.n_name as supp_nation,
|
|
n2.n_name as cust_nation,
|
|
extract(year from l_shipdate) as l_year,
|
|
l_extendedprice * (1 - l_discount) as volume
|
|
from
|
|
supplier,
|
|
lineitem,
|
|
orders,
|
|
customer,
|
|
nation n1,
|
|
nation n2
|
|
where
|
|
s_suppkey = l_suppkey
|
|
and o_orderkey = l_orderkey
|
|
and c_custkey = o_custkey
|
|
and s_nationkey = n1.n_nationkey
|
|
and c_nationkey = n2.n_nationkey
|
|
and (
|
|
(n1.n_name = 'FRANCE' and n2.n_name = 'GERMANY')
|
|
or (n1.n_name = 'GERMANY' and n2.n_name = 'FRANCE')
|
|
)
|
|
and l_shipdate between date '1995-01-01' and date '1996-12-31'
|
|
) as shipping
|
|
group by
|
|
supp_nation,
|
|
cust_nation,
|
|
l_year
|
|
order by
|
|
supp_nation,
|
|
cust_nation,
|
|
l_year;
|
|
|
|
Oracle Execution Plan
|
|
|
|
Operation: table access
|
|
Options: FULL
|
|
Object_type: TABLE
|
|
Other:
|
|
Object_owner: CALUSER01
|
|
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: table access
|
|
Options: FULL
|
|
Object_type: TABLE
|
|
Other:
|
|
Object_owner: CALUSER01
|
|
Search_columns:
|
|
Projection: L_ORDERKEY^L_SUPPKEY^L_EXTENDEDPRICE^L_DISCOUNT^L_SHIPDATE
|
|
Object_name: LINEITEM
|
|
Alias: LINEITEM@SEL$2
|
|
Extended_information: FP:L_SHIPDATE>=TO_DATE('1995-01-01 00:00:00', 'yyyy-mm-dd hh24:mi:ss') AND L_SHIPDATE<=TO_DATE('1996-12-31 00:00:00', 'yyyy-mm-dd hh24:mi:ss')
|
|
Access_predicates:
|
|
Filter_predicates: L_SHIPDATE>=TO_DATE('1995-01-01 00:00:00', 'yyyy-mm-dd hh24:mi:ss')&L_SHIPDATE<=TO_DATE('1996-12-31 00:00:00', 'yyyy-mm-dd hh24:mi:ss')
|
|
Select_level: SEL$F5BB74E1
|
|
--------------------
|
|
Operation: hash join
|
|
Options:
|
|
Object_type:
|
|
Other:
|
|
Object_owner:
|
|
Search_columns:
|
|
Projection: S_NATIONKEY^L_ORDERKEY^L_SHIPDATE^L_EXTENDEDPRICE^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: CALUSER01
|
|
Search_columns:
|
|
Projection: O_ORDERKEY^O_CUSTKEY
|
|
Object_name: ORDERS
|
|
Alias: ORDERS@SEL$2
|
|
Extended_information:
|
|
Access_predicates:
|
|
Filter_predicates:
|
|
Select_level: SEL$F5BB74E1
|
|
--------------------
|
|
Operation: hash join
|
|
Options:
|
|
Object_type:
|
|
Other:
|
|
Object_owner:
|
|
Search_columns:
|
|
Projection: S_NATIONKEY^L_DISCOUNT^L_SHIPDATE^L_EXTENDEDPRICE^O_CUSTKEY
|
|
Object_name:
|
|
Alias:
|
|
Extended_information: AP:O_ORDERKEY=L_ORDERKEY
|
|
Access_predicates: O_ORDERKEY=L_ORDERKEY
|
|
Filter_predicates:
|
|
Select_level:
|
|
--------------------
|
|
Operation: table access
|
|
Options: FULL
|
|
Object_type: TABLE
|
|
Other:
|
|
Object_owner: CALUSER01
|
|
Search_columns:
|
|
Projection: N1.N_NATIONKEY^N1.N_NAME
|
|
Object_name: NATION
|
|
Alias: N1@SEL$2
|
|
Extended_information: FP:N1.N_NAME='FRANCE' OR N1.N_NAME='GERMANY'
|
|
Access_predicates:
|
|
Filter_predicates: N1.N_NAME='FRANCE'|N1.N_NAME='GERMANY'
|
|
Select_level: SEL$F5BB74E1
|
|
--------------------
|
|
Operation: hash join
|
|
Options:
|
|
Object_type:
|
|
Other:
|
|
Object_owner:
|
|
Search_columns:
|
|
Projection: O_CUSTKEY^L_DISCOUNT^L_SHIPDATE^L_EXTENDEDPRICE^N1.N_NAME
|
|
Object_name:
|
|
Alias:
|
|
Extended_information: AP:S_NATIONKEY=N1.N_NATIONKEY
|
|
Access_predicates: S_NATIONKEY=N1.N_NATIONKEY
|
|
Filter_predicates:
|
|
Select_level:
|
|
--------------------
|
|
Operation: table access
|
|
Options: FULL
|
|
Object_type: TABLE
|
|
Other:
|
|
Object_owner: CALUSER01
|
|
Search_columns:
|
|
Projection: N2.N_NATIONKEY^N2.N_NAME
|
|
Object_name: NATION
|
|
Alias: N2@SEL$2
|
|
Extended_information: FP:(N2.N_NAME='FRANCE' OR N2.N_NAME='GERMANY') AND (N1.N_NAME='FRANCE' AND N2.N_NAME='GERMANY' OR N1.N_NAME='GERMANY' AND N2.N_NAME='FRANCE')
|
|
Access_predicates:
|
|
Filter_predicates: (N2.N_NAME='FRANCE'|N2.N_NAME='GERMANY')&(N1.N_NAME='FRANCE'&N2.N_NAME='GERMANY'|N1.N_NAME='GERMANY'&N2.N_NAME='FRANCE')
|
|
Select_level: SEL$F5BB74E1
|
|
--------------------
|
|
Operation: nested loops
|
|
Options:
|
|
Object_type:
|
|
Other:
|
|
Object_owner:
|
|
Search_columns:
|
|
Projection: O_CUSTKEY^L_DISCOUNT^L_SHIPDATE^L_EXTENDEDPRICE^N1.N_NAME^N2.N_NATIONKEY^N2.N_NAME
|
|
Object_name:
|
|
Alias:
|
|
Extended_information:
|
|
Access_predicates:
|
|
Filter_predicates:
|
|
Select_level:
|
|
--------------------
|
|
Operation: table access
|
|
Options: FULL
|
|
Object_type: TABLE
|
|
Other:
|
|
Object_owner: CALUSER01
|
|
Search_columns:
|
|
Projection: C_CUSTKEY^C_NATIONKEY
|
|
Object_name: CUSTOMER
|
|
Alias: CUSTOMER@SEL$2
|
|
Extended_information:
|
|
Access_predicates:
|
|
Filter_predicates:
|
|
Select_level: SEL$F5BB74E1
|
|
--------------------
|
|
Operation: hash join
|
|
Options:
|
|
Object_type:
|
|
Other:
|
|
Object_owner:
|
|
Search_columns:
|
|
Projection: N2.N_NAME^L_DISCOUNT^L_SHIPDATE^L_EXTENDEDPRICE^N1.N_NAME
|
|
Object_name:
|
|
Alias:
|
|
Extended_information: AP:C_CUSTKEY=O_CUSTKEY AND C_NATIONKEY=N2.N_NATIONKEY
|
|
Access_predicates: C_CUSTKEY=O_CUSTKEY&C_NATIONKEY=N2.N_NATIONKEY
|
|
Filter_predicates:
|
|
Select_level:
|
|
--------------------
|
|
Operation: sort
|
|
Options: GROUP BY
|
|
Object_type:
|
|
Other:
|
|
Object_owner:
|
|
Search_columns:
|
|
Projection: N1.N_NAME^N2.N_NAME^EXTRACT(YEAR FROM INTERNAL_FUNCTION(L_SHIPDATE))^SUM(L_EXTENDEDPRICE*(1-L_DISCOUNT))
|
|
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
|
|
1. SimpleColumn tpch.NATION.N_NAME
|
|
s/t/c/T/A: tpch/NATION/N_NAME/5/N1
|
|
|
|
2. SimpleColumn tpch.NATION.N_NAME
|
|
s/t/c/T/A: tpch/NATION/N_NAME/5/N2
|
|
|
|
3. ArithmeticColumn: FunctionColumn: extract(YEAR FROM INTERNAL_FUNCTION(L_SHIPDATE))
|
|
|
|
4. ArithmeticColumn: AggregateColumn sum(L_EXTENDEDPRICE*(1-L_DISCOUNT))
|
|
ArithmeticColumn: SimpleColumn L_EXTENDEDPRICE
|
|
s/t/c/T/A: ///0/
|
|
|
|
ConstantColumn: 1(n)
|
|
SimpleColumn L_DISCOUNT
|
|
s/t/c/T/A: ///0/
|
|
|
|
Operator: -
|
|
Operator: *
|
|
|
|
|
|
>>Filters
|
|
SimpleFilter
|
|
SimpleColumn tpch.LINEITEM.L_SHIPDATE
|
|
s/t/c/T/A: tpch/LINEITEM/L_SHIPDATE/35/LINEITEM
|
|
Operator: >= ArithmeticColumn: FunctionColumn: to_date('1995-01-01 00:00:00', 'yyyy-mm-dd hh24:mi:ss')
|
|
|
|
SimpleFilter
|
|
SimpleColumn tpch.LINEITEM.L_SHIPDATE
|
|
s/t/c/T/A: tpch/LINEITEM/L_SHIPDATE/35/LINEITEM
|
|
Operator: <= ArithmeticColumn: FunctionColumn: to_date('1996-12-31 00:00:00', 'yyyy-mm-dd hh24:mi:ss')
|
|
|
|
Operator: AND
|
|
SimpleFilter
|
|
SimpleColumn tpch.SUPPLIER.S_SUPPKEY
|
|
s/t/c/T/A: tpch/SUPPLIER/S_SUPPKEY/55/SUPPLIER
|
|
Operator: = SimpleColumn tpch.LINEITEM.L_SUPPKEY
|
|
s/t/c/T/A: tpch/LINEITEM/L_SUPPKEY/27/LINEITEM
|
|
|
|
Operator: and
|
|
SimpleFilter
|
|
SimpleColumn tpch.ORDERS.O_ORDERKEY
|
|
s/t/c/T/A: tpch/ORDERS/O_ORDERKEY/16/ORDERS
|
|
Operator: = SimpleColumn tpch.LINEITEM.L_ORDERKEY
|
|
s/t/c/T/A: tpch/LINEITEM/L_ORDERKEY/25/LINEITEM
|
|
|
|
Operator: and
|
|
SimpleFilter
|
|
SimpleColumn tpch.NATION.N_NAME
|
|
s/t/c/T/A: tpch/NATION/N_NAME/5/N1
|
|
Operator: = ConstantColumn: FRANCE(l)
|
|
SimpleFilter
|
|
SimpleColumn tpch.NATION.N_NAME
|
|
s/t/c/T/A: tpch/NATION/N_NAME/5/N1
|
|
Operator: = ConstantColumn: GERMANY(l)
|
|
Operator: OR
|
|
Operator: and
|
|
SimpleFilter
|
|
SimpleColumn tpch.SUPPLIER.S_NATIONKEY
|
|
s/t/c/T/A: tpch/SUPPLIER/S_NATIONKEY/58/SUPPLIER
|
|
Operator: = SimpleColumn tpch.NATION.N_NATIONKEY
|
|
s/t/c/T/A: tpch/NATION/N_NATIONKEY/4/N1
|
|
|
|
Operator: and
|
|
SimpleFilter
|
|
SimpleColumn tpch.NATION.N_NAME
|
|
s/t/c/T/A: tpch/NATION/N_NAME/5/N2
|
|
Operator: = ConstantColumn: FRANCE(l)
|
|
SimpleFilter
|
|
SimpleColumn tpch.NATION.N_NAME
|
|
s/t/c/T/A: tpch/NATION/N_NAME/5/N2
|
|
Operator: = ConstantColumn: GERMANY(l)
|
|
Operator: OR
|
|
SimpleFilter
|
|
SimpleColumn tpch.NATION.N_NAME
|
|
s/t/c/T/A: tpch/NATION/N_NAME/5/N1
|
|
Operator: = ConstantColumn: FRANCE(l)
|
|
SimpleFilter
|
|
SimpleColumn tpch.NATION.N_NAME
|
|
s/t/c/T/A: tpch/NATION/N_NAME/5/N2
|
|
Operator: = ConstantColumn: GERMANY(l)
|
|
Operator: AND
|
|
SimpleFilter
|
|
SimpleColumn tpch.NATION.N_NAME
|
|
s/t/c/T/A: tpch/NATION/N_NAME/5/N1
|
|
Operator: = ConstantColumn: GERMANY(l)
|
|
SimpleFilter
|
|
SimpleColumn tpch.NATION.N_NAME
|
|
s/t/c/T/A: tpch/NATION/N_NAME/5/N2
|
|
Operator: = ConstantColumn: FRANCE(l)
|
|
Operator: AND
|
|
Operator: OR
|
|
Operator: AND
|
|
Operator: and
|
|
SimpleFilter
|
|
SimpleColumn tpch.CUSTOMER.C_CUSTKEY
|
|
s/t/c/T/A: tpch/CUSTOMER/C_CUSTKEY/8/CUSTOMER
|
|
Operator: = SimpleColumn tpch.ORDERS.O_CUSTKEY
|
|
s/t/c/T/A: tpch/ORDERS/O_CUSTKEY/17/ORDERS
|
|
|
|
SimpleFilter
|
|
SimpleColumn tpch.CUSTOMER.C_NATIONKEY
|
|
s/t/c/T/A: tpch/CUSTOMER/C_NATIONKEY/11/CUSTOMER
|
|
Operator: = SimpleColumn tpch.NATION.N_NATIONKEY
|
|
s/t/c/T/A: tpch/NATION/N_NATIONKEY/4/N2
|
|
|
|
Operator: AND
|
|
Operator: and
|
|
>>Group By Columns
|
|
SimpleColumn tpch.NATION.N_NAME
|
|
s/t/c/T/A: tpch/NATION/N_NAME/5/N1
|
|
|
|
SimpleColumn tpch.NATION.N_NAME
|
|
s/t/c/T/A: tpch/NATION/N_NAME/5/N2
|
|
|
|
ArithmeticColumn: FunctionColumn: extract(YEAR FROM INTERNAL_FUNCTION(L_SHIPDATE))
|
|
|
|
ArithmeticColumn: AggregateColumn sum(L_EXTENDEDPRICE*(1-L_DISCOUNT))
|
|
ArithmeticColumn: SimpleColumn L_EXTENDEDPRICE
|
|
s/t/c/T/A: ///0/
|
|
|
|
ConstantColumn: 1(n)
|
|
SimpleColumn L_DISCOUNT
|
|
s/t/c/T/A: ///0/
|
|
|
|
Operator: -
|
|
Operator: *
|
|
|
|
|
|
SessionID: 4882
|
|
TxnID: 338
|
|
VerID: 338
|
|
--- Column Map ---
|
|
C_CUSTKEY : 0x92fb960
|
|
C_NATIONKEY : 0x92fb990
|
|
L_DISCOUNT : 0x92fc258
|
|
L_EXTENDEDPRICE : 0x92fc228
|
|
L_ORDERKEY : 0x92f7758
|
|
L_SHIPDATE : 0x92fc288
|
|
L_SUPPKEY : 0x92f7788
|
|
N_NAME : 0x92fc180
|
|
N_NAME : 0x92fb920
|
|
N_NATIONKEY : 0x92fc150
|
|
N_NATIONKEY : 0x9308ed0
|
|
O_CUSTKEY : 0x92fc450
|
|
O_ORDERKEY : 0x92fc2f8
|
|
S_NATIONKEY : 0x92f7728
|
|
S_SUPPKEY : 0x92f0df8
|
|
|