ITPUB论坛-中国最专业的IT技术社区

 
 注册
热搜:
查看: 779|回复: 21

如何使用HINT来修改达成此执行计划?

[复制链接]
论坛徽章:
304
奥迪
日期:2013-07-29 13:45:59红旗
日期:2014-02-07 10:47:20路虎
日期:2014-02-13 10:34:03保时捷
日期:2014-02-14 09:46:462014年新春福章
日期:2014-02-18 16:41:11马上有车
日期:2014-02-18 16:41:11马上有车
日期:2014-02-19 11:55:14马上有房
日期:2014-02-19 11:55:14马上有钱
日期:2014-02-19 11:55:14马上有对象
日期:2014-02-19 11:55:14
跳转到指定楼层
1#
发表于 2017-12-6 15:44 | 只看该作者 回帖奖励 |倒序浏览 |阅读模式
ORACLE 11204

有个DELETE 操作的SQL,带了个EXIST的子查询,效率很差,原因是,EXISTS 以主语句中的表作为驱动表,对表中的每条记录,和子查询作匹配后再删除;
改成 IN 子查询后,效率就很号,因为此时,优化器将先计算出子查询的结果集,以子查询的结果集作驱动表,再和主表关联匹配,

--原始语句,效率差
DELETE
FROM    TAB1 T1
WHERE   EXISTS
        (  SELECT 1  FROM    TAB2 T2  WHERE   t2.FVOUCHERID           = T1.FVOUCHERID   )
/

--测试语句,效率好
DELETE FROM    TAB1 T1 WHERE T1.FVOUCHERID  in  (  SELECT  t2.FVOUCHERID    FROM    TAB2 T2 )
/

现问,若不改代码,怎么使用提示来让优化器优先执行子查询,然后以子查询的结果集作驱动表?

认证徽章
论坛徽章:
5
ITPUB十周年纪念徽章
日期:2011-11-01 16:26:29鲜花蛋
日期:2011-12-20 16:06:45娜美
日期:2017-12-15 13:56:33技术图书徽章
日期:2017-11-17 09:13:02妮可·罗宾
日期:2017-11-17 10:43:23
2#
发表于 2017-12-6 16:05 | 只看该作者
看不到兩條當前的執行計劃,只能猜下 :
/*+ leading(t2) use_nl(t1) */ 這樣行嗎 ,或直接/*+ leading(t2) */

使用道具 举报

回复
论坛徽章:
0
3#
发表于 2017-12-6 16:17 | 只看该作者
用提示也改sql代码了吧,直接用sqlprofile呢,不用了删除就是了

使用道具 举报

回复
论坛徽章:
304
奥迪
日期:2013-07-29 13:45:59红旗
日期:2014-02-07 10:47:20路虎
日期:2014-02-13 10:34:03保时捷
日期:2014-02-14 09:46:462014年新春福章
日期:2014-02-18 16:41:11马上有车
日期:2014-02-18 16:41:11马上有车
日期:2014-02-19 11:55:14马上有房
日期:2014-02-19 11:55:14马上有钱
日期:2014-02-19 11:55:14马上有对象
日期:2014-02-19 11:55:14
4#
 楼主| 发表于 2017-12-6 16:57 | 只看该作者
guocun09 发表于 2017-12-6 16:05
看不到兩條當前的執行計劃,只能猜下 :
/*+ leading(t2) use_nl(t1) */ 這樣行嗎 ,或直接/*+ leading(t2 ...

你这个提示,是放在DELETE 之后吧?这时提示能够看到T2表?

使用道具 举报

回复
认证徽章
论坛徽章:
5
ITPUB十周年纪念徽章
日期:2011-11-01 16:26:29鲜花蛋
日期:2011-12-20 16:06:45娜美
日期:2017-12-15 13:56:33技术图书徽章
日期:2017-11-17 09:13:02妮可·罗宾
日期:2017-11-17 10:43:23
5#
发表于 2017-12-6 17:02 | 只看该作者
ZALBB 发表于 2017-12-6 16:57
你这个提示,是放在DELETE 之后吧?这时提示能够看到T2表?

是DELETE 之後,可以看到T2 。
不妨explain plan for 或DELETE 改SELECT 執行試試

使用道具 举报

回复
认证徽章
论坛徽章:
8
2009新春纪念徽章
日期:2009-01-04 14:52:28祖国60周年纪念徽章
日期:2009-10-09 08:28:002010新春纪念徽章
日期:2010-03-01 11:07:24ITPUB9周年纪念徽章
日期:2010-10-08 09:32:25ITPUB十周年纪念徽章
日期:2011-11-01 16:23:262013年新春福章
日期:2013-02-25 14:51:24沸羊羊
日期:2015-03-04 14:51:522015年新春福章
日期:2015-03-06 11:57:31
6#
发表于 2017-12-6 17:04 | 只看该作者
DELETE
FROM    TAB1 T1
WHERE   EXISTS
        (  SELECT /*+ unnest */ 1  FROM    TAB2 T2  WHERE   t2.FVOUCHERID           = T1.FVOUCHERID   )
/

使用道具 举报

回复
论坛徽章:
304
奥迪
日期:2013-07-29 13:45:59红旗
日期:2014-02-07 10:47:20路虎
日期:2014-02-13 10:34:03保时捷
日期:2014-02-14 09:46:462014年新春福章
日期:2014-02-18 16:41:11马上有车
日期:2014-02-18 16:41:11马上有车
日期:2014-02-19 11:55:14马上有房
日期:2014-02-19 11:55:14马上有钱
日期:2014-02-19 11:55:14马上有对象
日期:2014-02-19 11:55:14
7#
 楼主| 发表于 2017-12-6 17:09 | 只看该作者
running_life 发表于 2017-12-6 16:17
用提示也改sql代码了吧,直接用sqlprofile呢,不用了删除就是了

用SQLPROFILE 调试,涉及到语句中的对象的数据量,而我那语句的子查询,恰好用了表变量(数组变量),不好模拟,所以我只能要求用 HINT 强制执行计划,

使用道具 举报

回复
论坛徽章:
304
奥迪
日期:2013-07-29 13:45:59红旗
日期:2014-02-07 10:47:20路虎
日期:2014-02-13 10:34:03保时捷
日期:2014-02-14 09:46:462014年新春福章
日期:2014-02-18 16:41:11马上有车
日期:2014-02-18 16:41:11马上有车
日期:2014-02-19 11:55:14马上有房
日期:2014-02-19 11:55:14马上有钱
日期:2014-02-19 11:55:14马上有对象
日期:2014-02-19 11:55:14
8#
 楼主| 发表于 2017-12-6 17:39 | 只看该作者
1  原始语句的执行计划,可以看到,以主表为驱动表,
SQL>EXPLAIN PLAN FOR
DELETE  
FROM    TAB1
WHERE   EXISTS
        (
                SELECT  1
                FROM    TAB2 T0
                        INNER JOIN
                                (
                                        SELECT
                                                /*+ dynamic_sampling(2)*/
                                                COLUMN_VALUE AS FID
                                        FROM    TABLE(CAST(:PKValue_udt AS udt_inttable))
                     2    3    4               )
                                b
                        ON      t0.FGLVOUCHERID = b.FID
                WHERE   t0.FVOUCHERID           = TAB1.FVOUCHERID
        )
/
SELECT * FROM DISPLAY;
  5    6    7    8    9   10   11   12   13   14   15   16   17   18   19  
Explained.

Elapsed: 00:00:00.01
SQL>
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 1288766477

-----------------------------------------------------------------------------------------------------------------------
| Id  | Operation                            | Name                           | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------------------------------------
|   0 | DELETE STATEMENT                     |                                |     7 |    84 |   337M  (5)|173:53:55 |
|   1 |  DELETE                              | TAB1                           |       |       |            |          |
|*  2 |   INDEX FULL SCAN                    | IDX_TAB1_FVOUCHERID            |  1207K|    13M| 57270   (4)| 00:01:47 |
|   3 |    NESTED LOOPS                      |                                |     1 |    13 |    14   (8)| 00:00:01 |
|   4 |     TABLE ACCESS BY INDEX ROWID      | TAB2                           |     1 |    11 |     2   (0)| 00:00:01 |
|*  5 |      INDEX UNIQUE SCAN               | PK_TAB2                        |     1 |       |     2   (0)| 00:00:01 |
|*  6 |     COLLECTION ITERATOR PICKLER FETCH|                                |     1 |     2 |    12   (9)| 00:00:01 |
-----------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter( EXISTS (SELECT 0 FROM TABLE() "KOKBF$0","TAB2" "T0" WHERE "T0"."FVOUCHERID"=:B1 AND
              "T0"."FGLVOUCHERID"=VALUE(KOKBF$)))
   5 - access("T0"."FVOUCHERID"=:B1)
   6 - filter("T0"."FGLVOUCHERID"=VALUE(KOKBF$))

21 rows selected.

Elapsed: 00:00:00.02

2  使用 unnest 提示
SQL>EXPLAIN PLAN FOR
DELETE  
FROM    TAB1
WHERE   EXISTS
        (
                SELECT  /*+ unnest() */ 1
                FROM    TAB2 T0
                        INNER JOIN
                                (
                                        SELECT
                                                COLUMN_VALUE AS FID
                                        FROM    TABLE(CAST(:PKValue_udt AS udt_inttable))
                                )
                                b
           2    3    4    5                 ON      t0.FGLVOUCHERID = b.FID
                WHERE   t0.FVOUCHERID           = TAB1.FVOUCHERID
        )
/
SELECT * FROM DISPLAY;
  6    7    8    9   10   11   12   13   14   15   16   17   18  
Explained.

Elapsed: 00:00:00.02
SQL>
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 1244150433

---------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                              | Name                           | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------------------------------------------
|   0 | DELETE STATEMENT                       |                                |  2347K|    55M|       | 85622   (4)| 00:02:39 |
|   1 |  DELETE                                | TAB1                           |       |       |       |            |          |
|   2 |   MERGE JOIN SEMI                      |                                |  2347K|    55M|       | 85622   (4)| 00:02:39 |
|   3 |    INDEX FULL SCAN                     | IDX_TAB1_FVOUCHERID            |    24M|   276M|       | 57270   (4)| 00:01:47 |
|*  4 |    SORT UNIQUE                         |                                |   316K|  4012K|    12M| 28352   (5)| 00:00:53 |
|   5 |     VIEW                               | VW_SQ_1                        |   316K|  4012K|       | 26723   (5)| 00:00:50 |
|*  6 |      HASH JOIN                         |                                |   316K|  4012K|       | 26723   (5)| 00:00:50 |
|   7 |       COLLECTION ITERATOR PICKLER FETCH|                                |  8168 | 16336 |       |    31   (4)| 00:00:01 |
|   8 |       VIEW                             | index$_join$_002               |  3287K|    34M|       | 26559   (4)| 00:00:50 |
|*  9 |        HASH JOIN                       |                                |       |       |       |            |          |
|  10 |         INDEX FAST FULL SCAN           | PK_TAB2                        |  3287K|    34M|       | 10036   (3)| 00:00:19 |
|  11 |         INDEX FAST FULL SCAN           | IDX_TAB2_FGLVOUCHERID          |  3287K|    34M|       | 11204   (3)| 00:00:21 |
---------------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   4 - access("ITEM_1"="TAB1"."FVOUCHERID")
       filter("ITEM_1"="TAB1"."FVOUCHERID")
   6 - access("T0"."FGLVOUCHERID"=VALUE(KOKBF$))
   9 - access(ROWID=ROWID)

26 rows selected.

Elapsed: 00:00:00.02

3  我期望的执行计划,这里假设了表变量里包含10条记录,TABLE(CAST(:PKValue_udt AS udt_inttable)) t
SQL>EXPLAIN PLAN FOR
DELETE  
FROM    TAB1
WHERE   FVOUCHERID in
        (
                SELECT  t0.FVOUCHERID
                FROM    TAB2 T0
                        INNER JOIN
                                (
                                        SELECT   /*+ cardinality(t 10)*/
                                                COLUMN_VALUE AS FID
                                        FROM    TABLE(CAST(:PKValue_udt AS udt_inttable)) t
                                )
            2    3    4                        b
                        ON      t0.FGLVOUCHERID = b.FID)
/
SELECT * FROM DISPLAY;
  5    6    7    8    9   10   11   12   13   14   15   16  
Explained.

Elapsed: 00:00:00.01
SQL>
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 1718349958

--------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                               | Name                           | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------------------------------------
|   0 | DELETE STATEMENT                        |                                |  2875 | 71875 |   693   (1)| 00:00:02 |
|   1 |  DELETE                                 | TAB1                           |       |       |            |          |
|   2 |   NESTED LOOPS                          |                                |  2875 | 71875 |   693   (1)| 00:00:02 |
|   3 |    VIEW                                 | VW_NSO_1                       |   387 |  5031 |    71   (2)| 00:00:01 |
|   4 |     SORT UNIQUE                         |                                |   387 |  5031 |            |          |
|   5 |      NESTED LOOPS                       |                                |   387 |  5031 |    71   (2)| 00:00:01 |
|   6 |       NESTED LOOPS                      |                                |   390 |  5031 |    71   (2)| 00:00:01 |
|   7 |        COLLECTION ITERATOR PICKLER FETCH|                                |    10 |    20 |    31   (4)| 00:00:01 | <-- 以表变量为驱动表
|*  8 |        INDEX RANGE SCAN                 | IDX_TAB2_FGLVOUCHERID          |    39 |       |     2   (0)| 00:00:01 |
|   9 |       TABLE ACCESS BY INDEX ROWID       | TAB2                           |    39 |   429 |     4   (0)| 00:00:01 |
|* 10 |    INDEX RANGE SCAN                     | IDX_TAB1_FVOUCHERID            |     7 |    84 |     2   (0)| 00:00:01 |
--------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   8 - access("T0"."FGLVOUCHERID"=VALUE(KOKBF$))
  10 - access("FVOUCHERID"="FVOUCHERID")

23 rows selected.

Elapsed: 00:00:00.03
SQL>

使用道具 举报

回复
论坛徽章:
304
奥迪
日期:2013-07-29 13:45:59红旗
日期:2014-02-07 10:47:20路虎
日期:2014-02-13 10:34:03保时捷
日期:2014-02-14 09:46:462014年新春福章
日期:2014-02-18 16:41:11马上有车
日期:2014-02-18 16:41:11马上有车
日期:2014-02-19 11:55:14马上有房
日期:2014-02-19 11:55:14马上有钱
日期:2014-02-19 11:55:14马上有对象
日期:2014-02-19 11:55:14
9#
 楼主| 发表于 2017-12-6 17:47 | 只看该作者
sqysl 发表于 2017-12-6 17:04
DELETE
FROM    TAB1 T1
WHERE   EXISTS

刚才加了CARDINALITY进去测试了一下,是可以达成目的,但是CARDINALITY =10, 不准确,我希望是不用此提示,

使用道具 举报

回复
认证徽章
论坛徽章:
5
ITPUB十周年纪念徽章
日期:2011-11-01 16:26:29鲜花蛋
日期:2011-12-20 16:06:45娜美
日期:2017-12-15 13:56:33技术图书徽章
日期:2017-11-17 09:13:02妮可·罗宾
日期:2017-11-17 10:43:23
10#
发表于 2017-12-6 20:56 来自手机 | 只看该作者
ZALBB 发表于 2017-12-6 16:57
你这个提示,是放在DELETE 之后吧?这时提示能够看到T2表?

sorry,子查询可能用不到hint。之前一次优化偶然成功,我以为11g中 hint在子查询时有变化,刚查下文档并没有说明。
此例如要用我想的leading可能要指明query block :
第一种:
DELETE/*+ leading(@sq) use_nl(t1) */
FROM  TAB1 T1
WHERE EXISTS
  (SELECT /*+ qb_name(sq) */ 1FROM  TAB2 T2WHERE t2.FVOUCHERID   = T1.FVOUCHERID )
/
第二种:
直接指定查询块名hint  /*+ leading(@sel$2) use_nl(t1) */   
@sel$2 具体是多少要查下

使用道具 举报

回复

您需要登录后才可以回帖 登录 | 注册

本版积分规则

TOP技术积分榜 社区积分榜 徽章 团队 统计 知识索引树 积分竞拍 文本模式 帮助
  ITPUB首页 | ITPUB论坛 | 数据库技术 | 企业信息化 | 开发技术 | 微软技术 | 软件工程与项目管理 | IBM技术园地 | 行业纵向讨论 | IT招聘 | IT文档 |
  | | |
CopyRight 1999-2011 itpub.net All Right Reserved. 北京盛拓优讯信息技术有限公司版权所有 联系我们 网站律师 隐私政策 知识产权声明
 北京市公安局海淀分局网监中心备案编号:11010802021510 广播电视节目制作经营许可证:编号(京)字第1149号
  
快速回复 返回顶部 返回列表