Natural and Efficient Modeling of Temporal Information with Object-Relational Databases

Natural and Efficient Modeling of Temporal Informationwith Object-Relational DatabasesCarsten Kleiner,Udo W.LipeckUniversity of HannoverInstitute for Computer Science30159HannoverGermanyE-mail:{ck|ul}@informatik.uni-hannover.deAbstractIn this article we present a model for temporal data utilizing the benefits of object-relational database systems(ORDBS).In particular we show how attribute timestamping can be used for natural modeling of object attributes on the conceptual level and how this concept can be simply and efficiently implemented in state-of-the-art ORDBS.The attribute timestamping concept is based on introducing temporal versions of datatypes;it is translated into a logical and then into a physical model making use of the possibility to define user-specific types in ORDBS.Moreover on the physical level we make use of another feature of ORDBS:the definition of user-defined index structures;in particular adapted spatial indexes based on generalized search trees are applied.These index structures are then used to greatly improve performance of important temporal operators on the temporal datatypes.The experiments were carried out for valid time as well as bitemporal attributes and show the effectiveness of this approach.Submission for Track2:Time Management in DatabasesTopics:Temporal Data Models,Temporal Database Design,Indexing of Temporal Data1.Introduction1.1.MotivationMany recent complex applications involve time-varying information.Dependency on time is in most cases not a feature of a real-world object alone but also of its attributes.Temporal data is treated differently from spatial data since it seems to reflect humans perception of the real world more closely.Whereas the spatial position or extent of an object is usually perceived as a regular attribute of an object,time is more fundamental. All information(i.e.all attributes,standard as well as spatial)maybe subject to change over time.Time seems to be the governing dimension behind all information.Despite the fact that this position of time is subjectively introduced the many research publications in temporal modeling suggest that this is common sense.For temporal data in a specific application domain one needs to specify,if a valid-time,transaction-time or bitemporal version of the base information is required.In the spirit of object-oriented modeling using time on the attribute level as opposed to the class level as in many former publications should be preferred. This facilitates one-to-one modeling of real-world objects.This approach essentially means using attribute-timestamping as opposed to the widely used tuple-timestamping.We even propose to go one step further in modeling and suggest that combinations of attribute values and timestamps should be seen as basic value units; thus we say that the datatypes to be used in temporal applications should be temporal versions of each of thebasic datatypes.The reasons for using’datatype-timestamping’are the more adequate modeling capabilities and the ease of transforming these into a logical model if using ORDBS as shown in section2.2.Actually this was exactly the reason for using tuple-timestamping in most previous publications:there RDBS were used and an efficient implementation of attribute-timestamping with these systems was difficult if not impossible, or resulted in tuple-timestamping on the physical level.But since ORDBS have introduced as a central new feature the extensibility by user-defined datatypes and have become available,the stronger and more natural attribute-timestamping approach should be used by making use of temporal versions for attribute datatypes.In the remainder we give a brief overview of related publications.After that the conceptual model for temporal information is presented in brief before its translation into a logical model is described in section 2.2.Thereafter we focus on the physical design by describing storage model,operators,and index support of these operators.In section4results of performance tests on index-assisted queries of valid time as well as bitemporal information are presented before we close with a summary and some ideas for future work in this area.1.2.Related WorkThe theoretical foundation of the conceptual model for attribute-timestamping presented in section2.1is derived from the bitemporal conceptual datamodel in[JSS94]for tuple timestamping.Ideas for the logical and physical implementation of tuple timestamping temporal data models can be found in the references in [JSS94]as well.Reviewing all proposals for temporal query languages is out of the scope of this article;a prominent representative of those is TSQL2([Sno95]).ORDBS were introduced and described in detail by Stonebraker e.g.in[SB99].A commercial implemen-tation of a(basic)ORDBS is e.g.Oracle9i;the datatype extensibility features that are most important in this article are described in[Ora01].Temporal extensions of ORDBS based on tuple timestamping have been presented recently by[BSSJ99]and[YYW00].While the former is on a similar technical level to this article (nevertheless important differences in indexing will be explained in section3.3),the latter is more application oriented since it does not provide query optimization but rather a user-friendly interface.In contrast to those, our approach is rather based on attribute timestamping;such models were e.g.described in[TCG+93],but not considered in recent years since they are difficult to implement efficiently in purely relational database systems1.The connection of user-defined datatypes,attribute-timestamping and ORDBS has to the best of our knowledge never been researched before.Generalized search trees(GiST)were introduced in[HNP95]as extensible indexing structure for databases. An implementation of GiST called libGiST was written by the same group and is made available for down-load at .Their approach isfile-based and thus not directly usable in database systems.A connector tool linking libGiST with the extensible indexing interface of Oracle9i which is used in the tests in section4is described in detail in[Löckm01]and[Kle02].The idea of using spatial index structures for indexing pure temporal data has been used in several papers and is described in [MTT00].In this work we extend that idea to the newly defined temporal datatypes and embed it into an extensible indexing framework.2.Concept2.1.Conceptual ModelA unified approach for many tuple-timestamping data models has been described by[JSS94]under the notion of bitemporal conceptual datamodel(BCDM).Our main idea to extend the BCDM is to extend the set of available standard datatypes D={D1,...,D n}by bitemporal datatypes:D D=D∪{(D×D V T×D T T)|D∈D}for valid and transaction time domains D V T and D T T.D T T includes the special symbol UC for current validity up to a change(until changed).Thus we consider combinations of attribute values and two timestamps instead of original attribute values.These new datatypes can be introduced as user-defined types inextensible database systems like ORDBS.As usual we assume linear,discrete,bounded time dimensions and therefore use a countable number of chronons.This yields an intuitive model with a theoretical foundation which is not optimal for implementation though.A way to implement this data model logically will be described in the next section.2.2.Logical ModelDue to the huge sets of chronons to be stored the conceptual model would very inefficient if implemented directly.Thus and also to make optimal use of the new features of object-relational systems as well as to achieve a cleaner design a different logical model based on the concept should be used.Since this logical model is still system-independent but designed for the object-relational model,all features of ORDBS as described in[SB99]may be used.For simplification thefirst part of this section assumes a valid-time database ter on ideas for an extension to bitemporal information are given.The main idea of providing temporal versions of all datatypes that was introduced in section2.1is very well suited for use in object-relational systems.By introducing appropriate user-defined types it can directly be implemented in the object-relational model.Hence for all standard types to be used in an application(possibly including other user-defined types that need temporal versions such as spatio-temporal types)new datatypes are defined or can be generated by an appropriate preprocessor.New valid time datatypes are identified by the prefix vt_in addition to the regular type name.The required definitions for the derived type vt_integer as temporal version of the standard type integer are presented exemplarily infigure1.vt_integer values are lists of vt_integer_base values which are combinations of original values and temporal elements representing valid time stamps.Temporal information is assumed to be stored as temporal elements,i.e.sets of disjoint intervals;alternative representations of the temporal component such as a function from time into a standard type could also be used.The type vt_integer would remain unchanged when changing the implementation of temporal information.CREATE TYPE chronon(time LONGINT);CREATE TYPE interval(begin chronon,end chronon);CREATE TYPE tempElement SET OF interval;CREATE TYPE vt_integer_base(value INTEGER,time tempElement);CREATE TYPE vt_integer LIST OF vt_integer_base;Figure1:Datatypes required for valid-time attributesIn the definitions infigure1an integer attribute may be used for chronons from C since the set is isomorphic to the natural numbers.In a concrete implementation functions for transforming temporal information into a chronon and vice versa would be required.An interval is given by two chronons marking beginning and end of the interval.For integrity reasons the constraint begin<end has to be asserted which could e.g.be done in transformation functions or by using type triggers.Temporal elements can thus be simply defined as set of intervals in the ORDBS since set types are one of the important object-relational type constructors. Constraints on temporal elements include empty intersection between any two intervals of a single element and also,in order to achieve a normalized representation,that no two intervals of an element touch.Figure2illustrates how to use the datatype vt_integer in a table in order to store numbers with attached valid-time information as required e.g.for salaries of employees.CREATE TABLE employee(id NUMBER PRIMARY KEY,jobDuration tempElement,name vt_string,salary vt_integer);Figure2:Definition of table with temporal attributes(valid time only)Figure2shows the definition of a sample table storing valid time information.Note the very simple table declaration by using complex datatypes as a central feature of ORDBS.The declaration of a table is as simple as in the non-temporal case;one just has to use temporal versions of datatypes instead of the standard types. Such an easy declaration with the same semantics would not be possible if using tuple-timestamping.CREATE TYPE bt_integer_base(value NUMBER,valid tempElement,trans tempElement);CREATE TYPE bt_integerLIST OF bt_integer_base;Figure3:Datatypes required for bitemporal attributesAn extension of this model to bitemporal information seems straightforward in thefirst place:types of the_base kind could be easily extended to contain a second temporal element for the transaction time information belonging to the valid time information stored(cf.figure3).From a modeling point of view this is appropriate,if a different interval type is used for transaction time information to make room for storing the special symbol UC.Since the transaction time dimension has special characteristics in terms of updates and querying requirements a complete layer to make transaction time transparent for the end-user is required.Exact requirements and algorithms remain to be developed and are not the center of this work. Nevertheless from a more technical viewpoint the suggested extension can be used as basis for a physical model of bitemporal data.The above representation was used in the physical design as well as the optimization stage of this work which is described in detail in the next section.3.Physical DesignFor temporal datatypes the logical model developed in the previous section needs to be mapped to an implementable physical model atfirst.The logical model cannot be used directly since commercial ORDBS do not provide all the features of object-relational systems as described in[SB99].After a physical model has been developed the design of that model can be optimized by adding appropriate indexes and in the object-relational case user-defined index structures to optimally support queries on the data modeled.3.1.Storage ModelAn ideal object-relational representation of tables with attribute values of temporal datatypes would require nested tables as shown infigure4in order to map the logical model directly.Currently available ORDBS, however,do not provide all the features of ideal object-relational systems as needed here.Most of them offer user-defined datatypes but nesting of tables to represent complex objects are not yet/not fully supported.Since the developed logical model of vt_integer nests a list-and a set-valued attribute an alternative physical implementation has to be chosen.After some experiments with partial nestings([Bei01],we have chosen to use an unnested representation but to keep pairs of values and intervals(i.e.vt_integer_base in the above example)as a temporal datatype.Since only intervals can be associated with attribute values those have to be stored redundantly: once for every interval( Miller infigure4).By using this physical model temporal joins over the same attribute are efficiently supported since all entries for that attribute are stored in a single physical table and can be clustered.In addition and as shown in the next paragraph user-defined index structures can be easily added on these columns providing for efficient query answering.Finally this model is easily extensible to bitemporal information:the only change is that the nested table contains an additional interval attribute for the transaction time information.This may obviously further increase redundancy.1emp idjobDuration salary 3000{[15,30)}4000[50,75)}Miller{[15,20),[50,70)}Scott {[20,50)}name {[30,45),5000{[45,50)}26000{[10,40),Allen {[10,70)}{[15,75)}{[10,40),[50,70)}[50,70)}Figure 4:Ideal physical model in ORDBS3.2.OperatorsFor temporal datatypes several different important query types have been identified in the literature.In general queries can be differentiated by which dimensions the desired results are restricted to and whether they query for a range in a dimension or a point .For these query types we can use the notational conventions of [ST99],e.g.’range//point/range ’means querying for a range in the standard attribute domain,a certain point in time in the valid time dimension and a range in the transaction time dimension.In addition the extensive experiments presented in the sequel showed that the size of the range in a range query compared to the total range covered by any tuple is an important measure.Therefore whenever experiments are performed on range queries several different selectivities in every dimension will be tested.In particular selecting tuples that have a particular domain attribute value or range as well as selecting tuples overlapping a given point or range in time is possible.Of course,arbitrary combinations of these selections are also possible.In the sequel the most important results for different temporal queries will be presented with the objective of finding the optimal index structure for all possible query types.This is important since in most cases only one index can be built on a single column and it should provide for good query performance of as many query types as possible.3.3.IndexingBy using the extensibility features of ORDBS like Oracle9i as described in [Kle02]or [Ora01]together with the generalized search tree approach ([HNP95]and implementation libGiST )one can optimize the logical implementation of temporal data types by adding appropriate index structures.A similar approach for tuple timestamping has been described for Informix in [BSSJ99].This is not applicable here since we use attribute timestamping;also indexing was not implemented in an extensible framework like GiST in that work but rather for a fixed index structure.Since in temporal datatypes values from different domains or dimensions are combined into a single value the use of index structures from spatial databases seems straightforward.One can view the standard value as one dimension and the valid (as well as the transaction)time information as another dimension 2.Thus in the case where the standard datum is from a one-dimensional domain we obtain two-or three-dimensional do-mains for temporal datatypes which may be indexed by spatial indexes.Since it cannot be guaranteed that the standard datum remains one-dimensional (think of spatio-temporal data for instance)it is recommended not to use the two-(or at best three-)dimensional spatial indexes shipped with current object-relational databases but rather to use user-defined extensible indexes which may be adapted to as many dimensions as required.r e s p o n s e t i m e selectivity in %Figure 5:Preselection of suitable of all possible indexes on vt_integer4.Performance Evaluation4.1.Efficiency for Valid Time DataFor the temporal datatype vt_integer as introduced in sections 2.2and 3.1one obtains the dimen-sions of integer values together with the valid time dimension.After scaling one of the dimensions ap-propriately we can interpret these values as two-dimensional intervals in the integer-time-plane. E.g.the temporal integer value (3000,[10,30))(of type vt_integer_base )is treated like the two-dimensional in-terval [(3000,10),(3000,30)].Thus the following index-optimized physical implementations may be taken into account:1.No index at all2.Built-in B ∗-Tree index on integer valueser-defined B ∗-Tree index on integer values4.Built-in B ∗-Tree index on beginning of valid timeer-defined 2D R ∗-Tree index on temporal intervaler-defined 2D R ∗-Tree index on two-dimensional intervalser-defined 2D RSS-Tree index on two-dimensional intervals (see [KL00]for details)3The tests were conducted with data of three different sizes and structure of contents (two datasets were generated synthetically by methods adapted from [Bei01],the other one was generated using the S PY T IME -benchmark methods from [SZ01])showed that methods 1and 2were inacceptable for almost all querytypes (cf.figure 5).Therefore their results are omitted in the sequel.Method 4requires a complex recoding of queries to be able to use the index.A use of the user-defined operator directly is not possible in this case and an alternative implementation would incur additional call overhead during query execution.Since a complex recoding of queries should not be necessary and moreover since this method does not work for more complex user-defined types this method is also not considered any further.Its results are on average but the overhead for writing different queries does not outweigh this;also this approach would not be scalable to more complex datatypes.0123456705101520r e s p o n s e t i m e selectivity in %2D R*-Tree 2D RSS-Tree B-Tree (salary)R*-Tree (Valid-Time)Figure 6:Response Time for R//R query(equal ranges,small dataset)012345678901234567r e s p o n s e t i m eselectivity in %2D R*-Tree 2D RSS-Tree B-Tree (salary)R*-Tree (Valid-Time)Figure 7:Response Time for R//P query (medium dataset)051015202505101520r e s p o n s e t i m e selectivity in %2D R*-Tree 2D RSS-Tree B-Tree (salary)R*-Tree (Valid-Time)Figure 8:Response Time for *//R query (largedataset)0510152025020406080100r e s p o n s e t i m eselectivity in %2D RSS-Tree (without Overhead)2D RSS-Tree (with Overhead)No indexFigure 9:Response Times for R//R query with and without refine step (medium dataset)We only present the most important results here by discussing typical experimental results.An extensive overview of all experiments can be found in [Kle02].Figure 6shows the results for range//range queries.These queries retrieve all rows of the base table where the integer value is in a particular range and this value’s validity overlaps a given time interval.The 2D RSS-Tree index outperforms all other indexes for all selectivities.All response times grow logarithmically with query selectivity as could be expected by an estimation of theoretical index performance of tree indexes for spatial and B-tree indexes.The idea of using two-dimensional indexes significantly improves query performance,especially for the 2D RSS-Tree by factors of between 2and 6.Therefore these indexes are very well-suited for range//range queries.Figure 7shows the results for range//point queries;these queries only select rows which are valid at a certain point in time.Consequently the index on valid-time performs best in this case.But even though the 2D RSS-Tree contains the unhelpful salary information together with the time information it performs only slightly worse than the R ∗-Tree on valid time.The B-Tree index on salary does not help much for this query type (only the range restriction can be used)and thus its response time grows almost linearly with selectivity.In figure 8the restriction on the salary value was omitted and pure temporal queries were posed.Again the B-Tree index on salary naturally performs worst.A little bit surprising is the good performance of the 2D-RSS-Tree which performs almost exactly as good as the specialized R ∗-Tree on valid time.This underlinesthe overall very good performance of the2D-RSS-Tree.Even though for certain specialized query types other indexes are slightly better it is suggested to use ually users will expect to only create one index for one attribute4and get a robust index that helps with most queries that can be expected.Thus the 2D-RSS-Tree should be the choice since it provides a good performance over all possible query types for the datatype vt_integer.In spatial index structures usually the so-calledfilter and refine query evaluation is used since only ap-proximations of the spatial objects are inserted into the spatial index.For the datatype vt_integer as in this section the refinement step is not required for query processing since the objects stored in the index are intervals and thus not approximated in the index;thus all results returned by thefilter step from the index are also query results already.For the spatial domain or other more complex shapes a refinement step is required. This has a great impact on query performance as displayed infigure9.Thus for approximated geometries in a user-defined index a user-defined selectivity estimation is required to always choose the fastest plan for query execution.As we concluded from other experiments leading to similar results as infigure9depending on the size of the dataset queries retrieving more than30to50%of all rows should be executed without index assistance.Details about user-defined selectivity estimation will be investigated in further research.For non-approximated shapes such as vt_integer user-defined selectivity estimation is not required since index based evaluation is faster than using no index in any case.4.2.Efficiency for Bitemporal DataSimilar to vt_integer the bitemporal datatype bt_integer as introduced in section2.2and3.1can be interpreted as multidimensional data.Different from the preceding paragraph one obtains rectangles in three-dimensional space this time.Therefore more and different indexing options are available.In particular the following options were considered:1.No index at aller-defined B-Tree index on integer valueser-defined2D R∗-Tree index on temporal rectangleser-defined2D R∗-Tree index on valid time intervalser-defined3D R∗-Tree index on three-dimensional rectangleser-defined3D RSS-Tree index on three-dimensional rectanglesOther options that already performed poorly on vt_integer such as a built-in B∗-tree on salary are not considered in this paragraph since no indications for a better performance on the higher dimensional bt_integer are given.There would be the possibility to use two-dimensional spatial indexes for options3 and4but this would require huge programming overhead since the internal structure of the user-defined type would have to be mapped to the ORDBS specific spatial datatype which leads to huge storage overhead for the simple-shaped rectangles used here.Moreover thefirst option is omitted from all diagrams since it was clearly the slowest option(cf.figure10)except for queries with selectivities close to100%where by using proper cost and selectivity estimation the indexes will not be used anyway in a production implementation. Since results for3D R∗-Tree and3D RSS-Tree were almost the same(cf.figure10),only one of those indexes will be shown in the following comparisons.Figure11shows results for range//range/range queries with similar range size in all dimensions.Similar to the preceding section queries of this type are selections on integer and valid time values where the selection is further restricted by an additional clause determining the possible transaction time values.The three-dimensional indexes outperform all other indexes significantly by factors between2and6.Among the other indexes the B-Tree on salary is better than the temporal indexes where the bitemporal index outperforms ther e s p o n s e t i m e selectivity in %Figure 10:Preselection of suitable of all possible indexes on bt_integer0246810121402468101214r e s p o n s e t i m e selectivity in %3D R*-Tree 2D R*-Tree (bitemporal)B-Tree (salary)2D R*-Tree (valid time)Figure 11:Response Time for R//R/R query(equal ranges,medium dataset)0510152000.20.40.60.81r e s p o n s e t i m eselectivity in %3D R*-Tree 2D R*-Tree (bitemporal)B-Tree (salary)2D R*-Tree (valid time)Figure 12:Response Time for R//P/P query (medium dataset)valid time index.All response times grow logarithmically with query selectivity as could be expected by an estimation of theoretical index performance of tree indexes.Different results are obtained for range//point/point queries (cf.figure 12):3D R ∗-Tree and bitemporal 2D R ∗-Tree are by far the most efficient index structures for this query type.The difference is even larger for these queries since the salary range is growing faster than in the previous figure leading to even more inefficient salary-based indexes.If on the other hand queries ask for only a single salary value and ranges in the two temporal dimensions (point//range/range query)the pure temporal indexes perform extremely bad since they do not support the strongest selectivity restriction on salary.In this case the B-Tree index on salary is the most efficient index as expected,but the three-dimensional indexes also perform pretty good (cf.figure 13).They are only slightly worse than the B-Tree but have the advantage of their better performance on other query types.The huge difference of these two indexes to the temporal indexes basically prohibit using the latter if ever queries of this type could be used.Similarly to vt_integer the index entries into the multidimensional index structures are not approxima-tions of real objects but rather the objects themselves (boxes in the salary valid time transaction time space).Thus query results can be directly taken from the index-based filter step and no refinement is necessary.This。