Re: need some help understanding sloq query

Hello Esger,

Esger Abbink schreef:

> i have a little performance problem.
>
> db (simplified):
>
> table current:
> current_update_id, ...
>
> table datasets:
> set_id, update_id, ....
>
> table ents:
> e_id, set_id, ...
>
> table qtys:
> set_id, e_id, ...
>
> indexes are defined on all set_id's & datasets.update_id.
>
> an update consists of several sets which in turn consists of several ents, for
> a specific ent in a set multiple qtys may exist.
> (normal: 1 update - 1 set - few hundred ents - 1 qty per ent)
>
> now i want to do the following: i want to get some specific qty values for the
> ents of the last update only.
>
> so i do a query like:
>
> select some_other_fields from ents e, qtys q where e.set_id = q.set_id and
> e.e_id = q.e_id and e.set_id in (select set_id from datasets
> where update_id in (select cur_update_id from current)) and
> q.other_field = some_const ;

[SNIP]

If I understand correctly, the table current allways contains 1 row, namely the
current_update_id, identifying the update that you want to query on. That would mean you could
start out from this table and Inner Join all your tables... this way your result-set would be
all data for current_update_id:

select some_other_fields
from ents e,    qtys q,    datasets s,    current c
where e.set_id = q.set_id
and   e.e_id = q.e_id
and   s.set_id = e.set_id
and   s.update_id = c.current_update_id
and   q.other_field = some_const;

If you used primary keys and indexes this query should use indexes allways.
Other ideas:

select some_other_fields
from  ents e,        qtys q
where e.set_id = q.set_id
and   e.e_id = q.e_id
and   e.set_id in (select set_id                  from datasets                  where update_id = (select
max(current_update_id)from current))
 

Given your first example and queryplan, I think the problem is in the Mergejoin; both from
qtys and from ents an enormous amount of data is selected that is only narowed down in the
last step, the merge-join.

I think using my first query, the optimizer will be much more efficient. I couldn't reproduce
your query to test though... too much  vagueness.... If this doesn't help then post some
sql-definitions of your database.

Succes,
A. Prins.