Oracle 关于date 字段索引使用测试

原创布衣&凡尘 2022-07-21

2988

背景：

今天看到一个问答，发现个知识点，于是做了个简单的测试，希望有所帮助。

准备数据

SQL> create table t1 (id number,t_date date);
SQL>insert into t1 values(1,to_date('2022-01-11','yyyy-mm-dd'));
SQL>insert into t1 values(2,to_date('2022-02-11','yyyy-mm-dd'));
SQL>insert into t1 values(3,to_date('2022-03-11','yyyy-mm-dd'));
SQL>insert into t1 values(4,to_date('2022-04-11','yyyy-mm-dd'));
SQL>insert into t1 values(5,to_date('2022-05-11','yyyy-mm-dd'));
SQL>insert into t1 values(6,to_date('2022-06-11','yyyy-mm-dd'));
SQL>insert into t1 values(7,to_date('2022-07-11','yyyy-mm-dd'));
SQL>insert into t1 values(8,to_date('2022-08-11','yyyy-mm-dd'));
SQL>insert into t1 values(9,to_date('2022-09-11','yyyy-mm-dd'));
SQL>insert into t1 values(10,to_date('2022-10-11','yyyy-mm-dd'));
SQL>insert into t1 values(11,to_date('2022-11-11','yyyy-mm-dd'));
SQL>insert into t1 values(12,to_date('2022-12-11','yyyy-mm-dd'));
SQL> commit;
SQL> create index t_date_ind on t1 (t_date);

一、to_date 格式不同，影响查询是否走索引

1、未使用索引： where t_date=to_date(‘2022-03-11’,‘yyyy-mm-dd’);

SQL> Select * from t1 where t_date=to_date('2022-03-11','yyyy-mm-dd');

        ID T_DATE
---------- ------------------
         3 11-MAR-22
Execution Plan
----------------------------------------------------------
Plan hash value: 3617692013
--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |     1 |    22 |     2   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| T1   |     1 |    22 |     2   (0)| 00:00:01 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter("T_DATE"=TO_DATE(' 2022-03-11 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))

2、使用索引： where t_date=to_date(‘2022-03-11’,‘yyyy-mm-dd hh24:mi:ss’);

SQL> Select * from t1 where t_date=to_date('2022-03-11','yyyy-mm-dd hh24:mi:ss');

        ID T_DATE
---------- ------------------
         3 11-MAR-22
Execution Plan
----------------------------------------------------------
Plan hash value: 4277157620
------------------------------------------------------------------------------------------
| Id  | Operation                   | Name       | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |            |     1 |    22 |     2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T1         |     1 |    22 |     2   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | T_DATE_IND |     1 |       |     1   (0)| 00:00:01 |
------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("T_DATE"=TO_DATE('2022-03-11','yyyy-mm-dd hh24:mi:ss'))

二、数据量在全表数据的10%以上，有可能走的是全表扫描，而不走索引。

1）查询结果在全表数据的10% : 未走索引


SQL> Select * from t1 where t_date>to_date('2022-03-11','yyyy-mm-dd hh24:mi:ss');
        ID T_DATE
---------- ------------------
         4 11-APR-22
         5 11-MAY-22
         6 11-JUN-22
         7 11-JUL-22
         8 11-AUG-22
         9 11-SEP-22
        10 11-OCT-22
        11 11-NOV-22
        12 11-DEC-22
9 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 3617692013
--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |     9 |   198 |     2   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| T1   |     9 |   198 |     2   (0)| 00:00:01 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter("T_DATE">TO_DATE('2022-03-11','yyyy-mm-dd hh24:mi:ss'))

2）查询结果未达到全表数据的10%：走索引

SQL> Select * from t1 where t_date>to_date('2022-11-11','yyyy-mm-dd hh24:mi:ss');

        ID T_DATE
---------- ------------------
        12 11-DEC-22
Execution Plan
----------------------------------------------------------
Plan hash value: 4277157620
------------------------------------------------------------------------------------------
| Id  | Operation                   | Name       | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |            |     1 |    22 |     2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T1         |     1 |    22 |     2   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | T_DATE_IND |     1 |       |     1   (0)| 00:00:01 |
------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("T_DATE">TO_DATE('2022-11-11','yyyy-mm-dd hh24:mi:ss'))

三、对字段进行类型转换，不会走索引

SQL> Select * from t1 where to_char(t_date,'yyyy-mm-dd hh24:mi:ss')='2022-03-11 00:00:00';

        ID T_DATE
---------- ------------------
         3 11-MAR-22
Execution Plan
----------------------------------------------------------
Plan hash value: 3617692013
--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |     1 |    22 |     2   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| T1   |     1 |    22 |     2   (0)| 00:00:01 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter(TO_CHAR(INTERNAL_FUNCTION("T_DATE"),'yyyy-mm-dd hh24:mi:ss')='2022-03-11 00:00:00')

文档下载：

《Oracle Date 字段索引使用测试.dbf》文档下载https://www.modb.pro/doc/72521
《PL/Java.pdf》文档下载：https://www.modb.pro/doc/70867
《GP的资源队列.pdf》文档下载：https://www.modb.pro/doc/67644
《Greenplum psql客户端免交互执行SQL.pdf》https://www.modb.pro/doc/69806
《Oracle 自动收集统计信息机制》：https://www.modb.pro/db/403670
《Oracle_索引重建—优化索引碎片》：https://www.modb.pro/db/399543
《DBA_TAB_MODIFICATIONS表的刷新策略测试》https://www.modb.pro/db/414692

欢迎点赞支持或留言指正

最后修改时间：2022-07-21 10:06:04

「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」

关注作者