ITPub博客
首页 博客 专栏 学院 社区 IT新媒体评选
发博

首页 > 数据库 > Oracle > ​[20220414]Function based indexes and cursor sharing2.txt

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

原创 Oracle 作者：lfree 时间：2022-04-14 10:49:59 0 删除 编辑

[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 ( https://tanelpoder.com )
   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博客 ” ，链接：https://blog.itpub.net/267265/viewspace-2887038/ ，如需转载，请注明出处，否则将追究法律责任。

0
0
上一篇： [20220414]toad与绑定变量peek.txt
下一篇： [20220414]toad调用执行脚本问题.txt
请登录后发表评论 登录
全部评论
lfree
专家姓名：张文胜
熟悉oracle相关技术，擅长sql优化，rman备份与恢复，熟悉linux shell编程。

注册时间：2008-01-03

  • 博文量
    4006
  • 访问量
    7970691

最新文章

支持我们 作者招募 用户协议 FAQ Contact Us

北京盛拓优讯信息技术有限公司. 版权所有  京ICP备16024965号-8 北京市公安局海淀分局网监中心备案编号：11010802021510 niuxiaotong@pcpop.com 17352615567 未成年人举报专区

广播电视节目制作经营许可证(京) 字第1234号 中国互联网协会会员

违法和不良信息举报电话:18101376593