|
|
场景：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: 真是人多力量大
demonat gaolu1234 tjmzgn jimn1982 goberl ethcham abocide 3833020
|
|
IP卡
狗仔卡
发表于 2013-5-28 16:00
收藏
提升卡
置顶卡
变色卡
显身卡
