oracle ALTER 语句 在执行的时候，做了些什么,他的后台执行逻辑是什么

怎样优化SQL语句的执行？

环境：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)来代替，而且coun...全部

  环境：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 将语句中加入hints，让oracle的优化器使用嵌套循环，并且大表作为驱动表，生成新的执行计划： select /*+ ORDERED USE_NL(A) */ 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=109893304,CARD=1,BYTES=21) 5 SORT (AGGREGATE) (COST=,CARD=1,BYTES=21) 4 NESTED LOOPS (COST=109893304,CARD=1213745,BYTES=25488645) 1 TABLE ACCESS (FULL) OF 'SWORD。
  SWD_BILLDETAIL' (COST=165412,CARD=54863946,BYTES=603503406) 3 TABLE ACCESS (BY INDEX ROWID) OF 'SWORD。
  SUPER_USER' (COST=2,CARD=2794,BYTES=27940) 2 INDEX (RANGE SCAN) OF 'SWORD。IDX_SUPER_USER_CN' (NON-UNIQUE) (COST=1,CARD=2794,BYTES=) 这个查询耗费的时间较短，才20分钟，性能比较好。
  运行后的信息如下： COUNT(A。CHANNEL||B。USER_CLASS) ------------------------------ 1186387 Elapsed: 00:20:1208。
  87 Execution Plan ---------------------------------------------------------- 0 SELECT STATEMENT Optimizer=CHOOSE (Cost=109893304 Card=1 Bytes=21) 1 0 SORT (AGGREGATE) 2 1 NESTED LOOPS (Cost=109893304 Card=1213745 Bytes=25488645) 3 2 TABLE ACCESS (FULL) OF 'SWD_BILLDETAIL' (Cost=165412 Card=54863946 Bytes=603503406) 4 2 TABLE ACCESS (BY INDEX ROWID) OF 'SUPER_USER' (Cost=2Card=2794 Bytes=27940) 5 4 INDEX (RANGE SCAN) OF 'IDX_SUPER_USER_CN' (NON-UNIQUE) (Cost=1 Card=2794) Statistics ---------------------------------------------------------- 0 recursive calls 8823 db block gets 56650250 consistent gets 1413250 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 总结： 因 为上两个查询都是采用nested loop循环，这时采用哪个表作为driving table就很重要。
  在第一个sql中，小表(SUPER_USER)作为driving table，符合oracle优化的建议，但是由于SWD_BILLDETAIL表中cn列的值有很多重复的，这样对于SUPER_USER中的每一行，都会在SWD_BILLDETAIL中有很多行，利用索引查询出这些行的rowid很快，但是再利用这些rowid去查询SWD_BILLDETAIL表中的user_class列的值，就比较慢了。
  原因是这些rowid是随机的，而且该表比较大，不可能缓存到内存，所以几乎每次按照rowid查询都需要读物理磁盘，这就是该执行计划比较慢的真正原因。从结果可以得到验证：查询出1186387行，需要利用rowid从SWD_BILLDETAIL表中读取1186387次，而且大部分为从硬盘上读取。
   反其道而行之，利用大表(SWD_BILLDETAIL)作为driving表，这样大表只需要做一次全表扫描(而且会使用多块读功能，每次物理I/O都会读取几个oracle数据块，从而一次读取很多行，加快了执行效率)，对于读出的每一行，都与SUPER_USER中的行进行匹配，因为SUPER_USER表很小，所以可以全部放到内存中，这样匹配操作就极快，所以该sql执行的时间与SWD_BILLDETAIL表全表扫描的时间差不多(SWD_BILLDETAIL全表用11分钟，而此查询用20分钟)。
   另外：如果SWD_BILLDETAIL表中cn列的值唯一，则第一个sql执行计划执行的结果或许也会不错。如果SUPER_USER表也很大，如500万行，则第2个sql执行计划执行的结果反而又可能会差。
  其实，如果SUPER_USER表很小，则第2个sql语句的执行计划如果不利用SUPER_USER表的索引，查询或许会更快一些，我没有对此进行测试。 所以在进行性能调整时，具体问题要具体分析，没有一个统一的标准。
  收起