How long will it take?

Reloading a table.

truncate table sometable;

insert /*+append*/ into sometable select ...

commit;

How long will it take?

Table is populated earlier and the space consumed will be about the same after reload.

With following technique I was able to get such an estimate after starting the insert.

Before truncate see how much space there is to be consumed.

select sum(bytes) from user_segments where segment_name = 'SOMETABLE';

4445110272

Check the execution plan of the insert statement. Ensure it will start consuming space at the beginning of execute.

Start the load. Identify the session doing the insert statement and estimate.

select ((targetb-currb)/currb)*interv+currt estimatedfinnish 
  from (
 select currt
       -(select sql_exec_start from v$session where sid = :sid)  interv
     , currb, currt
     , :b4445110272 targetb
   from (
  select systimestamp currt
       , sum(bytes) currb 
    from user_segments 
   where segment_name like 'SOMETABLE' 
));

That is giving some time in the future.

Reasons why the estimate is not correct.

1. The execution plan does not start consuming space at the beginning.
2. The space used before truncate was not the same that it will be at the end of load.
3. Indexes in the table to be populated.

If there are indexes they are builded after insert phase. Estimate was given for the table to be populated. Index building phase may be monitored by looking at V$SESSION_LONGOPS.

Not overlapping (MV approach)

Presented earlier a not overlapping function based unique indexes approach. In this post I am using a materialized view and a unique constraining that. Several commits seen in here because MV approach makes constraints kind of deferrable. The MV is refreshed at commit phase. As the dirty hack function based unique indexes on the table itself are violated straight at the insert.

DROP TABLE Z CASCADE CONSTRAINTS PURGE;

DROP TABLE YEARS CASCADE CONSTRAINTS PURGE;

DROP MATERIALIZED VIEW Z_MV;

CREATE TABLE Z(Z NUMBER(16) NOT NULL
             , VALIDFROM NUMBER(4) NOT NULL
             , VALIDTILL NUMBER(4) NOT NULL
             , CONSTRAINT FRO2000 CHECK (2000 < VALIDFROM)
             , CONSTRAINT TIL2050 CHECK (VALIDTILL <= 2050)
             , CONSTRAINT FROTIL CHECK (VALIDFROM <= VALIDTILL)
             );


CREATE TABLE YEARS AS 
SELECT 2000+LEVEL TIM FROM DUAL CONNECT BY LEVEL < (2051-2000)
;

CREATE MATERIALIZED VIEW LOG ON Z WITH ROWID
;

CREATE MATERIALIZED VIEW LOG ON YEARS WITH ROWID
;

CREATE MATERIALIZED VIEW Z_MV REFRESH FAST ON COMMIT AS
SELECT Z.ROWID ZRID,T.ROWID TRID,Z.Z,T.TIM 
  FROM Z INNER JOIN YEARS T ON VALIDFROM < T.TIM AND T.TIM <= VALIDTILL 
;

With Oracle 11.1.0.7 and 11.2.0.1 getting
ORA-12054: cannot set the ON COMMIT refresh attribute for the materialized view

No worries. It is about inner join syntax not so widely supported with MVs.

CREATE MATERIALIZED VIEW Z_MV REFRESH FAST ON COMMIT AS
SELECT Z.ROWID ZRID,T.ROWID TRID,Z.Z,T.TIM 
  FROM Z,YEARS T 
 WHERE VALIDFROM < T.TIM AND T.TIM <= VALIDTILL 
;

ALTER TABLE Z_MV ADD CONSTRAINT Z_MV_U UNIQUE (Z,TIM);

CREATE INDEX Z_MV_ZRID_IDX ON Z_MV(ZRID);

ZRID indexed to give the optimizer at least a possibility to do small updates "fast". More about the issue may be read from Alberto Dell'Era's Oracle blog

INSERT INTO Z VALUES(1,2001,2011);

INSERT INTO Z VALUES(1,2011,2011);

COMMIT;

INSERT INTO Z VALUES(1,2010,2012);

COMMIT;

SQL ERROR: ORA-12008: ERROR IN MATERIALIZED VIEW REFRESH PATH
ORA-00001: UNIQUE CONSTRAINT (RAFU.Z_MV_U) VIOLATED


INSERT INTO Z VALUES(2,2049,2050);

COMMIT;

INSERT INTO Z VALUES(2,2049,2050);

COMMIT;

SQL ERROR: ORA-12008: ERROR IN MATERIALIZED VIEW REFRESH PATH
ORA-00001: UNIQUE CONSTRAINT (RAFU.Z_MV_U) VIOLATED



INSERT INTO Z VALUES(2,2010,2012);

COMMIT;

INSERT INTO Z VALUES(2,2001,2049);

COMMIT;

SQL ERROR: ORA-12008: ERROR IN MATERIALIZED VIEW REFRESH PATH
ORA-00001: UNIQUE CONSTRAINT (RAFU.Z_MV_U) VIOLATED


INSERT INTO Z VALUES(2,2014,2017);

COMMIT;

SELECT * FROM Z ORDER BY Z, VALIDFROM;

1 2001 2011
1 2011 2011
2 2010 2012
2 2014 2017
2 2049 2050

SELECT COUNT(*) FROM Z_MV;

16

Pipelining

In reducing number of function calls I wrote about how to rewrite a query that is calling a function. Another approach to the issue is to alter the function. The function includes only a SQL clause. The whole result of the clause is bulk collected first and then returned. The usage of the function is in IN clause. IN predicate is satisfied if there is one equality coming out of the select. So in the best cases it is not needed to populate the whole bulk collect inside the function. Using pipelining as an alternative here.

Original bulk collect version.

SQL> create or replace type ns_typ is table of number;
  2  /

Type created.

SQL> create or replace function rn(n number) return ns_typ is
  2    ret ns_typ;
  3    begin
  4     dbms_lock.sleep(1);
  5     select level bulk collect into ret from dual connect by level <= n;
  6    return ret;
  7  end;
  8  /

Function created.

And the pipelined version of the function.

SQL> create or replace function rnpiped(n number) return ns_typ pipelined is
  2  begin
  3    for ret in
  4     (select level l from dual connect by level <= n)
  5    loop
  6     dbms_lock.sleep(1/n);
  7     pipe row (ret.l);
  8    end loop;
  9  end;
 10  /

If the whole result returned from the function is needed there is no great difference in the execution times. Actually it seems to be increasing a bit.

SQL> select * from table(rn(10));

COLUMN_VALUE
------------
           1
           2
           3
           4
           5
           6
           7
           8
           9
          10

10 rows selected.

Elapsed: 00:00:01.00
SQL>
SQL> select * from table(rnpiped(10));

COLUMN_VALUE
------------
           1
           2
           3
           4
           5
           6
           7
           8
           9
          10

10 rows selected.

Elapsed: 00:00:01.07

But when used in IN predicate the results with this data are even better than in the previous post.

SQL> select * from ta a where a.n in (select * from table(rn(a.m)));

         N          M
---------- ----------
         1          2
         2          3

Elapsed: 00:00:10.00
SQL>
SQL> select * from ta a where a.n in (select * from table(rnpiped(a.m)));

         N          M
---------- ----------
         1          2
         2          3

Elapsed: 00:00:01.95

And putting both together.

SQL> with aa as (
  2    select *
  3      from ta a
  4    ), bb as (
  5    select distinct m
  6      from aa
  7    ), cc as (
  8    select /*+materialize*/ b.m, dd.column_value n
  9      from bb b, table(rn(b.m)) dd)
 10    select *
 11      from aa
 12     where (n,m) in (select n,m from cc)
 13  ;

         N          M
---------- ----------
         1          2
         2          3

Elapsed: 00:00:03.09
SQL>
SQL> with aa as (
  2    select *
  3      from ta a
  4    ), bb as (
  5    select distinct m
  6      from aa
  7    ), cc as (
  8    select /*+materialize*/ b.m, dd.column_value n
  9      from bb b, table(rnpiped(b.m)) dd)
 10    select *
 11      from aa
 12     where (n,m) in (select n,m from cc)
 13  ;

         N          M
---------- ----------
         1          2
         2          3

Elapsed: 00:00:00.68

Paging before join and aggregation

First test data.

drop table chi purge;

drop table par purge;


create table par 
( o varchar2(30)
, t varchar2(30)
, n varchar2(30)
, constraint par_pk primary key(o,t,n)) 
organization index ;

create table chi 
( o varchar2(30)
, t varchar2(30)
, n varchar2(30)
, c varchar2(30)
, constraint chi_pk primary key(o,t,n,c)
, constraint chi_par_fk foreign key(o,t,n) references par(o,t,n)) 
organization index ;

insert into par select distinct owner, object_type, object_name from dba_objects;

insert into chi(o,t,n,c) 
select distinct ta.owner, ob.object_type, ta.table_name, ta.column_name 
from all_tab_columns ta 
inner join all_objects ob 
   on ta.owner=ob.owner and TA.TABLE_NAME = ob. object_name
;

commit;

exec dbms_stats.gather_table_stats(user,'PAR');

exec dbms_stats.gather_table_stats(user,'CHI');

Query to start with and the info about execution.

SQL> SELECT n,t,o,cou
  2    FROM (  SELECT pa.o, pa.t, pa.n, COUNT ( * ) cou
  3              FROM par pa LEFT OUTER JOIN chi ch
  4                      ON pa.o = ch.o and pa.t = ch.t and pa.n = ch.n
  5          GROUP BY pa.o, pa.t, pa.n
  6          ORDER BY pa.t, pa.o, pa.n)
  7   WHERE ROWNUM < 3;

N                              T                              O                                     COU
------------------------------ ------------------------------ ------------------------------ ----------
C_COBJ#                        CLUSTER                        SYS                                     1
C_FILE#_BLOCK#                 CLUSTER                        SYS                                     3

Elapsed: 00:00:02.26

----------------------------------------------------------------------------------------------------
| Id  | Operation                       | Name   | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
----------------------------------------------------------------------------------------------------
|*  1 |  COUNT STOPKEY                  |        |      1 |        |      2 |00:00:02.19 |    2014 |
|   2 |   VIEW                          |        |      1 |    112K|      2 |00:00:02.19 |    2014 |
|*  3 |    SORT GROUP BY STOPKEY        |        |      1 |    112K|      2 |00:00:02.19 |    2014 |
|   4 |     MERGE JOIN OUTER            |        |      1 |    112K|    166K|00:00:01.93 |    2014 |
|   5 |      SORT JOIN                  |        |      1 |  64558 |  64558 |00:00:00.52 |     666 |
|   6 |       INDEX FULL SCAN DESCENDING| PAR_PK |      1 |  64558 |  64558 |00:00:00.06 |     666 |
|*  7 |      SORT JOIN                  |        |  64558 |    112K|    112K|00:00:00.87 |    1348 |
|   8 |       INDEX FULL SCAN           | CHI_PK |      1 |    112K|    112K|00:00:00.11 |    1348 |
----------------------------------------------------------------------------------------------------

The alternative to do paging first and aggregate only the rows to be retrieved.

Paging done in par inline view line 9, order by need to be done on both line 8 for paging and 14 to get the results back in order after joining and aggregating.

SQL>   SELECT pap.n,
  2           pap.t,
  3           pap.o,
  4           COUNT ( * ) cou
  5      FROM (SELECT *
  6              FROM (  SELECT pa.o, pa.t, pa.n
  7                        FROM par pa
  8                    ORDER BY pa.t, pa.o, pa.n)
  9             WHERE ROWNUM < 3
 10            ) pap
 11       LEFT OUTER JOIN chi ch
 12         ON pap.o = ch.o AND pap.t = ch.t AND pap.n = ch.n
 13  GROUP BY pap.o, pap.t, pap.n
 14  ORDER BY pap.t, pap.o, pap.n;

N                              T                              O                                     COU
------------------------------ ------------------------------ ------------------------------ ----------
C_COBJ#                        CLUSTER                        SYS                                     1
C_FILE#_BLOCK#                 CLUSTER                        SYS                                     3

Elapsed: 00:00:00.20

-----------------------------------------------------------------------------------------------
| Id  | Operation                  | Name   | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
-----------------------------------------------------------------------------------------------
|   1 |  SORT GROUP BY NOSORT      |        |      1 |      2 |      2 |00:00:00.20 |     673 |
|   2 |   NESTED LOOPS OUTER       |        |      1 |      2 |      4 |00:00:00.20 |     673 |
|   3 |    VIEW                    |        |      1 |      2 |      2 |00:00:00.20 |     666 |
|*  4 |     COUNT STOPKEY          |        |      1 |        |      2 |00:00:00.20 |     666 |
|   5 |      VIEW                  |        |      1 |  64558 |      2 |00:00:00.20 |     666 |
|*  6 |       SORT ORDER BY STOPKEY|        |      1 |  64558 |      2 |00:00:00.20 |     666 |
|   7 |        INDEX FULL SCAN     | PAR_PK |      1 |  64558 |  64558 |00:00:00.06 |     666 |
|*  8 |    INDEX RANGE SCAN        | CHI_PK |      2 |      1 |      4 |00:00:00.01 |       7 |
-----------------------------------------------------------------------------------------------

Notice cou column cannot be in order by condition.
Join needs to be outer.

Reducing the number of function calls

Using a slow function call in your query? Maybe you are calling it unnecessarily.

SQL> create or replace type ns_typ is table of number;
  2  /

Type created.

SQL> create or replace function rn(n number) return ns_typ is
  2  ret ns_typ;
  3  begin
  4   dbms_lock.sleep(1);
  5   select level bulk collect into ret from dual connect by level <= n;
  6  return ret;
  7  end;
  8  /

Function created.

SQL>
SQL> create table ta as select level n, mod(level,3)+1 m from dual connect by level <= 10;

Table created.

SQL> select * from ta;

         N          M
---------- ----------
         1          2
         2          3
         3          1
         4          2
         5          3
         6          1
         7          2
         8          3
         9          1
        10          2

10 rows selected.

SQL> set timi on

SQL> select * from ta a where a.n in (select * from table(rn(a.m)));

         N          M
---------- ----------
         1          2
         2          3

Elapsed: 00:00:10.01

The query is calling rn function for each ten rows of ta table. Each call takes one second as the function is using dbms_lock. There are only three distinct values that the function is needed to be called.

SQL> with aa as (
  2  select *
  3    from ta a
  4  ), bb as (
  5  select distinct m
  6    from aa
  7  ), cc as (
  8  select /*+materialize*/ b.m, dd.column_value n
  9    from bb b, table(rn(b.m)) dd)
 10  select *
 11    from aa
 12   where (n,m) in (select n,m from cc)
 13  ;

         N          M
---------- ----------
         1          2
         2          3

Elapsed: 00:00:03.03

Alternatively you might consider using result cache for the function.

SQL> create or replace function rn(n number) return ns_typ result_cache is
  2  ret ns_typ;
  3  begin
  4   dbms_lock.sleep(1);
  5   select level bulk collect into ret from dual connect by level <= n;
  6  return ret;
  7  end;
  8  /

Function created.

Elapsed: 00:00:00.04
SQL>
SQL> select * from ta a where a.n in (select * from table(rn(a.m)));

         N          M
---------- ----------
         1          2
         2          3

Elapsed: 00:00:03.01
SQL>
SQL> select * from ta a where a.n in (select * from table(rn(a.m)));

         N          M
---------- ----------
         1          2
         2          3

Elapsed: 00:00:00.00

Cleanup

SQL> drop table ta purge;
SQL> drop function rn;
SQL> drop type ns_typ;

Packing rows

So worth tasting. Having a Keisari Strong Munchener 5,7% brewed by Nokian panimo. Figuring out a thing done today.

The case was that we have a relation that has the only one over time feature present. There is the possibility that the same attribute is the same in several continuous rows. And we want to put those rows together in a query result. So the question here was how to implement pack operator presented in C.J. Date book Temporal data and the relational model. Some good alternatives are presented in Joe Celko's thinking in sets: auxiliary, temporal, and virtual tables in SQL. He presents a OLAP Function Solution. Here we have the not overlapping present so the question is a bit simpler. And to a example...

CREATE TABLE Z(Z NUMBER(16) NOT NULL
             , VALIDFROM NUMBER(4) NOT NULL
             , VALIDTILL NUMBER(4) NOT NULL
);

INSERT INTO Z VALUES (1,2000,2003);

INSERT INTO Z VALUES (1,2003,2004);

INSERT INTO Z VALUES (2,2004,2005);

INSERT INTO Z VALUES (2,2005,2008);

INSERT INTO Z VALUES (1,2008,2010);

SELECT Z, MINVALIDFROM, MAX(VALIDTILL) MAXVALIDTILL
  FROM (
  SELECT Z, VALIDFROM,VALIDTILL,PREVTILL
       , MAX(CASE WHEN VALIDFROM <= PREVTILL
                  THEN NULL
                  ELSE VALIDFROM
              END) 
         OVER ( PARTITION BY Z ORDER BY VALIDFROM, VALIDTILL
                ROWS UNBOUNDED PRECEDING) MINVALIDFROM
    FROM (
    SELECT Z
         , VALIDFROM,VALIDTILL
         , LAG(VALIDTILL) 
           OVER (PARTITION BY Z ORDER BY VALIDTILL) PREVTILL
      FROM Z
   ) 
)
GROUP BY Z, MINVALIDFROM
ORDER BY MINVALIDFROM, Z
;

1 2000 2004
2 2004 2008
1 2008 2010

The simpler part is to use LAG.

Datafile HWM

Cleaning up "joins gone wild" application bug. First cleaned up a mess that consumed a lot of space in tablespaces. After that had to move objects with high block_id values so the datafile size may be redused. To avoid hitting the error ORA-03297: file contains used data beyond requested RESIZE value. Alter table move may be used only the objects are not used. If you need to do this as an online operation use DBMS_REDEFINITION.

Generators I used for tablespaces containing only tables.

SELECT file_id,'alter table '||owner||'.'||segment_name||' move'||
   case when segment_type='TABLE PARTITION' then ' PARTITION '||partition_name
        when segment_type='TABLE SUBPARTITION' then ' SUBPARTITION '||partition_name 
    end ||';' sq, 
MAX(BLOCK_ID) max_block_id,
SEGMENT_NAME, partition_name, SEGMENT_TYPE
  FROM DBA_EXTENTS
 WHERE FILE_ID in (4,5,6,7,8)
 group by file_id,owner,SEGMENT_NAME, partition_name, SEGMENT_TYPE
 having MAX(BLOCK_ID) > 100000
 order by file_id,max_block_id desc
;

select 'alter database datafile '||file_id||' resize '||(ceil(max(block_id)*db_block_size/1024/1024/1024))||'G;'
  from dba_extents, (
    select value db_block_size 
      from v$parameter 
     where name='db_block_size' 
     )
 where FILE_ID in (4,5,6,7,8)
 group by db_block_size,file_id
 order by 1
;

Indexes went broken.

select 'alter index '||owner||'.'||index_name||' rebuild;' from all_indexes where status = 'UNUSABLE' order by 1;

select 'alter index '||index_owner||'.'||index_name||' rebuild partition '||partition_name||';' from all_ind_partitions where status = 'UNUSABLE' order by 1;

select 'alter index '||index_owner||'.'||index_name||' rebuild subpartition '||partition_name||';' from all_ind_subpartitions where status = 'UNUSABLE' order by 1;

Tablespaces including indexes.

SELECT distinct 'alter index '||owner||'.'||segment_name||' rebuild'||
   case when segment_type='INDEX PARTITION' then ' PARTITION '||partition_name
        when segment_type='INDEX SUBPARTITION' then ' SUBPARTITION '||partition_name 
    end ||';' sq, 
MAX(BLOCK_ID) max_block_id
  FROM DBA_EXTENTS
 WHERE FILE_ID in (9,10,11,12,13)
 group by file_id,owner,SEGMENT_NAME, partition_name, SEGMENT_TYPE
 having MAX(BLOCK_ID) > 100000
 order by max_block_id desc
;

And after that datafile resize.

Anoying Toad

Two anoying features of Toad. Use SQLPLUS or SQL Developer instead.

create table pa(p number constraint pa_pk primary key) 
partition by hash(p) 
partitions 2;

select partition_name from user_tab_partitions where table_name = 'PA';

SYS_P61
SYS_P62

Truncating a partition with Toad gives an error.

alter table pa truncate partition SYS_P61;

ORA-14006: invalid partition name

Modifying index visibility with Toad gives an error.

alter index pa_pk invisible;

ORA-14141: ALTER INDEX VISIBLE|INVISIBLE may not be combined with other operations

alter index pa_pk visible;

ORA-14141: ALTER INDEX VISIBLE|INVISIBLE may not be combined with other operations

Just get rid of the semi colon and you are able to use these commands with Toad.

alter index pa_pk visible

A dark side of Easter

It was an Easter a while ago. I had a bottle of Finnish porter beer. It was made by Laitilan breweries. My mother in law got some of it and made it clear that it tasted like mämmi. She put some vanilla sauce in the class and the taste was, well similar like mämmi with vanilla sauce. This year Laitilan breweries has a bottle that has a yellow lable saying Mämmi on it. They have actually made beer out of mämmi. Well if you do not know what mämmi is take alook of something else from Finnish kitchen called Kalakukko. They make that mainly in Kuopio. I had an exiting evening in Hakametsä. Ice hockey arena in Tampere. My favourite ice hockey team Tappara just won Kalpa - a team from Kuopio.

Putting things together that do not actually have nothing to do with each other make sometimes wheels running. Ice hockey and mämmi have nothing in common like vanilla sauce and Kuopio. During last year I have had several occasions when the ideas from spatial advisor have came to the rescue. There have been several cases when I have been dealing with query performance problems with temporal data. Well spatial information is more close to temporal than Kalpa is to Laitila. These ideas have come to the rescue several times when the number count in tables have increased from a million to ten or hundred million. Changing a traditional b-tree index to a function based locator index and a query where predicate to use that has helped the response time to be usable. Joining similar table with greater than or between comparison has also been a troublesome to deal with. Using those function based locator indexes and sdo_join have helped a lot.

Lets have a simple example not from those real life experiences like I did not reveal earlier. Lets copy the base situation from sum over time writing. Notable here is that the locator indexing is available also in free XE version and also SE and EE. The basic idea for function based locator index may be expressed like

DROP TABLE T CASCADE CONSTRAINTS PURGE;

DROP FUNCTION TF;


CREATE TABLE T (FRO DATE, TIL DATE, N NUMBER);

INSERT INTO T VALUES (TO_DATE('09.04.2009','dd.mm.yyyy'), TO_DATE('10.04.2009','dd.mm.yyyy'), 1);

INSERT INTO T VALUES (TO_DATE('10.04.2009','dd.mm.yyyy'), TO_DATE('12.04.2009','dd.mm.yyyy'), 2);

INSERT INTO T VALUES (TO_DATE('11.04.2009','dd.mm.yyyy'), TO_DATE('14.04.2009','dd.mm.yyyy'), 3);

COMMIT;

Query performing not with satisfactory response time.

SELECT * 
  FROM T 
 WHERE TIL >= TO_DATE('11.04.2009','dd.mm.yyyy') 
   AND FRO <= TO_DATE('14.04.2009','dd.mm.yyyy')
;

10.04.2009 12.04.2009 2
11.04.2009 14.04.2009 3

CREATE OR REPLACE FUNCTION TF(FRO DATE, TIL DATE) RETURN SDO_GEOMETRY deterministic as 
BEGIN
RETURN MDSYS.SDO_GEOMETRY(2002,NULL, NULL, SDO_ELEM_INFO_ARRAY (1,2,1),
    SDO_ORDINATE_ARRAY(to_number(to_char(FRO,'j')),0,to_number(to_char(TIL,'j')),0));
END;
/

SELECT TO_CHAR(to_date('19000101','yyyymmdd'),'J'),TO_CHAR(to_date('22000101','yyyymmdd'),'J') FROM dual;

--2415021 2524594

DELETE FROM USER_SDO_GEOM_METADATA WHERE TABLE_NAME = 'T';

INSERT INTO USER_SDO_GEOM_METADATA(TABLE_NAME,COLUMN_NAME,DIMINFO)
  VALUES (
  'T',
  'RAFU.TF(FRO,TIL)',
  SDO_DIM_ARRAY(
    SDO_DIM_ELEMENT('X', 2415021, 2524594, 0.5),
    SDO_DIM_ELEMENT('Y', 0, 0, 0.5)
     )
  )
;

COMMIT;

CREATE INDEX T_LOCATOR_IDX ON T(TF(FRO,TIL))
  INDEXTYPE IS mdsys.spatial_index;

SELECT * 
  FROM T 
 WHERE SDO_FILTER(TF(FRO,TIL),
                  TF(TO_DATE('11.04.2009','dd.mm.yyyy')
                    ,TO_DATE('14.04.2009','dd.mm.yyyy'))
                 ) = 'TRUE'
;

10.04.2009 12.04.2009 2
11.04.2009 14.04.2009 3

Just try with your own temporal data and PlanViz and compare the a-rows inside your query.

An another thing that I sayed in sum over time writing was "I like to see continous pair stored as open ended". It is just one thing to deal with this approach. SQL standard by the way says that the end in time interval should not be included in the interval. But if the end is the same than the start moment then the interval is presenting the one spot in the timeline. So in that case the end is not excluding.

Yet another thing about timelines and my earlier postings. Last year I wrote about some ideas about not overlapping daily. The idea presented in this post might near to the answer missing in that post. But it is not possible to create a domain index as a unique.

By the way Mämmi beer with vanilla sauce got called today white russian by my mother in law. Wonder what might Dude in Big Lebowski say about that.