【SQL優化】有場景有資料，採納者贈送全部pub幣，速進
訪問連結：http://www.itpub.net/thread-1872679-1-1.html
場景：有3張金融表，求出符合要求的結果集，已有SQL，請教能否在優化，採納者贈送全部pub幣！
合同金額表 finances_sum
IID主鍵 COPERATIONID合同業務ID CURRENCYKINDCODE幣種 validityflag資料有效狀態標誌
183 61 ENU 2
184 62 CNY 2
185 62 CNY 1
186 62 ENU 1
187 61 CNY 1
188 61 ENU 1
189 60 CNY 1
190 59 CNY 1
191 58 CNY 1
192 57 CNY 1
合同表 finances_con
IID主鍵 COPERATIONID合同業務ID contractinuredate合同生效日期 contractpausedate合同終止日期 validityflag資料有效狀態標誌
200 62 2014-1-1 2014-12-31 1
201 61 2014-1-1 2014-12-31 1
202 60 2014-1-1 2014-12-31 1
203 59 2014-1-1 2014-12-31 1
204 58 2014-1-1 2014-12-31 1
205 57 2010-3-8 2010-5-8 1
合同租金錶 finances_ren
IID主鍵 COPERATIONID合同業務ID RENTALSUM本期實還金額 validityflag資料有效狀態標誌
298 61 500000 2
299 62 500000 2
300 62 500000 1
301 62 400000 1
302 62 300000 1
303 61 500000 1
304 61 400000 1
305 61 300000 1
306 60 50 1
307 60 40 1
308 60 30 1
309 59 300000 1
310 59 200000 1
311 59 100000 1
312 58 400000 1
313 58 10000 1
314 58 10000 1
315 57 500 1
316 57 400 1
317 57 300 1
318 57 100 1
【問】
想得到的一個結果集,前4個欄位是合同金額表finances_sum的，後2個欄位是合同表finances_con的，最後1個欄位是合同租金錶finances_ren的
IID主鍵 COPERATIONID合同業務ID CURRENCYKINDCODE幣種 validityflag資料有效狀態標誌 contractinuredate合同生效日期 contractpausedate合同終止日期 MAX(RENTALSUM本期實還金額)
185 62 CNY 1 2014-1-1 2014-12-31 500000
186 62 ENU 1 2014-1-1 2014-12-31 500000
187 61 CNY 1 2014-1-1 2014-12-31 500000
188 61 ENU 1 2014-1-1 2014-12-31 500000
189 60 CNY 1 2014-1-1 2014-12-31 50
190 59 CNY 1 2014-1-1 2014-12-31 300000
191 58 CNY 1 2014-1-1 2014-12-31 400000
192 57 CNY 1 2010-3-8 2010-5-8 500
【答】
根據上述要求，分2條語句來實現
第1條SQL，前6個欄位結果集，涉及到合同金額表finances_sum和合同表finances_con
select a.*,b.contractinuredate,b.contractpausedate from finances_sum a,finances_con b where a.validityflag = 1 and a.coperationid=b.coperationid;
IID主鍵 COPERATIONID合同業務ID CURRENCYKINDCODE幣種 validityflag資料有效狀態標誌 contractinuredate合同生效日期 contractpausedate合同終止日期
185 62 CNY 1 2014-1-1 2014-12-31
186 62 ENU 1 2014-1-1 2014-12-31
187 61 CNY 1 2014-1-1 2014-12-31
188 61 ENU 1 2014-1-1 2014-12-31
189 60 CNY 1 2014-1-1 2014-12-31
190 59 CNY 1 2014-1-1 2014-12-31
191 58 CNY 1 2014-1-1 2014-12-31
192 57 CNY 1 2010-3-8 2010-5-8
第2條SQL，後面求“在合同業務ID中本期實還金額最大的記錄”，涉及到合同租金錶finances_ren
select coperationid,max(rentalsum) from finances_ren where validityflag =1 group by coperationid;
IID主鍵 COPERATIONID合同業務ID RENTALSUM本期實還金額 validityflag資料有效狀態標誌
300 62 500000 1
303 61 500000 1
306 60 50 1
309 59 300000 1
312 58 400000 1
315 57 500 1
【終問】現在想把上述這2條SQL合併成1條SQL來實現，具體如何操作
重現問題指令碼（包括建表和插入資料程式碼）
####################################################################################
--簡化版重現問題模型，建表和構建資料指令碼如下所示
--合同金額表 finances_sum
drop table finances_sum cascade constraints;
/*==============================================================*/
/* Table: finances_sum */
/*==============================================================*/
create table finances_sum (
IID NUMBER(20) not null,
COPERATIONID NUMBER(20),
CURRENCYKINDCODE VARCHAR2(5),
VALIDITYFLAG VARCHAR2(5),
constraint pk_finances_sum primary key (IID)
);
insert into finances_sum values(183,61,'ENU','2');
insert into finances_sum values(184,62,'CNY','2');
insert into finances_sum values(185,62,'CNY','1');
insert into finances_sum values(186,62,'ENU','1');
insert into finances_sum values(187,61,'CNY','1');
insert into finances_sum values(188,61,'ENU','1');
insert into finances_sum values(189,60,'CNY','1');
insert into finances_sum values(190,59,'CNY','1');
insert into finances_sum values(191,58,'CNY','1');
insert into finances_sum values(192,57,'CNY','1');
commit;
select * from finances_sum;
IID主鍵 COPERATIONID合同業務ID CURRENCYKINDCODE幣種 validityflag資料有效狀態標誌
183 61 ENU 2
184 62 CNY 2
185 62 CNY 1
186 62 ENU 1
187 61 CNY 1
188 61 ENU 1
189 60 CNY 1
190 59 CNY 1
191 58 CNY 1
192 57 CNY 1
--合同表 finances_con
drop table finances_con cascade constraints;
/*==============================================================*/
/* Table: finances_con */
/*==============================================================*/
create table finances_con (
IID NUMBER(20) not null,
COPERATIONID NUMBER(20),
CONTRACTINUREDATE DATE,
CONTRACTPAUSEDATE DATE,
VALIDITYFLAG VARCHAR2(5),
constraint pk_finances_con primary key (IID)
);
insert into finances_con values(200,62,to_date('2014-1-1', 'yyyy-mm-dd'),to_date('2014-12-31', 'yyyy-mm-dd'),'1');
insert into finances_con values(201,61,to_date('2014-1-1', 'yyyy-mm-dd'),to_date('2014-12-31', 'yyyy-mm-dd'),'1');
insert into finances_con values(202,60,to_date('2014-1-1', 'yyyy-mm-dd'),to_date('2014-12-31', 'yyyy-mm-dd'),'1');
insert into finances_con values(203,59,to_date('2014-1-1', 'yyyy-mm-dd'),to_date('2014-12-31', 'yyyy-mm-dd'),'1');
insert into finances_con values(204,58,to_date('2014-1-1', 'yyyy-mm-dd'),to_date('2014-12-31', 'yyyy-mm-dd'),'1');
insert into finances_con values(205,57,to_date('2010-3-8', 'yyyy-mm-dd'),to_date('2010-5-8', 'yyyy-mm-dd'),'1');
commit;
select * from finances_con;
IID主鍵 COPERATIONID合同業務ID contractinuredate合同生效日期 contractpausedate合同終止日期 validityflag資料有效狀態標誌
200 62 2014-1-1 2014-12-31 1
201 61 2014-1-1 2014-12-31 1
202 60 2014-1-1 2014-12-31 1
203 59 2014-1-1 2014-12-31 1
204 58 2014-1-1 2014-12-31 1
205 57 2010-3-8 2010-5-8 1
--合同租金錶 finances_ren
drop table finances_ren cascade constraints;
/*==============================================================*/
/* Table: finances_ren */
/*==============================================================*/
create table finances_ren (
IID NUMBER(20) not null,
COPERATIONID NUMBER(20),
RENTALSUM VARCHAR2(20),
VALIDITYFLAG VARCHAR2(5),
constraint pk_finances_ren primary key (IID)
);
insert into finances_ren values(298,61,'500000','2');
insert into finances_ren values(299,62,'500000','2');
insert into finances_ren values(300,62,'500000','1');
insert into finances_ren values(301,62,'400000','1');
insert into finances_ren values(302,62,'300000','1');
insert into finances_ren values(303,61,'500000','1');
insert into finances_ren values(304,61,'400000','1');
insert into finances_ren values(305,61,'300000','1');
insert into finances_ren values(306,60,'50','1');
insert into finances_ren values(307,60,'40','1');
insert into finances_ren values(308,60,'30','1');
insert into finances_ren values(309,59,'300000','1');
insert into finances_ren values(310,59,'200000','1');
insert into finances_ren values(311,59,'100000','1');
insert into finances_ren values(312,58,'400000','1');
insert into finances_ren values(313,58,'10000','1');
insert into finances_ren values(314,58,'10000','1');
insert into finances_ren values(315,57,'500','1');
insert into finances_ren values(316,57,'400','1');
insert into finances_ren values(317,57,'300','1');
insert into finances_ren values(318,57,'100','1');
commit;
select * from finances_ren;
IID主鍵 COPERATIONID合同業務ID RENTALSUM本期實還金額 validityflag資料有效狀態標誌
298 61 500000 2
299 62 500000 2
300 62 500000 1
301 62 400000 1
302 62 300000 1
303 61 500000 1
304 61 400000 1
305 61 300000 1
306 60 50 1
307 60 40 1
308 60 30 1
309 59 300000 1
310 59 200000 1
311 59 100000 1
312 58 400000 1
313 58 10000 1
314 58 10000 1
315 57 500 1
316 57 400 1
317 57 300 1
318 57 100 1
####################################################################################
答案：
1.select a.*,b.contractinuredate,b.contractpausedate,c.rentalsum
from finances_sum a,finances_con b,(select coperationid,max(rentalsum) rentalsum from finances_ren where validityflag =1 group by coperationid) c
where a.validityflag = 1 and a.coperationid=b.coperationid and a.coperationid=c.coperationid;
小結：資料量c表是b表的6倍，b表是a表的2倍，請見執行計劃，c表的肯定在a表中有，a表的不一定在c表中有，因此一般group by操作的可能性大一點。當資料量上來後後續就可以新增索引來測試了
Execution Plan
----------------------------------------------------------
Plan hash value: 3900923520
--------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 4 | 356 | 11 (19)| 00:00:01 |
|* 1 | HASH JOIN | | 4 | 356 | 11 (19)| 00:00:01 |
|* 2 | HASH JOIN | | 4 | 280 | 8 (25)| 00:00:01 |
| 3 | VIEW | | 4 | 100 | 4 (25)| 00:00:01 |
| 4 | HASH GROUP BY | | 4 | 56 | 4 (25)| 00:00:01 |
|* 5 | TABLE ACCESS FULL| EB_FINANCINGLEASE_RENTAL | 8 | 112 | 3 (0)| 00:00:01 |
|* 6 | TABLE ACCESS FULL | EB_FINANCINGLEASE_CONSUM | 6 | 270 | 3 (0)| 00:00:01 |
| 7 | TABLE ACCESS FULL | EB_FINANCINGLEASE_CON | 6 | 114 | 3 (0)| 00:00:01 |
--------------------------------------------------------------------------------------------------
2.select a.iid,a.COPERATIONID,a.CURRENCYKINDCODE,a.VALIDITYFLAG,b.contractinuredate,b.contractpausedate,max(c.rentalsum) max_rentalsum from finances_sum a,finances_con b,finances_ren c
where a.coperationid = b.coperationid
and a.coperationid = c.coperationid
and a.validityflag = '1'
and c.validityflag = '1'
group by a.iid,a.COPERATIONID,a.CURRENCYKINDCODE,a.VALIDITYFLAG,b.contractinuredate,b.contractpausedate order by 1;
Execution Plan
----------------------------------------------------------
Plan hash value: 1206426329
-------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 23 | 989 | 11 (19)| 00:00:01 |
| 1 | SORT GROUP BY | | 23 | 989 | 11 (19)| 00:00:01 |
|* 2 | HASH JOIN | | 23 | 989 | 10 (10)| 00:00:01 |
|* 3 | HASH JOIN | | 8 | 256 | 7 (15)| 00:00:01 |
| 4 | TABLE ACCESS FULL| FINANCES_CON | 6 | 114 | 3 (0)| 00:00:01 |
|* 5 | TABLE ACCESS FULL| FINANCES_SUM | 8 | 104 | 3 (0)| 00:00:01 |
|* 6 | TABLE ACCESS FULL | FINANCES_REN | 19 | 209 | 3 (0)| 00:00:01 |
3.select t1.iid,t1.COPERATIONID,t1.CURRENCYKINDCODE,t1.VALIDITYFLAG,t1.contractinuredate,t1.contractpausedate,t2.max_rentalsum from
(select a.iid,a.COPERATIONID,a.CURRENCYKINDCODE,a.VALIDITYFLAG,b.contractinuredate,b.contractpausedate from finances_sum a, finances_con b where a.validityflag = '1' and a.coperationid = b.coperationid) t1,
(select coperationid,max(rentalsum) max_rentalsum from finances_ren where validityflag = '1' group by coperationid) t2 where t1.coperationid = t2.coperationid order by t1.iid;
Execution Plan
----------------------------------------------------------
Plan hash value: 1206426329
-------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 23 | 1104 | 11 (19)| 00:00:01 |
| 1 | SORT GROUP BY | | 23 | 1104 | 11 (19)| 00:00:01 |
|* 2 | HASH JOIN | | 23 | 1104 | 10 (10)| 00:00:01 |
|* 3 | HASH JOIN | | 8 | 296 | 7 (15)| 00:00:01 |
| 4 | TABLE ACCESS FULL| FINANCES_CON | 6 | 144 | 3 (0)| 00:00:01 |
|* 5 | TABLE ACCESS FULL| FINANCES_SUM | 8 | 104 | 3 (0)| 00:00:01 |
|* 6 | TABLE ACCESS FULL | FINANCES_REN | 19 | 209 | 3 (0)| 00:00:01 |
-------------------------------------------------------------------------------------
Leonarding劉盛
2014.06.18
北京&summer
分享技術~成就夢想
Blog：www.leonarding.com
場景：有3張金融表，求出符合要求的結果集，已有SQL，請教能否在優化，採納者贈送全部pub幣！
合同金額表 finances_sum
IID主鍵 COPERATIONID合同業務ID CURRENCYKINDCODE幣種 validityflag資料有效狀態標誌
183 61 ENU 2
184 62 CNY 2
185 62 CNY 1
186 62 ENU 1
187 61 CNY 1
188 61 ENU 1
189 60 CNY 1
190 59 CNY 1
191 58 CNY 1
192 57 CNY 1
合同表 finances_con
IID主鍵 COPERATIONID合同業務ID contractinuredate合同生效日期 contractpausedate合同終止日期 validityflag資料有效狀態標誌
200 62 2014-1-1 2014-12-31 1
201 61 2014-1-1 2014-12-31 1
202 60 2014-1-1 2014-12-31 1
203 59 2014-1-1 2014-12-31 1
204 58 2014-1-1 2014-12-31 1
205 57 2010-3-8 2010-5-8 1
合同租金錶 finances_ren
IID主鍵 COPERATIONID合同業務ID RENTALSUM本期實還金額 validityflag資料有效狀態標誌
298 61 500000 2
299 62 500000 2
300 62 500000 1
301 62 400000 1
302 62 300000 1
303 61 500000 1
304 61 400000 1
305 61 300000 1
306 60 50 1
307 60 40 1
308 60 30 1
309 59 300000 1
310 59 200000 1
311 59 100000 1
312 58 400000 1
313 58 10000 1
314 58 10000 1
315 57 500 1
316 57 400 1
317 57 300 1
318 57 100 1
【問】
想得到的一個結果集,前4個欄位是合同金額表finances_sum的，後2個欄位是合同表finances_con的，最後1個欄位是合同租金錶finances_ren的
IID主鍵 COPERATIONID合同業務ID CURRENCYKINDCODE幣種 validityflag資料有效狀態標誌 contractinuredate合同生效日期 contractpausedate合同終止日期 MAX(RENTALSUM本期實還金額)
185 62 CNY 1 2014-1-1 2014-12-31 500000
186 62 ENU 1 2014-1-1 2014-12-31 500000
187 61 CNY 1 2014-1-1 2014-12-31 500000
188 61 ENU 1 2014-1-1 2014-12-31 500000
189 60 CNY 1 2014-1-1 2014-12-31 50
190 59 CNY 1 2014-1-1 2014-12-31 300000
191 58 CNY 1 2014-1-1 2014-12-31 400000
192 57 CNY 1 2010-3-8 2010-5-8 500
【答】
根據上述要求，分2條語句來實現
第1條SQL，前6個欄位結果集，涉及到合同金額表finances_sum和合同表finances_con
select a.*,b.contractinuredate,b.contractpausedate from finances_sum a,finances_con b where a.validityflag = 1 and a.coperationid=b.coperationid;
IID主鍵 COPERATIONID合同業務ID CURRENCYKINDCODE幣種 validityflag資料有效狀態標誌 contractinuredate合同生效日期 contractpausedate合同終止日期
185 62 CNY 1 2014-1-1 2014-12-31
186 62 ENU 1 2014-1-1 2014-12-31
187 61 CNY 1 2014-1-1 2014-12-31
188 61 ENU 1 2014-1-1 2014-12-31
189 60 CNY 1 2014-1-1 2014-12-31
190 59 CNY 1 2014-1-1 2014-12-31
191 58 CNY 1 2014-1-1 2014-12-31
192 57 CNY 1 2010-3-8 2010-5-8
第2條SQL，後面求“在合同業務ID中本期實還金額最大的記錄”，涉及到合同租金錶finances_ren
select coperationid,max(rentalsum) from finances_ren where validityflag =1 group by coperationid;
IID主鍵 COPERATIONID合同業務ID RENTALSUM本期實還金額 validityflag資料有效狀態標誌
300 62 500000 1
303 61 500000 1
306 60 50 1
309 59 300000 1
312 58 400000 1
315 57 500 1
【終問】現在想把上述這2條SQL合併成1條SQL來實現，具體如何操作
重現問題指令碼（包括建表和插入資料程式碼）
####################################################################################
--簡化版重現問題模型，建表和構建資料指令碼如下所示
--合同金額表 finances_sum
drop table finances_sum cascade constraints;
/*==============================================================*/
/* Table: finances_sum */
/*==============================================================*/
create table finances_sum (
IID NUMBER(20) not null,
COPERATIONID NUMBER(20),
CURRENCYKINDCODE VARCHAR2(5),
VALIDITYFLAG VARCHAR2(5),
constraint pk_finances_sum primary key (IID)
);
insert into finances_sum values(183,61,'ENU','2');
insert into finances_sum values(184,62,'CNY','2');
insert into finances_sum values(185,62,'CNY','1');
insert into finances_sum values(186,62,'ENU','1');
insert into finances_sum values(187,61,'CNY','1');
insert into finances_sum values(188,61,'ENU','1');
insert into finances_sum values(189,60,'CNY','1');
insert into finances_sum values(190,59,'CNY','1');
insert into finances_sum values(191,58,'CNY','1');
insert into finances_sum values(192,57,'CNY','1');
commit;
select * from finances_sum;
IID主鍵 COPERATIONID合同業務ID CURRENCYKINDCODE幣種 validityflag資料有效狀態標誌
183 61 ENU 2
184 62 CNY 2
185 62 CNY 1
186 62 ENU 1
187 61 CNY 1
188 61 ENU 1
189 60 CNY 1
190 59 CNY 1
191 58 CNY 1
192 57 CNY 1
--合同表 finances_con
drop table finances_con cascade constraints;
/*==============================================================*/
/* Table: finances_con */
/*==============================================================*/
create table finances_con (
IID NUMBER(20) not null,
COPERATIONID NUMBER(20),
CONTRACTINUREDATE DATE,
CONTRACTPAUSEDATE DATE,
VALIDITYFLAG VARCHAR2(5),
constraint pk_finances_con primary key (IID)
);
insert into finances_con values(200,62,to_date('2014-1-1', 'yyyy-mm-dd'),to_date('2014-12-31', 'yyyy-mm-dd'),'1');
insert into finances_con values(201,61,to_date('2014-1-1', 'yyyy-mm-dd'),to_date('2014-12-31', 'yyyy-mm-dd'),'1');
insert into finances_con values(202,60,to_date('2014-1-1', 'yyyy-mm-dd'),to_date('2014-12-31', 'yyyy-mm-dd'),'1');
insert into finances_con values(203,59,to_date('2014-1-1', 'yyyy-mm-dd'),to_date('2014-12-31', 'yyyy-mm-dd'),'1');
insert into finances_con values(204,58,to_date('2014-1-1', 'yyyy-mm-dd'),to_date('2014-12-31', 'yyyy-mm-dd'),'1');
insert into finances_con values(205,57,to_date('2010-3-8', 'yyyy-mm-dd'),to_date('2010-5-8', 'yyyy-mm-dd'),'1');
commit;
select * from finances_con;
IID主鍵 COPERATIONID合同業務ID contractinuredate合同生效日期 contractpausedate合同終止日期 validityflag資料有效狀態標誌
200 62 2014-1-1 2014-12-31 1
201 61 2014-1-1 2014-12-31 1
202 60 2014-1-1 2014-12-31 1
203 59 2014-1-1 2014-12-31 1
204 58 2014-1-1 2014-12-31 1
205 57 2010-3-8 2010-5-8 1
--合同租金錶 finances_ren
drop table finances_ren cascade constraints;
/*==============================================================*/
/* Table: finances_ren */
/*==============================================================*/
create table finances_ren (
IID NUMBER(20) not null,
COPERATIONID NUMBER(20),
RENTALSUM VARCHAR2(20),
VALIDITYFLAG VARCHAR2(5),
constraint pk_finances_ren primary key (IID)
);
insert into finances_ren values(298,61,'500000','2');
insert into finances_ren values(299,62,'500000','2');
insert into finances_ren values(300,62,'500000','1');
insert into finances_ren values(301,62,'400000','1');
insert into finances_ren values(302,62,'300000','1');
insert into finances_ren values(303,61,'500000','1');
insert into finances_ren values(304,61,'400000','1');
insert into finances_ren values(305,61,'300000','1');
insert into finances_ren values(306,60,'50','1');
insert into finances_ren values(307,60,'40','1');
insert into finances_ren values(308,60,'30','1');
insert into finances_ren values(309,59,'300000','1');
insert into finances_ren values(310,59,'200000','1');
insert into finances_ren values(311,59,'100000','1');
insert into finances_ren values(312,58,'400000','1');
insert into finances_ren values(313,58,'10000','1');
insert into finances_ren values(314,58,'10000','1');
insert into finances_ren values(315,57,'500','1');
insert into finances_ren values(316,57,'400','1');
insert into finances_ren values(317,57,'300','1');
insert into finances_ren values(318,57,'100','1');
commit;
select * from finances_ren;
IID主鍵 COPERATIONID合同業務ID RENTALSUM本期實還金額 validityflag資料有效狀態標誌
298 61 500000 2
299 62 500000 2
300 62 500000 1
301 62 400000 1
302 62 300000 1
303 61 500000 1
304 61 400000 1
305 61 300000 1
306 60 50 1
307 60 40 1
308 60 30 1
309 59 300000 1
310 59 200000 1
311 59 100000 1
312 58 400000 1
313 58 10000 1
314 58 10000 1
315 57 500 1
316 57 400 1
317 57 300 1
318 57 100 1
####################################################################################
答案：
1.select a.*,b.contractinuredate,b.contractpausedate,c.rentalsum
from finances_sum a,finances_con b,(select coperationid,max(rentalsum) rentalsum from finances_ren where validityflag =1 group by coperationid) c
where a.validityflag = 1 and a.coperationid=b.coperationid and a.coperationid=c.coperationid;
小結：資料量c表是b表的6倍，b表是a表的2倍，請見執行計劃，c表的肯定在a表中有，a表的不一定在c表中有，因此一般group by操作的可能性大一點。當資料量上來後後續就可以新增索引來測試了
Execution Plan
----------------------------------------------------------
Plan hash value: 3900923520
--------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 4 | 356 | 11 (19)| 00:00:01 |
|* 1 | HASH JOIN | | 4 | 356 | 11 (19)| 00:00:01 |
|* 2 | HASH JOIN | | 4 | 280 | 8 (25)| 00:00:01 |
| 3 | VIEW | | 4 | 100 | 4 (25)| 00:00:01 |
| 4 | HASH GROUP BY | | 4 | 56 | 4 (25)| 00:00:01 |
|* 5 | TABLE ACCESS FULL| EB_FINANCINGLEASE_RENTAL | 8 | 112 | 3 (0)| 00:00:01 |
|* 6 | TABLE ACCESS FULL | EB_FINANCINGLEASE_CONSUM | 6 | 270 | 3 (0)| 00:00:01 |
| 7 | TABLE ACCESS FULL | EB_FINANCINGLEASE_CON | 6 | 114 | 3 (0)| 00:00:01 |
--------------------------------------------------------------------------------------------------
2.select a.iid,a.COPERATIONID,a.CURRENCYKINDCODE,a.VALIDITYFLAG,b.contractinuredate,b.contractpausedate,max(c.rentalsum) max_rentalsum from finances_sum a,finances_con b,finances_ren c
where a.coperationid = b.coperationid
and a.coperationid = c.coperationid
and a.validityflag = '1'
and c.validityflag = '1'
group by a.iid,a.COPERATIONID,a.CURRENCYKINDCODE,a.VALIDITYFLAG,b.contractinuredate,b.contractpausedate order by 1;
Execution Plan
----------------------------------------------------------
Plan hash value: 1206426329
-------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 23 | 989 | 11 (19)| 00:00:01 |
| 1 | SORT GROUP BY | | 23 | 989 | 11 (19)| 00:00:01 |
|* 2 | HASH JOIN | | 23 | 989 | 10 (10)| 00:00:01 |
|* 3 | HASH JOIN | | 8 | 256 | 7 (15)| 00:00:01 |
| 4 | TABLE ACCESS FULL| FINANCES_CON | 6 | 114 | 3 (0)| 00:00:01 |
|* 5 | TABLE ACCESS FULL| FINANCES_SUM | 8 | 104 | 3 (0)| 00:00:01 |
|* 6 | TABLE ACCESS FULL | FINANCES_REN | 19 | 209 | 3 (0)| 00:00:01 |
3.select t1.iid,t1.COPERATIONID,t1.CURRENCYKINDCODE,t1.VALIDITYFLAG,t1.contractinuredate,t1.contractpausedate,t2.max_rentalsum from
(select a.iid,a.COPERATIONID,a.CURRENCYKINDCODE,a.VALIDITYFLAG,b.contractinuredate,b.contractpausedate from finances_sum a, finances_con b where a.validityflag = '1' and a.coperationid = b.coperationid) t1,
(select coperationid,max(rentalsum) max_rentalsum from finances_ren where validityflag = '1' group by coperationid) t2 where t1.coperationid = t2.coperationid order by t1.iid;
Execution Plan
----------------------------------------------------------
Plan hash value: 1206426329
-------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 23 | 1104 | 11 (19)| 00:00:01 |
| 1 | SORT GROUP BY | | 23 | 1104 | 11 (19)| 00:00:01 |
|* 2 | HASH JOIN | | 23 | 1104 | 10 (10)| 00:00:01 |
|* 3 | HASH JOIN | | 8 | 296 | 7 (15)| 00:00:01 |
| 4 | TABLE ACCESS FULL| FINANCES_CON | 6 | 144 | 3 (0)| 00:00:01 |
|* 5 | TABLE ACCESS FULL| FINANCES_SUM | 8 | 104 | 3 (0)| 00:00:01 |
|* 6 | TABLE ACCESS FULL | FINANCES_REN | 19 | 209 | 3 (0)| 00:00:01 |
-------------------------------------------------------------------------------------
Leonarding劉盛
2014.06.18
北京&summer
分享技術~成就夢想
Blog：www.leonarding.com
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/26686207/viewspace-1186391/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- 圖資料庫有哪些應用場景?資料庫
- MySQL資料SQL優化中，索引不被使用的典型場景總結MySql優化索引
- 資料庫效能優化有哪些方式資料庫優化
- 資料庫效能優化有哪些措施?資料庫優化
- Oracle資料的優化器有兩種優化方法：Oracle優化
- js裝飾者模式有哪些應用場景JS模式
- 有關效能調整的查詢和pub上的一個sql調優！SQL
- SAP MM 進口採購業務中供應商多送或者少送場景的處理
- 資料庫優化 - SQL優化資料庫優化SQL
- 大資料視覺化有哪些優勢大資料視覺化
- 薪資福利上漲龜速——大資料從業者有話說大資料
- 資料採集的方法有哪些
- 改進資料庫效能-SQL查詢優化資料庫SQL優化
- 大資料視覺化有哪些優勢呢？大資料視覺化
- 美顏SDK有哪些優勢？可以應用到哪些場景？
- Redis有哪些常用使用場景?Redis
- 優化資料~已經整理成了pdf檔案，pub上的好的優化優化
- 資料庫優化SQL資料庫優化SQL
- sql海量資料優化SQL優化
- 畫江湖之SQL優化 -10大經典案例場景SQL優化
- 不使用 MQ 如何實現 pub/sub 場景？MQ
- 業務場景下資料採集機制和策略
- MySQL在大資料、高併發場景下的SQL語句優化和"最佳實踐"MySql大資料優化
- PostgreSQL秒殺場景優化SQL優化
- 大資料視覺化平臺有哪些優勢大資料視覺化
- 目標檢測資料集，全部有標註
- etcd 在超大規模資料場景下的效能優化優化
- 數字化轉型辦公室的運用場景有哪些？
- 閒魚Flutter圖片框架架構演進（超詳細）底部有實戰書籍贈送Flutter框架架構
- 虛引用的使用場景有哪些？
- MYSQL資料庫------SQL優化MySql資料庫優化
- 資料庫及SQL優化資料庫SQL優化
- SQL Server2000有自動定期對變化table進行update statistics優化 ?SQLServer優化
- MySQL有哪些儲存引擎，各自的優缺點，應用場景MySql儲存引擎
- 大資料渠道有哪些優勢大資料
- 資料庫效能優化之SQL語句優化資料庫優化SQL
- 區塊鏈應用場景有哪些？區塊鏈
- 雲資料庫MySQL有什麼作用？有哪些優點？資料庫MySql