Temporal Validity and “wrong” results

Temporal validity is a new feature in Oracle 12c. It makes querying effective date ranges much simpler. For more details refer to the Oracle documentation.

This blog post explains in which situation one gets “wrong” or at least unexpected results when using temporal validity.

To demonstrate the situation let’s create a table with a time dimension and some records:

create table t1 (startdate date, enddate date); 
alter table t1 add period for t1period (startdate, enddate); 
insert into t1 values(null, date'2016-01-10'); 
insert into t1 values(date'2016-01-08', null); 
insert into t1 values(date'2016-01-08', date'2016-01-10'); 

So if you want to get the records from table T1 which are effective as of the day after tomorrow you can query it with the following select:

select * from t1 as of period for t1period date'2016-01-11';

The returned result is as expected

------------------- -------------------
2016-01-08 00:00:00

So what happens if you use a nonexistent time dimension:

select * from t1 as of period for t2period sysdate+2;
ERROR at line 1:
ORA-55603: invalid flashback archive or valid time period command

You get an error, which is also the expected behaviour. But what happens when you delete the only existing time dimension on the table and query the table again.

alter table t1 drop (period for t1period);

select * from t1 as of period for t2period sysdate+2;

------------------- -------------------
                    2016-01-10 00:00:00
2016-01-08 00:00:00
2016-01-08 00:00:00 2016-01-10 00:00:00

Oracle successfully executes the query and simply ignores the temporal validity clause. If someone accidentally drops your time dimension your application won’t notice and you get wrong results all the time until you have to analyze it because it became an issue (which will probably be very soon).

In my opinion the correct behaviour in this situation would be returning an error, as it is the case when you have an already existing time dimension on the table but use a non-existing one in the query. I already filed a bug in MOS to ask whether this is a bug or a design decision and I will keep this post updated on the outcome.

Loading data into index organized tables

Most of the time you don’t have full control of how your data gets loaded into the database, because it is often imposed by business rules. But in the case you have full control, you should take advantage of it.

I recently came across an index organized table which was kind of a denormalized form of other tables and which was truncated and reloaded every day before some heavy batch jobs queried it.

The data was loaded into the IOT with an insert as select statement. Because the data returned by the select was not ordered (or at least not ordered after the primary key columns of the IOT), the insert caused a lot of 50/50 block splits on the IOT.
I added a simple order by clause to the statement with the effect that only 90/10 block splits were done afterwards. This also resulted in a 50% smaller index size.

Here’s a small test case for demonstration:

Create a heap table which is later used to load data into the IOT
The c1 column will be the pk column of the later created IOT. I used a random function so the values for this column are not ordered in any way.

create table t_heap (c1 number, c2 varchar2(100));

SELECT dbms_random.random, lpad('#',100,'#')
FROM dual CONNECT BY rownum <= 100000;

Remove any duplicate values for c1 as this would lead to unique constraint violations later.

 lastValue t_heap.c1%type;
 cursor c_heap is select * from t_heap order by c1 for update;
  for rec in c_heap loop
    if rec.c1 = lastValue then
      delete from t_heap where current of c_heap;
    end if;
  lastValue := rec.c1;
  end loop;


Create the IOT

create table t_iot (c1 number, c2 varchar2(100),
 constraint pk_iot primary key (c1))
 organization index;

Check the index split statistics before inserting

select n.name,s.value from v$mystat s
join v$statname n on (s.statistic# = n.statistic#)
where n.name in ('leaf node splits', 'leaf node 90-10 splits');

NAME                                                                  VALUE
---------------------------------------------------------------- ----------
leaf node splits                                                          0
leaf node 90-10 splits                                                    0

Insert without order by clause

insert into t_iot select * from t_heap;

NAME                                                                  VALUE
---------------------------------------------------------------- ----------
leaf node splits                                                       2908
leaf node 90-10 splits                                                    0

select bytes/1024/1024 from dba_segments where segment_name='PK_IOT';


About ~3k block splits were done, all of them 50/50. The size of the index is ~23 MB.

Now insert with order by clause (Open a new database session, so the counters in v$mystat get reset)

truncate table t_iot;
insert into t_iot select * from t_heap order by c1;

NAME                                                                  VALUE
---------------------------------------------------------------- ----------
leaf node splits                                                       1458
leaf node 90-10 splits                                                 1458

select bytes/1024/1024 from dba_segments where segment_name='PK_IOT';


The amount of block splits is cut in half and because of the order by clause, only 90/10 block splits were done. The index size is also considerably smaller (12 MB compared to 23 MB).