12c gave us two new features among others: DBMS_UTILITY.EXPAND_SQL_TEXT and temporal validity. DBMS_UTILITY.EXPAND_SQL_TEXT is advertised as a means to expand SQL text that is using views, but it is also very useful to see how Oracle internally rewrites some SQL queries. Here I’m going to use it to see how temporal validity queries are actually executed. All tests are done using 12.1.0.2 Enterprise Edition.<\/p>\n
First I’ll create a table for testing with two periods – VALID and ACTIVE. VALID is using DATE datatype and ACTIVE is using hidden TIMESTAMP columns.<\/p>\n
CREATE TABLE temporal\n(\n id NUMBER PRIMARY KEY,\n t VARCHAR2 (100),\n valid_start DATE,\n valid_end DATE,\n PERIOD FOR valid (valid_start, valid_end)\n);\n\nALTER TABLE temporal\n ADD period FOR active;\n\n\nSQL> select column_name, data_type, hidden_column from user_tab_cols where table_name = 'TEMPORAL' order by column_id;\n\nCOLUMN_NAME DATA_TYPE HID\n-------------------- ------------------------------ ---\nID NUMBER NO\nT VARCHAR2 NO\nVALID_START DATE NO\nVALID_END DATE NO\nACTIVE NUMBER YES\nACTIVE_START TIMESTAMP(6) WITH TIME ZONE YES\nACTIVE_END TIMESTAMP(6) WITH TIME ZONE YES\nVALID NUMBER YES\n\n8 rows selected.\n<\/code><\/pre>\nFirst syntax that can be used to query temporal data is select id, t from temporal AS OF PERIOD FOR valid TIMESTAMP<\/strong>. Lets use DBMS_UTILITY.EXPAND_SQL_TEXT to look how Oracle actually executes this query (result is formatted for better readability using Toad).<\/p>\ndeclare\n c clob;\nbegin\n DBMS_UTILITY.EXPAND_SQL_TEXT(q'[select id, t from temporal as of period for valid DATE'2013-02-01']', c);\n dbms_output.put_line(c);\nend;\n\/\n\nSELECT \"A1\".\"ID\" \"ID\", \"A1\".\"T\" \"T\"\n FROM (SELECT \"A2\".\"ID\" \"ID\",\n \"A2\".\"T\" \"T\",\n \"A2\".\"VALID_START\" \"VALID_START\",\n \"A2\".\"VALID_END\" \"VALID_END\"\n FROM \"DBAUSER\".\"TEMPORAL\" \"A2\"\n WHERE (\"A2\".\"VALID_START\" IS NULL OR \"A2\".\"VALID_START\" <= TO_DATE (' 2013-02-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))\n AND (\"A2\".\"VALID_END\" IS NULL OR \"A2\".\"VALID_END\" > TO_DATE (' 2013-02-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))) \"A1\"\n<\/code><\/pre>\nNo surprises here, Oracle just replaced AS OF PERIOD clause with corresponding WHERE predicates. Another possibility to set the validity period is using DBMS_FLASHBACK_ARCHIVE.ENABLE_AT_VALID_TIME<\/strong>. Then select id, t from temporal<\/strong> is turned to:<\/p>\nSQL> exec DBMS_FLASHBACK_ARCHIVE.ENABLE_AT_VALID_TIME('CURRENT');\n\ndeclare\n c clob;\nbegin\n DBMS_UTILITY.EXPAND_SQL_TEXT(q'[select id, t from temporal]', c);\n dbms_output.put_line(c);\nend;\n\/\n\nSELECT \"A1\".\"ID\" \"ID\", \"A1\".\"T\" \"T\"\n FROM (SELECT \"A2\".\"ID\" \"ID\",\n \"A2\".\"T\" \"T\",\n \"A2\".\"VALID_START\" \"VALID_START\",\n \"A2\".\"VALID_END\" \"VALID_END\"\n FROM \"DBAUSER\".\"TEMPORAL\" \"A2\"\n WHERE (\"A2\".\"VALID_START\" IS NULL OR \"A2\".\"VALID_START\" <= SYSTIMESTAMP (6))\n AND (\"A2\".\"VALID_END\" IS NULL OR \"A2\".\"VALID_END\" > SYSTIMESTAMP (6))\n AND (\"A2\".\"ACTIVE_START\" IS NULL OR \"A2\".\"ACTIVE_START\" <= SYSTIMESTAMP (6))\n AND (\"A2\".\"ACTIVE_END\" IS NULL OR \"A2\".\"ACTIVE_END\" > SYSTIMESTAMP (6))) \"A1\"\n<\/code><\/pre>\nTwo interesting things to note there. First DBMS_FLASHBACK_ARCHIVE.ENABLE_AT_VALID_TIME does not currently have a possibility to name the period, so the restriction is applied to ALL periods defined on the table. Another thing, columns valid_start and valid_end were created as DATE, but values are set as TIMESTAMP, making index use impossible (DATE is automatically converted to TIMESTAMP, not the other way around). Not really a problem, just a thing to take a note of, since the default hidden period columns are created as TIMESTAMP, not DATE.<\/p>\n","protected":false},"excerpt":{"rendered":"
12c gave us two new features among others: DBMS_UTILITY.EXPAND_SQL_TEXT and temporal validity. DBMS_UTILITY.EXPAND_SQL_TEXT is advertised as a means to expand SQL text that is using views, but it is also very useful to see how Oracle internally rewrites some SQL queries. Here I’m going to use it to see how temporal validity queries are actually […]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[2],"tags":[4,10],"class_list":["post-33","post","type-post","status-publish","format-standard","hentry","category-blog-entry","tag-oracle","tag-sql"],"_links":{"self":[{"href":"https:\/\/ilmarkerm.eu\/blog\/wp-json\/wp\/v2\/posts\/33","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=33"}],"version-history":[{"count":3,"href":"https:\/\/ilmarkerm.eu\/blog\/wp-json\/wp\/v2\/posts\/33\/revisions"}],"predecessor-version":[{"id":152,"href":"https:\/\/ilmarkerm.eu\/blog\/wp-json\/wp\/v2\/posts\/33\/revisions\/152"}],"wp:attachment":[{"href":"https:\/\/ilmarkerm.eu\/blog\/wp-json\/wp\/v2\/media?parent=33"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/ilmarkerm.eu\/blog\/wp-json\/wp\/v2\/categories?post=33"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/ilmarkerm.eu\/blog\/wp-json\/wp\/v2\/tags?post=33"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}