select p.surplus_capital
from clspuser.crf_p2p_rpt_bill_pen_new p ,salaryuser.crf_salary_pi_account a
where p.loan_contract_no = a.loan_contract_no
and p.gz_ym = a.gz_ym;
查看该语句的执行计划如下:
SQL> select * from table(dbms_xplan.display_cursor(‘3dj0zd3kasn0f’));
PLAN_TABLE_OUTPUT
SQL_ID 3dj0zd3kasn0f, child number 0
select p.surplus_capital from clspuser.crf_p2p_rpt_bill_pen_new p
,salaryuser.crf_salary_pi_account a where p.loan_contract_no = a.loan_contract_no and p.gz_ym
= a.gz_ym
Plan hash value: 3637505838
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
| 0 | SELECT STATEMENT | | | | | 5615 (100)| |
|* 1 | HASH JOIN | | 371K| 19M| 13M| 5615 (4)| 00:01:08 |
| 2 | TABLE ACCESS FULL| CRF_SALARY_PI_ACCOUNT | 371K| 9058K| | 1091 (5)| 00:00:14 |
| 3 | TABLE ACCESS FULL| CRF_P2P_RPT_BILL_PEN_NEW | 580K| 17M| | 2639 (5)| 00:00:32 |
Predicate Information (identified by operation id):
1 - access(“P”.”LOAN_CONTRACT_NO”=”A”.”LOAN_CONTRACT_NO” AND “P”.”GZ_YM”=”A”.”GZ_YM”)
已选择22行。
查询两张表的行数如下所示:
SQL> select count(*) from salaryuser.crf_salary_pi_account;
COUNT(*)
370498
SQL> select count(*) from clspuser.crf_p2p_rpt_bill_pen_new;
COUNT(*)
627097
查看统计信息:
SQL>/
OWNER NAME OBJECT_TYPE STA LAST_ANALYZED
SALARYUSER CRF_SALARY_PI_ACCOUNT TABLE NO 12-8月 -15
SQL>/
OWNER NAME OBJECT_TYPE STA LAST_ANALYZED
CLSPUSER CRF_P2P_RPT_BILL_PEN_NEW TABLE NO 12-8月 -15
统计信息是最新的,且执行计划是正确的,故无需对表重新收集统计信息。
初步拟定对clspuser.crf_p2p_rpt_bill_pen_new,salaryuser.crf_salary_pi_account 两个表建立索引。
建立语句如下:
create index idx_bill_pen_new on clspuser.crf_p2p_rpt_bill_pen_new(loan_contract_no,surplus_capital,gz_ym);
create index idx_pi_account on salaryuser.crf_salary_pi_account(loan_contract_no,gz_ym);
SQL> select * from table(dbms_xplan.display_cursor(‘3dj0zd3kasn0f’));
PLAN_TABLE_OUTPUT
SQL_ID 3dj0zd3kasn0f, child number 0
select p.surplus_capital from clspuser.crf_p2p_rpt_bill_pen_new p
,salaryuser.crf_salary_pi_account a where p.loan_contract_no = a.loan_contract_no and
p.gz_ym = a.gz_ym
Plan hash value: 1176563666
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
| 0 | SELECT STATEMENT | | | | | 3400 (100)| |
|* 1 | HASH JOIN | | 371K| 19M| 13M| 3400 (3)| 00:00:41 |
| 2 | INDEX FAST FULL SCAN| IDX_PI_ACCOUNT | 371K| 9058K| | 515 (4)| 00:00:07 |
| 3 | INDEX FAST FULL SCAN| IDX_BILL_PEN_NEW | 580K| 17M| | 1000 (3)| 00:00:12 |
Predicate Information (identified by operation id):
1 - access(“P”.”LOAN_CONTRACT_NO”=”A”.”LOAN_CONTRACT_NO” AND “P”.”GZ_YM”=”A”.”GZ_YM”)
已选择22行。
通过对执行计划的观察,访问走的是INDEX FAST FULL SCAN,而不是之前的TABLE ACCESS FULL,cost和time明显降低,性能明显提升。