[翻译]Oracle中的增强子查询优化

maxleen

谢谢

xiaoe_7

mark 有机会再好好研究；
thanks

〇〇

newkid帮我优化的tpch q20在哪里呢

〇〇

找到了

/*修改前的第20个查询语句*/
select s_name,  s_address
from supplier, nation
where s_suppkey in (select ps_suppkey
from partsupp
where ps_partkey in (select p_partkey
from part
where p_name like 'forest%')
and ps_availqty > (select 0.5 * sum(l_quantity)
from lineitem
where l_partkey = ps_partkey
and l_suppkey = ps_suppkey
and l_shipdate >= date '1994-01-01'
and l_shipdate < date '1994-01-01' + interval '1' year))
and s_nationkey = n_nationkey
and n_name = 'CANADA'
order by s_name
limit 100;

/*修改后的第20个查询语句*/
select s_name,  s_address
from supplier, nation
where s_suppkey in (select ps_suppkey
                      from partsupp
                         ,(select l_partkey,l_suppkey, sum(l_quantity) l_quantity_SUM
                            from lineitem,part
                            where l_partkey = p_partkey and p_name like 'forest%'
                                   and l_shipdate >= date '1994-01-01'
                                   and l_shipdate < date '1994-01-01' + interval '1' year
                           GROUP BY l_partkey,l_suppkey
                           )
                     where l_partkey = ps_partkey
                           and l_suppkey = ps_suppkey
                           and ps_availqty > 0.5*l_quantity_SUM
                    )
      and s_nationkey = n_nationkey
      and n_name = 'CANADA'
order by s_name
limit 100;

〇〇

db2的优化器还是很认sql的
前面那个
---------------------------------------------
* Comment: " Q20"

---------------------------------------------

* SQL Statement Number 20:

select
        s_name,
        s_address
from
        tpch.supplier,
        tpch.nation
where
        s_suppkey in (
                select
                        ps_suppkey
                from
                        tpch.partsupp
                where
                        ps_partkey in (
                                select
                                        p_partkey
                                from
                                        tpch.part
                                where
                                        p_name like 'bisque%'
                        )
                        and ps_availqty > (
                                select
                                        0.5 * sum(l_quantity)
                                from
                                        tpch.lineitem
                                where
                                        l_partkey = ps_partkey
                                        and l_suppkey = ps_suppkey
                                        and l_shipdate >= date '1994-01-01'
                                        and l_shipdate < date '1994-01-01' + 1 year
                        )
        )
        and s_nationkey = n_nationkey
        and n_name = 'CANADA'
order by
        s_name
fetch first 10 rows only;

S_NAME                    S_ADDRESS                              
------------------------- ----------------------------------------
Supplier#000000020        iybAE,RmTymrZVYaFZva2SH,j               
Supplier#000000091        YV45D7TkfdQanOOZ7q9QxkyGUapU1oOWU6q3   
Supplier#000000205        rF uV8d0JNEk                           
Supplier#000000285        Br7e1nnt1yxrw6ImgpJ7YdhFDjuBf           
Supplier#000000287        7a9SP7qW5Yku5PvSg                       
Supplier#000000354        w8fOo5W,aS                              
Supplier#000000361        f8IUYRmdVXhQC9qJQjWknCXmzhe38vCbk6      
Supplier#000000475        xw4V6,4QQW LI5Qg EOKy4JD B4Cq1tjzaOma9Y
Supplier#000000530        0qwCMwobKY OcmLyfRXlagA8ukENJv,         
Supplier#000000532        ep92hT7VLaVlDKM7lgbj02kIL               

* 10 row(s) fetched, 10 row(s) output.

* Elapsed Time is:     587.439039 seconds

改了后

-sh-3.2$ date; time db2 -tvf /user1/ibm/q20newkid.sql ;date
2011年 05月 12日 星期四 19:19:05 CST
select s_name,  s_address from supplier, nation where s_suppkey in (select ps_suppkey from partsupp ,(select l_partkey,l_suppkey, sum(l_quantity) l_quantity_SUM from lineitem,part where l_partkey = p_partkey and p_name like 'bisque%' and l_shipdate >= date '1994-01-01' and l_shipdate < date '1994-01-01' + 1 year GROUP BY l_partkey,l_suppkey ) where l_partkey = ps_partkey and l_suppkey = ps_suppkey and ps_availqty > 0.5*l_quantity_SUM ) and s_nationkey = n_nationkey and n_name = 'CANADA' order by s_name fetch first 10 rows only

S_NAME                    S_ADDRESS                              
------------------------- ----------------------------------------
Supplier#000000020        iybAE,RmTymrZVYaFZva2SH,j               
Supplier#000000091        YV45D7TkfdQanOOZ7q9QxkyGUapU1oOWU6q3   
Supplier#000000205        rF uV8d0JNEk                           
Supplier#000000285        Br7e1nnt1yxrw6ImgpJ7YdhFDjuBf           
Supplier#000000287        7a9SP7qW5Yku5PvSg                       
Supplier#000000354        w8fOo5W,aS                              
Supplier#000000361        f8IUYRmdVXhQC9qJQjWknCXmzhe38vCbk6      
Supplier#000000475        xw4V6,4QQW LI5Qg EOKy4JD B4Cq1tjzaOma9Y
Supplier#000000530        0qwCMwobKY OcmLyfRXlagA8ukENJv,         
Supplier#000000532        ep92hT7VLaVlDKM7lgbj02kIL               

  10 record(s) selected.



real    0m25.700s
user    0m0.013s
sys     0m0.011s
2011年 05月 12日 星期四 19:19:31 CST

newkid

TPC测试不允许修改源代码对吧？所以只好在优化器下功夫了。

xieg_sky

顶一下

yeeXun

在国际三大数据库会议(之一，VLDB)期刊上面发表的，灰常珍贵！

yywangsx

好东西，收藏了仔细研究……

mdkii

很好的文章，翻译得也好，感谢lz的努力。
有一个很小的问题：
6.2节中：
”在新建的访问结构中(x, 2, 3), (x, 2, null), (x, null, 3),和(x, null, null)。“这句话不通，应该加上搜索两个字，
改成这样”在新建的访问结构中搜索 (x, 2, 3), (x, 2, null), (x, null, 3),和(x, null, null)“。

另外有两个地方没看懂：
1、2.3节中， Q5 为什么能展开成Q6，感觉Q6是一个很奇怪的查询。
2、6.2节中，第3点为什么要建立第二个hash 表？