sql - Oracle SQL 索引 DATE 与索引 TRUNC(DATE)

Question

我有一个名为DEXTRACTO的表，我需要根据列F_EXTRACTO(巫婆是DATE格式)查询一段时间BETWEEN DATE1 AND DATE2 条件(DATE1 和 DATE2 可以更改)。以下是该表的一些数据:

SQL> SELECT MIN(F_EXTRACTO), MAX(F_EXTRACTO), COUNT(1)
  2    FROM DEXTRACTO
  3  /

MIN(F_EXTRACTO) MAX(F_EXTRACTO)   COUNT(1)
--------------- --------------- ----------
03/01/2005      06/01/2017        13772806


SQL> SELECT COUNT(1) FROM DEXTRACTO WHERE F_EXTRACTO IS NULL
  2  /

  COUNT(1)
----------
         0

SQL> 

我想使用索引，但我不知道哪种方法更好。我应该在 F_EXTRACTO 列上使用它吗？或者我应该在 TRUNC(F_EXTRACTO) 上使用索引吗？我知道将索引与函数一起使用不是一个好主意，但是测试这两种方法我得到了这个......

SQL> create index INDEX_DATE on DEXTRACTO (F_EXTRACTO)
  2  /

Index created

SQL> create index INDEX_TRUNC on DEXTRACTO (TRUNC(F_EXTRACTO))
  2  /

Index created

SQL> 

F_EXTRACTO 上的测试索引:

SQL> explain plan for
  2  
  2  SELECT /*+ index (dextracto INDEX_DATE)  */ *
  3    FROM dextracto
  4   WHERE f_extracto
  5         BETWEEN to_date('01/01/2005','dd/mm/yyyy') AND SYSDATE
  6  /

Explained

SQL> select plan_table_output from table(dbms_xplan.display());

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
---------------------------------------------------------------------------
| Id  | Operation                    | Name       | Rows  | Bytes | Cost  |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |            |    12M|  1088M|   250K|
|   1 |  FILTER                      |            |       |       |       |
|   2 |   TABLE ACCESS BY INDEX ROWID| DEXTRACTO  |    12M|  1088M|   250K|
|   3 |    INDEX RANGE SCAN          | INDEX_DATE |    12M|       | 36972 |
---------------------------------------------------------------------------
Note
-----
   - 'PLAN_TABLE' is old version

13 rows selected

SQL> 

TRUNC(F_EXTRACTO) 上的测试索引:

SQL> explain plan for
  2  
  2  SELECT /*+ index (dextracto INDEX_TRUNC) */ *
  3    FROM dextracto
  4   WHERE TRUNC(f_extracto)
  5         BETWEEN to_date('01/01/2005','dd/mm/yyyy') AND SYSDATE
  6  /

Explained

SQL> select plan_table_output from table(dbms_xplan.display());

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
----------------------------------------------------------------------------
| Id  | Operation                    | Name        | Rows  | Bytes | Cost  |
----------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |             | 32437 |  2787K|  1130 |
|   1 |  FILTER                      |             |       |       |       |
|   2 |   TABLE ACCESS BY INDEX ROWID| DEXTRACTO   | 32437 |  2787K|  1130 |
|   3 |    INDEX RANGE SCAN          | INDEX_TRUNC | 58387 |       |   169 |
----------------------------------------------------------------------------
Note
-----
   - 'PLAN_TABLE' is old version

13 rows selected

SQL> 

所以......如果我使用索引F_EXTRACTO，成本是250000，但如果我使用索引TRUNC(F_EXTRACTO)，成本是1130。有人可以告诉我为什么吗这两种方法之间存在如此大的差异吗？如果您需要一些其他信息，请告诉我。

Answer 1

估计行数(12M 与 58387)之间的巨大差异很可能归因于过时的统计信息。我建议在添加索引后收集统计信息(例如，使用 DBMS_STATS.gather_table_stats )。

此外，EXPLAIN PLAN 并不保证该计划就是实际使用的计划。我宁愿运行查询，然后使用 dbms_xplan.display_cursor 检查实际执行计划。查看 v$sql/v$sqlarea View 以获取执行详细信息也是有意义的。