的Oracle SQL索引日期VS指数TRUNC（DATE）

问题描述：

我有一个名为DEXTRACTO表，我需要咨询基于F_EXTRACTO列了一段时间（巫DATE格式）与BETWEEN DATE1 AND DATE2条件（DATE1和DATE2可以改变）。下面是表的一些数据：的Oracle SQL索引日期VS指数TRUNC（DATE）

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。有人能告诉我为什么这两种方法存在这么大的差别？如果您需要一些辅助信息，请告诉我。

除了收集统计数据，这可能是有趣的谓语信息看上去太 - 尽管你可能需要重新创建你的计划表中，因为它的报告说，作为一个老版本，而不是现在显示的信息。另外，截断日期的联邦调查局只有在你的日期值有午夜以外的时间才会有用;即使如此，如果您搜索的开始/结束日期也有非午夜时间，并且您希望包括他们的整个日子。 –

答

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

此外，EXPLAIN PLAN不保证该计划是什么将被实际使用。我宁愿运行查询，然后用dbms_xplan.display_cursor检查实际执行计划。查看v$sql/v$sqlarea视图以了解执行细节也很有意义。

太棒了！我不知道'DBMS_STATS'和'DBMS_XPLAN'。我试过这种方式，实际上，两个查询都显示相同的行（13M）和相同的成本（283K），但两种计划都有所不同。所以...现在我知道不同成本的原因，并可以考虑更好的解决方案。谢谢，你们俩！ –

答

我想使用的索引，但我不知道至极的做法是更好的。

你会明确不使用索引来从表访问所有14M行（如你的例子）。收集统计信息后，重试解释计划而不提示，您将以低得多的索引访问成本访问FULL TABLE SCAN。根据成本INDEX/FTS的比率，您可以估算值得通过索引访问的表格部分。

要进入长达数个月的INDEX ACCESSs可能会更有效，但高于某个阈值FULL SCAN会更好（同时检查解释计划并执行 - 这可能会得到不同的结果）。

在您的使用案例中，我没有看到使用FBI的任何优点。消极的一面是非确定性顺序，并且仅支持区间的每日粒度。

是的！没有提示'FULL TABLE SCAN'具有较低的成本！非常感谢！！ –

的Oracle SQL索引日期VS指数TRUNC（DATE）

相关推荐