一次SQL Tuning引出来的not in , not exists 语句的N种写法 - Oracle数据库管理 - ITPUB论坛

rollingpig
版主

精华贴数 5
个人空间 100
技术积分 26725 (30)
社区积分 1659 (661)
注册日期 2001-10-18
论坛徽章:54

#1

使用道具

发表于 2005-8-10 12:58

一次SQL Tuning引出来的not in , not exists 语句的N种写法

今天，发现production上有一个SQL 耗了特多CPU time,于是抓了下来，看看是否可以tuning .

原始 SQL 如下(table名字改了一下)：

PHP code:



DELETE FROM test_table_1 a


      WHERE NOT EXISTS (SELECT *


                          FROM test_table_2 b


                         WHERE a.parent_id = b.id)





执行时间 60S


Buffer get: 160W


执行计划





Operation    Object Name    Rows    Bytes    Cost    Object Node    In/Out    PStart    PStop





DELETE STATEMENT Optimizer Mode=CHOOSE        1           2069                                       


  DELETE    TEST_TABLE_1                                                     


    HASH JOIN ANTI        1      26      2069                                       


      TABLE ACCESS FULL    TEST_TABLE_1    5 M    106 M    1210                                       


      TABLE ACCESS FULL    TEST_TABLE_2    46 K    181 K    48                                       


                                     





其中


test_table_1 row count 为 4百万


test_table_2 为 为 4万








TEST_TABLE_1上的 parent_id上有index.


  


...

__________________

提问的智慧Oracle版
0。尝试在google,论坛，metalink,online document里搜索。
1。写清楚你的执行log,报错信息，写清楚DB version , OS
2。Instance 方面的问题，请贴出alertlog
3。network的问题，贴出server的listener.ora , sqlnet.ora 并运行lsnrctl service, 贴出client的tnsnames.ora , sqlnet.ora ,并运行tnsping
4。DB总体性能问题，请于peak time做statspack,并上传statspack report。同时附上CPU/MEM配置，以及CPU IDLE/free menory的情况
5。SQL性能问题，列出当前SQL,以及PLAN。同时应提供表结构，index情况，Table的record数。CBO/RBO,table是否analyze等信息。
6。确保你的问题是Oracle相关。
提问的智慧 for Oracle[展开版]

只看该作者

rollingpig
版主

精华贴数 5
个人空间 100
技术积分 26725 (30)
社区积分 1659 (661)
注册日期 2001-10-18
论坛徽章:54

#2

使用道具

发表于 2005-8-10 13:23

第一种变换(not in)

现在来试一下变换

用not in 代替 not exists

PHP code:



DELETE FROM test_table_1 a


      WHERE parent_id  NOT IN (SELECT id


                          FROM test_table_2 b)





..

在9i 里面，Oracle把这个语句和前面的语句看为同一个语句，当然执行计划和执行时间也没有差别。

__________________

提问的智慧Oracle版
0。尝试在google,论坛，metalink,online document里搜索。
1。写清楚你的执行log,报错信息，写清楚DB version , OS
2。Instance 方面的问题，请贴出alertlog
3。network的问题，贴出server的listener.ora , sqlnet.ora 并运行lsnrctl service, 贴出client的tnsnames.ora , sqlnet.ora ,并运行tnsping
4。DB总体性能问题，请于peak time做statspack,并上传statspack report。同时附上CPU/MEM配置，以及CPU IDLE/free menory的情况
5。SQL性能问题，列出当前SQL,以及PLAN。同时应提供表结构，index情况，Table的record数。CBO/RBO,table是否analyze等信息。
6。确保你的问题是Oracle相关。
提问的智慧 for Oracle[展开版]

只看该作者

rollingpig
版主

精华贴数 5
个人空间 100
技术积分 26725 (30)
社区积分 1659 (661)
注册日期 2001-10-18
论坛徽章:54

#3

使用道具

发表于 2005-8-10 13:40

第二种变换（minus）

第二种变换（minus）

用minus,

把 not exists 部分先用minus去掉，再用in 来选择data。

PHP code:



DELETE FROM test_table_1 a


      WHERE parent_id IN (SELECT parent_id


                            FROM test_table_1


                          MINUS


                          SELECT ID


                            FROM test_table_2 b)





执行时间 ： 8S


Buffer get :20000





执行计划





Operation    Object Name    Rows    Bytes    Cost    Object Node    In/Out    PStart    PStop





DELETE STATEMENT Optimizer Mode=CHOOSE        2 G         23052                                       


  DELETE    TEST_TABLE_1                                                     


    HASH JOIN        2 G    89G    23052                                       


      VIEW    SYS.VW_NSO_1    5 M    63 M    5488                                       


        MINUS                                                         


          SORT UNIQUE        5 M    19 M                                          


            INDEX FAST FULL SCAN    TEST_INDX_1    5 M    19 M    457                                       


          SORT UNIQUE        46 K    181 K                                          


            TABLE ACCESS FULL    TEST_TABLE_2    46 K    181 K    48                                       


      TABLE ACCESS FULL    TEST_TABLE_1    5 M    106 M    1210                                       





速度提高了不少。


...

__________________

提问的智慧Oracle版
0。尝试在google,论坛，metalink,online document里搜索。
1。写清楚你的执行log,报错信息，写清楚DB version , OS
2。Instance 方面的问题，请贴出alertlog
3。network的问题，贴出server的listener.ora , sqlnet.ora 并运行lsnrctl service, 贴出client的tnsnames.ora , sqlnet.ora ,并运行tnsping
4。DB总体性能问题，请于peak time做statspack,并上传statspack report。同时附上CPU/MEM配置，以及CPU IDLE/free menory的情况
5。SQL性能问题，列出当前SQL,以及PLAN。同时应提供表结构，index情况，Table的record数。CBO/RBO,table是否analyze等信息。
6。确保你的问题是Oracle相关。
提问的智慧 for Oracle[展开版]

只看该作者

rollingpig
版主

精华贴数 5
个人空间 100
技术积分 26725 (30)
社区积分 1659 (661)
注册日期 2001-10-18
论坛徽章:54

#4

使用道具

发表于 2005-8-10 13:46

(minus + distinct )

在 minus之前加个 distinct看看

PHP code:



DELETE FROM test_table_1 a


      WHERE parent_id IN (SELECT distinct parent_id


                            FROM test_table_1


                          MINUS


                          SELECT ID


                            FROM test_table_2 b)





执行时间 ： 8S


Buffer get :20000





执行计划





Operation    Object Name    Rows    Bytes    Cost    Object Node    In/Out    PStart    PStop





DELETE STATEMENT Optimizer Mode=CHOOSE        2 G         23052                                       


  DELETE    TEST_TABLE_1                                                     


    HASH JOIN        2 G    89G    23052                                       


      VIEW    SYS.VW_NSO_1    5 M    63 M    5488                                       


        MINUS                                                         


          SORT UNIQUE        5 M    19 M                                          


            INDEX FAST FULL SCAN    TEST_INDX_1    5 M    19 M    457                                       


          SORT UNIQUE        46 K    181 K                                          


            TABLE ACCESS FULL    TEST_TABLE_2    46 K    181 K    48                                       


      TABLE ACCESS FULL    TEST_TABLE_1    5 M    106 M    1210           





和上一个一模一样。


由于用了in 的操作符，Oracle内部就给加了个  (SORT UNIQUE        )

__________________

提问的智慧Oracle版
0。尝试在google,论坛，metalink,online document里搜索。
1。写清楚你的执行log,报错信息，写清楚DB version , OS
2。Instance 方面的问题，请贴出alertlog
3。network的问题，贴出server的listener.ora , sqlnet.ora 并运行lsnrctl service, 贴出client的tnsnames.ora , sqlnet.ora ,并运行tnsping
4。DB总体性能问题，请于peak time做statspack,并上传statspack report。同时附上CPU/MEM配置，以及CPU IDLE/free menory的情况
5。SQL性能问题，列出当前SQL,以及PLAN。同时应提供表结构，index情况，Table的record数。CBO/RBO,table是否analyze等信息。
6。确保你的问题是Oracle相关。
提问的智慧 for Oracle[展开版]

只看该作者

Matilda
中级会员

精华贴数 1
个人空间 0
技术积分 1119 (1576)
社区积分 19 (7561)
注册日期 2002-11-3
论坛徽章:0

#5

使用道具

发表于 2005-8-10 13:47

沙发

只看该作者

rollingpig
版主

精华贴数 5
个人空间 100
技术积分 26725 (30)
社区积分 1659 (661)
注册日期 2001-10-18
论坛徽章:54

#6

使用道具

发表于 2005-8-10 14:08

可以用 in ,当然就可以用 exists 或者join 了(delete不好用join，只好改为SELECT)

PHP code:



Join





SELECT * FROM (SELECT DISTINCT parent_id 


                                   FROM TEST_TABLE_1


                                 MINUS 


                                 SELECT id 


                                   FROM TEST_TABLE_2 b ) tmp,TEST_TABLE_1 a 


      WHERE  tmp.parent_id   = a.id


执行时间 8S


Buffer get: 20000





Operation    OBJECT Name    ROWS    Bytes    Cost    OBJECT Node    IN/OUT    PStart    PStop





SELECT STATEMENT Optimizer MODE=CHOOSE        5 M         4493                                       


  HASH JOIN        5 M    237 M    4493                                       


    VIEW        9 K    125 K    3161                                       


      MINUS                                                         


        SORT UNIQUE        9 K    38 K                                          


          INDEX FAST FULL SCAN    TEST_INDX_1    5 M    19 M    457                                       


        SORT UNIQUE        46 K    181 K                                          


          TABLE ACCESS FULL    TEST_TABLE_2    46 K    181 K    48                                       


    TABLE ACCESS FULL    TEST_TABLE_1    5 M    164 M    1210                





用exists的





DELETE FROM TEST_TABLE_1 a  WHERE EXISTS (SELECT 1 FROM (SELECT DISTINCT parent_id 


                                   FROM TEST_TABLE_1


                                 MINUS 


                                 SELECT id 


                                   FROM TEST_TABLE_2 b )     


   WHERE parent_id  = a.parent_id


执行效率惨不忍睹





...

__________________

提问的智慧Oracle版
0。尝试在google,论坛，metalink,online document里搜索。
1。写清楚你的执行log,报错信息，写清楚DB version , OS
2。Instance 方面的问题，请贴出alertlog
3。network的问题，贴出server的listener.ora , sqlnet.ora 并运行lsnrctl service, 贴出client的tnsnames.ora , sqlnet.ora ,并运行tnsping
4。DB总体性能问题，请于peak time做statspack,并上传statspack report。同时附上CPU/MEM配置，以及CPU IDLE/free menory的情况
5。SQL性能问题，列出当前SQL,以及PLAN。同时应提供表结构，index情况，Table的record数。CBO/RBO,table是否analyze等信息。
6。确保你的问题是Oracle相关。
提问的智慧 for Oracle[展开版]

只看该作者

rollingpig
版主

精华贴数 5
个人空间 100
技术积分 26725 (30)
社区积分 1659 (661)
注册日期 2001-10-18
论坛徽章:54

#7

使用道具

发表于 2005-8-10 14:24

除了上面几种，还有一种不是很常用的方法
就是(outer join + is null )（也是为了方便，先用SELECT来演示,）

PHP code:



select * from test_table_1 a,test_table_2 b


where a.parent_id = b.id(+)


and b.id is null





在本例中执行效率也挺差的。（超过1min）


Operation    OBJECT Name    ROWS    Bytes    Cost    OBJECT Node    IN/OUT    PStart    PStop





SELECT STATEMENT Optimizer MODE=CHOOSE        24 M         3311                                       


  FILTER                                                         


    HASH JOIN OUTER                                                         


      TABLE ACCESS FULL    TEST_TABLE_1    5 M    164 M    1210                                       


      TABLE ACCESS FULL    TEST_TABLE_2    46 K    5 M    48       

<

PHP code:



加个 distinct 看看





SELECT a.*   FROM  TEST_TABLE_2 b ,(SELECT DISTINCT parentid FROM TEST_TABLE_1) a


                                  WHERE a.parentid = b.id(+)


                                        AND b.id IS NULL


                            


执行时间：1S


Buffer gets : 1000    


                                        


Operation    OBJECT Name    ROWS    Bytes    Cost    OBJECT Node    IN/OUT    PStart    PStop





SELECT STATEMENT Optimizer MODE=CHOOSE        47 K         3117                                       


  FILTER                                                         


    HASH JOIN OUTER                                                         


      VIEW        9 K    125 K    3067                                       


        SORT UNIQUE        9 K    38 K    3067                                       


          INDEX FAST FULL SCAN    TEST_INDX_1    5 M    19 M    457                                       


      TABLE ACCESS FULL    TEST_TABLE_2    46 K    181 K    48           


      


...

__________________

提问的智慧Oracle版
0。尝试在google,论坛，metalink,online document里搜索。
1。写清楚你的执行log,报错信息，写清楚DB version , OS
2。Instance 方面的问题，请贴出alertlog
3。network的问题，贴出server的listener.ora , sqlnet.ora 并运行lsnrctl service, 贴出client的tnsnames.ora , sqlnet.ora ,并运行tnsping
4。DB总体性能问题，请于peak time做statspack,并上传statspack report。同时附上CPU/MEM配置，以及CPU IDLE/free menory的情况
5。SQL性能问题，列出当前SQL,以及PLAN。同时应提供表结构，index情况，Table的record数。CBO/RBO,table是否analyze等信息。
6。确保你的问题是Oracle相关。
提问的智慧 for Oracle[展开版]

只看该作者

rollingpig
版主

精华贴数 5
个人空间 100
技术积分 26725 (30)
社区积分 1659 (661)
注册日期 2001-10-18
论坛徽章:54

#8

使用道具

发表于 2005-8-10 14:33

看来最后一种是最有效哦
改成DELETE形式看看

PHP code:



DELETE FROM TEST_TABLE_1 WHERE parentid IN (


SELEC parentid   FROM  TEST_TABLE_2 b ,(SELECT DISTINCT parentid FROM TEST_TABLE_1) a


                                  WHERE a.parentid = b.id(+)


                                        AND b.id IS NULL


)








成功了，执行时间 1S (实际上是 800-900ms)


Buffer_gets ：1000


执行计划


Operation    OBJECT Name    ROWS    Bytes    Cost    OBJECT Node    IN/OUT    PStart    PStop





DELETE STATEMENT Optimizer MODE=CHOOSE        6 K         119                                       


  DELETE    TEST_TABLE_1                                                     


    HASH JOIN SEMI        6 K    171 K    119                                       


      TABLE ACCESS FULL    TEST_TABLE_1    6 K    91 K    26                                       


      VIEW    SYS.VW_NSO_1    2 M    30 M    71                                       


        FILTER                                                         


          HASH JOIN OUTER                                                         


            INDEX FAST FULL SCAN    TEST_INDX_1    6 K    18 K    23                                       


            TABLE ACCESS FULL    TEST_TABLE_2    46 K    136 K    47      





...

__________________

提问的智慧Oracle版
0。尝试在google,论坛，metalink,online document里搜索。
1。写清楚你的执行log,报错信息，写清楚DB version , OS
2。Instance 方面的问题，请贴出alertlog
3。network的问题，贴出server的listener.ora , sqlnet.ora 并运行lsnrctl service, 贴出client的tnsnames.ora , sqlnet.ora ,并运行tnsping
4。DB总体性能问题，请于peak time做statspack,并上传statspack report。同时附上CPU/MEM配置，以及CPU IDLE/free menory的情况
5。SQL性能问题，列出当前SQL,以及PLAN。同时应提供表结构，index情况，Table的record数。CBO/RBO,table是否analyze等信息。
6。确保你的问题是Oracle相关。
提问的智慧 for Oracle[展开版]

只看该作者

rollingpig
版主

精华贴数 5
个人空间 100
技术积分 26725 (30)
社区积分 1659 (661)
注册日期 2001-10-18
论坛徽章:54

#9

使用道具

发表于 2005-8-10 14:34

可能not exists / not in 还有其他变换形式，大家可以讨论讨论。

__________________

提问的智慧Oracle版
0。尝试在google,论坛，metalink,online document里搜索。
1。写清楚你的执行log,报错信息，写清楚DB version , OS
2。Instance 方面的问题，请贴出alertlog
3。network的问题，贴出server的listener.ora , sqlnet.ora 并运行lsnrctl service, 贴出client的tnsnames.ora , sqlnet.ora ,并运行tnsping
4。DB总体性能问题，请于peak time做statspack,并上传statspack report。同时附上CPU/MEM配置，以及CPU IDLE/free menory的情况
5。SQL性能问题，列出当前SQL,以及PLAN。同时应提供表结构，index情况，Table的record数。CBO/RBO,table是否analyze等信息。
6。确保你的问题是Oracle相关。
提问的智慧 for Oracle[展开版]

只看该作者

violing
资深会员

精华贴数 1
个人空间 0
技术积分 2117 (733)
社区积分 9550 (158)
注册日期 2003-9-8
论坛徽章:3

#10

使用道具

发表于 2005-8-10 14:35

用HASH

这两个表一大一小,正好用HASH,速度可以提高到秒级,不信你就试试

__________________

马恩列斯毛刘周朱邓江胡V

只看该作者