从 TPCH 测试学习性能优化技巧之 Q2

一、     查询要求

Q2语句查询获得最小代价的供货商。得到给定的区域内，对于指定的零件（某一类型和大小的零件），哪个供应商能以最低的价格供应它，就可以选择哪个供应商来订货。

Q2语句的特点是：带有排序、聚集操作、子查询并存的多表查询操作。查询语句没有从语法上限制返回多少条元组，TPC-H标准规定，查询结果只返回前100行即可（通常依赖于应用程序实现）。

二、     Oracle执行

Oracle编写的查询SQL语句如下：

select * from (

         select   /*+ parallel(n) */

                   s_acctbal,s_name,n_name,p_partkey,p_mfgr,s_address,s_phone,s_comment

         from part,supplier,partsupp,nation,region

         where

                   p_partkey = ps_partkey

                   and s_suppkey = ps_suppkey

                   and p_size = 25

                   and p_type like '%COPPER'

                   and s_nationkey = n_nationkey

                   and n_regionkey = r_regionkey

                   and r_name = 'ASIA'

                   and ps_supplycost = (

                            select

                                     min(ps_supplycost)

                            from

                                     partsupp,

                                     supplier,

                                     nation,

                                     region

                            where