select *
from (SELECT BH.LAST_UPDATE_DATE,
BH.LAST_UPDATED_BY,
BH.CREATION_DATE,
BH.CREATED_BY,
BH.LAST_UPDATE_LOGIN,
BH.ENABLED_FLAG,
BH.BOE_HEADER_ID,
BH.BOE_NUM,
BH.BOE_TYPE_ID,
(SELECT BTL.BOE_TYPE_NAME
FROM SIE_BOE_TYPES_TL BTL
WHERE BTL.BOE_TYPE_ID = BH.BOE_TYPE_ID
AND BTL.LANGUAGE = USERENV('LANG')) AS BOE_TYPE_NAME,
BH.CHECK_UNIT,
BH.ORG_ID,
BH.BOE_DATE,
BH.STRIKE_BA LANCE_AMOUNT,
BH.BP_COUNT,
BH.BOE_DEPT_ID AS DEPT_ID,
BH.EMPLOYEE_ID,
(SELECT (SELECT EMPL.EMPLOYEE_NAME
FROM FBP.FBP_EMPLOYEES_TL EMPL
WHERE EMP.EMPLOYEE_ID = EMPL.EMPLOYEE_ID
AND EMPL.LANGUAGE = USERENV('LANG')) ||
(SELECT FDL.DEPT_LONG_NAME
FROM FBP.FBP_DEPTS_TL FDL
WHERE FDL.DEPT_ID = EMP.DEPT_ID
AND FDL.LANGUAGE = USERENV('LANG'))
FROM FBP. FBP_EMPLOYEES EMP
WHERE EMP.EMPLOYEE_ID = BH.EMPLOYEE_ID) AS EMPLOYEE_NAME,
BS.CURRENT_STATUS AS CURRENT_STATUS,
(SELECT LV.MEANING
FROM FBP.FBP_LOOKUP_VALUES LV
WHERE LV.LOOKUP_CODE = BS.CURRENT_STATUS
AND LV.LOOKUP_TYPE = 'BOE_STATUS'
AND LV.LANGUAGE = USERENV('LANG')
AND LV.ENABLED_FLAG = 'Y') AS BOE_STATUS_NAME,
BH.FA_ENABLED_FLAG,
BPH.BP_HEADER_ID,
BPH.BILL_PRACT_NUM AS BP_NUMBER,
BPH.BILL_TYPE,
BPH.BATCH_NUM,
BPH.BP_STATUS,
(SELECT LV.MEANING
FROM FBP.FBP_LOOKUP_VALUES LV
WHERE LV.LOOKUP_CODE = BPH.BP_STATUS
AND LV.LOOKUP_TYPE = 'SIE_BP_STATUS'
AND LV.LANGUAGE = USERENV('LANG')
AND LV.ENABLED_FLAG = 'Y') AS BP_STATUS_NAME,
BPH.BP_LOCATE,
BPH.INCEPT_FAIL_FLAG
FROM binbin1 BH,
binbin BS,
binbin2 BPH
WHERE BH.BOE_HEADER_ID = BS.BOE_HEADER_ID
AND BH.BP_NU MBER = BPH.BILL_PRACT_NUM
AND (1 = 2 OR BPH.BP_STATUS = :1)
AND BH.CHECK_UNIT = :2
ORDER BY BH.BOE_NUM DESC)
where rownum <= :3
Elapsed: 00:00:35.76
Execution Plan
----------------------------------------------------------
Plan hash value: 1631994699
-------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 20 | 50700 | 31905 (2)| 00:06:23 |
| 1 | TABLE ACCESS BY INDEX ROWID | SIE_BOE_TYPES_TL | 1 | 25 | 1 (0)| 00:00:01 |
|* 2 | INDEX UNIQUE SCAN | SIE_BOE_TYPES_TL_U1 | 1 | | 0 (0)| 00:00:01 |
| 3 | TABLE ACCESS BY INDEX ROWID | FBP_EMPLOYEES_TL | 1 | 18 | 2 (0)| 00:00:01 |
|* 4 | INDEX UNIQUE SCAN | FBP_EMPLOYEES_TL_U1 | 1 | | 1 (0)| 00:00:01 |
| 5 | TABLE ACCESS BY INDEX ROWID | FBP_DEPTS_TL | 1 | 28 | 2 (0)| 00:00:01 |
|* 6 | INDEX UNIQUE SCAN | FBP_DEPTS_TL_U1 | 1 | | 1 (0)| 00:00:01 |
| 7 | TABLE ACCESS BY INDEX ROWID | FBP_EMPLOYEES | 1 | 11 | 2 (0)| 00:00:01 |
|* 8 | INDEX UNIQUE SCAN | FBP_EMPLOYEES_PK | 1 | | 1 (0)| 00:00:01 |
|* 9 | TABLE ACCESS BY INDEX ROWID | FBP_LOOKUP_VALUES | 1 | 44 | 2 (0)| 00:00:01 |
|* 10 | INDEX UNIQUE SCAN | FBP_LOOKUP_VALUES_U1 | 1 | | 1 (0)| 00:00:01 |
|* 11 | TABLE ACCESS BY INDEX ROWID | FBP_LOOKUP_VALUES | 1 | 44 | 2 (0)| 00:00:01 |
|* 12 | INDEX UNIQUE SCAN | FBP_LOOKUP_VALUES_U1 | 1 | | 1 (0)| 00:00:01 |
|* 13 | COUNT STOPKEY | | | | | |
| 14 | VIEW | | 756 | 1871K| 31905 (2)| 00:06:23 |
|* 15 | SORT ORDER BY STOPKEY | | 756 | 149K| 31905 (2)| 00:06:23 |
| 16 | TABLE ACCESS BY INDEX ROWID| binbin | 1 | 23 | 3 (0)| 00:00:01 |
| 17 | NESTED LOOPS | | 756 | 149K| 31904 (2)| 00:06:23 |
|* 18 | HASH JOIN | | 756 | 132K| 29634 (2)| 00:05:56 |
|* 19 | TABLE ACCESS FULL | binbin2 | 9286 | 553K| 3626 (2)| 00:00:44 |
|* 20 | TABLE ACCESS FULL | binbin1 | 141K| 15M| 26006 (2)| 00:05:13 |
|* 21 | INDEX RANGE SCAN | binbin_N2 | 1 | | 2 (0)| 00:00:01 |
-------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("BTL"."BOE_TYPE_ID"=:B1 AND "BTL"."LANGUAGE"=USERENV('LANG'))
4 - access("EMPL"."EMPLOYEE_ID"=:B1 AND "EMPL"."LANGUAGE"=USERENV('LANG'))
6 - access("FDL"."DEPT_ID"=:B1 AND "FDL"."LANGUAGE"=USERENV('LANG'))
8 - access("EMP"."EMPLOYEE_ID"=:B1)
9 - filter("LV"."ENABLED_FLAG"='Y')
10 - access("LV"."LOOKUP_TYPE"='BOE_STATUS' AND "LV"."LOOKUP_CODE"=:B1 AND
"LV"."LANGUAGE"=USERENV('LANG'))
11 - filter("LV"."ENABLED_FLAG"='Y')
12 - access("LV"."LOOKUP_TYPE"='SIE_BP_STATUS' AND "LV"."LOOKUP_CODE"=:B1 AND
"LV"."LANGUAGE"=USERENV('LANG'))
13 - filter(ROWNUM<=20)
15 - filter(ROWNUM<=20)
18 - access("BH"."BP_NUMBER"="BPH"."BILL_PRACT_NUM")
19 - filter("BPH"."BP_STATUS"='ACCOUNT_ANT_RECEIVE')
20 - filter("BH"."CHECK_UNIT"='SGS' AND "BH"."BP_NUMBER" IS NOT NULL)
21 - access("BH"."BOE_HEADER_ID"="BS"."BOE_HEADER_ID")
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
135195 consistent gets
32002 physical reads
0 redo size
2283 bytes sent via SQL*Net to client
1422 bytes received via SQL*Net from client
1 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
0 rows processed
蛋疼binbin1.BP_NUMBER居然有空值，binbin2.BP_STATUS列没有建索引。
create index binbin2_status on binbin2 (BP_STATUS);
改写sql，并加上hint /*+ leading(BH) index_desc(BH binbin1_N10) index_desc(BH binbin1_N1) */
select * from (select *
from (SELECT /*+ leading(BH) index_desc(BH binbin1_N10) index_desc(BH binbin1_N1) */
BH.LAST_UPDATE_DATE,
BH.LAST_UPDATED_BY,
BH.CREATION_DATE,
BH.CREATED_BY,
BH.LAST_UPDATE_LOGIN,
BH.ENABLED_FLAG,
BH.BOE_HEADER_ID,
BH.BOE_NUM,
BH.BOE_TYPE_ID,
(SELECT BTL.BOE_TYPE_NAME
FROM SIE_BOE_TYPES_TL BTL
WHERE BTL.BOE_TYPE_ID = BH.BOE_TYPE_ID
AND BTL.LANGUAGE = USERENV('LANG')) AS BOE_TYPE_NAME,
BH.CHECK_UNIT,
BH.ORG_ID,
BH.BOE_DATE,
BH.STRIKE_BALANCE_AMOUNT,
BH.BP_COUNT,
BH.BOE_DEPT_ID AS DEPT_ID,
BH.EMPLOYEE_ID,
(SELECT (SELECT EMPL.EMPLOYEE_NAME
FROM FBP.FBP_EMPLOYEES_TL EMPL
WHERE EMP.EMPLOYEE_ID = EMPL.EMPLOYEE_ID
AND EMPL.LANGUAGE = USERENV('LANG')) ||
(SELECT FDL.DEPT_LONG_NAME
FROM FBP.FBP_DEPTS_TL FDL
WHERE FDL.DEPT_ID = EMP.DEPT_ID
AND FDL.LANGUAGE = USERENV('LANG'))
FROM FBP. FBP_EMPLOYEES EMP
WHERE EMP.EMPLOYEE_ID = BH.EMPLOYEE_ID) AS EMPLOYEE_NAME,
BS.CURRENT_STATUS AS CURRENT_STATUS,
(SELECT LV.MEANING
FROM FBP.FBP_LOOKUP_VALUES LV
WHERE LV.LOOKUP_CODE = BS.CURRENT_STATUS
AND LV.LOOKUP_TYPE = 'BOE_STATUS'
AND LV.LANGUAGE = USERENV('LANG')
AND LV.ENABLED_FLAG = 'Y') AS BOE_STATUS_NAME,
BH.FA_ENABLED_FLAG,
BPH.BP_HEADER_ID,
BPH.BILL_PRACT_NUM AS BP_NUMBER,
BPH.BILL_TYPE,
BPH.BATCH_NUM,
BPH.BP_STATUS,
(SELECT LV.MEANING
FROM FBP.FBP_LOOKUP_VALUES LV
WHERE LV.LOOKUP_CODE = BPH.BP_STATUS
AND LV.LOOKUP_TYPE = 'SIE_BP_STATUS'
AND LV.LANGUAGE = USERENV('LANG')
AND LV.ENABLED_FLAG = 'Y') AS BP_STATUS_NAME,
BPH.BP_LOCATE,
BPH.INCEPT_FAIL_FLAG
FROM binbin1 BH,
binbin BS,
binbin2 BPH
WHERE BH.BOE_HEADER_ID = BS.BOE_HEADER_ID
AND BH.BP_NUMBER = BPH.BILL_PRACT_NUM
AND (1 = 2 OR BPH.BP_STATUS = 'ACCOUNT_ANT_RECEIVE')
AND BH.CHECK_UNIT = 'SGS'
ORDER BY BH.BOE_NUM DESC)
where rownum <= 20) where rownum >=0;
Elapsed: 00:00:02.34
Execution Plan
----------------------------------------------------------
Plan hash value: 1760981258
-----------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 20 | 50700 | | 107K (1)| 00:21:26 |
| 1 | TABLE ACCESS BY INDEX ROWID | SIE_BOE_TYPES_TL | 1 | 25 | | 1 (0)| 00:00:01 |
|* 2 | INDEX UNIQUE SCAN | SIE_BOE_TYPES_TL_U1 | 1 | | | 0 (0)| 00:00:01 |
| 3 | TABLE ACCESS BY INDEX ROWID | FBP_EMPLOYEES_TL | 1 | 18 | | 2 (0)| 00:00:01 |
|* 4 | INDEX UNIQUE SCAN | FBP_EMPLOYEES_TL_U1 | 1 | | | 1 (0)| 00:00:01 |
| 5 | TABLE ACCESS BY INDEX ROWID | FBP_DEPTS_TL | 1 | 28 | | 2 (0)| 00:00:01 |
|* 6 | INDEX UNIQUE SCAN | FBP_DEPTS_TL_U1 | 1 | | | 1 (0)| 00:00:01 |
| 7 | TABLE ACCESS BY INDEX ROWID | FBP_EMPLOYEES | 1 | 11 | | 2 (0)| 00:00:01 |
|* 8 | INDEX UNIQUE SCAN | FBP_EMPLOYEES_PK | 1 | | | 1 (0)| 00:00:01 |
|* 9 | TABLE ACCESS BY INDEX ROWID | FBP_LOOKUP_VALUES | 1 | 44 | | 2 (0)| 00:00:01 |
|* 10 | INDEX UNIQUE SCAN | FBP_LOOKUP_VALUES_U1 | 1 | | | 1 (0)| 00:00:01 |
|* 11 | TABLE ACCESS BY INDEX ROWID | FBP_LOOKUP_VALUES | 1 | 44 | | 2 (0)| 00:00:01 |
|* 12 | INDEX UNIQUE SCAN | FBP_LOOKUP_VALUES_U1 | 1 | | | 1 (0)| 00:00:01 |
| 13 | COUNT | | | | | | |
|* 14 | FILTER | | | | | | |
| 15 | VIEW | | 20 | 50700 | | 107K (1)| 00:21:26 |
|* 16 | COUNT STOPKEY | | | | | | |
| 17 | VIEW | | 756 | 1871K| | 107K (1)| 00:21:26 |
|* 18 | SORT ORDER BY STOPKEY | | 756 | 149K| | 107K (1)| 00:21:26 |
| 19 | TABLE ACCESS BY INDEX ROWID | binbin | 1 | 23 | | 3 (0)| 00:00:01 |
| 20 | NESTED LOOPS | | 756 | 149K| | 107K (1)| 00:21:26 |
|* 21 | HASH JOIN | | 756 | 132K| 17M| 104K (1)| 00:20:59 |
|* 22 | TABLE ACCESS BY INDEX ROWID | binbin1 | 141K| 15M| | 103K (1)| 00:20:45 |
|* 23 | INDEX RANGE SCAN DESCENDING| binbin1_N10 | 178K| | | 389 (2)| 00:00:05 |
| 24 | TABLE ACCESS BY INDEX ROWID | binbin2 | 9286 | 553K| | 295 (1)| 00:00:04 |
|* 25 | INDEX RANGE SCAN | binbin2_STATUS | 9286 | | | 41 (0)| 00:00:01 |
|* 26 | INDEX RANGE SCAN | binbin_N2 | 1 | | | 2 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("BTL"."BOE_TYPE_ID"=:B1 AND "BTL"."LANGUAGE"=USERENV('LANG'))
4 - access("EMPL"."EMPLOYEE_ID"=:B1 AND "EMPL"."LANGUAGE"=USERENV('LANG'))
6 - access("FDL"."DEPT_ID"=:B1 AND "FDL"."LANGUAGE"=USERENV('LANG'))
8 - access("EMP"."EMPLOYEE_ID"=:B1)
9 - filter("LV"."ENABLED_FLAG"='Y')
10 - access("LV"."LOOKUP_TYPE"='BOE_STATUS' AND "LV"."LOOKUP_CODE"=:B1 AND "LV"."LANGUAGE"=USERENV('LANG'))
11 - filter("LV"."ENABLED_FLAG"='Y')
12 - access("LV"."LOOKUP_TYPE"='SIE_BP_STATUS' AND "LV"."LOOKUP_CODE"=:B1 AND
"LV"."LANGUAGE"=USERENV('LANG'))
14 - filter(ROWNUM>=0)
16 - filter(ROWNUM<=20)
18 - filter(ROWNUM<=20)
21 - access("BH"."BP_NUMBER"="BPH"."BILL_PRACT_NUM")
22 - filter("BH"."BP_NUMBER" IS NOT NULL)
23 - access("BH"."CHECK_UNIT"='SGS')
filter("BH"."CHECK_UNIT"='SGS')
25 - access("BPH"."BP_STATUS"='ACCOUNT_ANT_RECEIVE')
26 - access("BH"."BOE_HEADER_ID"="BS"."BOE_HEADER_ID")
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
83841 consistent gets
0 physical reads
0 redo size
2283 bytes sent via SQL*Net to client
1561 bytes received via SQL*Net from client
1 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
0 rows processed
蛋疼，虽然2秒跑出来了，但是逻辑读还有8万多。