分頁最佳化-蛋疼的空值

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 <= :3Elapsed: 00:00:35.76Execution 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.34Execution 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萬多。