You've already forked mariadb-columnstore-engine
mirror of
https://github.com/mariadb-corporation/mariadb-columnstore-engine.git
synced 2025-07-29 08:21:15 +03:00
148 lines
3.2 KiB
Plaintext
Executable File
148 lines
3.2 KiB
Plaintext
Executable File
select
|
|
p_brand,
|
|
p_type,
|
|
p_size,
|
|
count(distinct ps_suppkey) as supplier_cnt
|
|
from
|
|
partsupp,
|
|
part
|
|
where
|
|
p_partkey = ps_partkey
|
|
and p_brand <> ':1'
|
|
and p_type not like ':2%'
|
|
and p_size in (3, 4, 5, 6, 7, 8, 9, 10)
|
|
and ps_suppkey not in (
|
|
select
|
|
s_suppkey
|
|
from
|
|
supplier
|
|
where
|
|
s_comment like '%Customer%Complaints%'
|
|
)
|
|
group by
|
|
p_brand,
|
|
p_type,
|
|
p_size
|
|
order by
|
|
supplier_cnt desc,
|
|
p_brand,
|
|
p_type,
|
|
p_size;
|
|
|
|
-- Q16 Start --
|
|
|
|
Operation: table access
|
|
Options: FULL
|
|
Object_type: TABLE
|
|
Other:
|
|
Object_owner: CALUSER01
|
|
Search_columns:
|
|
Projection: PS_PARTKEY, PS_SUPPKEY
|
|
Object_name: PARTSUPP
|
|
Alias: PARTSUPP@SEL$1
|
|
Extended_information:
|
|
Access_predicates:
|
|
Filter_predicates:
|
|
Select_level: SEL$1
|
|
--------------------
|
|
Operation: table access
|
|
Options: FULL
|
|
Object_type: TABLE
|
|
Other:
|
|
Object_owner: CALUSER01
|
|
Search_columns:
|
|
Projection: P_PARTKEY, P_BRAND, P_TYPE, P_SIZE
|
|
Object_name: PART
|
|
Alias: PART@SEL$1
|
|
Extended_information: FP:(P_SIZE=3 OR P_SIZE=4 OR P_SIZE=5 OR P_SIZE=6 OR P_SIZE=7 OR P_SIZE=8 OR P_SIZE=9 OR P_SIZE=10) AND P_BRAND<>':1' AND P_TYPE NOT LIKE ':2%'
|
|
Access_predicates:
|
|
Filter_predicates: (P_SIZE=3|P_SIZE=4|P_SIZE=5|P_SIZE=6|P_SIZE=7|P_SIZE=8|P_SIZE=9|P_SIZE=10)&P_BRAND<>':1'&P_TYPE NOT LIKE ':2%'
|
|
Select_level: SEL$1
|
|
--------------------
|
|
Operation: hash join
|
|
Options:
|
|
Object_type:
|
|
Other:
|
|
Object_owner:
|
|
Search_columns:
|
|
Projection: PS_SUPPKEY, P_SIZE, P_BRAND, P_TYPE
|
|
Object_name:
|
|
Alias:
|
|
Extended_information: AP:P_PARTKEY=PS_PARTKEY
|
|
Access_predicates: P_PARTKEY=PS_PARTKEY
|
|
Filter_predicates:
|
|
Select_level:
|
|
--------------------
|
|
Operation: table access
|
|
Options: FULL
|
|
Object_type: TABLE
|
|
Other:
|
|
Object_owner: CALUSER01
|
|
Search_columns:
|
|
Projection: S_SUPPKEY, S_COMMENT
|
|
Object_name: SUPPLIER
|
|
Alias: SUPPLIER@SEL$2
|
|
Extended_information: FP:S_COMMENT LIKE '%Customer%Complaints%' AND LNNVL(S_SUPPKEY<>:B1)
|
|
Access_predicates:
|
|
Filter_predicates: S_COMMENT LIKE '%Customer%Complaints%'&LNNVL(S_SUPPKEY<>:B1)
|
|
Select_level: SEL$2
|
|
--------------------
|
|
Operation: filter
|
|
Options:
|
|
Object_type:
|
|
Other:
|
|
Object_owner:
|
|
Search_columns:
|
|
Projection: PS_SUPPKEY, P_SIZE, P_BRAND, P_TYPE
|
|
Object_name:
|
|
Alias:
|
|
Extended_information: FP: NOT EXISTS (SELECT /*+ */ 0 FROM SUPPLIER SUPPLIER WHERE S_COMMENT LIKE '%Customer%Complaints%' AND LNNVL(S_SUPPKEY<>:B1))
|
|
Access_predicates:
|
|
Filter_predicates: NOT EXISTS (SELECT /*+ */ 0 FROM SUPPLIER SUPPLIER WHERE S_COMMENT LIKE '%Customer%Complaints%'&LNNVL(S_SUPPKEY<>:B1))
|
|
Select_level:
|
|
--------------------
|
|
Operation: sort
|
|
Options: GROUP BY
|
|
Object_type:
|
|
Other:
|
|
Object_owner:
|
|
Search_columns:
|
|
Projection: P_BRAND, P_TYPE, P_SIZE, COUNT(DISTINCT PS_SUPPKEY)
|
|
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(DISTINCT PS_SUPPKEY), P_BRAND, P_TYPE, P_SIZE
|
|
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:
|
|
|
|
--- END -------
|
|
|