​[20220414]Function based indexes and cursor sharing2.txt

lfree發表於2022-04-14

[20220414]Function based indexes and cursor sharing2.txt

--//以前的測試:http://blog.itpub.net/267265/viewspace-2737440/ =>[20202117]Function based indexes and cursor sharing.txt
--//也就是從19c開始如果你設定cursor_sharing=force; 一些函式里面帶有引數的依舊可以使用索引。但是最近我最佳化遇到的問題,不
--//知道為什麼不能使用索引。

1.環境:
> @ pr
==============================
PORT_STRING                   : x86_64/Linux 2.4.xx
VERSION                       : 19.0.0.0.0
BANNER                        : Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
BANNER_FULL                   : Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.9.0.0.0
BANNER_LEGACY                 : Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
CON_ID                        : 0
PL/SQL procedure successfully completed.

2.分析:
> @ash/ash_index_helper % XXXXXX_YYY.TEMP_HSDJ &day 1 1
-- Santa's Little (Index) Helper BETA v0.5 - by Tanel Poder ( )
   SECONDS     AAS CPU   WAIT  Accessed_Table       Plan_Operation                           PLAN_CARD TABLE_ROWS FILTER_PCT  SQL_EXECS ELA_SEC/EXEC PREDICATES                                                   SQL_ID        MODULE1
---------- ------- ----- ----- -------------------- ---------------------------------------- --------- ---------- ---------- ---------- ------------ ------------------------------------------------------------ ------------- --------
        50      .0 100%    0%  TEMP_HSDJ            TABLE ACCESS FULL [XXXXXX_YYY.TEMP_HSDJ]      1552     156391 .992384472       1040        0.059  [F:] (TO_CHAR(INTERNAL_FUNCTION("T"."DJDATE"),:SYS_B_38)=:S 8y1j8tf28h2w8 w3wp.exe
                                                                                                                                                     YS_B_39 AND "T"."DJSTATUS" IS NULL)

> @ sql_id 8y1j8tf28h2w8
--SQL_ID = 8y1j8tf28h2w8
select * from (select ...
from temp_hsdj t
where t.djstatus is null and  to_char(t.djdate,:"SYS_B_38") =  :"SYS_B_39" order by djdate desc ) where :"SYS_B_40"=:"SYS_B_41" and  rownum < = :"SYS_B_42"  ;

> @ bind_cap 8y1j8tf28h2w8 :sys_B_3
SQL_ID        CHILD_NUMBER WAS NAME                                       POSITION MAX_LENGTH LAST_CAPTURED       DATATYPE_STRING VALUE_STRING
------------- ------------ --- ---------------------------------------- ---------- ---------- ------------------- --------------- ------------
8y1j8tf28h2w8            1 YES :SYS_B_38                                        39         32 2022-04-13 17:34:04 VARCHAR2(32)    yyyy-mm-dd
                           YES :SYS_B_39                                        40         32 2022-04-13 17:34:04 VARCHAR2(32)    2022-04-13

--//注太長我僅僅列出where部分。
--//我已經建立了函式索引。
CREATE INDEX XXXXXX_YYY.IF_TEMP_HSDJ_DJDATE ON XXXXXX_YYY.TEMP_HSDJ (TO_CHAR("DJDATE",'yyyy-mm-dd'))
LOGGING
TABLESPACE XXXXXX_YYY
PCTFREE    10
INITRANS   2
MAXTRANS   255
STORAGE    (
            INITIAL          64K
            NEXT             1M
            MAXSIZE          UNLIMITED
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            FREELISTS        1
            FREELIST GROUPS  1
            BUFFER_POOL      DEFAULT
            FLASH_CACHE      DEFAULT
            CELL_FLASH_CACHE DEFAULT
           )
NOPARALLEL
COMPRESS 1;

--//為了測試需要,我簡單改寫指令碼如下,便於重複測試:
$ cat 8y1j8tf28h2w8.sql9_x
set termout off
alter session set current_schema=XXXXXX_YYY;
alter session set statistics_level=all;
alter session set cursor_sharing = force;
select t.section ,t.brxm  from temp_hsdj t where t.djstatus is null and  to_char(t.djdate,'yyyy-mm-dd') =  '2022-04-13'  ;
set termout on
@dpc '' outline ''
rollback;
alter session set current_schema=SYS ;

PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID  fatkcpsnxa2g8, child number 0
-------------------------------------
select t.section ,t.brxm  from temp_hsdj t where t.djstatus is null and
 to_char(t.djdate,:"SYS_B_0") =  :"SYS_B_1"
Plan hash value: 1883277554
-----------------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                           | Name                | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | A-Rows |   A-Time   | Buffers |
-----------------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |                     |      1 |        |       |    30 (100)|          |   1219 |00:00:00.01 |      60 |
|*  1 |  TABLE ACCESS BY INDEX ROWID BATCHED| TEMP_HSDJ           |      1 |   1084 | 32520 |    30   (0)| 00:00:01 |   1219 |00:00:00.01 |      60 |
|*  2 |   INDEX RANGE SCAN                  | IF_TEMP_HSDJ_DJDATE |      1 |   1086 |       |     2   (0)| 00:00:01 |   1221 |00:00:00.01 |      16 |
-----------------------------------------------------------------------------------------------------------------------------------------------------

--//你可以發現確實可以使用我建立的函式索引,注意看sql語句輸出部分,說明cursor_sharing = force是起作用的。
--//按照生產系統的執行條件修改如下:

$ cat  8y1j8tf28h2w8.sql9_x
$ cat  8y1j8tf28h2w8.sql9_x
set termout off
alter session set current_schema=XXXXXX_YYY;
alter session set statistics_level=all;
alter session set cursor_sharing = force;
--select t.section ,t.brxm  from temp_hsdj t where t.djstatus is null and  to_char(t.djdate,'yyyy-mm-dd') =  '2022-04-13' ;
select * from (select t.section ,t.brxm  from temp_hsdj t where t.djstatus is null and  to_char(t.djdate,'yyyy-mm-dd') =  '2022-04-13'  order by djdate desc ) where 1=1 and  rownum < = 30  ;
set termout on
@dpc '' outline ''
rollback;
alter session set current_schema=SYS ;

--//執行計劃如下:
Plan hash value: 1191506493
----------------------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation               | Name      | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
----------------------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT        |           |      1 |        |       |  1079 (100)|          |     30 |00:00:00.06 |    3967 |       |       |          |
|*  1 |  COUNT STOPKEY          |           |      1 |        |       |            |          |     30 |00:00:00.06 |    3967 |       |       |          |
|   2 |   VIEW                  |           |      1 |   1562 |   128K|  1079   (1)| 00:00:01 |     30 |00:00:00.06 |    3967 |       |       |          |
|*  3 |    SORT ORDER BY STOPKEY|           |      1 |   1562 | 42174 |  1079   (1)| 00:00:01 |     30 |00:00:00.06 |    3967 | 20480 | 20480 |18432  (0)|
|*  4 |     FILTER              |           |      1 |        |       |            |          |   1219 |00:00:00.06 |    3967 |       |       |          |
|*  5 |      TABLE ACCESS FULL  | TEMP_HSDJ |      1 |   1562 | 42174 |  1078   (1)| 00:00:01 |   1219 |00:00:00.06 |    3967 |       |       |          |
----------------------------------------------------------------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
   1 - SEL$1
   2 - SEL$2 / from$_subquery$_001@SEL$1
   3 - SEL$2
   5 - SEL$2 / T@SEL$2
Outline Data
-------------
  /*+
      BEGIN_OUTLINE_DATA
      IGNORE_OPTIM_EMBEDDED_HINTS
      OPTIMIZER_FEATURES_ENABLE('19.1.0')
      DB_VERSION('19.1.0')
      ALL_ROWS
      OUTLINE_LEAF(@"SEL$2")
      OUTLINE_LEAF(@"SEL$1")
      NO_ACCESS(@"SEL$1" "from$_subquery$_001"@"SEL$1")
      FULL(@"SEL$2" "T"@"SEL$2")
      END_OUTLINE_DATA
  */
Peeked Binds (identified by position):
--------------------------------------
   5 - (NUMBER): 30
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter(ROWNUM<=:SYS_B_4)
   3 - filter(ROWNUM<=:SYS_B_4)
   4 - filter(:SYS_B_2=:SYS_B_3)
   5 - filter((TO_CHAR(INTERNAL_FUNCTION("T"."DJDATE"),:SYS_B_0)=:SYS_B_1 AND "T"."DJSTATUS" IS NULL))

--//我僅僅加了order by以及取前面幾行的語句,導致無法使用函式索引,到底是那裡導致該問題。

$ cat  8y1j8tf28h2w8.sql9_x
set termout off
alter session set current_schema=XXXXXX_YYY;
alter session set statistics_level=all;
alter session set cursor_sharing = force;
--select t.section ,t.brxm  from temp_hsdj t where t.djstatus is null and  to_char(t.djdate,'yyyy-mm-dd') =  '2022-04-13' ;
--select * from (select t.section ,t.brxm  from temp_hsdj t where t.djstatus is null and  to_char(t.djdate,'yyyy-mm-dd') =  '2022-04-13'  order by djdate desc ) where 1=1 and  rownum < = 30  ;
select t.section ,t.brxm  from temp_hsdj t where t.djstatus is null and  to_char(t.djdate,'yyyy-mm-dd') =  '2022-04-13'  order by djdate desc  ;
set termout on
@dpc '' outline ''
rollback;
alter session set current_schema=SYS ;

Plan hash value: 3530033302
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                            | Name                | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                     |                     |      1 |        |       |    31 (100)|          |   1219 |00:00:00.01 |      35 |       |       |          |
|   1 |  SORT ORDER BY                       |                     |      1 |   1084 | 41192 |    31   (4)| 00:00:01 |   1219 |00:00:00.01 |      35 |   106K|   106K|96256  (0)|
|*  2 |   TABLE ACCESS BY INDEX ROWID BATCHED| TEMP_HSDJ           |      1 |   1084 | 41192 |    30   (0)| 00:00:01 |   1219 |00:00:00.01 |      35 |       |       |          |
|*  3 |    INDEX RANGE SCAN                  | IF_TEMP_HSDJ_DJDATE |      1 |   1086 |       |     2   (0)| 00:00:01 |   1221 |00:00:00.01 |       3 |       |       |          |
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
--//排除了order by djdate desc 可能性。

$ cat  8y1j8tf28h2w8.sql9_x
set termout off
alter session set current_schema=XXXXXX_YYY;
alter session set statistics_level=all;
alter session set cursor_sharing = force;
--select t.section ,t.brxm  from temp_hsdj t where t.djstatus is null and  to_char(t.djdate,'yyyy-mm-dd') =  '2022-04-13' ;
--select * from (select t.section ,t.brxm  from temp_hsdj t where t.djstatus is null and  to_char(t.djdate,'yyyy-mm-dd') =  '2022-04-13'  order by djdate desc ) where 1=1 and  rownum < = 30  ;
--select t.section ,t.brxm  from temp_hsdj t where t.djstatus is null and  to_char(t.djdate,'yyyy-mm-dd') =  '2022-04-13'  order by djdate desc  ;
select * from (select t.section ,t.brxm  from temp_hsdj t where t.djstatus is null and  to_char(t.djdate,'yyyy-mm-dd') =  '2022-04-13'  order by djdate ) where  rownum < = 30  ;
set termout on
@dpc '' outline ''
rollback;
alter session set current_schema=SYS ;


Plan hash value: 4209826087
----------------------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation               | Name      | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
----------------------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT        |           |      1 |        |       |  1079 (100)|          |     30 |00:00:00.06 |    3967 |       |       |          |
|*  1 |  COUNT STOPKEY          |           |      1 |        |       |            |          |     30 |00:00:00.06 |    3967 |       |       |          |
|   2 |   VIEW                  |           |      1 |   1562 |   128K|  1079   (1)| 00:00:01 |     30 |00:00:00.06 |    3967 |       |       |          |
|*  3 |    SORT ORDER BY STOPKEY|           |      1 |   1562 | 42174 |  1079   (1)| 00:00:01 |     30 |00:00:00.06 |    3967 |  4096 |  4096 | 4096  (0)|
|*  4 |     TABLE ACCESS FULL   | TEMP_HSDJ |      1 |   1562 | 42174 |  1078   (1)| 00:00:01 |   1219 |00:00:00.06 |    3967 |       |       |          |
----------------------------------------------------------------------------------------------------------------------------------------------------------
--//可以發現只要在外面加入rownum < = 30  限制條件,oracle就不再使用函式索引。

$ cat  8y1j8tf28h2w8.sql9_x
set termout off
alter session set current_schema=XXXXXX_YYY;
alter session set statistics_level=all;
alter session set cursor_sharing = force;
--select t.section ,t.brxm  from temp_hsdj t where t.djstatus is null and  to_char(t.djdate,'yyyy-mm-dd') =  '2022-04-13' ;
--select * from (select t.section ,t.brxm  from temp_hsdj t where t.djstatus is null and  to_char(t.djdate,'yyyy-mm-dd') =  '2022-04-13'  order by djdate desc ) where 1=1 and  rownum < = 30  ;
--select t.section ,t.brxm  from temp_hsdj t where t.djstatus is null and  to_char(t.djdate,'yyyy-mm-dd') =  '2022-04-13'  order by djdate desc  ;
--select * from (select t.section ,t.brxm  from temp_hsdj t where t.djstatus is null and  to_char(t.djdate,'yyyy-mm-dd') =  '2022-04-13'  order by djdate ) where  rownum < = 30  ;
select * from (select /*+ MATERIALIZE index(t if_temp_hsdj_djdate) */ t.section ,t.brxm  from temp_hsdj t where t.djstatus is null and  to_char(t.djdate,'yyyy-mm-dd') =  '2022-04-13'  order by djdate ) where  rownum < = 30  ;
set termout on
@dpc '' outline ''
rollback;
alter session set current_schema=SYS ;

--//依舊不使用我建立的索引。修改約束限制DJDATE not null呢?
Plan hash value: 3895191266

-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                              | Name                | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                       |                     |      1 |        |       |  4235 (100)|          |     30 |00:00:00.10 |    4239 |       |       |          |
|*  1 |  COUNT STOPKEY                         |                     |      1 |        |       |            |          |     30 |00:00:00.10 |    4239 |       |       |          |
|   2 |   VIEW                                 |                     |      1 |   1562 |   128K|  4235   (1)| 00:00:01 |     30 |00:00:00.10 |    4239 |       |       |          |
|*  3 |    SORT ORDER BY STOPKEY               |                     |      1 |   1562 | 42174 |  4235   (1)| 00:00:01 |     30 |00:00:00.10 |    4239 |  4096 |  4096 | 4096  (0)|
|*  4 |     TABLE ACCESS BY INDEX ROWID BATCHED| TEMP_HSDJ           |      1 |   1562 | 42174 |  4234   (1)| 00:00:01 |   1219 |00:00:00.10 |    4239 |       |       |          |
|   5 |      INDEX FULL SCAN                   | IF_TEMP_HSDJ_DJDATE |      1 |    156K|       |   243   (1)| 00:00:01 |    156K|00:00:00.02 |     243 |       |       |          |
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
--//可以使用,但是操作=INDEX FULL SCAN,執行效率低下。

--//如果我使用with改寫:
$ cat  8y1j8tf28h2w8.sql9_x
set termout off
alter session set current_schema=XXXXXX_YYY;
alter session set statistics_level=all;
alter session set cursor_sharing = force;
--select t.section ,t.brxm  from temp_hsdj t where t.djstatus is null and  to_char(t.djdate,'yyyy-mm-dd') =  '2022-04-13' ;
--select * from (select t.section ,t.brxm  from temp_hsdj t where t.djstatus is null and  to_char(t.djdate,'yyyy-mm-dd') =  '2022-04-13'  order by djdate desc ) where 1=1 and  rownum < = 30  ;
--select t.section ,t.brxm  from temp_hsdj t where t.djstatus is null and  to_char(t.djdate,'yyyy-mm-dd') =  '2022-04-13'  order by djdate desc  ;
--select * from (select t.section ,t.brxm  from temp_hsdj t where t.djstatus is null and  to_char(t.djdate,'yyyy-mm-dd') =  '2022-04-13'  order by djdate ) where  rownum < = 30  ;
--select * from (select /*+ MATERIALIZE INDEX_ASC(t if_temp_hsdj_djdate) */ t.section ,t.brxm  from temp_hsdj t where t.djstatus is null and  to_char(t.djdate,'yyyy-mm-dd') =  '2022-04-13'  order by to_char(t.djdate,'yyyy-mm-dd') desc) where  rownum < = 30  ;
with a as (select /*+ MATERIALIZE INDEX_ASC(t if_temp_hsdj_djdate) */ t.section ,t.brxm  from temp_hsdj t where t.djstatus is null and  to_char(t.djdate,'yyyy-mm-dd') =  '2022-04-13'  order by t.djdate desc)
select * from a where   rownum < = 30  ;
set termout on
@dpc '' outline ''
rollback;
alter session set current_schema=SYS ;

Plan hash value: 138295244
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                                | Name                       | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                         |                            |      1 |        |       |    34 (100)|          |     30 |00:00:00.01 |      37 |       |       |          |
|   1 |  TEMP TABLE TRANSFORMATION               |                            |      1 |        |       |            |          |     30 |00:00:00.01 |      37 |       |       |          |
|   2 |   LOAD AS SELECT (CURSOR DURATION MEMORY)| SYS_TEMP_0FD9D8104_1930D14 |      1 |        |       |            |          |      0 |00:00:00.01 |      36 |  1024 |  1024 |          |
|   3 |    SORT ORDER BY                         |                            |      1 |   1084 | 41192 |    31   (4)| 00:00:01 |   1219 |00:00:00.01 |      35 |   106K|   106K|96256  (0)|
|*  4 |     TABLE ACCESS BY INDEX ROWID BATCHED  | TEMP_HSDJ                  |      1 |   1084 | 41192 |    30   (0)| 00:00:01 |   1219 |00:00:00.01 |      35 |       |       |          |
|*  5 |      INDEX RANGE SCAN                    | IF_TEMP_HSDJ_DJDATE        |      1 |   1086 |       |     2   (0)| 00:00:01 |   1221 |00:00:00.01 |       3 |       |       |          |
|*  6 |   COUNT STOPKEY                          |                            |      1 |        |       |            |          |     30 |00:00:00.01 |       0 |       |       |          |
|   7 |    VIEW                                  |                            |      1 |   1084 | 91056 |     3   (0)| 00:00:01 |     30 |00:00:00.01 |       0 |       |       |          |
|   8 |     TABLE ACCESS FULL                    | SYS_TEMP_0FD9D8104_1930D14 |      1 |   1084 | 18428 |     3   (0)| 00:00:01 |     30 |00:00:00.01 |       0 |       |       |          |
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

--//取消MATERIALIZE ,INDEX_ASC(t if_temp_hsdj_djdate)提示看到如下:
Plan hash value: 2137499364
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                              | Name                | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                       |                     |      1 |        |       |    31 (100)|          |     30 |00:00:00.01 |      35 |       |       |          |
|*  1 |  COUNT STOPKEY                         |                     |      1 |        |       |            |          |     30 |00:00:00.01 |      35 |       |       |          |
|   2 |   VIEW                                 |                     |      1 |   1084 | 91056 |    31   (4)| 00:00:01 |     30 |00:00:00.01 |      35 |       |       |          |
|*  3 |    SORT ORDER BY STOPKEY               |                     |      1 |   1084 | 41192 |    31   (4)| 00:00:01 |     30 |00:00:00.01 |      35 | 20480 | 20480 |18432  (0)|
|*  4 |     TABLE ACCESS BY INDEX ROWID BATCHED| TEMP_HSDJ           |      1 |   1084 | 41192 |    30   (0)| 00:00:01 |   1219 |00:00:00.01 |      35 |       |       |          |
|*  5 |      INDEX RANGE SCAN                  | IF_TEMP_HSDJ_DJDATE |      1 |   1086 |       |     2   (0)| 00:00:01 |   1221 |00:00:00.01 |       3 |       |       |          |
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

--//最終我也不知道問題在那裡。實際上最佳的方式提示開發在where條件裡面使用to_char,trunc之類的函式儘可能避免,可以開發一直
--//沒長大,不斷反反覆覆範這類錯誤。

SELECT /*+ gather_plan_statistics */ *
  FROM (  SELECT t.section, t.brxm
            FROM temp_hsdj t
           WHERE     t.djstatus IS NULL
                 AND t.djdate BETWEEN TO_DATE ('2022-04-13', 'yyyy-mm-dd')
                                  AND   TO_DATE ('2022-04-13', 'yyyy-mm-dd')
                                      + 86359 / 86400
        ORDER BY djdate DESC)
 WHERE 1 = 1 AND ROWNUM <= 30;


-----------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                      | Name               | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | A-Rows |   A-Time   | Buffers |
-----------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT               |                    |      1 |        |       |     3 (100)|          |     30 |00:00:00.01 |       4 |
|*  1 |  COUNT STOPKEY                 |                    |      1 |        |       |            |          |     30 |00:00:00.01 |       4 |
|   2 |   VIEW                         |                    |      1 |     30 |  2520 |     3   (0)| 00:00:01 |     30 |00:00:00.01 |       4 |
|*  3 |    TABLE ACCESS BY INDEX ROWID | TEMP_HSDJ          |      1 |   1023 | 27621 |     3   (0)| 00:00:01 |     30 |00:00:00.01 |       4 |
|*  4 |     INDEX RANGE SCAN DESCENDING| I_TEMP_HSDJ_DJDATE |      1 |     30 |       |     2   (0)| 00:00:01 |     31 |00:00:00.01 |       2 |
-----------------------------------------------------------------------------------------------------------------------------------------------

--//你可以發現操作id=4 充分利用了 INDEX RANGE SCAN DESCENDING ,降序讀取的方式。前面的讀取IF_TEMP_HSDJ_DJDATE是3(注意我還建立的索引是壓縮的)
--//而前面I_TEMP_HSDJ_DJDATE的邏輯讀是2。最終的邏輯讀更小。

3.總結:
--//再次提醒開發在謂詞條件裡面在使用to_char,trunc,lower,upper之類的函式要小心,不要反反覆覆的範低階錯誤,看上去一輩子也長不大。
--//至少對於自己所從事的職業附上一點點責任,看上去更加專業一點。

來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/267265/viewspace-2887038/,如需轉載,請註明出處,否則將追究法律責任。

相關文章