已选择28行。
已用时间: 00: 00: 01.03
Execution Plan ---------------------------------------------------------- 0 SELECT STATEMENT Optimizer=CHOOSE 1 0 NESTED LOOPS 2 1 VIEW 3 2 SORT (UNIQUE) 4 3 UNION-ALL 5 4 SORT (GROUP BY) 6 5 TABLE ACCESS (FULL) OF ''''CJ601'''' 7 4 SORT (GROUP BY) 8 7 TABLE ACCESS (FULL) OF ''''CJ601'''' 9 4 SORT (GROUP BY) 10 9 TABLE ACCESS (FULL) OF ''''CJ601'''' 11 1 TABLE ACCESS (BY INDEX ROWID) OF ''''DJZCLX'''' 12 11 INDEX (UNIQUE SCAN) OF ''''SYS_C002814'''' (UNIQUE)
Statistics ---------------------------------------------------------- 199 recursive calls 0 db block gets 13854 consistent gets 2086 physical reads 0 redo size 1480 bytes sent via SQL*Net to client 514 bytes received via SQL*Net from client 3 SQL*Net roundtrips to/from client 8 sorts (memory) 0 sorts (disk) 28 rows processed
SQL> select code 代码 , substrb('''' '''',1,item_level*2-2)||b.reg_type 登记注册类型, cnt 家数 from 2 ( 3 select 4 case when code3 is not null then code3 5 when code2<>''''0'''' then code2 6 else code1 7 end code,cnt 8 from ( 9 select substr(z01_08,1,1)||''''00'''' code1 , substr(z01_08,1,2)||''''0'''' code2 , substr(z01_08,1,3) code3 ,count(*) cnt 10 from cj601 11 group by rollup(substr(z01_08,1,1),substr(z01_08,1,2),substr(z01_08,1,3)) 12 ) where code2<>code3 or code3 is null and code1<>''''00'''' 13 ) 14 c, djzclx b where c.code=b.reg_code 15 order by 1 16 ;
已选择28行。
已用时间: 00: 00: 00.07
Execution Plan ---------------------------------------------------------- 0 SELECT STATEMENT Optimizer=CHOOSE 1 0 SORT (ORDER BY) 2 1 NESTED LOOPS 3 2 VIEW 4 3 FILTER 5 4 SORT (GROUP BY ROLLUP) 6 5 TABLE ACCESS (FULL) OF ''''CJ601'''' 7 2 TABLE ACCESS (BY INDEX ROWID) OF ''''DJZCLX'''' 8 7 INDEX (UNIQUE SCAN) OF ''''SYS_C002814'''' (UNIQUE)
Statistics ---------------------------------------------------------- 0 recursive calls 0 db block gets 4628 consistent gets 701 physical reads 0 redo size 1480 bytes sent via SQL*Net to client 514 bytes received via SQL*Net from client 3 SQL*Net roundtrips to/from client 2 sorts (memory) 0 sorts (disk) 28 rows processed
SQL> select code 代码 , substrb('''' '''',1,item_level*2-2)||b.reg_type 登记注册类型, cnt 家数 from 2 ( 3 select 4 case when code3 is not null then code3 5 when code2<>''''0'''' then code2 6 else code1 7 end code,cnt 8 from ( 9 select substr(z01_08,1,1)||''''00'''' code1 , substr(z01_08,1,2)||''''0'''' code2 , substr(z01_08,1,3) code3 ,sum(cnt) cnt 10 from (select substr(z01_08,1,3) z01_08,count(*) cnt from cj601 group by substr(z01_08,1,3)) 11 group by rollup(substr(z01_08,1,1),substr(z01_08,1,2),substr(z01_08,1,3)) 12 ) where code2<>code3 or code3 is null and code1<>''''00'''' 13 ) 14 c, djzclx b where c.code=b.reg_code 15 order by 1 16 ;
已选择28行。
已用时间: 00: 00: 00.06
Execution Plan ---------------------------------------------------------- 0 SELECT STATEMENT Optimizer=CHOOSE 1 0 SORT (ORDER BY) 2 1 NESTED LOOPS 3 2 VIEW 4 3 FILTER 5 4 SORT (GROUP BY ROLLUP) 6 5 VIEW 7 6 SORT (GROUP BY) 8 7 TABLE ACCESS (FULL) OF ''''CJ601'''' 9 2 TABLE ACCESS (BY INDEX ROWID) OF ''''DJZCLX'''' 10 9 INDEX (UNIQUE SCAN) OF ''''SYS_C002814'''' (UNIQUE)
Statistics ---------------------------------------------------------- 0 recursive calls 0 db block gets 4628 consistent gets 705 physical reads 0 redo size 1480 bytes sent via SQL*Net to client 514 bytes received via SQL*Net from client 3 SQL*Net roundtrips to/from client 3 sorts (memory) 0 sorts (disk) 28 rows processed
SQL>
第3种的一致性取和物理读都超过第2种,不过还是快一些
上一页 [1] [2] 没有相关教程
|