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万多。