处理一条很慢SQL语句过程

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明显降低，性能明显提升。