场景：sino_loan 是一张大表 60w行，sino_org 是一张小表 29行 ，对这2个表进行关联查询
目的：分页显示，每三十行一页，第一页显示rownum 1~30的数据，第二页显示rownum 31~60的数据，第三页显示rownum 61~90的数据，后面以此类推。。。。。。

sino_loan字段：IID, DGETDATE, SORGCODE, SLOANTYPE, SLOANCOMPACTCODE, SACCOUNT,SAREACODE, DDATEOPENED, DDATECLOSED, SCURRENCY, ICREDITLIMIT, ISHAREACCOUNT,
IMAXDEBT, IGUARANTEEWAY, STERMSFREQ, SMONTHDURATION, SMONTHUNPAID, STREATYPAYDUE,
ITREATYPAYAMOUNT, DBILLINGDATE, DRECENTPAYDATE, ISCHEDULEDAMOUNT, IACTUALPAYAMOUNT,
IBALANCE, ICURTERMSPASTDUE, IAMOUNTPASTDUE, IAMOUNTPASTDUE30, IAMOUNTPASTDUE60,
IAMOUNTPASTDUE90, IAMOUNTPASTDUE180, ITERMSPASTDUE, IMAXTERMSPASTDUE, ICLASS5STAT,
IACCOUNTSTAT, SPAYSTAT24MONTH, IINFOINDICATOR, SNAME, SCERTTYPE, SCERTNO,
SKEEPCOLUMN, IPERSONID, SPIN, SMSGFILENAME, ILINENO, STOPORGCODE, ISTATE,
ILOANID, IPBCSTATE

sino_loan表上的索引创建：
当然建这么多索引，会有不合理的地方，这是研发在测试的时候创建的，姑且看之
create index idx_sino_loan1 on sino_loan (ipersonid,istate,sorgcode,dgetdate) tablespace sinojfs_idx;
create index idx_sino_loan2 on sino_loan (istate,sorgcode) tablespace sinojfs_idx;
create index idx_sino_loan3 on sino_loan (sorgcode) tablespace sinojfs_idx;
create bitmap index idx_sino_loan4 on sino_loan (istate) tablespace sinojfs_idx;    因为istate字段的值只有0，-1，并且0值占了总记录的99%，我们为其创建为位图索引

sino_org字段：SORGCODE,SORGNAME,SPARENT,SLEVEL,SADDRESS,SEMAIL,SPHONE,SAREACODE,SCONTACT,SORGTYPE,SZIPCODE,ISTATE,SREMARK,SZIPPASSWORD
constraint PK_ORG primary key (sorgcode);   这个字段已经加主键了

对这2个表做分析
execute dbms_stats.gather_table_stats('sinojfs','sino_loan',CASCADE=>TRUE);
execute dbms_stats.gather_table_stats('sinojfs','sino_org',CASCADE=>TRUE);

下面抛出要执行的SQL语句，这三条语句都是实现一个功能，在取rownum 500031~500060的记录  
（1）select * from
( select t.*, rownum rn from
   ( select loan.* , org.sorgname from sino_loan loan,sino_org org where loan.istate = 0 and org.sorgcode = loan.sorgcode order by loan.DBILLINGDATE,loan.iid desc ) t  
where rownum <= 500060  )
where rn >= 500031;
执行时间：11.156 seconds

（2）select t1.*,org.sorgname from
( select t.*, rownum rn from
   ( select loan.* from sino_loan loan where loan.istate = 0 order by loan.dbillingdate desc, loan.iid desc ) t  where rownum <= 500060  ) t1 , sino_org org where rn >= 500031 and org.sorgcode = t1.sorgcode;
执行时间：10.516 seconds

（3）select l.*,org.sorgname from sino_loan l, sino_org org where org.sorgcode = l.sorgcode and l.iid in
(select t1.iid from
  ( select t.iid, rownum rn from
     ( select loan.iid from sino_loan loan where loan.istate = 0 order by loan.dbillingdate desc, loan.iid desc ) t  where rownum <= 500060  ) t1 where rn >= 500031);
执行时间：2.297 seconds

现在我们看一下三条sql语句的执行计划
第一条语句
SINOJFS@base> select * from
  2   ( select t.*, rownum rn from
   ( select loan.* , org.sorgname from sino_loan loan,sino_org org where loan.istate = 0 and org.sorgcode = loan.sorgcode order by loan.DBILLINGDATE desc,loan.iid desc ) t  
  4  where rownum <= 500060  )
  5  where rn >= 500031;
30 rows selected.
Elapsed: 00:00:10.44
Execution Plan
----------------------------------------------------------
Plan hash value: 954778783
----------------------------------------------------------------------------------------------
| Id  | Operation                | Name      | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT         |           |   500K|   296M|       | 43495   (1)| 00:08:42 |
|*  1 |  VIEW                    |           |   500K|   296M|       | 43495   (1)| 00:08:42 |
|*  2 |   COUNT STOPKEY          |           |       |       |       |            |          |
|   3 |    VIEW                  |           |   598K|   347M|       | 43495   (1)| 00:08:42 |
|*  4 |     SORT ORDER BY STOPKEY|           |   598K|   169M|   467M| 43495   (1)| 00:08:42 |
|*  5 |      HASH JOIN           |           |   598K|   169M|       |  5385   (1)| 00:01:05 |
|   6 |       TABLE ACCESS FULL  | SINO_ORG  |    29 |   493 |       |     3   (0)| 00:00:01 |
|*  7 |       TABLE ACCESS FULL  | SINO_LOAN |   598K|   159M|       |  5379   (1)| 00:01:05 |
----------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter("RN">=500031)
   2 - filter(ROWNUM<=500060)
   4 - filter(ROWNUM<=500060)
   5 - access("ORG"."SORGCODE"="LOAN"."SORGCODE")
   7 - filter("LOAN"."ISTATE"=0)

Statistics
----------------------------------------------------------
        205  recursive calls
         12  db block gets
      24166  consistent gets
      21877  physical reads
          0  redo size
       7239  bytes sent via SQL*Net to client
        503  bytes received via SQL*Net from client
          3  SQL*Net roundtrips to/from client
          0  sorts (memory)
          1  sorts (disk)
         30  rows processed

第二条语句
select t1.*,org.sorgname from
  2   ( select t.*, rownum rn from
  3     ( select loan.* from sino_loan loan where loan.istate = 0 order by loan.dbillingdate desc, loan.iid desc ) t  where rownum <= 500060  )
  4          t1 , sino_org org where rn >= 500031 and org.sorgcode = t1.sorgcode;
30 rows selected.
Elapsed: 00:00:09.65
Execution Plan
----------------------------------------------------------
Plan hash value: 3299718750
-----------------------------------------------------------------------------------------------
| Id  | Operation                 | Name      | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT          |           |   500K|   282M|       | 41348   (1)| 00:08:17 |
|*  1 |  HASH JOIN                |           |   500K|   282M|       | 41348   (1)| 00:08:17 |
|   2 |   TABLE ACCESS FULL       | SINO_ORG  |    29 |   493 |       |     3   (0)| 00:00:01 |
|*  3 |   VIEW                    |           |   500K|   274M|       | 41341   (1)| 00:08:17 |
|*  4 |    COUNT STOPKEY          |           |       |       |       |            |          |
|   5 |     VIEW                  |           |   598K|   321M|       | 41341   (1)| 00:08:17 |
|*  6 |      SORT ORDER BY STOPKEY|           |   598K|   159M|   445M| 41341   (1)| 00:08:17 |
|*  7 |       TABLE ACCESS FULL   | SINO_LOAN |   598K|   159M|       |  5379   (1)| 00:01:05 |
-----------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - access("ORG"."SORGCODE"="T1"."SORGCODE")
   3 - filter("RN">=500031)
   4 - filter(ROWNUM<=500060)
   6 - filter(ROWNUM<=500060)
   7 - filter("LOAN"."ISTATE"=0)

Statistics
----------------------------------------------------------
        197  recursive calls
         11  db block gets
      24166  consistent gets
      20990  physical reads
          0  redo size
       7239  bytes sent via SQL*Net to client
        503  bytes received via SQL*Net from client
          3  SQL*Net roundtrips to/from client
          0  sorts (memory)
          1  sorts (disk)
         30  rows processed

第三条语句
select l.*,org.sorgname from sino_loan l, sino_org org where org.sorgcode = l.sorgcode and l.iid in
  2  (select t1.iid from
  3    ( select t.iid, rownum rn from
  4       ( select loan.iid from sino_loan loan where loan.istate = 0 order by loan.dbillingdate desc, loan.iid desc ) t  where rownum <= 500060  ) t1 where rn >= 500031);
30 rows selected.
Elapsed: 00:00:01.47
Execution Plan
----------------------------------------------------------
Plan hash value: 793243073
-------------------------------------------------------------------------------------------------
| Id  | Operation                   | Name      | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |           |   500K|   147M|       | 22702   (1)| 00:04:33 |
|*  1 |  HASH JOIN                  |           |   500K|   147M|       | 22702   (1)| 00:04:33 |
|   2 |   TABLE ACCESS FULL         | SINO_ORG  |    29 |   493 |       |     3   (0)| 00:00:01 |
|*  3 |   HASH JOIN RIGHT SEMI      |           |   500K|   139M|    11M| 22696   (1)| 00:04:33 |
|   4 |    VIEW                     | VW_NSO_1  |   500K|  6348K|       |  8458   (1)| 00:01:42 |
|*  5 |     VIEW                    |           |   500K|  8790K|       |  8458   (1)| 00:01:42 |
|*  6 |      COUNT STOPKEY          |           |       |       |       |            |          |
|   7 |       VIEW                  |           |   598K|  2920K|       |  8458   (1)| 00:01:42 |
|*  8 |        SORT ORDER BY STOPKEY|           |   598K|  8762K|    32M|  8458   (1)| 00:01:42 |
|*  9 |         TABLE ACCESS FULL   | SINO_LOAN |   598K|  8762K|       |  5377   (1)| 00:01:05 |
|  10 |    TABLE ACCESS FULL        | SINO_LOAN |   598K|   159M|       |  5377   (1)| 00:01:05 |
-------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - access("ORG"."SORGCODE"="L"."SORGCODE")
   3 - access("L"."IID"="$nso_col_1")
   5 - filter("RN">=500031)
   6 - filter(ROWNUM<=500060)
   8 - filter(ROWNUM<=500060)
   9 - filter("LOAN"."ISTATE"=0)

Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
      48327  consistent gets
          0  physical reads
          0  redo size
       7029  bytes sent via SQL*Net to client
        503  bytes received via SQL*Net from client
          3  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
         30  rows processed

从上面的执行计划来看，第三条语句的效果是最好的，效率是最高的。
这期间我们已经测试了三种不同的join方法，最后hash join的效率最高
select /*+ use_hash(sino_loan,sino_org) */ sino_loan.* from sino_loan,sino_org where sino_loan.sorgcode = sino_org.sorgcode;
select /*+ use_merge(sino_loan,sino_org) */ sino_loan.* from sino_loan,sino_org where sino_loan.sorgcode = sino_org.sorgcode;
select /*+ use_nl(sino_loan,sino_org) */ sino_loan.* from sino_loan,sino_org where sino_loan.sorgcode = sino_org.sorgcode;
当然我能想到的一个把*号替换成具体的字段名


讨论话题：
讨论话题：试问还有没有更好的一种SQL优化方法可以提高效率呢？ 请大师们提提宝贵意见!!

讨论时间：2013.5.28--2013.6.11

活动奖品：活动结束后将会抽取5-10名会员赠送ITPUB独家编写的《数据库设计与开发规范》一本。

SQL修改方案
1.create index idx_sino_loan_c1 on sino_loan(istate,dbillingdate desc,iid desc) tablespace sinojfs_idx;   
因为执行计划中SORT ORDER BY STOPKEY是最耗费时间的，添加排序索引，省略了排序过程，节约了时间

2.采用rowid分页，直接定位rowid，提高了读取效率
3.先查询出分页的记录再去关联，也就是说先缩小结果集再去join，减少中间计算量

修改后的语句和执行计划
select l.*,org.sorgname from sino_loan l, sino_org org where org.sorgcode = l.sorgcode and l.rowid in               
    (select t1.rd from                  
       (SELECT t.rd, rownum rn FROM
          (SELECT rowid rd FROM sino_loan loan WHERE loan.istate = 0 ORDER BY loan.dbillingdate DESC, loan.iid DESC) t WHERE rownum <= 500060) t1 where rn >=500031);

30 rows selected.

Elapsed: 00:00:00.19

Execution Plan
----------------------------------------------------------
Plan hash value: 3687635103

-------------------------------------------------------------------------------------------------
| Id  | Operation                    | Name             | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |                  |     1 |   329 | 16264   (1)| 00:03:16 |
|   1 |  NESTED LOOPS                |                  |     1 |   329 | 16264   (1)| 00:03:16 |
|   2 |   NESTED LOOPS               |                  |     1 |   312 | 16263   (1)| 00:03:16 |
|   3 |    VIEW                      | VW_NSO_1         |   500K|  5860K| 13969   (1)| 00:02:48 |
|   4 |     HASH UNIQUE              |                  |     1 |    11M|            |          |
|*  5 |      VIEW                    |                  |   500K|    11M| 13969   (1)| 00:02:48 |
|*  6 |       COUNT STOPKEY          |                  |       |       |            |          |
|   7 |        VIEW                  |                  |  3301K|    37M| 13969   (1)| 00:02:48 |
|*  8 |         INDEX RANGE SCAN     | IDX_SINO_LOAN_C1 |  3301K|    88M| 13969   (1)| 00:02:48 |
|   9 |    TABLE ACCESS BY USER ROWID| SINO_LOAN        |     1 |   300 |     1   (0)| 00:00:01 |
|  10 |   TABLE ACCESS BY INDEX ROWID| SINO_ORG         |     1 |    17 |     1   (0)| 00:00:01 |
|* 11 |    INDEX UNIQUE SCAN         | PK_ORG           |     1 |       |     0   (0)| 00:00:01 |
-------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   5 - filter("RN">=500031)
   6 - filter(ROWNUM<=500060)
   8 - access("LOAN"."ISTATE"=0)
  11 - access("ORG"."SORGCODE"="L"."SORGCODE")


Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
       2144  consistent gets
          0  physical reads
          0  redo size
       7029  bytes sent via SQL*Net to client
        503  bytes received via SQL*Net from client
          3  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
         30  rows processed

声明 sino_loan 表现有数据330w行，sino_org 表现有数据29行
索引情况
sino_loan
alter table sino_loan add constraint pk_sino_loan primary key (iid) using index tablespace sinojfs_idx;
create index idx_sino_loan1 on sino_loan (ipersonid,istate,sorgcode,dgetdate) tablespace sinojfs_idx;
create index idx_sino_loan_c1 on sino_loan (istate,dbillingdate desc,iid desc) tablespace sinojfs_idx;
sino_org
alter table sino_org add constraint pk_sino_org primary key (iid) using index tablespace sinojfs_idx;

这里只用到了idx_sino_loan_c1和pk_sino_org，其它2个索引会在其它sql中使用

小结：优化成果
（1）执行时间  从6秒降低到1秒
（2）consistent gets  从48327降低到 2144
（3）Cost      从58259降低到16264
（4）消除了全表扫描和排序耗时过程


感谢大家的献策献计，集思广益:lovely:  真是人多力量大

 

#########################################################