环境: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 |
|