no_expand优化案例

 bond 来看一个烂语句：
select a.*,b.dn from temp_allcrmuser a, phs_smc_user b
 where a.USERNUMBER=b.dn
 and (a.ACTFLAG<>b.ACT_FLG
 or a.ENABLEFLAG<>b.ENABLE_FLG);
汽水 15:50:29
但是对你自己做实验，搞工作极其有利
bond15:50:34
-------------------------------------------------------------------------------------------------
| Id  | Operation                     | Name            | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |                 |     2 |   112 |    12  (17)| 00:00:01 |
|   1 |  CONCATENATION                |                 |       |       |            |          |
|   2 |   MERGE JOIN                  |                 |     1 |    56 |     6  (17)| 00:00:01 |
|   3 |    TABLE ACCESS BY INDEX ROWID| PHS_SMC_USER    |    22 |   396 |     3   (0)| 00:00:01 |
|   4 |     INDEX FULL SCAN           | IND_SMC_USER_DN |    22 |       |     1   (0)| 00:00:01 |
|*  5 |    FILTER                     |                 |       |       |            |          |
|*  6 |     SORT JOIN                 |                 |    82 |  3116 |     3  (34)| 00:00:01 |
|   7 |      TABLE ACCESS FULL        | TEMP_ALLCRMUSER |    82 |  3116 |     2   (0)| 00:00:01 |
|   8 |   MERGE JOIN                  |                 |     1 |    56 |     6  (17)| 00:00:01 |
|   9 |    TABLE ACCESS BY INDEX ROWID| PHS_SMC_USER    |    22 |   396 |     3   (0)| 00:00:01 |
|  10 |     INDEX FULL SCAN           | IND_SMC_USER_DN |    22 |       |     1   (0)| 00:00:01 |
|* 11 |    FILTER                     |                 |       |       |            |          |
|* 12 |     SORT JOIN                 |                 |    82 |  3116 |     3  (34)| 00:00:01 |
|  13 |      TABLE ACCESS FULL        | TEMP_ALLCRMUSER |    82 |  3116 |     2   (0)| 00:00:01 |
-------------------------------------------------------------------------------------------------
bond(510330025) 15:50:48
  5 - filter("A"."ENABLEFLAG"<>"B"."ENABLE_FLG")
   6 - access("A"."USERNUMBER"="B"."DN")
       filter("A"."USERNUMBER"="B"."DN")
  11 - filter("A"."ACTFLAG"<>"B"."ACT_FLG" AND LNNVL("A"."ENABLEFLAG"<>"B"."ENABLE_FLG"))
  12 - access("A"."USERNUMBER"="B"."DN")
       filter("A"."USERNUMBER"="B"."DN")
SELECT *
  FROM (SELECT rownum,
               a.owner       AS owner_a,
               b.owner       AS owner_b,
               a.object_id   AS object_id_a,
               b.object_id   AS object_id_b,
               a.object_name
          FROM test1 a
         INNER JOIN test2 b
            ON b.object_name = a.object_name)
 WHERE (owner_a <> owner_b OR object_id_a <> object_id_b)
重庆-有教无类16:23:11
这个它改不了了
重庆-有教无类 16:26:13
咋样
重庆-有教无类16:26:17
还con不
bond 16:27:17
==
bond 16:30:59
select * from
(
select rownum a_rownum,a.ACTFLAG a_ACTFLAG,a.ENABLEFLAG a_ENABLEFLAG,b.dn,b.ACT_FLG b_ACT_FLG,b.ENABLE_FLG  b_ENABLE_FLG
from temp_allcrmuser a
join phs_smc_user b
 on a.USERNUMBER=b.dn) t
where (a_ACTFLAG<>b_ACT_FLG or a_ENABLEFLAG<>b_ENABLE_FLG);
----------------------------------------------------------------------------------------
| Id  | Operation            | Name            | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |                 |    22 |  1694 |  8740   (6)| 00:01:45 |
|*  1 |  VIEW                |                 |    22 |  1694 |  8740   (6)| 00:01:45 |
|   2 |   COUNT              |                 |       |       |            |          |
|*  3 |    HASH JOIN         |                 |    22 |  1232 |  8740   (6)| 00:01:45 |
|   4 |     TABLE ACCESS FULL| PHS_SMC_USER    |    22 |   396 |    12   (0)| 00:00:01 |
|   5 |     TABLE ACCESS FULL| TEMP_ALLCRMUSER |    10M|   394M|  8571   (4)| 00:01:43 |
--------------------------------------------------------------------------------------

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

   1 - filter("A_ACTFLAG"<>"B_ACT_FLG" OR "A_ENABLEFLAG"<>"B_ENABLE_FLG")
   3 - access("A"."USERNUMBER"="B"."DN")
bond 16:31:05
靠，可以了
bond 16:31:18
就是因为rownum？
史狮16:31:22
太牛b
。。。。。省略。。。。。

bond 16:41:27
select /*+ Use_hash(a,b) no_expand*/ a.*,b.dn from temp_allcrmuser a, phs_smc_user b
 where a.USERNUMBER=b.dn
 and (a.ACTFLAG<>b.ACT_FLG
 or a.ENABLEFLAG<>b.ENABLE_FLG);
 
--------------------------------------------------------------------------------------
| Id  | Operation          | Name            | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |                 |    18 |  1008 |  8740   (6)| 00:01:45 |
|*  1 |  HASH JOIN         |                 |    18 |  1008 |  8740   (6)| 00:01:45 |
|   2 |   TABLE ACCESS FULL| PHS_SMC_USER    |    22 |   396 |    12   (0)| 00:00:01 |
|   3 |   TABLE ACCESS FULL| TEMP_ALLCRMUSER |    10M|   394M|  8571   (4)| 00:01:43 |
--------------------------------------------------------------------------------------

用no_expand HINT也可以

在ORACLE11g 之前，CBO 还是会犯SB ，11g之后 不会自动的 expand了，

注意，上面都是我的学生