[20140111]trunc与函数索引.txt

经常看sql语句，经常会遇到使用函数trunc(create_date)的情况，这种情况经常出现。
很多情况下，是开发人员的无知，实际上修改为create_date between trunc(sysdate) and trunc(sysdate+1)-1/86400就ok了。
就可以避免函数索引的建立，但是我这里提到的情况是这种函数索引是否对普通的查询是否有效。

拿scott.emp表来说。

create index if_emp_hiredate on emp(trunc(hiredate));

SCOTT@test01p> select * from emp where hiredate=to_date('1980/12/17','yyyy-mm-dd');

     EMPNO ENAME      JOB              MGR HIREDATE                   SAL       COMM     DEPTNO
---------- ---------- --------- ---------- ------------------- ---------- ---------- ----------
      7369 SMITH      CLERK           7902 1980-12-17 00:00:00        800                    20

SCOTT@test01p> @dpc '' ''
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID  1ct8dum3uyy7u, child number 0
-------------------------------------
select * from emp where hiredate=to_date('1980/12/17','yyyy-mm-dd')

Plan hash value: 4059437819

-------------------------------------------------------------------------------------
| Id  | Operation                           | Name            | E-Rows | Cost (%CPU)|
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |                 |        |     2 (100)|
|*  1 |  TABLE ACCESS BY INDEX ROWID BATCHED| EMP             |      1 |     2   (0)|
|*  2 |   INDEX RANGE SCAN                  | IF_EMP_HIREDATE |      1 |     1   (0)|
-------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter("HIREDATE"=TO_DATE(' 1980-12-17 00:00:00', 'syyyy-mm-dd
              hh24:mi:ss'))
   2 - access("EMP"."SYS_NC00009$"=TRUNC(TO_DATE(' 1980-12-17 00:00:00',
              'syyyy-mm-dd hh24:mi:ss')))

--可以发现查询使用函数索引，即使是查询条件hiredate=to_date('1980/12/17','yyyy-mm-dd')，这种情况与使用substr(a,1,N)一样。
--即使使用hiredate between to_date('1980/12/17','yyyy-mm-dd')-1 and to_date('1980/12/17','yyyy-mm-dd')+1;条件也一样。

PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID  0phtjuq0q7u8u, child number 0
-------------------------------------
select * from emp where hiredate between
to_date('1980/12/17','yyyy-mm-dd')-1 and
to_date('1980/12/17','yyyy-mm-dd')+1

Plan hash value: 4059437819

-------------------------------------------------------------------------------------
| Id  | Operation                           | Name            | E-Rows | Cost (%CPU)|
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |                 |        |     2 (100)|
|*  1 |  TABLE ACCESS BY INDEX ROWID BATCHED| EMP             |      1 |     2   (0)|
|*  2 |   INDEX RANGE SCAN                  | IF_EMP_HIREDATE |      1 |     1   (0)|
-------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter(("HIREDATE"              hh24:mi:ss') AND "HIREDATE">=TO_DATE(' 1980-12-16 00:00:00', 'syyyy-mm-dd
              hh24:mi:ss')))
   2 - access("EMP"."SYS_NC00009$">=TRUNC(TO_DATE(' 1980-12-16 00:00:00',
              'syyyy-mm-dd hh24:mi:ss')) AND "EMP"."SYS_NC00009$"              1980-12-18 00:00:00', 'syyyy-mm-dd hh24:mi:ss')))

--反过来讲并不是讲鼓励大家建立这样的函数索引，而是如果应用中已经存在trunc(hiredate)这样的条件，并且hiredate = :1或者
--hiredate between :b1 and :b2这样的查询条件存在，要考虑是否可以减少1个索引的建立，比如如果trunc(create_date)条件返回在
--100条以内，也许create_date这样的索引可以不建立，同样可以获得很好的效果，当然最好测试评估。另外这样的函数索引重复值很
--高，可以选择索引前缀压缩。

SCOTT@test01p> @ver
BANNER                                                                               CON_ID
-------------------------------------------------------------------------------- ----------
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production              0

SCOTT@test> @ver
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production

create table t as select rownum id ,sysdate-100+rownum/100 create_date,lpad('X',80,'X') vc from dual connect by levelselect trunc(create_date),count(*) from t group by trunc(create_date) order by 1;
create index if_t_create_date on t (trunc(create_date)) compress 1;
create index i_t_create_date on t (create_date) ;
exec dbms_stats.gather_table_stats(user,'T');

select /*+ gather_plan_statistics */ id,create_date,substr(vc,1,20) c20 from t where  create_date between trunc(sysdate) and trunc(sysdate+1)-1/86400;

SCOTT@test> @dpc '' ''
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID  actssvadvtbsd, child number 0
-------------------------------------
select /*+ gather_plan_statistics */ id,create_date,substr(vc,1,20) c20
from t where  create_date between trunc(sysdate) and
trunc(sysdate+1)-1/86400

Plan hash value: 3730485863

-----------------------------------------------------------------------------------------------------------------------
| Id  | Operation                    | Name            | Starts | E-Rows | Cost (%CPU)| A-Rows |   A-Time   | Buffers |
-----------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |                 |      1 |        |     3 (100)|     38 |00:00:00.01 |       5 |
|*  1 |  FILTER                      |                 |      1 |        |            |     38 |00:00:00.01 |       5 |
|   2 |   TABLE ACCESS BY INDEX ROWID| T               |      1 |     39 |     3   (0)|     38 |00:00:00.01 |       5 |
|*  3 |    INDEX RANGE SCAN          | I_T_CREATE_DATE |      1 |     39 |     2   (0)|     38 |00:00:00.01 |       3 |
-----------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter(TRUNC(SYSDATE@!)   3 - access("CREATE_DATE">=TRUNC(SYSDATE@!) AND "CREATE_DATE"              407407407407407407)

--可以发现这样逻辑读仅仅5个。使用普通索引.

select /*+ gather_plan_statistics index(t if_t_create_date) */ id,create_date,substr(vc,1,20) c20 from t
where  create_date between trunc(sysdate) and trunc(sysdate+1)-1/86400;

SCOTT@test> @dpc '' ''
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID  anu23m7rj13cx, child number 0
-------------------------------------
select /*+ gather_plan_statistics index(t if_t_create_date) */
id,create_date,substr(vc,1,20) c20 from t where  create_date between
trunc(sysdate) and trunc(sysdate+1)-1/86400

Plan hash value: 231836870

------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                    | Name             | Starts | E-Rows | Cost (%CPU)| A-Rows |   A-Time   | Buffers |
------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |                  |      1 |        |     4 (100)|     38 |00:00:00.01 |       5 |
|*  1 |  FILTER                      |                  |      1 |        |            |     38 |00:00:00.01 |       5 |
|*  2 |   TABLE ACCESS BY INDEX ROWID| T                |      1 |     39 |     4   (0)|     38 |00:00:00.01 |       5 |
|*  3 |    INDEX RANGE SCAN          | IF_T_CREATE_DATE |      1 |     99 |     2   (0)|     38 |00:00:00.01 |       3 |
------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter(TRUNC(SYSDATE@!)   2 - filter(("CREATE_DATE">=TRUNC(SYSDATE@!) AND "CREATE_DATE"              407407407407407407))
   3 - access("T"."SYS_NC00004$">=TRUNC(TRUNC(SYSDATE@!)) AND
              "T"."SYS_NC00004$"

--上下执行对比，cost一样。
--改名
alter index  i_t_create_date rename to iz_t_create_date;

select /*+ gather_plan_statistics */ id,create_date,substr(vc,1,20) c20 from t where  create_date between trunc(sysdate) and trunc(sysdate+1)-1/86400;

SCOTT@test> @dpc '' ''
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------
SQL_ID  actssvadvtbsd, child number 0
-------------------------------------
select /*+ gather_plan_statistics */ id,create_date,substr(vc,1,20) c20
from t where  create_date between trunc(sysdate) and
trunc(sysdate+1)-1/86400

Plan hash value: 1209507809

------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                    | Name             | Starts | E-Rows | Cost (%CPU)| A-Rows |   A-Time   | Buffers |
------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |                  |      1 |        |     3 (100)|     38 |00:00:00.01 |       5 |
|*  1 |  FILTER                      |                  |      1 |        |            |     38 |00:00:00.01 |       5 |
|   2 |   TABLE ACCESS BY INDEX ROWID| T                |      1 |     39 |     3   (0)|     38 |00:00:00.01 |       5 |
|*  3 |    INDEX RANGE SCAN          | IZ_T_CREATE_DATE |      1 |     39 |     2   (0)|     38 |00:00:00.01 |       3 |
------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter(TRUNC(SYSDATE@!)   3 - access("CREATE_DATE">=TRUNC(SYSDATE@!) AND "CREATE_DATE"              07407407407407407)

-- 可以发现如果索引名字重新排序，优先选择依旧是普通索引。
-- 如果数据聚集性很好，逻辑读会很小。
-- 如果数据分布不聚集,大家有兴趣测试一下,不做了。

--换一个思路，大家认为还有什么函数索引也能使用在一般常规的查询呢？我仅仅知道substr（name,1,N) 以及trunc。
--其他的呢？

时间： 2024-07-29 06:05:34

[20140111]trunc与函数索引.txt的相关文章

[20170402]函数索引standard_hash.txt

[20170402]函数索引standard_hash.txt --//这几天放假,在家里看书<<Apress.Expert.Oracle.Indexing.and.Access.Paths.Maximum.Performance.for.Your.Database.2nd.Edition.148421983X.pdf>> --//这本书比较合适初学者,我以前看过第一版,所以这个版本看的很快. --//里面提到函数standard_hash,可以用来满足一般的查询,做一个记录. 1

[20150803]使用函数索引注意的问题.txt

[20150803]使用函数索引注意的问题.txt --昨天在10g下优化时遇到一个奇怪的问题,做一个记录: --首先说明一下,最近一段时间在做一个项目的优化,这个项目实际上ETL项目: http://baike.baidu.com/link?url=OlbL-2LIVu06toxpf5-PxgekWlOtRgrdwPhGYNx9TgCnCC5WdAGiwOWQXcfUbujcUNwUU6ojdanwP1wSbC_Vf95sgbq7PonHaEZWBVrqkQm ETL,是英文 Extract-

[20171202]关于函数索引的状态.txt

[20171202]关于函数索引的状态.txt --//我曾经在一篇贴子提到索引可以disable吗?链接: --//http://blog.itpub.net/267265/viewspace-2123537/ --//实际上仅仅函数索引能disable,为什么呢?实际上自己以前并不搞清楚实际上这个跟oracle使用函数的特殊性有关. --//如果一个表删除某个字段,对应的索引也会删除.如果定义的函数删除了,对应的函数索引呢?通过例子来说明问题: 1.环境: SCOTT@test01p> @

[20120130]函数索引与取max值的问题1.txt

[20120130]函数索引与取max值的问题1.txt 1.建立测试例子:SQL> select * from v$version ;BANNER----------------------------------------------------------------Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64biPL/SQL Release 10.2.0.3.0 - ProductionCORE    10

0429建立Extended Statistics函数索引问题

[20160429]建立Extended Statistics 和函数索引问题.txt --11G支持相关数据的统计分析,比如如果两个字段存在相关性通过分析,能够得到更加良好的统计信息,以及生成好的执行计划. --但是如果结合函数索引呢?通过一个简单的例子来说明: --前次做的测试: http://blog.itpub.net/267265/viewspace-2089119/ 1.环境: SCOTT@book> @ &r/ver1 PORT_STRING                  

0427建立Extended Statistics函数索引问题

[20160427]建立Extended Statistics 和函数索引问题.txt --11G支持相关数据的统计分析,比如如果两个字段存在相关性通过分析,能够得到更加良好的统计信息,以及生成好的执行计划. --但是如果结合函数索引呢?通过一个简单的例子来说明: 1.环境: SCOTT@book> @ &r/ver1 PORT_STRING                    VERSION        BANNER ------------------------------ ---

[20150513]函数索引与CURSOR_SHARING=FORCE

[20150513]函数索引与CURSOR_SHARING=FORCE.txt --经常awr报表,大量听到的建议是你们的应用没有使用绑定变量.国内的许多项目这个问题更加严重,我敢打赌国内80%甚至更高的比例在 --应用中没有绑定变量(OLTP系统). --如果一个新项目我只要看一下程序使用绑定变量的情况,就知道这个项目是垃圾还是豆腐渣工程.到目前为止我接触的项目仅仅有1个做 --的稍微好一点. --如果不修改代码,一个最简单的方式就是修改参数CURSOR_SHARING = FORCE(补充一

Oracle之函数索引

Oracle之函数索引 在Oracle中,有一类特殊的索引,称为函数索引(Function-Based Indexes,FBI),它基于对表中列进行计算后的结果创建索引.函数索引在不修改应用程序的逻辑基础上提高了查询性能.如果没有函数索引,那么任何在列上执行了函数的查询都不能使用这个列的索引.当在查询中包含该函数时,数据库才会使用该函数索引.函数索引可以是一个B-Tree索引或位图索引. 用于生成索引的函数可以是算术表达式,也可以是一个包含SQL函数.用户定义PL/SQL函数.包函数,或C调用的

TO_DATE函数索引报错ORA-01743

开发同学有一个需求,如下这张表: CREATE TABLE TBL_EFFDATE (ROUTID NUMBER(20,0) NOT NULL, EFFDTE CHAR(7), EDDATE CHAR(7), ICID CHAR(1), FREQ CHAR(7) ); 其中EFFDTE保存的是DDMONYY格式的日期,由于表之前未有归档机制,因此产生了许多垃圾数据,现在需要根据EFFDTE删除16年以前的所有数据,表的数据量在百万级,16年以前的占了绝大部分. 对于这需求来说,SQL的条件很明确