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.