You've already forked mariadb-columnstore-engine
mirror of
https://github.com/mariadb-corporation/mariadb-columnstore-engine.git
synced 2025-07-30 19:23:07 +03:00
356 lines
7.8 KiB
Plaintext
Executable File
356 lines
7.8 KiB
Plaintext
Executable File
Q21
|
|
select
|
|
s_name,
|
|
count(*) as numwait
|
|
from
|
|
supplier,
|
|
lineitem l1,
|
|
orders,
|
|
nation
|
|
where
|
|
s_suppkey = l1.l_suppkey
|
|
and o_orderkey = l1.l_orderkey
|
|
and o_orderstatus = 'F'
|
|
and l1.l_receiptdate > l1.l_commitdate
|
|
and exists (
|
|
select
|
|
*
|
|
from
|
|
lineitem l2
|
|
where
|
|
l2.l_orderkey = l1.l_orderkey
|
|
and l2.l_suppkey <> l1.l_suppkey
|
|
)
|
|
and not exists (
|
|
select
|
|
*
|
|
from
|
|
lineitem l3
|
|
where
|
|
l3.l_orderkey = l1.l_orderkey
|
|
and l3.l_suppkey <> l1.l_suppkey
|
|
and l3.l_receiptdate > l3.l_commitdate
|
|
)
|
|
and s_nationkey = n_nationkey
|
|
and n_name = 'SAUDI ARABIA'
|
|
group by
|
|
s_name
|
|
order by
|
|
numwait desc,
|
|
s_name;
|
|
|
|
Oracle Execution Plan
|
|
|
|
Operation: table access
|
|
Options: FULL
|
|
Object_type: TABLE
|
|
Other:
|
|
Object_owner: CALUSER01
|
|
Search_columns:
|
|
Projection: S_SUPPKEY^S_NAME^S_NATIONKEY
|
|
Object_name: SUPPLIER
|
|
Alias: SUPPLIER@SEL$1
|
|
Extended_information:
|
|
Access_predicates:
|
|
Filter_predicates:
|
|
Select_level: SEL$CC7EC59E
|
|
--------------------
|
|
Operation: table access
|
|
Options: FULL
|
|
Object_type: TABLE
|
|
Other:
|
|
Object_owner: CALUSER01
|
|
Search_columns:
|
|
Projection: L1.L_ORDERKEY^L1.L_SUPPKEY
|
|
Object_name: LINEITEM
|
|
Alias: L1@SEL$1
|
|
Extended_information: FP:L1.L_RECEIPTDATE>L1.L_COMMITDATE
|
|
Access_predicates:
|
|
Filter_predicates: L1.L_RECEIPTDATE>L1.L_COMMITDATE
|
|
Select_level: SEL$CC7EC59E
|
|
--------------------
|
|
Operation: hash join
|
|
Options:
|
|
Object_type:
|
|
Other:
|
|
Object_owner:
|
|
Search_columns:
|
|
Projection: L1.L_SUPPKEY^S_NATIONKEY^S_NAME^L1.L_ORDERKEY
|
|
Object_name:
|
|
Alias:
|
|
Extended_information: AP:S_SUPPKEY=L1.L_SUPPKEY
|
|
Access_predicates: S_SUPPKEY=L1.L_SUPPKEY
|
|
Filter_predicates:
|
|
Select_level:
|
|
--------------------
|
|
Operation: table access
|
|
Options: FULL
|
|
Object_type: TABLE
|
|
Other:
|
|
Object_owner: CALUSER01
|
|
Search_columns:
|
|
Projection: O_ORDERKEY
|
|
Object_name: ORDERS
|
|
Alias: ORDERS@SEL$1
|
|
Extended_information: FP:O_ORDERSTATUS='F'
|
|
Access_predicates:
|
|
Filter_predicates: O_ORDERSTATUS='F'
|
|
Select_level: SEL$CC7EC59E
|
|
--------------------
|
|
Operation: hash join
|
|
Options:
|
|
Object_type:
|
|
Other:
|
|
Object_owner:
|
|
Search_columns:
|
|
Projection: L1.L_ORDERKEY^L1.L_SUPPKEY^S_NATIONKEY^S_NAME
|
|
Object_name:
|
|
Alias:
|
|
Extended_information: AP:O_ORDERKEY=L1.L_ORDERKEY
|
|
Access_predicates: O_ORDERKEY=L1.L_ORDERKEY
|
|
Filter_predicates:
|
|
Select_level:
|
|
--------------------
|
|
Operation: table access
|
|
Options: FULL
|
|
Object_type: TABLE
|
|
Other:
|
|
Object_owner: CALUSER01
|
|
Search_columns:
|
|
Projection: N_NATIONKEY
|
|
Object_name: NATION
|
|
Alias: NATION@SEL$1
|
|
Extended_information: FP:N_NAME='SAUDI ARABIA'
|
|
Access_predicates:
|
|
Filter_predicates: N_NAME='SAUDI ARABIA'
|
|
Select_level: SEL$CC7EC59E
|
|
--------------------
|
|
Operation: hash join
|
|
Options:
|
|
Object_type:
|
|
Other:
|
|
Object_owner:
|
|
Search_columns:
|
|
Projection: L1.L_ORDERKEY^L1.L_SUPPKEY^S_NAME
|
|
Object_name:
|
|
Alias:
|
|
Extended_information: AP:S_NATIONKEY=N_NATIONKEY
|
|
Access_predicates: S_NATIONKEY=N_NATIONKEY
|
|
Filter_predicates:
|
|
Select_level:
|
|
--------------------
|
|
Operation: table access
|
|
Options: FULL
|
|
Object_type: TABLE
|
|
Other:
|
|
Object_owner: CALUSER01
|
|
Search_columns:
|
|
Projection: L3.L_ORDERKEY^L3.L_SUPPKEY
|
|
Object_name: LINEITEM
|
|
Alias: L3@SEL$3
|
|
Extended_information: FP:L3.L_RECEIPTDATE>L3.L_COMMITDATE
|
|
Access_predicates:
|
|
Filter_predicates: L3.L_RECEIPTDATE>L3.L_COMMITDATE
|
|
Select_level: SEL$CC7EC59E
|
|
--------------------
|
|
Operation: hash join
|
|
Options: ANTI
|
|
Object_type:
|
|
Other:
|
|
Object_owner:
|
|
Search_columns:
|
|
Projection: L1.L_ORDERKEY^L1.L_SUPPKEY^S_NAME
|
|
Object_name:
|
|
Alias:
|
|
Extended_information: AP:L3.L_ORDERKEY=L1.L_ORDERKEY FP:L3.L_SUPPKEY<>L1.L_SUPPKEY
|
|
Access_predicates: L3.L_ORDERKEY=L1.L_ORDERKEY
|
|
Filter_predicates: L3.L_SUPPKEY<>L1.L_SUPPKEY
|
|
Select_level:
|
|
--------------------
|
|
Operation: table access
|
|
Options: FULL
|
|
Object_type: TABLE
|
|
Other:
|
|
Object_owner: CALUSER01
|
|
Search_columns:
|
|
Projection: L2.L_ORDERKEY^L2.L_SUPPKEY
|
|
Object_name: LINEITEM
|
|
Alias: L2@SEL$2
|
|
Extended_information:
|
|
Access_predicates:
|
|
Filter_predicates:
|
|
Select_level: SEL$CC7EC59E
|
|
--------------------
|
|
Operation: hash join
|
|
Options: SEMI
|
|
Object_type:
|
|
Other:
|
|
Object_owner:
|
|
Search_columns:
|
|
Projection: S_NAME
|
|
Object_name:
|
|
Alias:
|
|
Extended_information: AP:L2.L_ORDERKEY=L1.L_ORDERKEY FP:L2.L_SUPPKEY<>L1.L_SUPPKEY
|
|
Access_predicates: L2.L_ORDERKEY=L1.L_ORDERKEY
|
|
Filter_predicates: L2.L_SUPPKEY<>L1.L_SUPPKEY
|
|
Select_level:
|
|
--------------------
|
|
Operation: hash
|
|
Options: GROUP BY
|
|
Object_type:
|
|
Other:
|
|
Object_owner:
|
|
Search_columns:
|
|
Projection: S_NAME^COUNT(ALL)
|
|
Object_name:
|
|
Alias:
|
|
Extended_information:
|
|
Access_predicates:
|
|
Filter_predicates:
|
|
Select_level:
|
|
--------------------
|
|
Operation: sort
|
|
Options: ORDER BY
|
|
Object_type:
|
|
Other:
|
|
Object_owner:
|
|
Search_columns:
|
|
Projection: COUNT(ALL)^S_NAME
|
|
Object_name:
|
|
Alias:
|
|
Extended_information:
|
|
Access_predicates:
|
|
Filter_predicates:
|
|
Select_level: SEL$CC7EC59E
|
|
--------------------
|
|
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
|
|
ArithmeticColumn: AggregateColumn count(ALL)
|
|
ArithmeticColumn: SimpleColumn ALL
|
|
s/t/c/T/A: ///0/
|
|
|
|
|
|
SimpleColumn tpch.SUPPLIER.S_NAME
|
|
s/t/c/T/A: tpch/SUPPLIER/S_NAME/56/SUPPLIER
|
|
|
|
>>Filters
|
|
SimpleFilter
|
|
SimpleColumn tpch.LINEITEM.L_RECEIPTDATE
|
|
s/t/c/T/A: tpch/LINEITEM/L_RECEIPTDATE/37/L1
|
|
Operator: > SimpleColumn tpch.LINEITEM.L_COMMITDATE
|
|
s/t/c/T/A: tpch/LINEITEM/L_COMMITDATE/36/L1
|
|
|
|
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/L1
|
|
|
|
Operator: and
|
|
SimpleFilter
|
|
SimpleColumn tpch.ORDERS.O_ORDERSTATUS
|
|
s/t/c/T/A: tpch/ORDERS/O_ORDERSTATUS/18/
|
|
Operator: = ConstantColumn: F(l)
|
|
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/L1
|
|
|
|
Operator: and
|
|
SimpleFilter
|
|
SimpleColumn tpch.NATION.N_NAME
|
|
s/t/c/T/A: tpch/NATION/N_NAME/5/
|
|
Operator: = ConstantColumn: SAUDI ARABIA(l)
|
|
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/NATION
|
|
|
|
Operator: and
|
|
SimpleFilter
|
|
SimpleColumn tpch.LINEITEM.L_SUPPKEY
|
|
s/t/c/T/A: tpch/LINEITEM/L_SUPPKEY/27/L3
|
|
Operator: > SimpleColumn tpch.LINEITEM.L_ORDERKEY
|
|
s/t/c/T/A: tpch/LINEITEM/L_ORDERKEY/25/L3
|
|
|
|
Operator: and
|
|
SimpleFilter
|
|
SimpleColumn tpch.LINEITEM.L_ORDERKEY
|
|
s/t/c/T/A: tpch/LINEITEM/L_ORDERKEY/25/L3
|
|
Operator: = SimpleColumn tpch.LINEITEM.L_ORDERKEY
|
|
s/t/c/T/A: tpch/LINEITEM/L_ORDERKEY/25/L1
|
|
|
|
Operator: and
|
|
SimpleFilter
|
|
SimpleColumn tpch.LINEITEM.L_SUPPKEY
|
|
s/t/c/T/A: tpch/LINEITEM/L_SUPPKEY/27/L3
|
|
Operator: <> SimpleColumn tpch.LINEITEM.L_SUPPKEY
|
|
s/t/c/T/A: tpch/LINEITEM/L_SUPPKEY/27/L1
|
|
|
|
Operator: and
|
|
SimpleFilter
|
|
SimpleColumn tpch.LINEITEM.L_ORDERKEY
|
|
s/t/c/T/A: tpch/LINEITEM/L_ORDERKEY/25/L2
|
|
Operator: = SimpleColumn tpch.LINEITEM.L_ORDERKEY
|
|
s/t/c/T/A: tpch/LINEITEM/L_ORDERKEY/25/L1
|
|
|
|
Operator: and
|
|
SimpleFilter
|
|
SimpleColumn tpch.LINEITEM.L_SUPPKEY
|
|
s/t/c/T/A: tpch/LINEITEM/L_SUPPKEY/27/L2
|
|
Operator: <> SimpleColumn tpch.LINEITEM.L_SUPPKEY
|
|
s/t/c/T/A: tpch/LINEITEM/L_SUPPKEY/27/L1
|
|
|
|
Operator: and
|
|
>>Group By Columns
|
|
ArithmeticColumn: AggregateColumn count(ALL)
|
|
ArithmeticColumn: SimpleColumn ALL
|
|
s/t/c/T/A: ///0/
|
|
|
|
|
|
|
|
SimpleColumn tpch.SUPPLIER.S_NAME
|
|
s/t/c/T/A: tpch/SUPPLIER/S_NAME/56/SUPPLIER
|
|
|
|
SessionID: 4878
|
|
TxnID: 336
|
|
VerID: 336
|
|
--- Column Map ---
|
|
L_COMMITDATE : 0x94e0bc0
|
|
L_ORDERKEY : 0x94c0788
|
|
L_ORDERKEY : 0x94e00f0
|
|
L_ORDERKEY : 0x94e0480
|
|
L_RECEIPTDATE : 0x94e0b50
|
|
L_SUPPKEY : 0x94e0af0
|
|
L_SUPPKEY : 0x94e0120
|
|
L_SUPPKEY : 0x94e04b0
|
|
N_NAME : 0x94dfd50
|
|
N_NATIONKEY : 0x94dfcd0
|
|
O_ORDERKEY : 0x94e0ca0
|
|
O_ORDERSTATUS : 0x94e0dd8
|
|
S_NAME : 0x94c0728
|
|
S_NATIONKEY : 0x94c0758
|
|
S_SUPPKEY : 0x94b9df8
|
|
|