oracle 执行计划3

环境：oracle 817 + linux + 阵列柜

　　swd_billdetail 表5000万条数据

　　SUPER_USER 表2800条数据

　　连接列上都有索引，而且super_user中的一条对应于swd_billdetail表中的很多条记录

　　表与索引都做了分析。

　　实际应用的查询为：

　　select a.CHANNEL, B.user_class

　　from swd_billdetail B, SUPER_USER A

　　where A.cn = B.cn;

　　这样在分析时导致查询出的数据过多，不方便，所以用count(a.CHANNEL||B.user_class)来代替，而且count(a.CHANNEL||B.user_class)操作本身并不占用过多的时间，所以可以接受此种替代。

　　利用索引查询出SWD_BILLDETAIL表中所有记录的方法：

SQL> select count(id) from SWD_BILLDETAIL;
COUNT(ID)
----------
  53923574
Elapsed: 00:02:166.00
Execution Plan
----------------------------------------------------------
0      SELECT STATEMENT Optimizer=CHOOSE (Cost=18051 Card=1)
1    0   SORT (AGGREGATE)
2    1     INDEX (FAST FULL SCAN) OF 'SYS_C001851' (UNIQUE) (Cost=18051 Card=54863946)

Statistics
----------------------------------------------------------
          0  recursive calls
       1952  db block gets
     158776  consistent gets
     158779  physical reads
       1004  redo size
        295  bytes sent via SQL*Net to client
        421  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
          1  rows processed

利用全表扫描从SWD_BILLDETAIL表中取出全部数据的方法：

SQL> select count(user_class) from swd_billdetail;
COUNT(USER_CLASS)
-----------------
         53923574
Elapsed: 00:11:703.07
Execution Plan
----------------------------------------------------------
0      SELECT STATEMENT Optimizer=CHOOSE (Cost=165412 Card=1 Bytes=2)
1    0   SORT (AGGREGATE)
2    1     TABLE ACCESS (FULL) OF 'SWD_BILLDETAIL' (Cost=165412 Card=54863946 Bytes=109727892)

Statistics
----------------------------------------------------------
          0  recursive calls
       8823  db block gets
    1431070  consistent gets
    1419520  physical reads
          0  redo size
        303  bytes sent via SQL*Net to client
        421  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
          1  rows processed

select count(a.CHANNEL||B.user_class)
from  swd_billdetail B, SUPER_USER A
where A.cn = B.cn;
EXEC_ORDER PLANLINE
---------- -----------------------------------------------------------------------------------------------------------
         6   SELECT STATEMENT  OPT_MODE:CHOOSE (COST=108968,CARD=1,BYTES=21)
         5     SORT  (AGGREGATE)  (COST=,CARD=1,BYTES=21)
         4       NESTED LOOPS   (COST=108968,CARD=1213745,BYTES=25488645)
         1         TABLE ACCESS  (FULL) OF 'SWORD.SUPER_USER'  (COST=2,CARD=2794,BYTES=27940)
         3         TABLE ACCESS  (BY INDEX ROWID) OF 'SWORD.SWD_BILLDETAIL'  (COST=39,CARD=54863946,BYTES=603503406)
         2           INDEX  (RANGE SCAN) OF 'SWORD.IDX_DETAIL_CN' (NON-UNIQUE)  (COST=3,CARD=54863946,BYTES=)

　　这个查询耗费的时间很长，需要1个多小时。

　　运行后的信息如下：

COUNT(A.CHANNEL||B.USER_CLASS)
------------------------------
                       1186387

Elapsed: 01:107:6429.87

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=108968 Card=1 Bytes=21)
   1    0   SORT (AGGREGATE)
   2    1     NESTED LOOPS (Cost=108968 Card=1213745 Bytes=25488645)
   3    2       TABLE ACCESS (FULL) OF 'SUPER_USER' (Cost=2 Card=2794Bytes=27940)
   4    2       TABLE ACCESS (BY INDEX ROWID) OF 'SWD_BILLDETAIL' (Cost=39 Card=54863946 Bytes=603503406)
   5    4         INDEX (RANGE SCAN) OF 'IDX_DETAIL_CN' (NON-UNIQUE) (Cost=3 Card=54863946)

Statistics
----------------------------------------------------------
          0  recursive calls
          4  db block gets
    1196954  consistent gets
    1165726  physical reads
          0  redo size
        316  bytes sent via SQL*Net to client
        421  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          2  sorts (memory)
          0  sorts (disk)
          1  rows processed