为什么后边的语句少一层子查询,但是执行计划相同?physical reads统计信息还比前边的差?
请教更好的写法?又是行列转换,我不太会用分析函数.争取这帖以后不再问
select substr(industry_name,1,20) 行业名称, c.*
from(
select 行业代码,sum(a1),sum(a2),sum(a3),sum(b1),sum(b2),sum(b3),sum(c1),sum(c2),sum(c3) from
(
select 行业代码,
decode(注册类型码,'1',营业利润率,null) A1,
decode(注册类型码,'2',营业利润率,null) A2,
decode(注册类型码,'3',营业利润率,null) A3,
decode(注册类型码,'1',主营业务利润率,null) B1,
decode(注册类型码,'2',主营业务利润率,null) B2,
decode(注册类型码,'3',主营业务利润率,null) B3,
decode(注册类型码,'1',营业成本比率,null) C1,
decode(注册类型码,'2',营业成本比率,null) C2,
decode(注册类型码,'3',营业成本比率,null) C3
from(
select
substr(z01_064,1,2) 行业代码,substr(z01_08,1,1) 注册类型码,
round(sum(b04_45)/sum(b04_29)*100,2) 营业利润率,
round(sum(b04_29-b04_30-b04_31)/sum(b04_29)*100,2) 主营业务利润率,
round(sum(b04_34+b04_35+b04_43)/sum(b04_29)*100,2) 营业成本比率
from cj601 a,cj604 b where a.uuid=b.uuid
group by substr(z01_064,1,2),substr(z01_08,1,1)
))group by 行业代码
)c,industry
where industry_code=行业代码;
已选择39行。
Execution Plan
----------------------------------------------------------
0SELECT STATEMENT Optimizer=CHOOSE
10 NESTED LOOPS
21 VIEW
32 SORT (GROUP BY)
43 VIEW
54 SORT (GROUP BY)
65
NESTED LOOPS
76
TABLE ACCESS (FULL) OF 'CJ604'
86
TABLE ACCESS (BY INDEX ROWID) OF 'CJ601'
98
INDEX (UNIQUE SCAN) OF 'CJ601_UI' (UNIQUE)
101 TABLE ACCESS (BY INDEX ROWID) OF 'INDUSTRY'
11 10 INDEX (UNIQUE SCAN) OF 'SYS_C002819' (UNIQUE)
Statistics
----------------------------------------------------------
0recursive calls
0db block gets
168251consistent gets
3192physical reads
0redo size
3491bytes sent via SQL*Net to client
525bytes received via SQL*Net from client
4SQL*Net roundtrips to/from client
2sorts (memory)
0sorts (disk)
39rows processed
-- 2----
select substr(industry_name,1,20) 行业名称, c.*
from(
select 行业代码,
sum(decode(注册类型码,'1',营业利润率,null)) A1,
sum(decode(注册类型码,'2',营业利润率,null)) A2,
sum(decode(注册类型码,'3',营业利润率,null)) A3,
sum(decode(注册类型码,'1',主营业务利润率,null)) B1,
sum(decode(注册类型码,'2',主营业务利润率,null)) B2,
sum(decode(注册类型码,'3',主营业务利润率,null)) B3,
sum(decode(注册类型码,'1',营业成本比率,null)) C1,
sum(decode(注册类型码,'2',营业成本比率,null)) C2,
sum(decode(注册类型码,'3',营业成本比率,null)) C3
from(
select
substr(z01_064,1,2) 行业代码,substr(z01_08,1,1) 注册类型码,
round(sum(b04_45)/sum(b04_29)*100,2) 营业利润率,
round(sum(b04_29-b04_30-b04_31)/sum(b04_29)*100,2) 主营业务利润率,
round(sum(b04_34+b04_35+b04_43)/sum(b04_29)*100,2) 营业成本比率
from cj601 a,cj604 b where a.uuid=b.uuid
group by substr(z01_064,1,2),substr(z01_08,1,1)
)group by 行业代码
)c,industry
where industry_code=行业代码;
Execution Plan
----------------------------------------------------------
0SELECT STATEMENT Optimizer=CHOOSE
10 NESTED LOOPS
21 VIEW
32 SORT (GROUP BY)
43 VIEW
54 SORT (GROUP BY)
65
NESTED LOOPS
76
TABLE ACCESS (FULL) OF 'CJ604'
86
TABLE ACCESS (BY INDEX ROWID) OF 'CJ601'
98
INDEX (UNIQUE SCAN) OF 'CJ601_UI' (UNIQUE)
101 TABLE ACCESS (BY INDEX ROWID) OF 'INDUSTRY'
11 10 INDEX (UNIQUE SCAN) OF 'SYS_C002819' (UNIQUE)
Statistics
----------------------------------------------------------
0recursive calls
0db block gets
168251consistent gets
3294physical reads
0redo size
3446bytes sent via SQL*Net to client
525bytes received via SQL*Net from client
4SQL*Net roundtrips to/from client
2sorts (memory)
0sorts (disk)
39rows processed
|
