Re: Modelling objects with variable number of properties in an RDBMS

"VC" <boston103_at_hotmail.com> wrote in message news:cOWdnUFVM4rt1vTenZ2dnUVZ_smdnZ2d_at_comcast.com...

> The queries were not substantially hard. They did not perform well with
> multitable access due to various reasons. With "unions" unindexed
multiple
> table access was comparable to a single table access, but indexed access
> was substantially slower due to the fact that multiple indexes had to be
> traversed from the root index node down to index leaves (similarly to
> locally partitioned indexes in Oracle). With "joins" they experienced
> performance hit both for indexed access and unindexed table queries (more
> for the latter). A trivial example in Oracle :
>
> -- single table
> create table t select object_id,owner, object_type, created,
last_ddl_time,
> status, timestamp from all_objects;
> -- two tables representing the vertically partitioned table 't' with
> object_id being the primary key
> create table t1 as select object_id,owner, object_type, created from
> all_objects;
> create table t2 as select object_id, last_ddl_time, status, timestamp from
> all_objects;
>
> -- two queries with stats
> SQL> select * from t where object_id between 200 and 400 and
> object_type='TABLE'
> 2 /
>
> Statistics
> ----------------------------------------------------------
> 39 consistent gets
> 0 sorts (memory)
>
> SQL> select * from t1 join t2 on t1.object_id=t2.object_id
> where t1.object_id between 200 and 400 and object_type='TABLE' 2
> Statistics
> ----------------------------------------------------------
> 97 consistent gets
> 2 sorts (memory)
>
>
> So when we try to glue together what'd better be a single table, we get
two
> times more get operations and two sort operations. They experienced
> approximately 5-6 times performance hit for 7-8 way joins (which is quite
> understandable).

It seems this is not an OLTP problem, but an OLAP one. I think a DBMS with support for OLAP would perform better. Received on Thu Nov 03 2005 - 09:57:10 CET