Why does Oracle implicitly add SYS_EXTRACT_UTC to index?

user_5502785 0
Viewed 332

I have a table that contains a TIMESTAMP(6) WITH TIME ZONE column, as well as a INTERVAL DAY(4) TO SECOND(0) column. For the sake of clarity, let's assume the following DDL:

CREATE TABLE TEST_TABLE
(
 time_col TIMESTAMP(6) WITH TIME ZONE,
 interval_col INTERVAL DAY(4) TO SECOND(0)
);

This table contains an index that uses these two columns:

CREATE INDEX IDX_DELAY ON TEST_TABLE(time_col+interval_col);

When running this DDL, Oracle creates the index but implicitly adds the SYS_EXTRACT_UTC function:

CREATE INDEX IDX_DELAY ON TEST_TABLE(SYS_EXTRACT_UTC(time_col+interval_col));

The same DDL was run on other databases in the past and the SYS_EXTRACT_UTC function is not present. I don't know if the databases were migrated from older Oracle versions, but currently they all run on 12c (12.1.0.2.0). I've dropped the index on one of them and recreated it, which caused Oracle to add the SYS_EXTRACT_UTC function.

Why does this happen? Was there a change in behavior in previous Oracle DB releases? Also, what could be the impact of having this function in the index (or the lack of it)?

Thanks in advance!

oracle12c utc indexing oracle sql
1 Answers

One way or another, to create an index you have to be able to compare values to each other (decide if they are equal, or decide which is greater and which is lesser). In order to compare timestamps with time zone, you must convert them to a single time zone; UTC seems like a natural choice.

I can't tell if Oracle in older versions actually stored timestamps with time zone in the index, without converting them to a standard time zone first; perhaps it did, and that's why you don't see SYS_EXTRACT_UTC in older versions. If so, it's a good thing that someone had the good idea to use this function, and to store normalized timestamps in the index. This makes everything simpler and more efficient.

Of course, it is also possible that Oracle has been doing this all along, and they only decided more recently to show us exactly what it is doing.

In any case, I don't see any reason you would worry about this. What is the concern?

EDIT - a Google search lead me to this article by Jonathan Lewis; he says he started writing about it in 2013, which is when Oracle 12 was released. Perhaps it's a coincidence, or perhaps something did change in that version (either the actual way the index is created/stored, or what Oracle chooses to show us).

https://jonathanlewis.wordpress.com/2019/05/29/timestamp-oddity/

Admin1
Related