子查询解嵌套not in 无法展开改写

原创 2013年12月04日 15:07:47
SQL> explain plan for select *
  from OPS$CZTEST1.SAVJ_ATOMJOURBAK
 where ((list_flag = '1' and prt_flag = '0') and
       acct_no not in
       (select acct_no
           from OPS$CZTEST1.savb_basicinfo1
          where ((card_no is not null and base_acct_no is null) or
                (book_flag = '1' and bus_code = 21))));  2    3    4    5    6    7    8 

Explained.

SQL> select * from table(dbms_xplan.display());

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 3909253513

--------------------------------------------------------------------------------------
| Id  | Operation                             | Name                         | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |                                       | 54953 |    18M|   594M  (1)      |999:59:59 |
|*  1 |  FILTER                               |                                       |             |            |                           |          |
|*  2 |   TABLE ACCESS FULL   | SAVJ_ATOMJOUR0  | 54954 |    18M|  1042   (3)       | 00:00:13 |
|*  3 |   TABLE ACCESS FULL  | SAVB_BASICINFO1   |     1       |    73   | 11220   (1)     | 00:02:15 |
--------------------------------------------------------------------------------------

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

   1 - filter( NOT EXISTS (SELECT /*+ */ 0 FROM
              "OPS$CZTEST1"."SAVB_BASICINFO1" "SAVB_BASICINFO1" WHERE ("BASE_ACCT_NO" IS
              NULL AND "CARD_NO" IS NOT NULL OR "BUS_CODE"=21 AND "BOOK_FLAG"='1') AND
              LNNVL("ACCT_NO"<>:B1)))
   2 - filter("PRT_FLAG"='0' AND "LIST_FLAG"='1')
   3 - filter(("BASE_ACCT_NO" IS NULL AND "CARD_NO" IS NOT NULL OR
              "BUS_CODE"=21 AND "BOOK_FLAG"='1') AND LNNVL("ACCT_NO"<>:B1))

21 rows selected.


可以看到这里:
( NOT EXISTS (SELECT /*+ */ 0 FROM
              "OPS$CZTEST1"."SAVB_BASICINFO1" "SAVB_BASICINFO1" WHERE ("BASE_ACCT_NO" IS
              NULL AND "CARD_NO" IS NOT NULL OR "BUS_CODE"=21 AND "BOOK_FLAG"='1') AND
              LNNVL("ACCT_NO"<>:B1)))

子查询没有展开,FILTER 循环类似于NL循环,会导致表SAVB_BASICINFO1访问多次,not in 可以改写成左关联的形式,改写如下:
SQL> explain plan for select count(*)
  from OPS$CZTEST1.SAVJ_ATOMJOURBAK a,
       (select acct_no
          from OPS$CZTEST1.savb_basicinfo1
         where (card_no is not null and base_acct_no is null)
        or
          (book_flag = '1' and bus_code = 21)) b
 where a.list_flag = '1'
   and a.prt_flag = '0'
   and a.acct_no = b.acct_no(+)
   and b.acct_no is null;  2    3    4    5    6    7    8    9   10   11  

Explained.

SQL> select * from table(dbms_xplan.display());

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 4182698075

-----------------------------------------------------------------------------------------------
| Id  | Operation                           | Name                          | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |                                       |     1      |   110 |       | 13060   (1)| 00:02:37 |
|   1 |  SORT AGGREGATE        |                                      |     1       |   110 |       |            |          |
|*  2 |   HASH JOIN ANTI            |                                     |             1 |   110 |  2632K| 13060   (1)| 00:02:37 |
|*  3 |    TABLE ACCESS FULL | SAVJ_ATOMJOUR0  | 54954 |  1985K|       |  1041   (2)| 00:00:13 |
|*  4 |    TABLE ACCESS FULL | SAVB_BASICINFO1 |   166K|    11M|       | 11216   (1)| 00:02:15 |
-----------------------------------------------------------------------------------------------

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

   2 - access("ACCT_NO"="ACCT_NO")
   3 - filter("PRT_FLAG"='0' AND "LIST_FLAG"='1')
   4 - filter("BASE_ACCT_NO" IS NULL AND "CARD_NO" IS NOT NULL OR "BUS_CODE"=21 AND
              "BOOK_FLAG"='1')

19 rows selected.

 

 

 


 

子查询解嵌套in改写为exists

SELECT *   FROM (SELECT pubformdat0_.id                  id332_,                pubformdat0_.domain_...
  • zhaoyangjian724
  • zhaoyangjian724
  • 2013年12月04日 16:24
  • 638

SQL改写, 聚合语句, left join 改写标量子查询

最近遇到一个SQL 改写, 其实改写难度一般,但连续强调用 left  join  改写标量子查询,   不知道为何 如此多的标量子查询 案例 说明: 为了公司机密, 所以用scott 用户...
  • daiqiulong2
  • daiqiulong2
  • 2015年09月28日 23:14
  • 1376

hebernate,JPA select 子查询语句问题

HQL语句不能使用select接子查询语句,所以力求在一条sql语句中使用Criteria包含group by和count,order by这几个关键字,几乎是不可实现的。 查询数据库视图就可以了。...
  • wang124454731
  • wang124454731
  • 2017年01月06日 16:34
  • 3125

MySQL嵌套查询(子查询)

MySQL嵌套查询(子查询)
  • Trisyp
  • Trisyp
  • 2017年11月06日 18:08
  • 333

sql子查询 嵌套SELECT语句

嵌套SELECT语句也叫子查询,一个 SELECT 语句的查询结果能够作为另一个语句的输入值。子查询不但能够出现在Where子句中,也能够出现在from子句中,作为一个临时表使用,也能够出现在sele...
  • wangqi0079
  • wangqi0079
  • 2013年08月23日 15:13
  • 5771

MySQL-子查询(嵌套)

了解子查询的最好方法就是看例子现在我们来看看orders表和orderitems表orders表 orderitems表 现在我想要查找所有订购物品id为TNT2的客户,怎么查? orderit...
  • PythonKidDz
  • PythonKidDz
  • 2017年02月17日 11:58
  • 2219

Mybatis关联查询(嵌套查询)

三张表:user article blog 表的存储sql文件: /* Navicat MySQL Data Transfer Source Server :...
  • xingxiupaioxue
  • xingxiupaioxue
  • 2015年07月03日 10:46
  • 2377

hive中子查询实例

Hive只支持在FROM子句中使用子查询,子查询必须有名字,并且列必须唯一:SELECT ... FROM(subquery) name ... 确认下是否一定要求列必须唯一?      建表语句...
  • shenxiaoming77
  • shenxiaoming77
  • 2015年10月29日 13:57
  • 8063

MyBatis嵌套查询解析

Mybatis表现关联关系比hibernate简单,没有分那么细致one-to-many、many-to-one、one-to-one。而是只有两种association(一)、collection(...
  • canot
  • canot
  • 2016年05月24日 00:41
  • 14829

listview嵌套gridview,并实现grid元素部分显示以及点击展开与折叠

有时我们需要用GridView显示目录列表,有时甚至是二级的,即listview每一个item里面又各自嵌入一个gridview,但是当二级目录(数据条目)的数量过多时,界面会比较臃肿,这时我们就想要...
  • duguju
  • duguju
  • 2015年10月31日 23:08
  • 3519
内容举报
返回顶部
收藏助手
不良信息举报
您举报文章:子查询解嵌套not in 无法展开改写
举报原因:
原因补充:

(最多只允许输入30个字)