You've already forked mariadb-columnstore-engine
mirror of
https://github.com/mariadb-corporation/mariadb-columnstore-engine.git
synced 2025-10-30 07:25:34 +03:00
177 lines
4.5 KiB
Plaintext
Executable File
177 lines
4.5 KiB
Plaintext
Executable File
Q15
|
|
create view revenue (supplier_no, total_revenue) as
|
|
select
|
|
l_suppkey,
|
|
sum(l_extendedprice * (1 - l_discount))
|
|
from
|
|
lineitem
|
|
where
|
|
l_shipdate >= date '1995-01-01'
|
|
and l_shipdate < date '1995-01-01' + interval '3' month
|
|
group by
|
|
l_suppkey;
|
|
|
|
:o
|
|
select
|
|
s_suppkey,
|
|
s_name,
|
|
s_address,
|
|
s_phone,
|
|
total_revenue
|
|
from
|
|
supplier,
|
|
revenue
|
|
where
|
|
s_suppkey = supplier_no
|
|
and total_revenue = (
|
|
select
|
|
max(total_revenue)
|
|
from
|
|
revenue
|
|
)
|
|
order by
|
|
s_suppkey;
|
|
|
|
Oracle Execution Plan
|
|
|
|
Operation: table access
|
|
Options: FULL
|
|
Object_type: TABLE
|
|
Other:
|
|
Object_owner: CALUSER01
|
|
Search_columns:
|
|
Projection: L_SUPPKEY^L_EXTENDEDPRICE^L_DISCOUNT
|
|
Object_name: LINEITEM
|
|
Alias: LINEITEM@SEL$4
|
|
Extended_information: FP:L_SHIPDATE>=TO_DATE('1995-01-01 00:00:00', 'yyyy-mm-dd hh24:mi:ss') AND L_SHIPDATE<TO_DATE('1995-04-01 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('1995-04-01 00:00:00', 'yyyy-mm-dd hh24:mi:ss')
|
|
Select_level: SEL$4
|
|
--------------------
|
|
Operation: table access
|
|
Options: FULL
|
|
Object_type: TABLE
|
|
Other:
|
|
Object_owner: CALUSER01
|
|
Search_columns:
|
|
Projection: ROWID^S_SUPPKEY^S_NAME^S_ADDRESS^S_PHONE
|
|
Object_name: SUPPLIER
|
|
Alias: SUPPLIER@SEL$1
|
|
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_SUPPKEY^L_EXTENDEDPRICE^L_DISCOUNT
|
|
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('1995-04-01 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('1995-04-01 00:00:00', 'yyyy-mm-dd hh24:mi:ss')
|
|
Select_level: SEL$F5BB74E1
|
|
--------------------
|
|
Operation: sort
|
|
Options: GROUP BY
|
|
Object_type:
|
|
Other:
|
|
Object_owner:
|
|
Search_columns:
|
|
Projection: L_SUPPKEY^SUM(L_EXTENDEDPRICE*(1-L_DISCOUNT))
|
|
Object_name:
|
|
Alias:
|
|
Extended_information:
|
|
Access_predicates:
|
|
Filter_predicates:
|
|
Select_level: SEL$4
|
|
--------------------
|
|
Operation: hash join
|
|
Options:
|
|
Object_type:
|
|
Other:
|
|
Object_owner:
|
|
Search_columns:
|
|
Projection: S_SUPPKEY^L_SUPPKEY^ROWID^S_PHONE^S_NAME^S_ADDRESS^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: view
|
|
Options:
|
|
Object_type: VIEW
|
|
Other:
|
|
Object_owner: CALUSER01
|
|
Search_columns:
|
|
Projection: TOTAL_REVENUE
|
|
Object_name:
|
|
Alias: REVENUE@SEL$3
|
|
Extended_information:
|
|
Access_predicates:
|
|
Filter_predicates:
|
|
Select_level: SEL$4
|
|
--------------------
|
|
Operation: sort
|
|
Options: GROUP BY
|
|
Object_type:
|
|
Other:
|
|
Object_owner:
|
|
Search_columns:
|
|
Projection: S_SUPPKEY^ROWID^S_PHONE^S_ADDRESS^S_NAME^L_SUPPKEY^SUM(L_EXTENDEDPRICE*(1-L_DISCOUNT))
|
|
Object_name:
|
|
Alias:
|
|
Extended_information:
|
|
Access_predicates:
|
|
Filter_predicates:
|
|
Select_level:
|
|
--------------------
|
|
Operation: sort
|
|
Options: AGGREGATE
|
|
Object_type:
|
|
Other:
|
|
Object_owner:
|
|
Search_columns:
|
|
Projection: MAX(TOTAL_REVENUE)
|
|
Object_name:
|
|
Alias:
|
|
Extended_information:
|
|
Access_predicates:
|
|
Filter_predicates:
|
|
Select_level: SEL$3
|
|
--------------------
|
|
Operation: filter
|
|
Options:
|
|
Object_type:
|
|
Other:
|
|
Object_owner:
|
|
Search_columns:
|
|
Projection: S_SUPPKEY^S_PHONE^S_ADDRESS^S_NAME^SUM(L_EXTENDEDPRICE*(1-L_DISCOUNT))
|
|
Object_name:
|
|
Alias:
|
|
Extended_information: FP:SUM(L_EXTENDEDPRICE*(1-L_DISCOUNT))= (SELECT MAX(TOTAL_REVENUE) FROM (SELECT L_SUPPKEY SUPPLIER_NO,SUM(L_EXTENDEDPRICE*(1-L_DISCOUNT)) TOTAL_REVENUE FROM S_CALUSER01.LINEITEM LINEITEM WHERE L_SHIPDATE>=TO_DATE('1995-01-01 00:00:00', 'yyyy-mm-dd hh24:mi:ss') AND L_SHIPDATE<TO_DATE('1995-04-01 00:00:00', 'yyyy-mm-dd hh24:mi:ss') GROUP BY L_SUPPKEY) REVENUE)
|
|
Access_predicates:
|
|
Filter_predicates: SUM(L_EXTENDEDPRICE*(1-L_DISCOUNT))= (SELECT MAX(TOTAL_REVENUE) FROM (SELECT L_SUPPKEY SUPPLIER_NO,SUM(L_EXTENDEDPRICE*(1-L_DISCOUNT)) TOTAL_REVENUE FROM S_CALUSER01.LINEITEM LINEITEM WHERE L_SHIPDATE>=TO_DATE('1995-01-01 00:00:00', 'yyyy-mm-dd hh24:mi:ss')&L_SHIPDATE<TO_DATE('1995-04-01 00:00:00', 'yyyy-mm-dd hh24:mi:ss') GROUP BY L_SUPPKEY) REVENUE)
|
|
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:
|
|
--------------------
|