Ilmar Kerm

Oracle, databases, Linux and maybe more

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.

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.

CREATE TABLE temporal
(
  id            NUMBER PRIMARY KEY,
  t             VARCHAR2 (100),
  valid_start   DATE,
  valid_end     DATE,
  PERIOD FOR valid (valid_start, valid_end)
);

ALTER TABLE temporal
  ADD period FOR active;


SQL> select column_name, data_type, hidden_column from user_tab_cols where table_name = 'TEMPORAL' order by column_id;

COLUMN_NAME          DATA_TYPE                      HID
-------------------- ------------------------------ ---
ID                   NUMBER                         NO
T                    VARCHAR2                       NO
VALID_START          DATE                           NO
VALID_END            DATE                           NO
ACTIVE               NUMBER                         YES
ACTIVE_START         TIMESTAMP(6) WITH TIME ZONE    YES
ACTIVE_END           TIMESTAMP(6) WITH TIME ZONE    YES
VALID                NUMBER                         YES

8 rows selected.

First syntax that can be used to query temporal data is select id, t from temporal AS OF PERIOD FOR valid TIMESTAMP. Lets use DBMS_UTILITY.EXPAND_SQL_TEXT to look how Oracle actually executes this query (result is formatted for better readability using Toad).

declare
  c clob;
begin
  DBMS_UTILITY.EXPAND_SQL_TEXT(q'[select id, t from temporal as of period for valid DATE'2013-02-01']', c);
  dbms_output.put_line(c);
end;
/

SELECT "A1"."ID" "ID", "A1"."T" "T"
  FROM (SELECT "A2"."ID" "ID",
               "A2"."T" "T",
               "A2"."VALID_START" "VALID_START",
               "A2"."VALID_END" "VALID_END"
          FROM "DBAUSER"."TEMPORAL" "A2"
         WHERE     ("A2"."VALID_START" IS NULL OR "A2"."VALID_START" <= TO_DATE (' 2013-02-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))
               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"

No 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. Then select id, t from temporal is turned to:

SQL> exec DBMS_FLASHBACK_ARCHIVE.ENABLE_AT_VALID_TIME('CURRENT');

declare
  c clob;
begin
  DBMS_UTILITY.EXPAND_SQL_TEXT(q'[select id, t from temporal]', c);
  dbms_output.put_line(c);
end;
/

SELECT "A1"."ID" "ID", "A1"."T" "T"
  FROM (SELECT "A2"."ID" "ID",
               "A2"."T" "T",
               "A2"."VALID_START" "VALID_START",
               "A2"."VALID_END" "VALID_END"
          FROM "DBAUSER"."TEMPORAL" "A2"
         WHERE     ("A2"."VALID_START" IS NULL OR "A2"."VALID_START" <= SYSTIMESTAMP (6))
               AND ("A2"."VALID_END" IS NULL OR "A2"."VALID_END" > SYSTIMESTAMP (6))
               AND ("A2"."ACTIVE_START" IS NULL OR "A2"."ACTIVE_START" <= SYSTIMESTAMP (6))
               AND ("A2"."ACTIVE_END" IS NULL OR "A2"."ACTIVE_END" > SYSTIMESTAMP (6))) "A1"

Two 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.

One link that I have to send to developers quite frequently is how to use XMLTABLE in SQL queries to bind comma separated list of values instead of generating large IN list directly to the query (and this way avoid new sqlid/cursor/wasted memory for each different value combination provided). The link that I usually send is this, but in this post I’d like to expand it a little, so it would work even when the string contains special XML characters.

For numbers, the usage is simple:

> var num_list varchar2(100)
> exec :num_list := '2668,2669,2670'

PL/SQL procedure successfully completed.

> SELECT id FROM ath_case WHERE id IN (
 SELECT (column_value).getNumberVal() FROM xmltable(:num_list)
 );

        ID
----------
      2668
      2669
      2670

> exec :num_list := '2671,2672,2673,2674'

PL/SQL procedure successfully completed.

> SELECT id FROM ath_case WHERE id IN (
 SELECT (column_value).getNumberVal() FROM xmltable(:num_list)
 );

        ID
----------
      2671
      2672
      2673
      2674

If the binded list consists of strings, then some extra steps are needed – the comma-separated has to be enclosed with double-quotes and the values have to be XML-encoded (XML special characters, like " replaced with codes).

> var str_list varchar2(100)
> exec :str_list := '"GI1","BI1"'

PL/SQL procedure successfully completed.

> SELECT u.first_name FROM ath_user u 
 JOIN ath_team t ON u.id = t.manager_id 
 WHERE t.name IN (
 SELECT DBMS_XMLGEN.CONVERT((column_value).getStringVal(), 1) FROM xmltable(:str_list)
 );

FIRST_NAME
-----------
Riho
Kaur

> exec :str_list := '"OS1","OS2"'

PL/SQL procedure successfully completed.

> SELECT u.first_name FROM ath_user u 
 JOIN ath_team t ON u.id = t.manager_id 
 WHERE t.name IN (
 SELECT DBMS_XMLGEN.CONVERT((column_value).getStringVal(), 1) FROM xmltable(:str_list)
 );

FIRST_NAME
-----------
Markko
Aive

> set define off
> exec :str_list := '"value1","value2","value &quot; with quot","value &amp; with amp"';

PL/SQL procedure successfully completed.

> SELECT DBMS_XMLGEN.CONVERT((column_value).getStringVal(), 1) FROM xmltable(:str_list);

DBMS_XMLGEN.CONVERT((COLUMN_VALUE).GETSTRINGVAL(),1)
-------------------------------------------------------------------------
value1
value2
value " with quot
value & with amp

Welcome to my first blog 🙂 And I’ll start with something simple, but very cool at the same time.

I really like Oracle 11g database and one of my favourite features is the PIVOT/UNPIVOT clause in SQL. PIVOT is used to present rows as columns and UNPIVOT is the opposite operation, columns are presented as rows. I have found them very useful for APEX applications, when I need to present data in table rows as columns in an APEX form for user input.

PIVOT feature seems to have recieved a lot of bloggers attention, but I haven’t read much about UNPIVOT. Using UNPIVOT with one column is pretty straight forward and easy, just a small example (I use Oracle Database 11.1.0.7 Enterprise Edition for all the examples here):

create table unpivot_ex1 (
 id number primary key,
 jan number,
 feb number,
 mar number,
 apr number
);

Fill it up with test data:

insert into unpivot_ex1 (id, jan, feb, mar, apr)
 values (1, 1, 2, null, 4);
insert into unpivot_ex1 (id, jan, feb, mar, apr)
 values (2, 5, 6, null, 8);
insert into unpivot_ex1 (id, jan, feb, mar, apr)
 values (3, 9, 10, null, null);
commit;

SELECT * FROM unpivot_ex1;

    ID        JAN        FEB        MAR        APR
---------- ---------- ---------- ---------- ----------
     1          1          2                     4
     2          5          6                     8
     3          9         10

Now, if you want to select JAN, FEB, MAR, APR columns as separate rows, then its possible to use the simplest UNPIVOT clause:

SELECT * FROM unpivot_ex1 
  UNPIVOT INCLUDE NULLS
    (monthly_value FOR month IN (jan, feb, mar, apr));

        ID MON MONTHLY_VALUE
---------- --- -------------
         1 JAN             1
         1 FEB             2
         1 MAR
         1 APR             4
         2 JAN             5
         2 FEB             6
         2 MAR
         2 APR             8
         3 JAN             9
         3 FEB            10
         3 MAR
         3 APR

12 rows selected.

Now, lets take a more interesting case. What if some of the columns are combined into logical groups and you want to preserve them in one row? For example:

create table unpivot_ex2 (
  id number primary key,
  jan_income number,
  jan_expense number,
  feb_income number,
  feb_expense number,
  mar_income number,
  mar_expense number
);

For every month there is income and expense column.

insert into unpivot_ex2 values (1, 1, 1, 2, 3, 5, 2);
insert into unpivot_ex2 values (2, 5, 6, 4, 4, null, null);
commit;

SELECT * FROM unpivot_ex2;

        ID JAN_INCOME JAN_EXPENSE FEB_INCOME FEB_EXPENSE MAR_INCOME MAR_EXPENSE
---------- ---------- ----------- ---------- ----------- ---------- -----------
         1          1           1          2           3          5           2
         2          5           6          4           4

UNPIVOT allows to create column groups, like this:

SELECT * FROM unpivot_ex2
  UNPIVOT INCLUDE NULLS
    ((income, expense) FOR month IN (
     (jan_income, jan_expense), (feb_income, feb_expense), 
     (mar_income, mar_expense)));

        ID MONTH                      INCOME    EXPENSE
---------- ---------------------- ---------- ----------
         1 JAN_INCOME_JAN_EXPENSE          1          1
         1 FEB_INCOME_FEB_EXPENSE          2          3
         1 MAR_INCOME_MAR_EXPENSE          5          2
         2 JAN_INCOME_JAN_EXPENSE          5          6
         2 FEB_INCOME_FEB_EXPENSE          4          4
         2 MAR_INCOME_MAR_EXPENSE

6 rows selected.

How cool is that? 🙂