database/mariadb-columnstore-engine
1
0
Fork 0
mirror of https://github.com/mariadb-corporation/mariadb-columnstore-engine.git synced 2025-07-29 08:21:15 +03:00
Code Activity
Files
cmapi-grammar-fixes
mariadb-columnstore-engine/dbcon/doc/q10_plan.txt
david hill f6afc42dd0 the begginning
2016-01-06 14:08:59 -06:00

321 lines
7.4 KiB
Plaintext
Executable File
Raw Permalink Blame History

Q10
select
c_custkey,
c_name,
sum(l_extendedprice * (1 - l_discount)) as revenue,
c_acctbal,
n_name,
c_address,
c_phone,
c_comment
from
customer,
orders,
lineitem,
nation
where
c_custkey = o_custkey
and l_orderkey = o_orderkey
and o_orderdate >= date '1993-10-01'
and o_orderdate < date '1993-10-01' + interval '3' month
and l_returnflag = 'R'
and c_nationkey = n_nationkey
group by
c_custkey,
c_name,
c_acctbal,
c_phone,
n_name,
c_address,
c_comment
order by
revenue desc;
Oracle Execution Plan
Operation: table access
Options: FULL
Object_type: TABLE
Other:
Object_owner: CALUSER01
Search_columns:
Projection: L_ORDERKEY^L_EXTENDEDPRICE^L_DISCOUNT
Object_name: LINEITEM
Alias: LINEITEM@SEL$1
Extended_information: FP:L_RETURNFLAG='R'
Access_predicates:
Filter_predicates: L_RETURNFLAG='R'
Select_level: SEL$1
--------------------
Operation: table access
Options: FULL
Object_type: TABLE
Other:
Object_owner: CALUSER01
Search_columns:
Projection: C_CUSTKEY^C_NAME^C_ADDRESS^C_NATIONKEY^C_PHONE^C_ACCTBAL^C_COMMENT
Object_name: CUSTOMER
Alias: CUSTOMER@SEL$1
Extended_information:
Access_predicates:
Filter_predicates:
Select_level: SEL$1
--------------------
Operation: buffer
Options: SORT
Object_type:
Other:
Object_owner:
Search_columns:
Projection: L_ORDERKEY^L_EXTENDEDPRICE^L_DISCOUNT
Object_name:
Alias:
Extended_information:
Access_predicates:
Filter_predicates:
Select_level:
--------------------
Operation: merge join
Options: CARTESIAN
Object_type:
Other:
Object_owner:
Search_columns:
Projection: C_CUSTKEY^C_NAME^C_ADDRESS^C_NATIONKEY^C_PHONE^C_ACCTBAL^C_COMMENT^L_ORDERKEY^L_EXTENDEDPRICE^L_DISCOUNT
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: O_ORDERKEY^O_CUSTKEY
Object_name: ORDERS
Alias: ORDERS@SEL$1
Extended_information: FP:O_ORDERDATE>=TO_DATE('1993-10-01 00:00:00', 'yyyy-mm-dd hh24:mi:ss') AND O_ORDERDATE<TO_DATE('1994-01-01 00:00:00', 'yyyy-mm-dd hh24:mi:ss')
Access_predicates:
Filter_predicates: O_ORDERDATE>=TO_DATE('1993-10-01 00:00:00', 'yyyy-mm-dd hh24:mi:ss')&O_ORDERDATE<TO_DATE('1994-01-01 00:00:00', 'yyyy-mm-dd hh24:mi:ss')
Select_level: SEL$1
--------------------
Operation: hash join
Options:
Object_type:
Other:
Object_owner:
Search_columns:
Projection: C_CUSTKEY^C_NAME^C_ADDRESS^C_NATIONKEY^C_PHONE^C_ACCTBAL^C_COMMENT^L_DISCOUNT^L_EXTENDEDPRICE
Object_name:
Alias:
Extended_information: AP:C_CUSTKEY=O_CUSTKEY AND L_ORDERKEY=O_ORDERKEY
Access_predicates: C_CUSTKEY=O_CUSTKEY&L_ORDERKEY=O_ORDERKEY
Filter_predicates:
Select_level:
--------------------
Operation: table access
Options: FULL
Object_type: TABLE
Other:
Object_owner: CALUSER01
Search_columns:
Projection: N_NATIONKEY^N_NAME
Object_name: NATION
Alias: NATION@SEL$1
Extended_information:
Access_predicates:
Filter_predicates:
Select_level: SEL$1
--------------------
Operation: hash join
Options:
Object_type:
Other:
Object_owner:
Search_columns:
Projection: C_CUSTKEY^C_NAME^C_ADDRESS^C_PHONE^C_ACCTBAL^C_COMMENT^L_DISCOUNT^L_EXTENDEDPRICE^N_NAME
Object_name:
Alias:
Extended_information: AP:C_NATIONKEY=N_NATIONKEY
Access_predicates: C_NATIONKEY=N_NATIONKEY
Filter_predicates:
Select_level:
--------------------
Operation: hash
Options: GROUP BY
Object_type:
Other:
Object_owner:
Search_columns:
Projection: C_CUSTKEY^C_NAME^C_ACCTBAL^C_PHONE^N_NAME^C_ADDRESS^C_COMMENT^SUM(L_EXTENDEDPRICE*(1-L_DISCOUNT))
Object_name:
Alias:
Extended_information:
Access_predicates:
Filter_predicates:
Select_level:
--------------------
Operation: sort
Options: ORDER BY
Object_type:
Other:
Object_owner:
Search_columns:
Projection: SUM(L_EXTENDEDPRICE*(1-L_DISCOUNT))^C_CUSTKEY^C_NAME^C_COMMENT^C_ACCTBAL^N_NAME^C_ADDRESS^C_PHONE
Object_name:
Alias:
Extended_information:
Access_predicates:
Filter_predicates:
Select_level: SEL$1
--------------------
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 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: *
SimpleColumn tpch.CUSTOMER.C_CUSTKEY
s/t/c/T/A: tpch/CUSTOMER/C_CUSTKEY/8/CUSTOMER
SimpleColumn tpch.CUSTOMER.C_NAME
s/t/c/T/A: tpch/CUSTOMER/C_NAME/9/CUSTOMER
SimpleColumn tpch.CUSTOMER.C_COMMENT
s/t/c/T/A: tpch/CUSTOMER/C_COMMENT/15/CUSTOMER
SimpleColumn tpch.CUSTOMER.C_ACCTBAL
s/t/c/T/A: tpch/CUSTOMER/C_ACCTBAL/13/CUSTOMER
SimpleColumn tpch.NATION.N_NAME
s/t/c/T/A: tpch/NATION/N_NAME/5/NATION
SimpleColumn tpch.CUSTOMER.C_ADDRESS
s/t/c/T/A: tpch/CUSTOMER/C_ADDRESS/10/CUSTOMER
SimpleColumn tpch.CUSTOMER.C_PHONE
s/t/c/T/A: tpch/CUSTOMER/C_PHONE/12/CUSTOMER
>>Filters
SimpleFilter
SimpleColumn tpch.LINEITEM.L_RETURNFLAG
s/t/c/T/A: tpch/LINEITEM/L_RETURNFLAG/33/
Operator: = ConstantColumn: R(l)
SimpleFilter
SimpleColumn tpch.ORDERS.O_ORDERDATE
s/t/c/T/A: tpch/ORDERS/O_ORDERDATE/20/
Operator: >= ArithmeticColumn: FunctionColumn: to_date('1993-10-01 00:00:00', 'yyyy-mm-dd hh24:mi:ss')
SimpleFilter
SimpleColumn tpch.ORDERS.O_ORDERDATE
s/t/c/T/A: tpch/ORDERS/O_ORDERDATE/20/
Operator: < ArithmeticColumn: FunctionColumn: to_date('1994-01-01 00:00:00', 'yyyy-mm-dd hh24:mi:ss')
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.LINEITEM.L_ORDERKEY
s/t/c/T/A: tpch/LINEITEM/L_ORDERKEY/25/LINEITEM
Operator: = SimpleColumn tpch.ORDERS.O_ORDERKEY
s/t/c/T/A: tpch/ORDERS/O_ORDERKEY/16/ORDERS
Operator: AND
Operator: and
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/NATION
Operator: and
>>Group By Columns
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: *
SimpleColumn tpch.CUSTOMER.C_CUSTKEY
s/t/c/T/A: tpch/CUSTOMER/C_CUSTKEY/8/CUSTOMER
SimpleColumn tpch.CUSTOMER.C_NAME
s/t/c/T/A: tpch/CUSTOMER/C_NAME/9/CUSTOMER
SimpleColumn tpch.CUSTOMER.C_COMMENT
s/t/c/T/A: tpch/CUSTOMER/C_COMMENT/15/CUSTOMER
SimpleColumn tpch.CUSTOMER.C_ACCTBAL
s/t/c/T/A: tpch/CUSTOMER/C_ACCTBAL/13/CUSTOMER
SimpleColumn tpch.NATION.N_NAME
s/t/c/T/A: tpch/NATION/N_NAME/5/NATION
SimpleColumn tpch.CUSTOMER.C_ADDRESS
s/t/c/T/A: tpch/CUSTOMER/C_ADDRESS/10/CUSTOMER
SimpleColumn tpch.CUSTOMER.C_PHONE
s/t/c/T/A: tpch/CUSTOMER/C_PHONE/12/CUSTOMER
SessionID: 4854
TxnID: 324
VerID: 324
--- Column Map ---
C_ACCTBAL : 0x9a6cc10
C_ADDRESS : 0x9a6cb80
C_COMMENT : 0x9a6cc40
C_CUSTKEY : 0x9a6cb20
C_NAME : 0x9a6cb50
C_NATIONKEY : 0x9a6cbb0
C_PHONE : 0x9a6cbe0
L_DISCOUNT : 0x9a4c758
L_EXTENDEDPRICE : 0x9a4c728
L_ORDERKEY : 0x9a45df8
L_RETURNFLAG : 0x9a4c7b8
N_NAME : 0x9a6be30
N_NATIONKEY : 0x9a6be00
O_CUSTKEY : 0x9a6ce38
O_ORDERDATE : 0x9a6bca8
O_ORDERKEY : 0x9a6ce08
View Git Blame Copy Permalink