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