一个sql语句的改写

最新推荐文章于 2025-07-07 17:25:47 发布

huangliang0703

最新推荐文章于 2025-07-07 17:25:47 发布

阅读量674

点赞数

CC 4.0 BY-SA版权

本文链接：https://blog.youkuaiyun.com/huangliang0703/article/details/51725591

--1、创建订购黑名单临时表和索引
create table mig_US_USERINFORMATIONEXT (OperType varchar2(4),UserType varchar2(4),msisdn varchar2(11),MOBILE VARCHAR2(32),UserID VARCHAR2(128)) tablespace tbs_mread_dat;
create index idx_mig_US_USERinfoext_1 on mig_US_USERINFORMATIONEXT (msisdn) tablespace tbs_mread_idx;
 
--2、生成订购黑名单数据
truncate table mig_US_USERINFORMATIONEXT;
insert into mig_US_USERINFORMATIONEXT (OperType,UserType,msisdn)
SELECT DISTINCT
       '1' AS OperType--操作类型：1=加入特殊名单，2=退出
       ,'1' AS UserType--用户类型：1=黑名单；2=灰名单
       ,b.msisdn AS UserID--手机号码
FROM US_USERINFORMATIONEXT b
WHERE b.ORDERBLANKFLAG='1';
 
--3、生成订购灰名单数据
insert into mig_US_USERINFORMATIONEXT (OperType,UserType,msisdn)
SELECT DISTINCT
       '1' AS OperType--操作类型：1=加入特殊名单，2=退出
       ,'2' AS UserType--用户类型：1=黑名单；2=灰名单
       ,g.msisdn AS UserID--手机号码
FROM CON_READ_BLACKLIST_EXT g
WHERE  g.MSISDN NOT IN (select  msisdn from mig_US_USERINFORMATIONEXT where UserType = '1');

执行到第三不hang 住了，等待事件 latch free

查看执行计划：

Plan hash value: 1648364032
--------------------------------------------------------------------------------
| Id  | Operation                | Name                          | Rows  | Bytes
--------------------------------------------------------------------------------
|   0 | INSERT STATEMENT         |                               |       |
|   1 |  LOAD TABLE CONVENTIONAL |                               |       |
|   2 |   HASH UNIQUE            |                               |     1 |    26
|*  3 |    HASH JOIN ANTI NA     |                               |     1 |    26
|   4 |     INDEX FAST FULL SCAN | CON_READ_BLACKLIST_EXT_MSISDN |  2372K|    27
|*  5 |     TABLE ACCESS FULL    | MIG_US_USERINFORMATIONEXT     |  3297K|    44

933051 rows inserted

将语句改写：

insert into huang (OperType,UserType,msisdn)
SELECT DISTINCT
       '1' AS OperType--操作类型：1=加入特殊名单，2=退出
       ,'2' AS UserType--用户类型：1=黑名单；2=灰名单
       ,g.msisdn AS UserID--手机号码
FROM CON_READ_BLACKLIST_EXT g
WHERE  g.MSISDN  IN (
  select msisdn from CON_READ_BLACKLIST_EXT 
  minus
  select msisdn from mig_US_USERINFORMATIONEXT);

933032 rows inserted

Plan hash value: 2055671167
--------------------------------------------------------------------------------
| Id  | Operation                  | Name                          | Rows  | Byt
--------------------------------------------------------------------------------
|   0 | INSERT STATEMENT           |                               |       |
|   1 |  LOAD TABLE CONVENTIONAL   |                               |       |
|   2 |   HASH UNIQUE              |                               |     1 |
|*  3 |    HASH JOIN               |                               |  2628K|
|   4 |     VIEW                   | VW_NSO_1                      |  2372K|
|   5 |      MINUS                 |                               |       |
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
|   6 |       SORT UNIQUE          |                               |  2372K|
|   7 |        INDEX FAST FULL SCAN| CON_READ_BLACKLIST_EXT_MSISDN |  2372K|
|   8 |       SORT UNIQUE          |                               |  3297K|
|   9 |        TABLE ACCESS FULL   | MIG_US_USERINFORMATIONEXT     |  3297K|
|  10 |     INDEX FAST FULL SCAN   | CON_READ_BLACKLIST_EXT_MSISDN |  2372K|
--------------------------------------------------------------------------------