19.no_unnest 子查询不展开的优化案例

最新推荐文章于 2024-04-29 20:57:20 发布
最新推荐文章于 2024-04-29 20:57:20 发布
阅读量386 收藏 7
点赞数 8
分类专栏： Oracle-SQL性能调优 文章标签： sql 数据库 mysql
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/weixin_43346403/article/details/135928417
版权

1.优化前SQL

select count(1)
  from (SELECT distinct rp.registNo,
                        rp.policyNo,
                        rp.plancode,
                        rp.riskcode,
                        rp.itemno,
                        rp.endorseqno,
                        gcrm.damagestartdate,
                        gcrm.damageaddress,
                        gcrm.damagecode,
                        gcrm.damagename,
                        gcrm.reporttype,
                        gcrm.reportorname,
                        gcrm.reportorphonenumber,
                        gcrm.reportdate,
                        gcrm.operatorCode,
                        rp.insuredName,
                        gcrm.canceldate,
                        ggr.riskclass,
                        ggr.classcname,
                        ggc.companycode,
                        ggc.companyCName,
                        (SELECT round(c.sumEstLoss, 2) 
                           FROM Gcclaimmain c
                          WHERE c.registNo = rp.registNo
                            AND c.policyNo = rp.policyNo
                            AND c.planCode = rp.planCode
                            AND c.riskCode = rp.riskCode
                            AND c.itemNo = rp.itemNo
                            AND ROWNUM = 1) sumEstLoss,
                        (SELECT round(c.sumPaid, 2)
                           FROM GcClaimMain c
                          WHERE c.registNo = rp.registNo
                            AND c.policyNo = rp.policyNo
                            AND c.planCode = rp.planCode
                            AND c.riskCode = rp.riskCode
                            AND c.itemNo = rp.itemNo
                            AND ROWNUM = 1) sumPaid,
                        gcrm.phonenumber,
                        ggcd.codecode,
                        ggcd.codecname
          FROM GcRegistPolicy rp
         INNER JOIN gcregistmain gcrm
            on (gcrm.registno = rp.registno)
         INNER JOIN ggriskclass ggr
            ON (ggr.riskclass = rp.riskclass)
         INNER JOIN GgCompany ggc
            ON (ggc.companycode = rp.companycode)
         INNER JOIN ggcode ggcd
            ON (ggcd.codecode = rp.channeldetailcode and
               ggcd.codetype = 'UnderWriteChannelTip')
         WHERE 1 = 1
           and substr(rp.riskcode, 0, 2) not in ('08', '09', '16')
           and (rp.autorelationpolicyflag is null or
               rp.autorelationpolicyflag <> '1')
           and gcrm.status = '4'
           and not exists
         (select 'x'
                  from gcclaimmain a
                 where rp.registNo = a.registNo
                   AND rp.policyNo = a.policyNo
                   AND rp.planCode = a.planCode
                   AND rp.riskCode = a.riskCode
                   AND rp.itemNo = a.itemNo
                   AND (a.endcasestatus in ('1', '3') or a.CaseType = '4')
		 )
           and not exists (select 'X'
                  from gccancellation a
                 where a.businesstype = 'regist'
                   and a.status <> '5'
                   and a.businessno = rp.registNo
                union
                select 'X'
                  from gccancellation a, gcclaimmain b
                 where a.businessno = b.claimno
                   and b.registNo = rp.registNo
                   and b.policyNo = rp.policyNo
                   and b.riskCode = rp.riskCode
                   and a.businesstype = 'claim'
                   and a.status <> '5')
           and not exists (select 'x'
                  from gcdelegatedetail del
                 where del.registno = rp.registno
                   and del.policyno = rp.policyno
                   and del.riskCode = rp.riskCode
               )
         order by gcrm.damagestartdate desc);

执行计划查看

--优化前 
Execution Plan
----------------------------------------------------------
Plan hash value: 3729601010

-------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation				  | Name		    | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
-------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT			  		|			    			|	  1 |	  2 | 13980   (1)| 00:02:48 |	    |	    |
|   1 |  SORT AGGREGATE 			  		|			    			|	  1 |	  2 |		 |	    |	    |	    |
|   2 |   VIEW					  			|			    			|	  1 |	  2 | 13980   (1)| 00:02:48 |	    |	    |
|*  3 |    COUNT STOPKEY			  		|			    			|	    |	    |		 |	    |	    |	    |
|*  4 |     TABLE ACCESS BY GLOBAL INDEX ROWID| GCCLAIMMAIN 			|	  1 |	 62 |	  3   (0)| 00:00:01 | ROWID | ROWID |
|*  5 |      INDEX UNIQUE SCAN			  	| INDEX_CLAIM_UNIQUECLAIM 	|	  1 |	    |	  2   (0)| 00:00:01 |	    |	    |
|*  6 |    COUNT STOPKEY			  		|			    			|	    |	    |		 |	    |	    |	    |
|*  7 |     TABLE ACCESS BY GLOBAL INDEX ROWID	  | GCCLAIMMAIN 	    |	  1 |	 62 |	  3   (0)| 00:00:01 | ROWID | ROWID |
|*  8 |      INDEX UNIQUE SCAN			  	| INDEX_CLAIM_UNIQUECLAIM 	|	  1 |	    |	  2   (0)| 00:00:01 |	    |	    |
|   9 |    HASH UNIQUE				  		|			    			|	  1 |	456 | 13980   (1)| 00:02:48 |	    |	    |
|* 10 |     FILTER				  			|			    			|	    |	    |		 |	    |	    |	    |
|  11 |      NESTED LOOPS ANTI			  	|			    			|	  1 |	456 | 13969   (1)| 00:02:48 |	    |	    |
|  12 |       NESTED LOOPS ANTI 		  	|			    			|	  1 |	394 | 13967   (1)| 00:02:48 |	    |	    |
|  13 |        NESTED LOOPS			  		|			    			|	  1 |	388 | 13957   (1)| 00:02:48 |	    |	    |
|  14 | 	NESTED LOOPS			  		|			    			|	  1 |	232 | 13955   (1)| 00:02:48 |	    |	    |
|  15 | 	 NESTED LOOPS			  		|			    			|	  1 |	178 | 13954   (1)| 00:02:48 |	    |	    |
|* 16 | 	  HASH JOIN			  			|			    			|	  1 |	164 | 13953   (1)| 00:02:48 |	    |	    |
|  17 | 	   TABLE ACCESS BY INDEX ROWID	  | GGCODE		    		|	 50 |  2500 |	 27   (0)| 00:00:01 |	    |	    |
|* 18 | 	    INDEX RANGE SCAN		  	| PK_GGCODE		    		|	 50 |	    |	  3   (0)| 00:00:01 |	    |	    |
|  19 | 	   PARTITION HASH ALL		  	|			    			|	117 | 13338 | 13926   (1)| 00:02:48 |	  1 |	  8 |
|* 20 | 	    TABLE ACCESS FULL		  	| GCREGISTPOLICY	   		|	117 | 13338 | 13926   (1)| 00:02:48 |	  1 |	  8 |
|  21 | 	  TABLE ACCESS BY INDEX ROWID	| GGRISKCLASS 	    		|	  1 |	 14 |	  1   (0)| 00:00:01 |	    |	    |
|* 22 | 	   INDEX UNIQUE SCAN		  	| PK_GGRISKCLASS	    	|	  1 |	    |	  0   (0)| 00:00:01 |	    |	    |
|  23 | 	 TABLE ACCESS BY INDEX ROWID	| GGCOMPANY		    		|	  1 |	 54 |	  1   (0)| 00:00:01 |	    |	    |
|* 24 | 	  INDEX UNIQUE SCAN		  		| PK_GGCOMPANY	    		|	  1 |	    |	  0   (0)| 00:00:01 |	    |	    |
|* 25 | 	TABLE ACCESS BY GLOBAL INDEX ROWID| GCREGISTMAIN	    	|	  1 |	156 |	  2   (0)| 00:00:01 | ROWID | ROWID |
|* 26 | 	 INDEX UNIQUE SCAN		  		| PK_GCREGISTMAIN	    	|	  1 |	    |	  1   (0)| 00:00:01 |	    |	    |
|  27 |        VIEW				  			| VW_SQ_1		    		|	  1 |	  6 |	 10  (20)| 00:00:01 |	    |	    |
|  28 | 	SORT UNIQUE			  			|			    			|	  2 |	202 |	 10  (20)| 00:00:01 |	    |	    |
|  29 | 	 UNION ALL PUSHED PREDICATE	  	|			    			|	    |	    |		     |	    |	    |	    |
|* 30 | 	  INDEX RANGE SCAN		  		| PK_DELEGATEDETAIL	    	|	  1 |	 49 |	  4   (0)| 00:00:01 |	    |	    |
|* 31 | 	  INDEX RANGE SCAN		  		| PK_DELEGATEDETAIL	    	|	  1 |	 52 |	  4   (0)| 00:00:01 |	    |	    |
|* 32 |       TABLE ACCESS BY GLOBAL INDEX ROWID  | GCCLAIMMAIN 	    |	914K|	 54M|	  2   (0)| 00:00:01 | ROWID | ROWID |
|* 33 |        INDEX UNIQUE SCAN		  	| INDEX_CLAIM_UNIQUECLAIM 	|	  1 |	    |	  1   (0)| 00:00:01 |	    |	    |
|  34 |      SORT UNIQUE			  		|			    			|	  2 |	260 |	 10  (20)| 00:00:01 |	    |	    |
|  35 |       UNION-ALL 			  		|			    			|	    |	    |		     |	    |	    |	    |
|* 36 |        TABLE ACCESS BY INDEX ROWID	| GCCANCELLATION	    	|	  1 |	 30 |	  3   (0)| 00:00:01 |	    |	    |
|* 37 | 	INDEX RANGE SCAN		  		| PK_GCCANCELLATI	    	|	  1 |	    |	  2   (0)| 00:00:01 |	    |	    |
|  38 |        NESTED LOOPS			  		|			    			|	  1 |	100 |	  5   (0)| 00:00:01 |	    |	    |
|* 39 | 	TABLE ACCESS BY GLOBAL INDEX ROWID| GCCLAIMMAIN 	    	|	  1 |	 70 |	  3   (0)| 00:00:01 | ROWID | ROWID |
|* 40 | 	 INDEX UNIQUE SCAN		  		| INDEX_CLAIM_UNIQUECLAIM 	|	  1 |	    |	  2   (0)| 00:00:01 |	    |	    |
|* 41 | 	TABLE ACCESS BY INDEX ROWID	  	| GCCANCELLATION	    	|	  1 |	 30 |	  2   (0)| 00:00:01 |	    |	    |
|* 42 | 	 INDEX RANGE SCAN		  		| PK_GCCANCELLATI	    	|	  1 |	    |	  1   (0)| 00:00:01 |	    |	    |
-------------------------------------------------------------------------------------------------------------------------------------

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

   3 - filter(ROWNUM=1)
   4 - filter("C"."POLICYNO"=:B1 AND "C"."ITEMNO"=:B2 AND "C"."PLANCODE"=:B3)
   5 - access("C"."REGISTNO"=:B1 AND "C"."RISKCODE"=:B2)
   6 - filter(ROWNUM=1)
   7 - filter("C"."POLICYNO"=:B1 AND "C"."ITEMNO"=:B2 AND "C"."PLANCODE"=:B3)
   8 - access("C"."REGISTNO"=:B1 AND "C"."RISKCODE"=:B2)
  10 - filter( NOT EXISTS ( (SELECT 'X' FROM "GCCANCELLATION" "A" WHERE "A"."BUSINESSNO"=:B1 AND "A"."BUSINESSTYPE"='regist'
	      AND "A"."STATUS"<>'5')UNION (SELECT 'X' FROM "GCCLAIMMAIN" "B","GCCANCELLATION" "A" WHERE "A"."BUSINESSNO"="B"."CLAIMNO" AND
	      "A"."BUSINESSTYPE"='claim' AND "A"."STATUS"<>'5' AND "B"."RISKCODE"=:B2 AND "B"."REGISTNO"=:B3 AND "B"."POLICYNO"=:B4)))
  16 - access("GGCD"."CODECODE"="RP"."CHANNELDETAILCODE")
  18 - access("GGCD"."CODETYPE"='UnderWriteChannelTip')
  20 - filter(SUBSTR("RP"."RISKCODE",0,2)<>'08' AND SUBSTR("RP"."RISKCODE",0,2)<>'09' AND SUBSTR("RP"."RISKCODE",0,2)<>'16'
	      AND ("RP"."AUTORELATIONPOLICYFLAG" IS NULL OR "RP"."AUTORELATIONPOLICYFLAG"<>'1'))
  22 - access("GGR"."RISKCLASS"="RP"."RISKCLASS")
  24 - access("GGC"."COMPANYCODE"="RP"."COMPANYCODE")
  25 - filter("GCRM"."STATUS"='4')
  26 - access("GCRM"."REGISTNO"="RP"."REGISTNO")
  30 - access("DEL"."REGISTNO"="RP"."REGISTNO" AND "DEL"."POLICYNO"="RP"."POLICYNO" AND "DEL"."RISKCODE"="RP"."RISKCODE")
       filter("DEL"."RISKCODE"="RP"."RISKCODE")
  31 - access("DEL"."REGISTNO"="RP"."REGISTNO" AND "DEL"."POLICYNO"="RP"."POLICYNO" AND "DEL"."RISKCODE"="RP"."RISKCODE" AND
	      "DEL"."TIMES"=1)
       filter("DEL"."RISKCODE"="RP"."RISKCODE" AND "DEL"."TIMES"=1)
  32 - filter(("A"."CASETYPE"='4' OR ("A"."ENDCASESTATUS"='1' OR "A"."ENDCASESTATUS"='3')) AND
	      "RP"."POLICYNO"="A"."POLICYNO" AND "RP"."PLANCODE"="A"."PLANCODE" AND "RP"."ITEMNO"="A"."ITEMNO")
  33 - access("RP"."REGISTNO"="A"."REGISTNO" AND "RP"."RISKCODE"="A"."RISKCODE")
  36 - filter("A"."BUSINESSTYPE"='regist' AND "A"."STATUS"<>'5')
  37 - access("A"."BUSINESSNO"=:B1)
  39 - filter("B"."POLICYNO"=:B1)
  40 - access("B"."REGISTNO"=:B1 AND "B"."RISKCODE"=:B2)
  41 - filter("A"."BUSINESSTYPE"='claim' AND "A"."STATUS"<>'5')
  42 - access("A"."BUSINESSNO"="B"."CLAIMNO")


Statistics
----------------------------------------------------------
	  1  recursive calls
	  0  db block gets
5465026  consistent gets
	  0  physical reads
	  0  redo size
	525  bytes sent via SQL*Net to client
	520  bytes received via SQL*Net from client
	  2  SQL*Net roundtrips to/from client
 383767  sorts (memory)
	  0  sorts (disk)
	  1  rows processed


--优化前10s~11s,

2.优化后SQL 加HINT;/*+NO_UNNEST*/，不做子查询展开

select count(1)
  from (SELECT distinct rp.registNo,
                        rp.policyNo,
                        rp.plancode,
                        rp.riskcode,
                        rp.itemno,
                        rp.endorseqno,
                        gcrm.damagestartdate,
                        gcrm.damageaddress,
                        gcrm.damagecode,
                        gcrm.damagename,
                        gcrm.reporttype,
                        gcrm.reportorname,
                        gcrm.reportorphonenumber,
                        gcrm.reportdate,
                        gcrm.operatorCode,
                        rp.insuredName,
                        gcrm.canceldate,
                        ggr.riskclass,
                        ggr.classcname,
                        ggc.companycode,
                        ggc.companyCName,
                        (SELECT round(c.sumEstLoss, 2) 
                           FROM Gcclaimmain c
                          WHERE c.registNo = rp.registNo
                            AND c.policyNo = rp.policyNo
                            AND c.planCode = rp.planCode
                            AND c.riskCode = rp.riskCode
                            AND c.itemNo = rp.itemNo
                            AND ROWNUM = 1) sumEstLoss,
                        (SELECT round(c.sumPaid, 2)
                           FROM GcClaimMain c
                          WHERE c.registNo = rp.registNo
                            AND c.policyNo = rp.policyNo
                            AND c.planCode = rp.planCode
                            AND c.riskCode = rp.riskCode
                            AND c.itemNo = rp.itemNo
                            AND ROWNUM = 1) sumPaid,
                        gcrm.phonenumber,
                        ggcd.codecode,
                        ggcd.codecname
          FROM GcRegistPolicy rp
         INNER JOIN gcregistmain gcrm
            on (gcrm.registno = rp.registno)
         INNER JOIN ggriskclass ggr
            ON (ggr.riskclass = rp.riskclass)
         INNER JOIN GgCompany ggc
            ON (ggc.companycode = rp.companycode)
         INNER JOIN ggcode ggcd
            ON (ggcd.codecode = rp.channeldetailcode and
               ggcd.codetype = 'UnderWriteChannelTip')
         WHERE 1 = 1
           and substr(rp.riskcode, 0, 2) not in ('08', '09', '16')
           and (rp.autorelationpolicyflag is null or
               rp.autorelationpolicyflag <> '1')
           and gcrm.status = '4'
           and not exists
         (select 'x'
                  from gcclaimmain a
                 where rp.registNo = a.registNo
                   AND rp.policyNo = a.policyNo
                   AND rp.planCode = a.planCode
                   AND rp.riskCode = a.riskCode
                   AND rp.itemNo = a.itemNo
                   AND (a.endcasestatus in ('1', '3') or a.CaseType = '4')
		 )
           and not exists (select /*+no_unnest*/'X'
                  from gccancellation a
                 where a.businesstype = 'regist'
                   and a.status <> '5'
                   and a.businessno = rp.registNo
                union
                select 'X'
                  from gccancellation a, gcclaimmain b
                 where a.businessno = b.claimno
                   and b.registNo = rp.registNo
                   and b.policyNo = rp.policyNo
                   and b.riskCode = rp.riskCode
                   and a.businesstype = 'claim'
                   and a.status <> '5')
           and not exists (select /*+no_unnest*/'x'
                  from gcdelegatedetail del
                 where del.registno = rp.registno
                   and del.policyno = rp.policyno
                   and del.riskCode = rp.riskCode
               )
         order by gcrm.damagestartdate desc);

执行时间5s;

执行i计划查看 

Elapsed: 00:00:04.87
--优化后:5s;
Execution Plan
----------------------------------------------------------
Plan hash value: 2928421648

-------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation				  | Name		                        | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
-------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT			  			|			    		|	  1 |	  2 | 13980   (1)| 00:02:48 |	    |	    |
|   1 |  SORT AGGREGATE 			  			|			    		|	  1 |	  2 |		 	 |	    |	    |	    |
|   2 |   VIEW					  				|			    		|	  1 |	  2 | 13980   (1)| 00:02:48 |	    |	    |
|*  3 |    COUNT STOPKEY			  			|			    		|	    |	    |		     |	    |	    |	    |
|*  4 |     TABLE ACCESS BY GLOBAL INDEX ROWID	  | GCCLAIMMAIN 	    |	  1 |	 62 |	  3   (0)| 00:00:01 | ROWID | ROWID |
|*  5 |      INDEX UNIQUE SCAN			  		| INDEX_CLAIM_UNIQUECLAIM |	  1 |	    |	  2   (0)| 00:00:01 |	    |	    |
|*  6 |    COUNT STOPKEY			  			|			    		|	    |	    |		     |	    |	    |	    |
|*  7 |     TABLE ACCESS BY GLOBAL INDEX ROWID	  | GCCLAIMMAIN 	    |	  1 |	 62 |	  3   (0)| 00:00:01 | ROWID | ROWID |
|*  8 |      INDEX UNIQUE SCAN			  		| INDEX_CLAIM_UNIQUECLAIM |	  1 |	    |	  2   (0)| 00:00:01 |	    |	    |
|   9 |    HASH UNIQUE				  			|			    		|	  1 |	450 | 13980   (1)| 00:02:48 |	    |	    |
|* 10 |     FILTER				  				|			    		|	    |	    |		     |	    |	    |	    |
|  11 |      NESTED LOOPS ANTI			  		|			    		|	  1 |	450 | 13959   (1)| 00:02:48 |	    |	    |
|  12 |       NESTED LOOPS			  			|			    		|	  1 |	388 | 13957   (1)| 00:02:48 |	    |	    |
|  13 |        NESTED LOOPS			 			|			    		|	  1 |	232 | 13955   (1)| 00:02:48 |	    |	    |
|  14 | 	NESTED LOOPS			  			|			    		|	  1 |	178 | 13954   (1)| 00:02:48 |	    |	    |
|* 15 | 	 HASH JOIN	--这里之前时NESTLOOP    |			    		|	  1 |	164 | 13953   (1)| 00:02:48 |	    |	    |
|  16 | 	  TABLE ACCESS BY INDEX ROWID	  	| GGCODE		    	|	 50 |  2500 |	 27   (0)| 00:00:01 |	    |	    |
|* 17 | 	   INDEX RANGE SCAN		  			| PK_GGCODE		    	|	 50 |	    |	  3   (0)| 00:00:01 |	    |	    |
|  18 | 	  PARTITION HASH ALL		  		|			    		|	117 | 13338 | 13926   (1)| 00:02:48 |	  1 |	  8 |
|* 19 | 	   TABLE ACCESS FULL		  		| GCREGISTPOLICY	    |	117 | 13338 | 13926   (1)| 00:02:48 |	  1 |	  8 |
|  20 | 	 TABLE ACCESS BY INDEX ROWID	  	| GGRISKCLASS 	    	|	  1 |	 14 |	  1   (0)| 00:00:01 |	    |	    |
|* 21 | 	  INDEX UNIQUE SCAN		  			| PK_GGRISKCLASS	    |	  1 |	    |	  0   (0)| 00:00:01 |	    |	    |
|  22 | 	TABLE ACCESS BY INDEX ROWID	  		| GGCOMPANY		    	|	  1 |	 54 |	  1   (0)| 00:00:01 |	    |	    |
|* 23 | 	 INDEX UNIQUE SCAN		  			| PK_GGCOMPANY	    	|	  1 |	    |	  0   (0)| 00:00:01 |	    |	    |
|* 24 |        TABLE ACCESS BY GLOBAL INDEX ROWID | GCREGISTMAIN	    |	  1 |	156 |	  2   (0)| 00:00:01 | ROWID | ROWID |
|* 25 | 	INDEX UNIQUE SCAN		  			| PK_GCREGISTMAIN	    |	  1 |	    |	  1   (0)| 00:00:01 |	    |	    |
|* 26 |       TABLE ACCESS BY GLOBAL INDEX ROWID  | GCCLAIMMAIN 	    |	914K|	 54M|	  2   (0)| 00:00:01 | ROWID | ROWID |
|* 27 |        INDEX UNIQUE SCAN		  		| INDEX_CLAIM_UNIQUECLAIM |	  1 |	    |	  1   (0)| 00:00:01 |	    |	    |
|  28 |      SORT UNIQUE			  			|			    		|	  2 |	260 |	 10  (20)| 00:00:01 |	    |	    |
|  29 |       UNION-ALL 			  			|			    		|	    |	    |		 |	    |	    |	    |
|* 30 |        TABLE ACCESS BY INDEX ROWID	  	| GCCANCELLATION	    |	  1 |	 30 |	  3   (0)| 00:00:01 |	    |	    |
|* 31 | 	INDEX RANGE SCAN		  			| PK_GCCANCELLATI	    |	  1 |	    |	  2   (0)| 00:00:01 |	    |	    |
|  32 |        NESTED LOOPS			  			|			    		|	  1 |	100 |	  5   (0)| 00:00:01 |	    |	    |
|* 33 | 	TABLE ACCESS BY GLOBAL INDEX ROWID	| GCCLAIMMAIN 	    	|	  1 |	 70 |	  3   (0)| 00:00:01 | ROWID | ROWID |
|* 34 | 	 INDEX UNIQUE SCAN		  			| INDEX_CLAIM_UNIQUECLAIM |	  1 |	    |	  2   (0)| 00:00:01 |	    |	    |
|* 35 | 	TABLE ACCESS BY INDEX ROWID	  		| GCCANCELLATION	    |	  1 |	 30 |	  2   (0)| 00:00:01 |	    |	    |
|* 36 | 	 INDEX RANGE SCAN		  			| PK_GCCANCELLATI	    |	  1 |	    |	  1   (0)| 00:00:01 |	    |	    |
|  37 |      SORT UNIQUE			  			|			    		|	  2 |	202 |	 10  (20)| 00:00:01 |	    |	    |
|  38 |       UNION-ALL 			  			|			    		|	    |	    |		 |	    |	    |	    |
|* 39 |        INDEX RANGE SCAN 		  		| PK_DELEGATEDETAIL	    |	  1 |	 49 |	  4   (0)| 00:00:01 |	    |	    |
|* 40 |        INDEX RANGE SCAN 		  		| PK_DELEGATEDETAIL	    |	  1 |	 52 |	  4   (0)| 00:00:01 |	    |	    |
-------------------------------------------------------------------------------------------------------------------------------------

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

   3 - filter(ROWNUM=1)
   4 - filter("C"."POLICYNO"=:B1 AND "C"."ITEMNO"=:B2 AND "C"."PLANCODE"=:B3)
   5 - access("C"."REGISTNO"=:B1 AND "C"."RISKCODE"=:B2)
   6 - filter(ROWNUM=1)
   7 - filter("C"."POLICYNO"=:B1 AND "C"."ITEMNO"=:B2 AND "C"."PLANCODE"=:B3)
   8 - access("C"."REGISTNO"=:B1 AND "C"."RISKCODE"=:B2)
  10 - filter( NOT EXISTS ( (SELECT /*+ NO_UNNEST */ 'X' FROM "GCCANCELLATION" "A" WHERE "A"."BUSINESSNO"=:B1 AND
	      "A"."BUSINESSTYPE"='regist' AND "A"."STATUS"<>'5')UNION (SELECT 'X' FROM "GCCLAIMMAIN" "B","GCCANCELLATION" "A" WHERE
	      "A"."BUSINESSNO"="B"."CLAIMNO" AND "A"."BUSINESSTYPE"='claim' AND "A"."STATUS"<>'5' AND "B"."RISKCODE"=:B2 AND
	      "B"."REGISTNO"=:B3 AND "B"."POLICYNO"=:B4)) AND  NOT EXISTS ( (SELECT /*+ NO_UNNEST */ 'x' FROM "GCDELEGATEDETAIL" "DEL"
	      WHERE "DEL"."POLICYNO"=:B5 AND "DEL"."REGISTNO"=:B6 AND "DEL"."RISKCODE"=:B7)UNION (SELECT 'x' FROM "GCDELEGATEDETAIL" "DEL"
	      WHERE "DEL"."POLICYNO"=:B8 AND "DEL"."REGISTNO"=:B9 AND "DEL"."RISKCODE"=:B10 AND "DEL"."TIMES"=1)))
  15 - access("GGCD"."CODECODE"="RP"."CHANNELDETAILCODE")
  17 - access("GGCD"."CODETYPE"='UnderWriteChannelTip')
  19 - filter(SUBSTR("RP"."RISKCODE",0,2)<>'08' AND SUBSTR("RP"."RISKCODE",0,2)<>'09' AND SUBSTR("RP"."RISKCODE",0,2)<>'16'
	      AND ("RP"."AUTORELATIONPOLICYFLAG" IS NULL OR "RP"."AUTORELATIONPOLICYFLAG"<>'1'))
  21 - access("GGR"."RISKCLASS"="RP"."RISKCLASS")
  23 - access("GGC"."COMPANYCODE"="RP"."COMPANYCODE")
  24 - filter("GCRM"."STATUS"='4')
  25 - access("GCRM"."REGISTNO"="RP"."REGISTNO")
  26 - filter(("A"."CASETYPE"='4' OR ("A"."ENDCASESTATUS"='1' OR "A"."ENDCASESTATUS"='3')) AND
	      "RP"."POLICYNO"="A"."POLICYNO" AND "RP"."PLANCODE"="A"."PLANCODE" AND "RP"."ITEMNO"="A"."ITEMNO")
  27 - access("RP"."REGISTNO"="A"."REGISTNO" AND "RP"."RISKCODE"="A"."RISKCODE")
  30 - filter("A"."BUSINESSTYPE"='regist' AND "A"."STATUS"<>'5')
  31 - access("A"."BUSINESSNO"=:B1)
  33 - filter("B"."POLICYNO"=:B1)
  34 - access("B"."REGISTNO"=:B1 AND "B"."RISKCODE"=:B2)
  35 - filter("A"."BUSINESSTYPE"='claim' AND "A"."STATUS"<>'5')
  36 - access("A"."BUSINESSNO"="B"."CLAIMNO")
  39 - access("DEL"."REGISTNO"=:B1 AND "DEL"."POLICYNO"=:B2 AND "DEL"."RISKCODE"=:B3)
       filter("DEL"."RISKCODE"=:B1)
  40 - access("DEL"."REGISTNO"=:B1 AND "DEL"."POLICYNO"=:B2 AND "DEL"."RISKCODE"=:B3 AND "DEL"."TIMES"=1)
       filter("DEL"."RISKCODE"=:B1 AND "DEL"."TIMES"=1)

Statistics
----------------------------------------------------------
	  0  recursive calls
	  0  db block gets
3662137  consistent gets (--这里有变化)
	  0  physical reads
	  0  redo size
	525  bytes sent via SQL*Net to client
	520  bytes received via SQL*Net from client
	  2  SQL*Net roundtrips to/from client
  21346  sorts (memory)(--这里有变化)
	  0  sorts (disk)
	  1  rows processed

优化前:11s，优化后5s;

3.总结

GGRISKCLASS 表优化前和前面的结果连接方式时NEST LOOP,优化后Hash Join.

排序内存和逻辑读都有减少，时间缩短为原理的1/2；由此可见，对于子查询展开和不展开执行效率不同，Oracle有时候也会选错执行计划。但是我们可以通过HINT调整SQL的执行计划，让它走指定的执行计划，从而优化SQL;

戒掉贪嗔痴(薛双奇)
关注
  • 8
    点赞
  • 踩
  • 7
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
专栏目录
unnest:非常快速地将嵌套数据结构扩展和堆叠到 R data.frames 中
05-29
描述是一个 0 依赖 R 包，用于非常快速、单次复制和单遍、分层数据结构的取消嵌套。 unnest(..)函数将分层列表作为输入...安装install.packages( " unnest " )# ordevtools :: install_github( " vspinu/unnest " )
firebase_block_v3
04-20
每个event_name都有嵌套的key-value对，例如event_params.key和event_params.value，这意味着每个查询都将需要一些复杂的UNNEST操作与user_properties键值对相同​​，都高度嵌套该积木为您做什么？ 创建一种选择...
Oracle三组难缠的hint no_unnest/unnest，push_subq，push_pred
jarry_gao的专栏
05-30 2794
Oracle三组难缠的hint no_unnest/unnest，push_subq，push_pred 2012-02-24   友情使欢欣倍增，使疾苦减半。经常有人把这三个hint搞混，主如果因为对三种重写道理不清楚。特总结如下。（实验景象为10204） 1. no_unnest， unnest unnest我们称为对子查询展开，顾名思义，就是别让子查询孤单地嵌套（ne
Oracle SQL调优系列之no_unnest和unnest用法简介
Nicky's blog
01-11 7176
Oracle调优之子查询嵌套 本博客介绍Oracle SQL调优的一种常用也是很实用的方法，也即/*+no_unnest */和/+ unnest/，介绍Oracle 的/*+unnest /、/+ no_unnest */ `之前，先介绍一下Hint。 Hint对于开发人员来说，可能不是很熟悉，但是对于DBA来说，Hint可是一种调优的利器，Hint 是Oracle 提供的一种SQL语法，是or...
（Oracle）SQL优化案例：大表hash连接优化
最新发布
但行好事，莫问前程
04-29 1574
大表与大表连接，且需查询大量数据的优化案例。本篇文章是将速度再10秒左右的SQL优化到1.5s左右；因为没有优化到1s以下，所以可能还存在更优优化方法；但其中涉及的优化技巧，可以供您赏析。
lightdb-no_unnest hint
紫无之紫的博客
02-03 316
Lightdb 从 23.1 版本开始支持 oracle 中的unnest/no_unnest hint。
CBO hint:no_unnest,push_subq,push_pred的用法
conghuan4367的博客
05-29 263
常常有人把这三个hint搞混，主要是因为对三种重写原理不清楚。特总结如下。（实验环境为10204） 1. no_unnest, unnest unnest我们称为对子查询展开，顾名思义，就是别让子查询孤单地嵌套(nest)在...
no_unnest与unnest
ciliting2867的博客
07-23 206
看了pub上一篇关于no_unnest的文章后学习了一番，照着做了一下测试，加深理解： unnest我们称为对子查询展开，顾名思义，就是别让子查询孤单地嵌套(nest)在里面。所以un_unnest双重否定代表肯定，即让子查询...
Oracle-Sql优化
weixin_42417531的博客
04-02 1344
本文结合索引数据结构、执行计划，给出一些sql优化的思路
tidyfast:快速有效的替代方法，可用于构建基于data.table #rdatatable #rstats的Tidyr函数
04-16
tidyfast v0.3.1 tidyfast的目标是在dplyr使用data.table为某些tidyr （和一些dplyr ）功能提供快速有效的替代方法。... 嵌套和dplyr::group_nest()嵌套（类似于dplyr::group_nest()和tidyr::unnest() ）： dt
unique-random:生成连续唯一的随机数
04-30
对于不需要连续两次显示同一张幻灯片的幻灯片显示很有用。 安装 $ npm install unique-random 用法 import uniqueRandom from 'unique-random' ; const random = uniqueRandom ( 1 , 10 ) ; console . log ( random ...
oracle sql优化分享案例
07-07
oracle sql案例的分享ppt，具体调优和改写sql的例子
崔华-基于Oracle的SQL优化典型案例.7z
04-22
崔华在DTCC2013 中国数据库大会，分享的关于Oracle SQL优化的典型案例分析，在应有系统开发初期，由于数据库数据较少，对于sql语句各种写法的编写体现不出sql的性能优劣，随着数据的不断增加，出现海量数据，劣质sql与优质sql在执行效率甚至存在百倍差距，文档中给出一些建设性建议。
基于案例的sql优化
10-16
基于案例的sql优化
性能优化案例分析与Oracle语句优化53个规则详解
05-20
内有两份文档：Oracle语句优化，数据库优化总结，SQL优化，常见优化案例分析技术及优化规则详解。(大公司内部资料)
oracle数据库性能优化案例分析及最佳实践
12-14
1 案例分析 案例一：Library cache lock等待 问题背景： 严重的Library cache lock等待，导致SQL执行的很慢 问题分析： Library cache lock等待常见场景： DDL、统计信息搜集 Namespace→1：table/view/sequence/synonym/ 错误密码登陆 Namespace→79：Account status FailureParse Namespace→82：SQL AREA BUILD ADG Namespace→74：DBINSTANCE 案例二：row cache lock等待 问题背景： 大量的row cac
pgsql-postal:PostgreSQL绑定libpostal
05-25
PostgreSQL邮政地址规范化器 动机 是一个C库，用于解析/规范世界各地的街道地址。 直接在PostgreSQL中拥有该功能可能会很有用。 此扩展用于此。 使用说明 由于支持JSONB，因此需要PostgreSQL 9.4及更高版本... unnest
Oracle表查询变换优化思路
xiaobinhxb的博客
08-12 849
Oracle表查询变换优化思路，包括子查询非嵌套、视图合并、谓词推入、让半连接和反连接作驱动表
sql优化实例oracle,Oracle性能优化-SQL优化(案例三)
weixin_42563415的博客
04-05 230
Oracle性能优化-SQL优化(案例三)问题：客户反馈取消记账耗时太长，10分钟也结束不了。问题分析：问题重现后，远程查看主要慢在一条查询SQL。查看执行计划gl_voucher大表全表扫描，实际上SQL中谓词字段上有特别高效的索引，怀疑统计信息不准确。查看gl_voucher统计信息记录行数0行，显然oracle认为扫描一个0行的表不需要走索引。SQL> Select * from us...
select ddp.sale_planid ,unnest(case when(ddp.plan_receiver_id is null or ddp.plan_receiver_id[1] is null) then ARRAY[-1]::integer[] else ddp.plan_receiver_id end) cabinet_rule_id_r from ( select dp.plan_receiver_id --unnest(case when dp.cabinet_rule_id is null or dp.cabinet_rule_id[1] is null then ARRAY[-1]::integer[] else dp.cabinet_rule_id end ) cabinet_rule_id_r ,unnest(case when (dp.next_plan_id is null or dp.next_plan_id[1] is null) then ARRAY[-1]::integer[] else dp.next_plan_id end) as sale_planid --销地计划 from ods.ods_durian_delivery_plan AS dp where dp.type='ORIGIN') ddpSQL纠错
06-15
这段 SQL 语句存在语法错误。应该将 `CASE` 表达式的 `THEN` 和 `ELSE` 子句中的 `ARRAY[-1]::integer[]` 改为 `ARRAY[-1]`，同时在 `FROM` 子句中给子查询指定一个别名。修改后的 SQL 语句如下： ``` SELECT ddp.sale_planid, unnest(CASE WHEN (ddp.plan_receiver_id IS NULL OR ddp.plan_receiver_id[1] IS NULL) THEN ARRAY[-1] ELSE ddp.plan_receiver_id END) AS cabinet_rule_id_r FROM ( SELECT dp.plan_receiver_id, unnest(CASE WHEN (dp.next_plan_id IS NULL OR dp.next_plan_id[1] IS NULL) THEN ARRAY[-1] ELSE dp.next_plan_id END) AS sale_planid FROM ods.ods_durian_delivery_plan AS dp WHERE dp.type = 'ORIGIN' ) ddp; ```

“相关推荐”对你有帮助么？

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值