database - 是否可以为在 View 中转换为 DATE 的 TIMESTAMP 列构建基于 Oracle 函数的索引？

Question

我在 Oracle 11g 文档和论坛中搜索了有关如何获取此 View 和查询以使用基于函数的索引的示例，但到目前为止我一无所获。感谢任何帮助。

我使用的应用程序不支持直接查询的 TIMESTAMP 数据类型，但支持在 View 中将其转换为 DATE 的数据类型。但是，我随后必须使用时间戳函数查询此转换列。这有点往返。第 3 方应用程序发送与我在 SQL*Plus/SQL Developer 中使用的相同的查询并显示在下方。

我实际上并不需要 TIMESTAMP 数据类型的小数秒粒度，只是出于数据供应商的原因需要在该数据库中使用它。

据我所知，当函数应用于该列时，不会使用列索引。我无法完成的是构建一个适当的基于函数的索引来避免对数百万行(在 200 万到 6 亿行之间，具体取决于表)进行全表扫描。我不想使用提示，但在这一点上，任何事情都比全表扫描好，我愿意接受所有建议。

这是所需的 View :

SELECT
CAST(SAMPLE_TABLE.TIMESTAMP_COLUMN as DATE) as TIMESTAMP_COLUMN
FROM TEST_USER.SAMPLE_TABLE;

这是一个带有解释计划前缀的示例查询:

explain plan for
select * FROM SAMPLE_VIEW WHERE TIMESTAMP_COLUMN = timestamp '2010-08-10 12:00:00';

select plan_table_output
from table(dbms_xplan.display('plan_table',null,'all'));
-----------------------------------------------------------------------------------------------------
| Id  | Operation            | Name         | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
-----------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |              |   200K|  1562K|   169K  (1)| 00:33:56 |       |       |
|   1 |  PARTITION LIST ALL  |              |   200K|  1562K|   169K  (1)| 00:33:56 |     1 |     6 |
|   2 |   PARTITION RANGE ALL|              |   200K|  1562K|   169K  (1)| 00:33:56 |     1 |    49 |
|*  3 |    TABLE ACCESS FULL | SAMPLE_TABLE |   200K|  1562K|   169K  (1)| 00:33:56 |     1 |   294 |
-----------------------------------------------------------------------------------------------------     
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------    
   1 - SEL$F5BB74E1
   3 - SEL$F5BB74E1 / SAMPLE_TABLE@SEL$2  
Predicate Information (identified by operation id):
---------------------------------------------------
   3 - filter(INTERNAL_FUNCTION(CAST(INTERNAL_FUNCTION("TIMESTAMP_COLUMN") AS DATE))=TIMESTAMP_COLUMN' 
              2010-08-10 12:00:00.000000000')  
Column Projection Information (identified by operation id):
-----------------------------------------------------------   
   1 - "TIMESTAMP_COLUMN"[TIMESTAMP WITH LOCAL TIME ZONE,11]
   2 - "TIMESTAMP_COLUMN"[TIMESTAMP WITH LOCAL TIME ZONE,11]
   3 - "TIMESTAMP_COLUMN"[TIMESTAMP WITH LOCAL TIME ZONE,11]

当然，只要我从 View 中删除 Cast 函数，就会按预期使用索引:

改变的观点:

SELECT
TIMESTAMP_COLUMN
FROM TEST_USER.SAMPLE_TABLE;

相同的查询:

explain plan for
select * FROM SAMPLE_VIEW WHERE TIMESTAMP_COLUMN = timestamp '2010-08-10 12:00:00';

select plan_table_output
from table(dbms_xplan.display('plan_table',null,'all'));
------------------------------------------------------------------------------------
| Id  | Operation        | Name              | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT |                   |     2 |    22 |     3   (0)| 00:00:01 |
|*  1 |  INDEX RANGE SCAN| TIMESTAMP_COL_IDX |     2 |    22 |     3   (0)| 00:00:01 |
------------------------------------------------------------------------------------ 
Query Block Name / Object Alias (identified by operation id):
------------------------------------------------------------- 
   1 - SEL$F5BB74E1 / SAMPLE_TABLE@SEL$2
Predicate Information (identified by operation id):
---------------------------------------------------   
   1 - access("TIMESTAMP_COLUMN"=TIMESTAMP' 2010-08-10 12:00:00.000000000')
Column Projection Information (identified by operation id):
-----------------------------------------------------------
   1 - "TIMESTAMP_COLUMN"[TIMESTAMP WITH LOCAL TIME ZONE,11]

这是在没有转换函数的情况下使用的普通索引 DDL (TIMESTAMP_COL_IDX)。我使用 SQL Developer 的 GUI 构建了这个:

CREATE INDEX "TEST_USER"."TIMESTAMP_COL_IDX" ON "TEST_USER"."SAMPLE_TABLE" ("TIMESTAMP_COLUMN") 
  PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS 
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "TEST" ;

这是我无法调用并且可能编写不当的基于函数的索引 DDL:

CREATE INDEX "TEST_USER"."TIMESTAMP_FBI_IDX" ON "TEST_USER"."SAMPLE_TABLE" (CAST("TIMESTAMP_COLUMN" AS DATE)) 
  PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS 
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "TEST" ;

我已经收集了 View 所基于的表的统计信息，但在 11g 中我认为在构建索引后这不是“必需的”。它没有改变执行计划。

谢谢。

编辑 #1

当我说:“但是，我必须使用时间戳函数查询这个 cast 列。这有点往返。”...

...我应该澄清我需要使用的整个工作流程，这是造成这种情况的原因。以下是按时间顺序排列的工作流程:

1. 数据以 TIMESTAMP 格式传送到 Oracle 数据库，并且必须这样存储。
2. Web 发布应用程序不支持 TIMESTAMP 但允许 DATE，因此使用 Cast。
3. Web 查询应用程序必须查询为 TIMESTAMP 或在 DATE 上使用时间戳函数。

这就是我所说的数据“往返”的意思；数据库和 Web 应用程序都使用 TIMESTAMP，但发布到 Web 应用程序的中间件不能，从而导致这种导致全表扫描的昂贵转换场景。

Answer 1

正如 Jokke Heikkilä 所指出的，您正在使用时间戳值进行查询。这会导致比较的左侧(即 View 的日期列)隐式转换为与常量相同的数据类型。你正在有效地做:

select * FROM SAMPLE_VIEW
WHERE cast(TIMESTAMP_COLUMN as timestamp) = timestamp '2010-08-10 12:00:00';

...正如您已经指出的那样，当函数应用于列时不会使用索引。

如果您将右侧设为日期，则将使用索引:

explain plan for
select * FROM SAMPLE_VIEW
WHERE TIMESTAMP_COLUMN = cast(timestamp '2010-08-10 12:00:00' as date);

select plan_table_output
from table(dbms_xplan.display('plan_table',null,'all'));

----------------------------------------------------------------------------------------                                                                                                                                                                                                                     
| Id  | Operation        | Name                | Rows  | Bytes | Cost (%CPU)| Time     |                                                                                                                                                                                                                     
----------------------------------------------------------------------------------------                                                                                                                                                                                                                     
|   0 | SELECT STATEMENT |                     |     1 |     9 |     1   (0)| 00:00:01 |                                                                                                                                                                                                                     
|*  1 |  INDEX RANGE SCAN| TIMESTAMP_FBI_IDX   |     1 |     9 |     1   (0)| 00:00:01 |                                                                                                                                                                                                                     
----------------------------------------------------------------------------------------                                                                                                                                                                                                                     

Query Block Name / Object Alias (identified by operation id):                                                                                                                                                                                                                                                
-------------------------------------------------------------                                                                                                                                                                                                                                                

   1 - SEL$F5BB74E1 / SAMPLE_TABLE@SEL$2                                                                                                                                                                                                                                                                     

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

   1 - access(CAST(INTERNAL_FUNCTION("TIMESTAMP_COLUMN") AS                                                                                                                                                                                                                                                  
              date)=CAST(TIMESTAMP' 2010-08-10 12:00:00.000000000' AS date))                                                                                                                                                                                                                                 

Column Projection Information (identified by operation id):                                                                                                                                                                                                                                                  
-----------------------------------------------------------                                                                                                                                                                                                                                                  

   1 - CAST(INTERNAL_FUNCTION("TIMESTAMP_COLUMN") AS date)[DATE,7]      

你不必投右手边，它只需要是一个日期；如果你正在寻找午夜那么你可以使用日期文字，否则你可以使用 to_date:

WHERE TIMESTAMP_COLUMN = to_date('2010-08-10 12:00:00', 'YYYY-MM-DD HH24:MI:SS');

您好像说您正在使用的应用程序只支持日期，所以大概您无论如何都可以传递一个正确的日期值；虽然我对你说“然后我必须使用时间戳函数查询这个转换列”感到困惑，并且应用程序在它生成的查询中指定了时间戳文字，这似乎没有关系。

如果您需要能够按日期或时间戳数据类型进行查询，那么您可以在 View 中同时包含这两种数据:

CREATE OR REPLACE VIEW SAMPLE_VIEW AS
SELECT TIMESTAMP_COLUMN,
  CAST(SAMPLE_TABLE.TIMESTAMP_COLUMN as DATE) as DATE_COLUMN
FROM TEST_USER.SAMPLE_TABLE;

然后在相关列中查询您所拥有的值的数据类型:

WHERE TIMESTAMP_COLUMN = timestamp '2010-08-10 12:00:00'

或

WHERE DATE_COLUMN = to_date('2010-08-10 12:00:00', 'YYYY-MM-DD HH24:MI:SS')

第一个将使用 timestamp_col_idx，第二个将使用 timestamp_fbi_idx。当然，您的选择列表也需要更改才能获得正确的列。

作为另一种可能的替代方案，如果您可以更改从仅限日期层发送的查询，您可以将其转换为时间戳并直接查询表:

select * FROM SAMPLE_TABLE
WHERE TIMESTAMP_COLUMN =
  cast(to_date('2010-08-10 12:00:00', 'YYYY-MM-DD HH24:MI:SS') as timestamp);

.. 但这取决于查询的构建方式以及您提供日期值的方式。不过，在那种情况下，您不需要 View 或 FBI。