{"id":847,"date":"2026-07-17T16:34:17","date_gmt":"2026-07-17T14:34:17","guid":{"rendered":"https:\/\/ilmarkerm.eu\/blog\/?p=847"},"modified":"2026-07-17T16:38:58","modified_gmt":"2026-07-17T14:38:58","slug":"timestamp-with-time-zone-and-covering-indexes","status":"publish","type":"post","link":"https:\/\/ilmarkerm.eu\/blog\/2026\/07\/timestamp-with-time-zone-and-covering-indexes\/","title":{"rendered":"TIMESTAMP WITH TIME ZONE and covering indexes"},"content":{"rendered":"\n<p class=\"wp-block-paragraph\">Covering indexes are indexes that have all columns present in the index, that the query needs. Removing the need to do an potentially very expensive TABLE ACCESS BY ROWID operation (that fetches the row information from table after locating the rows using an index).<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">But there is a little gotcha when using covering index on a column with TIMESTAMP WITH TIME ZONE data type.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">Oracle silently indexes TIMESTAMP WITH TIME ZONE data type actually as a function SYS_EXTRACT_UTC(column), even if you do not specify the function excplcitly.<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>CREATE TABLE timestamptz_covering_test (\n    v timestamp with time zone not null\n);\nINSERT INTO timestamptz_covering_test (v) SELECT systimestamp+numtodsinterval(rownum, 'second') from all_objects;\nCREATE INDEX idx_timestamptz_covering_test_v on timestamptz_covering_test (v);<\/code><\/pre>\n\n\n\n<p class=\"wp-block-paragraph\">What Oracle actually indexed was<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>CREATE INDEX \"IDX_TIMESTAMPTZ_COVERING_TEST_V\" ON \"TIMESTAMPTZ_COVERING_TEST\" (SYS_EXTRACT_UTC(\"V\"));<\/code><\/pre>\n\n\n\n<p class=\"wp-block-paragraph\">When using the index in WHERE clause, all works as expected, the index is used as an access predicate as expected.<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>select 1 from timestamptz_covering_test\nwhere v between systimestamp and systimestamp+interval '1' minute;\n\n----------------------------------------------------------------------\n| Id  | Operation         | Name                            | E-Rows |\n----------------------------------------------------------------------\n|   0 | SELECT STATEMENT  |                                 |        |\n|*  1 |  FILTER           |                                 |        |\n|*  2 |   INDEX RANGE SCAN| IDX_TIMESTAMPTZ_COVERING_TEST_V |     62 |\n----------------------------------------------------------------------\n \nPredicate Information (identified by operation id):\n---------------------------------------------------\n \n   1 - filter(SYS_EXTRACT_UTC(SYSTIMESTAMP(6)+INTERVAL'+00 00:01:00' \n              DAY(2) TO SECOND(0))&gt;=SYS_EXTRACT_UTC(SYSTIMESTAMP(6)))\n   2 - access(\"TIMESTAMPTZ_COVERING_TEST\".\"SYS_NC00002$\"&gt;=SYS_EXTRACT_UT\n              C(SYSTIMESTAMP(6)) AND \"TIMESTAMPTZ_COVERING_TEST\".\"SYS_NC00002$\"&lt;=SYS_E\n              XTRACT_UTC(SYSTIMESTAMP(6)+INTERVAL'+00 00:01:00' DAY(2) TO SECOND(0)))<\/code><\/pre>\n\n\n\n<p class=\"wp-block-paragraph\">But&#8230; If we get into the covering index territory. Look at this example, we are NOT querying any extra columns, only the indexed v column, but suddenly we have TABLE ACCESS BY INDEX ROWID!!!<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>select v at time zone 'Europe\/Tallinn' from timestamptz_covering_test\nwhere v between systimestamp and systimestamp+interval '1' minute\n \nPlan hash value: 3222247287\n \n-----------------------------------------------------------------------------------------\n| Id  | Operation                            | Name                            | E-Rows |\n-----------------------------------------------------------------------------------------\n|   0 | SELECT STATEMENT                     |                                 |        |\n|*  1 |  FILTER                              |                                 |        |\n|   2 |   TABLE ACCESS BY INDEX ROWID BATCHED| TIMESTAMPTZ_COVERING_TEST       |     62 |\n|*  3 |    INDEX RANGE SCAN                  | IDX_TIMESTAMPTZ_COVERING_TEST_V |     62 |\n-----------------------------------------------------------------------------------------\n \nPredicate Information (identified by operation id):\n---------------------------------------------------\n \n   1 - filter(SYS_EXTRACT_UTC(SYSTIMESTAMP(6)+INTERVAL'+00 00:01:00' DAY(2) TO \n              SECOND(0))&gt;=SYS_EXTRACT_UTC(SYSTIMESTAMP(6)))\n   3 - access(\"TIMESTAMPTZ_COVERING_TEST\".\"SYS_NC00002$\"&gt;=SYS_EXTRACT_UTC(SYSTIME\n              STAMP(6)) AND \"TIMESTAMPTZ_COVERING_TEST\".\"SYS_NC00002$\"&lt;=SYS_EXTRACT_UTC(SYSTIME\n              STAMP(6)+INTERVAL'+00 00:01:00' DAY(2) TO SECOND(0)))<\/code><\/pre>\n\n\n\n<p class=\"wp-block-paragraph\">The reason is simple &#8211; SYS_EXTRACT_UTC returns TIMESTAMP data type &#8211; so in the index, all time zone information is lost and the original value has to be fetched from the table. In the query we should keep it in mind and rewrite.<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>select from_tz(sys_extract_utc(v), 'UTC') at time zone 'Europe\/Tallinn' \nfrom timestamptz_covering_test\nwhere v between systimestamp and systimestamp+interval '1' minute\n \nPlan hash value: 4012349952\n \n----------------------------------------------------------------------\n| Id  | Operation         | Name                            | E-Rows |\n----------------------------------------------------------------------\n|   0 | SELECT STATEMENT  |                                 |        |\n|*  1 |  FILTER           |                                 |        |\n|*  2 |   INDEX RANGE SCAN| IDX_TIMESTAMPTZ_COVERING_TEST_V |     62 |\n----------------------------------------------------------------------\n \nPredicate Information (identified by operation id):\n---------------------------------------------------\n \n   1 - filter(SYS_EXTRACT_UTC(SYSTIMESTAMP(6)+INTERVAL'+00 00:01:00' \n              DAY(2) TO SECOND(0))&gt;=SYS_EXTRACT_UTC(SYSTIMESTAMP(6)))\n   2 - access(\"TIMESTAMPTZ_COVERING_TEST\".\"SYS_NC00002$\"&gt;=SYS_EXTRACT_UT\n              C(SYSTIMESTAMP(6)) AND \"TIMESTAMPTZ_COVERING_TEST\".\"SYS_NC00002$\"&lt;=SYS_E\n              XTRACT_UTC(SYSTIMESTAMP(6)+INTERVAL'+00 00:01:00' DAY(2) TO SECOND(0)))<\/code><\/pre>\n\n\n\n<p class=\"wp-block-paragraph\">All good now.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">When you plan to use TIMESTAMP WITH TIME ZONE as a value from index &#8211; you have to convert it back to TIMESTAMP WITH TIME ZONE.<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>FROM_TZ(SYS_EXTRACT_UTC(indexed_column), 'UTC')<\/code><\/pre>\n\n\n\n<p class=\"wp-block-paragraph\">So just keep it in mind. I just had a case where a query like that had to process about 300000 rows, they created a covering index, but it did not help much. 75% of the time was spent on TABLE ACCESS BY ROWID.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">Mandatory version information: 19.31<\/p>\n","protected":false},"excerpt":{"rendered":"<p>Covering indexes are indexes that have all columns present in the index, that the query needs. Removing the need to do an potentially very expensive TABLE ACCESS BY ROWID operation (that fetches the row information from table after locating the rows using an index). But there is a little gotcha when using covering index on [&hellip;]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"closed","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[2],"tags":[4],"class_list":["post-847","post","type-post","status-publish","format-standard","hentry","category-blog-entry","tag-oracle"],"_links":{"self":[{"href":"https:\/\/ilmarkerm.eu\/blog\/wp-json\/wp\/v2\/posts\/847","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/ilmarkerm.eu\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/ilmarkerm.eu\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/ilmarkerm.eu\/blog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/ilmarkerm.eu\/blog\/wp-json\/wp\/v2\/comments?post=847"}],"version-history":[{"count":2,"href":"https:\/\/ilmarkerm.eu\/blog\/wp-json\/wp\/v2\/posts\/847\/revisions"}],"predecessor-version":[{"id":849,"href":"https:\/\/ilmarkerm.eu\/blog\/wp-json\/wp\/v2\/posts\/847\/revisions\/849"}],"wp:attachment":[{"href":"https:\/\/ilmarkerm.eu\/blog\/wp-json\/wp\/v2\/media?parent=847"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/ilmarkerm.eu\/blog\/wp-json\/wp\/v2\/categories?post=847"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/ilmarkerm.eu\/blog\/wp-json\/wp\/v2\/tags?post=847"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}