已用时间:  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]