Tag: sql
- Written by: ilmarkerm
- Category: Blog entry
- Published: August 29, 2014
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.
- Written by: ilmarkerm
- Category: Blog entry
- Published: June 25, 2012
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 " with quot","value & 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
- Written by: ilmarkerm
- Category: Blog entry
- Published: July 21, 2009
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? 🙂