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